-- Example of running the script:
-- sqlplus <userid>/<pw> @schemaCheckOracle.sql
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 schemaCheckOracle.&&schema_name.out
ACCEPT schema_name char prompt 'Enter the Schema Name > '
variable b0 varchar2(50)
exec :b0 := upper('&schema_name');
spool schemaCheckOracle.&&schema_name..out
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 '------ Tables with Deferred 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
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: schemaCheckOracle.&&schema_name..out' from dual
/
spool off
undefine b0
-- exit
