We're here to help!

Data Discovery - Pre-flight SQL script

Follow
  • Note individual scripts & a tar file to download can also be found in attachment @ http://support.dbvisit.com/hc/en-us/articles/220551807-Data-Discovery-Scripts-Replicate

    The following script can help determine if the tables in your schema contain unsupported datatypes:

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

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

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

Unsupported Identity Columns (12c)

-- 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 new_value spooltime noprint
select SYS_CONTEXT('USERENV', '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;
 

Unsupported LOB options (11g SecureFiles):

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

Redo Volume Trending Report :

set linesize 200
set echo off
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-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

Tables with no Primary or Unique Keys

set linesize 200
set pagesize 20000
column owner format A40
column table_name format A60
column timendate new_value spooltime noprint
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 &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%')
order by owner, table_name
/

spool off


Check for DELETE Cascade constraints

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

Check for Compressed tables (partitioned & non-partitioned)

-- Run on Source as a pre-check
REM ****************************
REM Compressed table list
REM ****************************
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

Check for Encryption & encrypted columns

-- Run on Source as a pre-check
REM ****************************
REM *** Encryption
REM ****************************
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
Have more questions? Submit a request

Comments