...
Tables with no Primary or Unique Keys
| No Format |
|---|
set linesize 200
set pagesize 20000
column owner format A40
column table_name format A60
set echo off
ttitle left '***** Tables without PK or UK output to nokeys.lst *****' skip 2
btitle off
spool nokeys.txt
select name,dbid, database_role, log_Mode from v$database;
select instance_name from v$instance;
select owner, table_name
from dba_tables
where owner not in (
'XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',
'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS')
and table_name not like 'BIN%'
and (owner, table_name) not in (
select owner, table_name
from dba_constraints
where constraint_type in ( 'P', 'U' )
and owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',
'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS')
and table_name not like 'BIN%')
order by owner, table_name
/
spool off |
...
| No Format |
|---|
-- Run on Source & post imported target db spool del_constraints.txt column owner format A35 set pagesize 4000 set linesize 132 select owner, constraint_name, delete_rule,status FROM dba_constraints where delete_rule = 'CASCADE' and owner not in ( 'XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES', 'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS') order by 1,2; spool off |
For 12C, Check for existence of IDENTITY feature columns
| No Format |
|---|
-- find_identity_col.sql
--
spool identity_cols_found.txt
ttitle left '***** 12c Tables that have an identity feature column *****' skip 2
btitle off
set linesize 132
column owner format A40
column table_name format A40
column column_name format A40
select owner, table_name, column_name
from ALL_TAB_IDENTITY_COLS
where owner not in (
'XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200',
'FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP',
'ORDSYS','DBVREP','APEX_040200','LBACSYS')
and table_name not like 'BIN%'
order by 1,2,3;
spool off |