Note scripts 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:
| 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
spool 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',
'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')
and table_name not in (select view_name from dba_views)
order by owner,table_name;
spool off
|
Unsupported Identity Columns (12c)
| 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
spool 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):
| 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
spool unsupported_LOB.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 :
| No Format |
|---|
set linesize 200 set echo off spool 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
| 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 |
Check for DELETE Cascade constraints
| No Format |
|---|
-- Run on Source & post imported target db
spool delete_cascade_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)
| No Format |
|---|
-- Run on Source as a pre-check REM **************************** REM Compressed table list REM **************************** set linesize 200 spool list_compressed.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
| No Format |
|---|
-- Run on Source as a pre-check
REM ****************************
REM *** Encryption
REM ****************************
spool list_encrypt.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 |
Check for Transparent Data Encryptions (TDE)
| No Format |
|---|
-- Run on Source as a pre-check
REM ************************************
REM *** Check TDE if parameter found to be TRUE in init/pfile
REM ************************************
column timendate new_value spooltime noprint
set echo off
set linesize 132
set pagesize 50000
btitle off
ttitle off
prompt ***********************************************************************
prompt ** Check if TDE Feature is enabled, OK if query returns not found errors
select SYS_CONTEXT('USERENV', 'DB_NAME')||'_'||'chk_tde'||'_'||to_char(sysdate,'mmddyyyy') timeNdate from dual;
spool &spooltime-output.txt
set echo on
SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;
SELECT STATUS FROM V$ENCRYPTION_WALLET;
SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;
SELECT * FROM DBA_ENCRYPTED_COLUMNS
order by owner, table_name, column_name;
set echo off
spool off
|