...
No Format |
---|
Prompt Specify owner for the tables that you want to replicate Prompt Specify table_name(s) for the tables that you want to replicate. % for all. column owner format A45 set linesize 200 set pagesize 5000 ttitle left '***** Unsupported Data Types *****' skip 2 btitle off spoolcolumn unsupported.txt select owner, table_name, column_name, data_type from sys.dba_tab_cols where data_type not in ( 'NUMBER', 'FLOAT', 'VARCHAR2', 'VARCHAR', 'CHAR', 'NCHAR', 'NVARCHAR2', 'LONG', 'LONG RAW', 'DATE', 'RAW', 'BFILE', 'CLOB', 'BLOB', 'NCLOBtimendate new_value spooltime noprint select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual; set echo off spool &spooltime-unsupported_datatypes.txt select owner, table_name, column_name, data_type from sys.dba_tab_cols where data_type not in ( 'NUMBER', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE', ) and data_type not like 'INTERVAL%' and data_type not like 'TIMESTAMP%' 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_'ROWID', 'VARCHAR2', 'VARCHAR', 'CHAR', 'NVARCHAR2', 'NCHAR', 'NCHAR2', 'LONG', 'LONG RAW', 'DATE', 'RAW', 'BFILE', 'CLOB', 'BLOB', 'NCLOB' ) and data_type not like 'INTERVAL%' and data_type not like 'TIMESTAMP%' 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','FLOWS_030000','GSMADMIN_INTERNAL') and table_name not in (select view_name from dba_views) order by owner,table_name; spool off |
Check for XMLTYPEs stored as other than CLOB type.
No Format |
---|
-- Run on Source as a pre-check. REM *********************************** REM XML Columns with storage != CLOB REM *********************************** set linesize 300 column owner format A40 column table_name format A45 column column_name format A30 column storage_type format A15 ttitlecolumn ontimendate ttitlenew_value left '***** XMLTypes, NOT storage type CLOB *****' skip 2 spool xmltype_storage.txt spooltime noprint select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual; set echo off spool &spooltime-xmltype_storage.txt ttitle on ttitle left '***** XMLTypes, NOT storage type CLOB *****' skip 2 select owner,table_name,column_name,storage_type from DBA_xml_tab_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','DMSYS') and storage_type != 'CLOB' order by owner,table_name,column_name; spool off |
...
No Format |
---|
-- find_identity_col.sql -- ttitle left '***** 12c Tables that have an identity feature column *****' skip 2 btitle off set linesize 150 column owner format A40 column table_name format A40 column column_name format A40 column timendate spool identity.txt new_value spooltime noprint select ownerSYS_CONTEXT('USERENV', table_'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual; set echo off spool &spooltime-identity.txt 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; |
...
No Format |
---|
Prompt Specify owner for the tables that you want to replicate Prompt Specify table_name(s) for the tables that you want to replicate. % for all. column owner format A45 set linesize 200 set pagesize 5000 column spooltimendate unsupportednew_LOB.txtvalue selectspooltime owner, table_name, column_name from dba_lobs where owner not in ( 'XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES', 'EXFSYS','APPQOSSYS','noprint select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual; set echo off spool &spooltime-LOB_columns.txt select owner, table_name, column_name from dba_lobs 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 encrypt not in ('NO','NONE') or compression not in ('NO','NONE') or deduplication not in ('NO','NONE') order by owner); spool off |
...
No Format |
---|
set linesize 200 set echo off column spooltimendate redonew_estimate.txtvalue spooltime noprint select name,dbid, database_role, log_Mode from v$database; select instance_name from SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual; set echo off spool &spooltime-redo_estimate.txt select name,dbid, database_role, log_Mode from v$database; select instance_name from v$instance; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD'; ttitle left '***** Number of redo files for previous 7 days by hour *****' skip 2 btitle off SELECT to_date(first_time) DAY, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23" from sys.v$log_history where to_date(first_time) > sysdate -8 GROUP by to_char(first_time,'YYYY-MON-DD'), to_date(first_time) order by to_date(first_time) / rem Size of logs to help do the math in bytes. ttitle left '***** Size of redo logs. Multiple number of logs per hour by Size of Redo to get volume in Meg/Hr *****' skip 2 btitle off select group#,members,archived, status, trunc((bytes)/1000000) Size_of_Redo_Megbytes from sys.v$log / spool off exit |
...
No Format |
---|
set linesize 200 set pagesize 20000 column owner format A40 column table_name format A60 column settimendate echonew_value offspooltime noprint ttitle left '***select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual; 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 PK or UK output to nokeys.lst *****' skip 2 btitle off spool &spooltime-noUniqKeys.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%' and (owner, table_name) not in ( selectorder by 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 |
Check for DELETE Cascade constraints
No Format |
---|
-- Run on Source & post imported target db spool spool off |
Check for DELETE Cascade constraints
No Format |
---|
-- Run on Source & post imported target db
column timendate new_value spooltime noprint
select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual;
set echo off
spool &spooltime-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 |
...
No Format |
---|
-- Run on Source as a pre-check REM **************************** REM Compressed table list REM *********************************** set linesize 200 spool list_compressed.txt set linesize 200 column timendate new_value spooltime noprint select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual; set echo off spool &spooltime-compressed_objs.txt ttitle left '***** Table Compression Assessment *****' skip 2 btitle off SELECT owner, table_name, compression 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 compression = 'ENABLED' order by 1,2; ttitle left '***** Partitioned Table Compression Assessment *****' skip 2 btitle off SELECT table_owner,table_name, partition_name, compression from dba_tab_partitions where table_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 compression = 'ENABLED' order by 1,2,3; spool off |
...
No Format |
---|
-- Run on Source as a pre-checka pre-check REM **************************** REM *** Encryption REM ****************************** REM *** Encryption REM **************************** spool list_encrypt******** column timendate new_value spooltime noprint select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'unsupported_types'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual; set echo off spool &spooltime-encrytion_chk.txt ttitle left '***** Encryption Assessment *****' skip 2 btitle off select * from dict where lower(table_name) like '%encrypted%'; select owner, table_name, COLUMN_NAME, ENCRYPTION_ALG from DBA_ENCRYPTED_COLUMNS 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') order by 1,2,3; spool off |
...