-- This script spools the output to a file named ggp.out 
-- Oracle Support document ID 1296168.1
-- Example of running the script:
-- sqlplus <userid>/<pw> @ggp.sql

-- 9-17-07 SGEORGE - Added sequences 
--            - added reverse key index check
--  3-24-08 SGEORGE - Added average archive log size
--              - Added number of archive log switches by hour and day
-- 3-35-08  SGEORGE - Added query for tables without PK/UK
--                  - and columns > 256k
-- 3-27-08 SGEORGE - added DB info and Supp log info 
-- 4-1-08 SGEORGE - Added sum of log files for 7 days
-- 5-9-08 SGEORGE - Corrected error on 256K on PK UK query
--                - removed dup PK-UK query
-- 9-14-10 TMATTEI - Update query for Tables with no PK or UI and rowsize > 256K to 1M
-- 9-14-10 TMATTEI - Update query for Tables with rowsize > 512K to 2M
-- 9-14-10 TMATTEI - Updated query for Tables With Columns of UNSUPPORTED Datatypes to include ORDDICOM data type
-- 10-14-10 SGEORGE - Updated query for tables with compression enabled.
-- 02-17-11 SGEORGE - Added query to check for Compressed Tablespaces.
-- 2-21-11 TMATTEI - Updated query to list Domain Indexes
-- 9-6-11 SGEORGE - updated queries and removeed "COMPRESS_FOR" from compression queries.  Compatable with Oracle 10g and 11g
-- 9-7-11 SGEORGE - Added check for columns with Default values.  0 default values may cause replication problems
-- 9-27-11 SGEORGE - Removed check for default not null columns - issue has been resolved in latest release
-- 11-15-11 SGEORGE - Added check for deferred constraints.  Deferred constaints may cause ADD TRANDATA to select the 
--                - wrong column for logging. Use KEYCOLS for tables with deferred constaints.
-- 12-7-11 SGEORGE - Added check for tables with NOLOGGING. Tables with the NOLOGGING option may cause problems  
--                 - downstream at the target.
--                 - fixed format on archive log summary to HH24
--           - fixed various format issues
-- 05-06-14 SGEORGE - Updated comments added new view for IE.
-- 11-03-14 SGEORGE -  Limitation - this script will not pick up user defined datatypes (UDTs) based on varrays and LOB's.
--                     Please use the GoldenGate documentation for a complete listing of supported data types.
-- 04-29-15 SGEORGE - Added comment about deferred constraints
-- 06-11-15 SGEORGE - Fixed query based on feedback on NOLOGGING query
--
set null "NULL VALUE"
set feedback off
set heading off
set linesize 132 
set pagesize 9999
set echo off
set verify off

col table_name for a30
col column_name for a30
col data_type for a15
col object_type for a20
col constraint_type_desc for a30

--spool ggp.&&schema_name.out
 
ACCEPT schema_name char prompt 'Enter the Schema Name > '
variable b0 varchar2(50)
exec :b0 := upper('&schema_name');

spool ggp.&&schema_name..out
prompt - Most of the output relates to the Classic Capture configuration.  
prompt - If you are using Integrated Capture PLEASE see the new DBA view - DBA_GOLDENGATE_SUPPORT_MODE (listed below).
Prompt - IF using IE also please see health Check MOS Document ID 1448324.1
Prompt - Please be sure to read the Installing and Configuring Oracle GoldenGate for Oracle Database documentation for 
Prompt - a complete list of supported and non-supported data types.  

SET Heading off
SELECT '------ System Info for: '||:b0 
FROM dual;
set heading on
select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "DateTime: " from dual
/
select banner from v$version
/
SET Heading off
SELECT '------ Database Level Supplemental Log Check - 9i and 10g: ' 
FROM dual;
SET Heading on
SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN, SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI 
  FROM V$DATABASE
/  

select name, log_mode "LogMode", 
supplemental_log_data_min "SupLog: Min", supplemental_log_data_pk "PK",
supplemental_log_data_ui "UI", force_logging "Forced",
supplemental_log_data_fk "FK", supplemental_log_data_all "All",
to_char(created, 'MM-DD-YYYY HH24:MI:SS') "Created"
from v$database
/

select  
platform_name
from v$database
/
set heading off
SELECT '------ Objects stored in Tablespaces with Compression are not supported in the current release of OGG ' 
FROM dual;
SET Heading on
select
    TABLESPACE_NAME,
    DEF_TAB_COMPRESSION
