set null "NULL VALUE"set feedback offset heading offset linesize 132 set pagesize 9999set echo offset verify offset trimspool oncol table_name for a30col column_name for a30col data_type for a15col object_type for a20col constraint_type_desc for a30col Owner format a15spool AllSchemaCheckOracle.outset heading offSELECT '------ System Info: 'FROM dual;set heading onselect to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "DateTime: " from dual/select banner from v$version/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_namefrom v$database/set heading offselect '-----------gather language info-------------' from dual;set heading onSelect * from v$nls_parameters;set heading offselect '-------gather db size------' from dual;set heading onSelect sum(bytes)/1024/1024/1024 db_size_G from dba_segments;set heading offselect '-------gather db size(no index)-----' from dual;set heading onSelect sum(bytes)/1024/1024/1024 no_index_db_size_G from dba_segments where segment_type not like '%IND%'and owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC');set heading offselect '-------gather schemas size------' from dual;set heading onSelect sum(bytes)/1024/1024/1024 owner_size_G,owner from dba_segments where owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')and segment_type not like '%IND%'group by owner order by 1;set heading offselect '-------gather 10 tables of (max lob_segment size)' from dual;set heading onselect t.owner,t.table_name,t.lob_size_G from (select l.owner owner,l.table_name table_name,sum(s.bytes)/1024/1024/1024 lob_size_G from all_lobs l,dba_segments s where l.owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')and l.segment_name=s.segment_namegroup by l.owner,l.table_name order by 3 desc) t where rownum<10 ;set heading offselect '------------info count(partition_name) --------' from dual;set heading onselect table_owner,table_name,count(partition_name)from dba_tab_partitionswhere table_owner not in ('SYS','SYSTEM')group by table_owner,table_nameorder by 3;col segment_name format a40set heading offselect '-------gather max 10 tables------' from dual;set heading onselect t.segment_name,t.table_size_G from (select segment_name,sum(bytes)/1024/1024/1024 table_size_Gfrom dba_segments where owner not in ('SYS','SYSTEM') and segment_type in ('TABLE','TABLE PARTITION') group by segment_nameorder by 2 desc) t where rownum<10;set heading offSELECT '------ Objects stored in Tablespaces with Compression are not supported in the current release of DSG ' FROM dual;set heading onselectTABLESPACE_NAME,DEF_TAB_COMPRESSIONfrom DBA_TABLESPACESwhere DEF_TAB_COMPRESSION <> 'DISABLED';set heading offSELECT '------ Distinct Object Types and their Count By Schema: 'FROM dual;set heading onSELECT owner, object_type, count(*) totalFROM all_objectsWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')GROUP BY object_type, owner/col mview_name format a40col container_name format a40set heading offSELECT '------ info materized view---'FROM dual;set heading onSELECT owner, mview_name, container_nameFROM dba_mviewsWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')/select LOG_OWNER,MASTER mv_name,LOG_TABLE from all_mview_logs;set heading offSELECT '------ Distinct Column Data Types and their Count in the Schema: ' FROM dual;set heading onSELECT data_type, count(*) totalFROM all_tab_columnsWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')GROUP BY data_type/set heading offSELECT '------ Tables with NOLOGGING setting ' FROM dual;SELECT '------ This may cause problems with missing data down stream. ' FROM dual;set heading onselect owner, table_name, ' ', logging from DBA_TABLESwhere logging <> 'YES'and owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')UNIONselect owner, table_name, partitioning_type, DEF_LOGGING "LOGGING" from DBA_part_tableswhere DEF_LOGGING != 'YES' and owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')UNIONselect table_owner, table_name, PARTITION_NAME, logging from DBA_TAB_PARTITIONSwhere logging <> 'YES' and table_owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')UNIONselect table_owner, table_name, PARTITION_NAME, logging from DBA_TAB_SUBPARTITIONSwhere logging <> 'YES' and table_owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC');set heading offSELECT '------ Tables Defined with column_id >250 'FROM dual;set heading onSELECT owner,table_name, max(column_id) column_id_over_200FROM all_tab_columnsWHERE owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')GROUP BY table_name,ownerHAVING max(column_id) > 200/set heading offSELECT '------ Tables With CLOB, BLOB, LONG, NCLOB or LONG RAW Columns in ALL Schemas ' FROM dual;set heading onSELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPEFROM all_tab_columnsWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')AND data_type in ('CLOB', 'BLOB', 'LONG', 'LONG RAW', 'NCLOB')/set heading offSELECT '------ Tables With Columns of UNSUPPORTED Datatypes in ALL Schemas ' FROM dual;set heading onSELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPEFROM all_tab_columnsWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')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 offSELECT '------ Cluster, or Object Tables - ALL UNSUPPORTED - in ALL Schemas 'FROM dual;set heading onSELECT OWNER, TABLE_NAME, CLUSTER_NAME, TABLE_TYPE FROM all_all_tablesWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')AND (cluster_name is NOT NULL or TABLE_TYPE is NOT NULL)/set heading off Select '------ All tables that have compression enabled (which we do not currently support): 'from dual;set heading onselect owner, table_namefrom DBA_TABLESwhere COMPRESSION = 'ENABLED'/SELECT TABLE_OWNER, TABLE_NAME, COMPRESSIONFROM ALL_TAB_PARTITIONSWHERE (COMPRESSION = 'ENABLED')/set heading off Select '------ list temporary tables----------- 'from dual;set heading onselect owner, table_name,temporaryfrom DBA_TABLESwhere TEMPORARY <> 'N'/set heading offSELECT '------ IOT (Fully support for Oracle 10GR2 (with or without overflows) using GGS 10.4 and higher) - in All Schemas: ' FROM dual;set heading onSELECT OWNER, TABLE_NAME, IOT_TYPE, TABLE_TYPE FROM all_all_tablesWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')AND (IOT_TYPE is not null or TABLE_TYPE is NOT NULL)/set heading off--自定义索引SELECT '------ Tables with Domain or Context Indexes' FROM dual;set heading onSELECT OWNER, TABLE_NAME, index_name, index_type FROM dba_indexes WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')and index_type = 'DOMAIN'/set heading offSELECT '------ Types of Constraints on the Tables in ALL Schemas 'FROM dual;set heading onSELECT DECODE(constraint_type,'P','PRIMARY KEY','U','UNIQUE', 'C', 'CHECK', 'R', 'REFERENTIAL') constraint_type_desc, count(*) totalFROM all_constraintsWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')GROUP BY constraint_type/set heading offSELECT '------ Cascading Deletes on the Tables in ALL Schemas ' FROM dual;set heading onSELECT owner, table_name, constraint_nameFROM all_constraintsWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')and constraint_type = 'R' and delete_rule = 'CASCADE'/set heading offSELECT '------ Tables Defined with Triggers in ALL Schema: 'FROM dual;set heading onSELECT table_name, COUNT(*) trigger_countFROM all_triggersWHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')GROUP BY table_name/set heading offSELECT '------ Performance issues - Reverse Key Indexes Defined in ALL Schema: 'FROM dual;col TABLE_OWNER format a10col INDEX_TYPE format a12SET Heading onselect OWNER, INDEX_NAME,INDEX_TYPE, TABLE_OWNER,TABLE_NAME, TABLE_TYPE, UNIQUENESS,CLUSTERING_FACTOR,NUM_ROWS,LAST_ANALYZED,BUFFER_POOLfrom dba_indexeswhere index_type = 'NORMAL/REV'And OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')/set heading offSELECT '------ Performance issues - function Indexes Defined in ALL Schema: 'FROM dual;col TABLE_OWNER format a10col INDEX_TYPE format a12SET Heading onselect OWNER, INDEX_NAME,INDEX_TYPE, TABLE_OWNER,TABLE_NAME, TABLE_TYPE, UNIQUENESS,CLUSTERING_FACTOR,NUM_ROWS,LAST_ANALYZED,BUFFER_POOLfrom dba_indexeswhere index_type = 'FUNCTION-BASED NORMAL'And OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')/SET Heading offSELECT '------ Sequence numbers - Sequences could be a issue for HA configurations 'FROM dual;COLUMN SEQUENCE_OWNER FORMAT a15COLUMN SEQUENCE_NAME FORMAT a30COLUMN INCR FORMAT 999COLUMN CYCLE FORMAT A5COLUMN ORDER FORMAT A5SET Heading onSELECT SEQUENCE_OWNER,SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY INCR,CYCLE_FLAG CYCLE,ORDER_FLAG "ORDER",CACHE_SIZE,LAST_NUMBERFROM DBA_SEQUENCESWHERE SEQUENCE_OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')/set linesize 132col "Avg Log Size" format 999,999,999select sum (BLOCKS) * max(BLOCK_SIZE)/ count(*)"Avg Log Size" From gV$ARCHIVED_LOG;Prompt Table: Frequency of Log Switches by hour and daySELECT 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 offSELECT '------ Summary of log volume processed by day for last 7 days: 'FROM dual;set heading onselect to_char(first_time, 'mm/dd') ArchiveDate,sum(BLOCKS*BLOCK_SIZE/1024/1024) LOGMBfrom v$archived_logwhere first_time > sysdate - 7group by to_char(first_time, 'mm/dd')order by to_char(first_time, 'mm/dd');/set heading offSELECT '------ Summary of log volume processed per hour for last 7 days: ' FROM dual;set heading onselect to_char(first_time, 'MM-DD-YYYY') ArchiveDate, to_char(first_time, 'HH24') ArchiveHour,sum(BLOCKS*BLOCK_SIZE/1024/1024) LogMBfrom v$archived_logwhere first_time > sysdate - 7group 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 offselect '* This output may be found in file: AllSchemsCheckOracle.out' from dual/spool offundefine b0-- exit
