...
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 |
...