from DBA_TABLESPACES
where 
DEF_TAB_COMPRESSION <> 'DISABLED';

set heading off
SELECT '------ Distinct Object Types and their Count in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT object_type, count(*) total
FROM all_objects
WHERE OWNER = :b0
GROUP BY object_type
/


SET Heading off
SELECT '------ Distinct Column Data Types and their Count in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT data_type, count(*) total
FROM all_tab_columns
WHERE OWNER = :b0 
GROUP BY data_type
/


SET Heading off
SELECT '------ Tables With No Primary Key or Unique Index in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT distinct(table_name)
  FROM all_tables
 WHERE owner = :b0 
MINUS
(SELECT obj1.name
  FROM SYS.user$ user1,
       SYS.user$ user2,
       SYS.cdef$ cdef,
       SYS.con$ con1,
       SYS.con$ con2,
       SYS.obj$ obj1,
       SYS.obj$ obj2
 WHERE user1.name = :b0 
   AND cdef.type# = 2 
   AND con2.owner# = user2.user#(+)
   AND cdef.robj# = obj2.obj#(+)
   AND cdef.rcon# = con2.con#(+)
   AND obj1.owner# = user1.user#
   AND cdef.con# = con1.con#
   AND cdef.obj# = obj1.obj#
UNION
SELECT idx.table_name
  FROM all_indexes idx
 WHERE idx.owner = :b0 
   AND idx.uniqueness = 'UNIQUE')
/

SET Heading off
SELECT '------ Tables with no PK or UI and rowsize > 1M in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT distinct(table_name)
  FROM all_tab_columns
 WHERE owner = :b0
 group by table_name
 HAVING sum(data_length) > 1000000
MINUS
(SELECT obj1.name
  FROM SYS.user$ user1,
       SYS.user$ user2,
       SYS.cdef$ cdef,
       SYS.con$ con1,
       SYS.con$ con2,
       SYS.obj$ obj1,
       SYS.obj$ obj2
 WHERE user1.name = :b0 
   AND cdef.type# = 2 
   AND con2.owner# = user2.user#(+)
   AND cdef.robj# = obj2.obj#(+)
   AND cdef.rcon# = con2.con#(+)
   AND obj1.owner# = user1.user#
   AND cdef.con# = con1.con#
   AND cdef.obj# = obj1.obj#
UNION
SELECT idx.table_name
  FROM all_indexes idx
 WHERE idx.owner = :b0 
   AND idx.uniqueness = 'UNIQUE')
/

set heading off
SELECT '------ Tables with NOLOGGING setting ' FROM dual;
SELECT '------ This may cause problems with missing data down stream. Schema: ' ||:b0 FROM dual;
set heading on
select owner, table_name, ' ' "PARTITION_NAME", ' ' "PARTITIONING_TYPE", logging from DBA_TABLES
where logging <> 'YES' and OWNER = :b0
union
select owner, table_name, ' ', partitioning_type, DEF_LOGGING "LOGGING" from DBA_part_tables
where DEF_LOGGING != 'YES' and owner = :b0
union
select table_owner, table_name, PARTITION_NAME, ' ', logging from DBA_TAB_PARTITIONS
where logging <> 'YES' and table_owner = :b0
union
select table_owner, table_name, PARTITION_NAME, ' ', logging from DBA_TAB_SUBPARTITIONS
where logging <> 'YES' and table_owner = :b0
;


set heading off
SELECT '------ Tables with Deferred constraints.Deferred constraints may cause TRANDATA to chose an incorrect Key ' FROM dual;
SELECT '------ Any table with a deferred constraint will need more research to avoid any issues' FROM dual;
SELECT '------ Tables with Deferred PK constraints should be added using KEYCOLS in the trandata statement. Schema: ' ||:b0 FROM dual;
set heading on
SELECT c.TABLE_NAME,
  c.CONSTRAINT_NAME,
  c.CONSTRAINT_TYPE,
  c.DEFERRABLE,
  c.DEFERRED,
  c.VALIDATED,
  c.STATUS,
  i.INDEX_TYPE,
  c.INDEX_NAME,
  c.INDEX_OWNER
FROM dba_constraints c,
  dba_indexes i
WHERE
    i.TABLE_NAME   = c.TABLE_NAME
AND i.OWNER        = c.OWNER
AND c.owner = :b0
AND  c.DEFERRED = 'DEFERRED';

set heading off
SELECT '------ Tables Defined with Rowsize > 2M in the Schema: '||:b0
FROM dual;
SET Heading on
SELECT table_name, sum(data_length) row_length_over_2M
FROM all_tab_columns
WHERE OWNER = :b0 
GROUP BY table_name
HAVING sum(data_length) > 2000000
/

SET Heading off
SELECT '------ Tables that will Fail Add Trandata (Only an issue for Oracle versions below Oracle 10G) in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT distinct(table_name)
FROM dba_tab_columns
WHERE owner = :b0 
AND column_id > 32
AND table_name in
(SELECT distinct(table_name)
  FROM all_tables
 WHERE owner = :b0 
MINUS
(SELECT obj1.name
  FROM SYS.user$ user1,
       SYS.user$ user2,
       SYS.cdef$ cdef,
       SYS.con$ con1,
       SYS.con$ con2,
       SYS.obj$ obj1,
       SYS.obj$ obj2
 WHERE user1.name = :b0 
   AND cdef.type# = 2
   AND con2.owner# = user2.user#(+)
   AND cdef.robj# = obj2.obj#(+)
   AND cdef.rcon# = con2.con#(+)
   AND obj1.owner# = user1.user#
   AND cdef.con# = con1.con#
   AND cdef.obj# = obj1.obj#
UNION
SELECT idx.table_name
  FROM all_indexes idx
 WHERE idx.owner = :b0 
   AND idx.uniqueness = 'UNIQUE'))
/

SET Heading off
SELECT '------ Tables With CLOB, BLOB, LONG, NCLOB or LONG RAW Columns in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM all_tab_columns
WHERE OWNER = :b0 
AND data_type in ('CLOB', 'BLOB', 'LONG', 'LONG RAW', 'NCLOB')
/

SET Heading off
SELECT '------ Tables With Columns of UNSUPPORTED Datatypes in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM all_tab_columns
WHERE OWNER = :b0 
AND (data_type in ('ORDDICOM', 'BFILE', 'TIMEZONE_REGION', 'BINARY_INTEGER', 'PLS_INTEGER', 'UROWID', 'URITYPE', 'MLSLABEL', 'TIMEZONE_ABBR', 'ANYDATA', 'ANYDATASET', 'ANYTYPE')
or data_type like 'INTERVAL%')
/

SET Heading off
SELECT '----- Tables with Compressed data is not supported - in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT TABLE_NAME, COMPRESSION
FROM all_all_tables
WHERE OWNER = :b0
AND (COMPRESSION = 'ENABLED');
 
SELECT TABLE_NAME, COMPRESSION
FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER = :b0
AND (COMPRESSION = 'ENABLED');

SET Heading off
SELECT '----- Cluster (DML and DDL supported for 9i or later) or Object Tables (DML supported for 10G or later, no DDL) - in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT TABLE_NAME, CLUSTER_NAME, TABLE_TYPE 
FROM all_all_tables
WHERE OWNER = :b0 
AND (cluster_name is NOT NULL or TABLE_TYPE is NOT NULL)
/

SET Heading off
SELECT '------ IOT (Fully support for Oracle 10GR2 (with or without overflows) using GGS 10.4 and higher) - in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT TABLE_NAME, IOT_TYPE, TABLE_TYPE 
FROM all_all_tables
WHERE OWNER = :b0 
AND (IOT_TYPE is not null or TABLE_TYPE is NOT NULL)
/

SET Heading off
SELECT '------ Tables with Domain or Context Indexes - in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT TABLE_NAME, index_name, index_type 
FROM dba_indexes WHERE OWNER = :b0
and index_type = 'DOMAIN'
/

SET Heading off
SELECT '------ Types of Constraints on the Tables in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT DECODE(constraint_type,'P','PRIMARY KEY','U','UNIQUE', 'C', 'CHECK', 'R', 
'REFERENTIAL') constraint_type_desc, count(*) total
FROM all_constraints
WHERE OWNER = :b0 
GROUP BY constraint_type
/

SET Heading off
SELECT '------ Cascading Deletes on the Tables in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT table_name, constraint_name
FROM all_constraints
WHERE OWNER = :b0 and constraint_type = 'R' and delete_rule = 'CASCADE'
/

SET Heading off
SELECT '------ Tables Defined with Triggers in the Schema: '||:b0 
FROM dual;
SET Heading on
SELECT table_name, COUNT(*) trigger_count
FROM all_triggers
WHERE OWNER = :b0 
GROUP BY table_name
/
SET Heading off
SELECT '------ Performance issues - Reverse Key Indexes Defined in the Schema: '||:b0
FROM dual;
col Owner format a10
col TABLE_OWNER format a10
col INDEX_TYPE format a12
SET Heading on

select 
    OWNER,      
    INDEX_NAME,
    INDEX_TYPE, 
    TABLE_OWNER,
    TABLE_NAME, 
    TABLE_TYPE, 
    UNIQUENESS,
    CLUSTERING_FACTOR,
    NUM_ROWS,
    LAST_ANALYZED,
    BUFFER_POOL
from dba_indexes
where index_type = 'NORMAL/REV'
      And OWNER = :b0
/

SET Heading off
PROMPT 'For Integrated Extract please see MOS doc -1448324.1 '
SELECT '------ Integrated Extract unsupported objects in '||:b0
FROM dual;
Set heading on 
column object_name format a35
select object_name, support_mode from DBA_GOLDENGATE_SUPPORT_MODE  WHERE OWNER = :b0 and support_mode = 'NONE';


SET Heading off
SELECT '------ Sequence numbers: '||:b0
FROM dual;

COLUMN SEQUENCE_OWNER FORMAT a15
COLUMN SEQUENCE_NAME FORMAT a30
COLUMN INCR FORMAT 999
COLUMN CYCLE FORMAT A5
COLUMN ORDER FORMAT A5
SET Heading on
SELECT SEQUENCE_OWNER,
      SEQUENCE_NAME,
      MIN_VALUE,
      MAX_VALUE,
      INCREMENT_BY INCR,
      CYCLE_FLAG CYCLE,
      ORDER_FLAG "ORDER",
      CACHE_SIZE,
      LAST_NUMBER
  FROM DBA_SEQUENCES
 WHERE SEQUENCE_OWNER=UPPER(:b0)
 /
set linesize 132

col "Avg Log Size" format 999,999,999
select sum (BLOCKS) * max(BLOCK_SIZE)/ count(*)"Avg Log Size" From gV$ARCHIVED_LOG;

Prompt Table: Frequency of Log Switches by hour and day
SELECT SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) DAY, 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'00',1,0)),'99') "00", 
     TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'01',1,0)),'99') "01", 
      TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'02',1,0)),'99') "02", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'03',1,0)),'99') "03", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'04',1,0)),'99') "04", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'05',1,0)),'99') "05", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'06',1,0)),'99') "06", 
       TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'07',1,0)),'99') "07", 
     TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'08',1,0)),'99') "08", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'09',1,0)),'99') "09", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'10',1,0)),'99') "10", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'11',1,0)),'99') "11", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'12',1,0)),'99') "12", 
       TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'13',1,0)),'99') "13", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'14',1,0)),'99') "14", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'15',1,0)),'99') "15", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'16',1,0)),'99') "16", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'17',1,0)),'99') "17", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'18',1,0)),'99') "18", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'19',1,0)),'99') "19", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'20',1,0)),'99') "20", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'21',1,0)),'99') "21", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'22',1,0)),'99') "22", 
        TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'23',1,0)),'99') "23" 
FROM      V$LOG_HISTORY 
GROUP  BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) 
order by 1;
SET Heading off
SELECT '------ Summary of log volume processed by day for last 7 days: '
FROM dual;
SET Heading on
select to_char(first_time, 'mm/dd') ArchiveDate,
       sum(BLOCKS*BLOCK_SIZE/1024/1024) LOGMB
from v$archived_log
where first_time > sysdate - 7
group by to_char(first_time, 'mm/dd')
order by to_char(first_time, 'mm/dd');
/
SET Heading off
SELECT '------ Summary of log volume processed per hour for last 7 days: ' 
FROM dual;
SET Heading on
select to_char(first_time, 'MM-DD-YYYY') ArchiveDate, 
       to_char(first_time, 'HH24') ArchiveHour,
       sum(BLOCKS*BLOCK_SIZE/1024/1024) LogMB
from v$archived_log
where first_time > sysdate - 7
group by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24')
order by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24');
/     

set heading off
select '* This output may be found in file: ggp.&&schema_name..out' from dual
/

spool off
undefine b0
-- exit
