查詢系統關鍵資訊

set linesize 300
set pagesize 0
set trims on
set term off
spool off


spool dba_tables.log
select owner||','||table_name||','||PCT_FREE||','||INITIAL_EXTENT||','||NUM_ROWS||','||BLOCKS||','||AVG_ROW_LEN||','||trim(DEGREE)||','||to_char(LAST_ANALYZED,'YYYY-MM-DD')||','||PARTITIONED||','||BUFFER_POOL||','||TEMPORARY||','||USER_STATS||','||STATUS
from dba_tables
where owner  in('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_indexes.log
select owner||','||index_name||','||table_owner||','||table_name||','||PCT_FREE||','||INITIAL_EXTENT||','||blevel||','||NUM_ROWS||','||LEAF_BLOCKS||','||DISTINCT_KEYS||','||CLUSTERING_FACTOR||','||trim(DEGREE)||','||to_char(LAST_ANALYZED,'YYYY-MM-DD')||','||PARTITIONED||','||BUFFER_POOL||','||USER_STATS||','||STATUS
from dba_indexes
where table_owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_ind_columns.log
select index_owner||','||index_name||','||table_name||','||COLUMN_NAME||','||COLUMN_POSITION||','||DESCEND
from dba_ind_columns
where table_owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_tab_partitions.log
select table_owner||','||table_name||','||COMPOSITE||','||PARTITION_NAME||','||PARTITION_POSITION||','||PCT_FREE||','||INITIAL_EXTENT||','||NUM_ROWS||','||BLOCKS||','||AVG_ROW_LEN||','||to_char(LAST_ANALYZED,'YYYY-MM-DD')||','||BUFFER_POOL||','||USER_STATS
from dba_tab_partitions
where table_owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_ind_partitions.log
select index_owner||','||index_name||','||COMPOSITE||','||PARTITION_NAME||','||PARTITION_POSITION||','||PCT_FREE||','||INITIAL_EXTENT||','||blevel||','||NUM_ROWS||','||LEAF_BLOCKS||','||DISTINCT_KEYS||','||CLUSTERING_FACTOR||','||to_char(LAST_ANALYZED,'YYYY-MM-DD')||','||BUFFER_POOL||','||USER_STATS||','||STATUS
from dba_ind_partitions
where index_owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_part_tables.log
select owner||','||table_name||','||PARTITIONING_TYPE||','||SUBPARTITIONING_TYPE||','||PARTITIONING_KEY_COUNT||','||SUBPARTITIONING_KEY_COUNT||','||PARTITION_COUNT
from dba_part_tables
where owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_part_indexes.log
select owner||','||index_name||','||table_name||','||PARTITIONING_TYPE||','||SUBPARTITIONING_TYPE||','||PARTITIONING_KEY_COUNT||','||SUBPARTITIONING_KEY_COUNT||','||PARTITION_COUNT||','||LOCALITY||','||ALIGNMENT
from dba_part_indexes
where owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_part_key_columns.log
select owner||','||name||','||object_type||','||COLUMN_NAME||','||COLUMN_POSITION
from dba_part_key_columns
where owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_subpart_key_columns.log
select owner||','||name||','||object_type||','||COLUMN_NAME||','||COLUMN_POSITION
from dba_subpart_key_columns
where owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_tab_subpartitions.log
select table_owner||','||table_name||','||PARTITION_NAME||','||SUBPARTITION_NAME||','||SUBPARTITION_POSITION||','||PCT_FREE||','||INITIAL_EXTENT||','||NUM_ROWS||','||BLOCKS||','||AVG_ROW_LEN||','||to_char(LAST_ANALYZED,'YYYY-MM-DD')||','||BUFFER_POOL||','||USER_STATS
from dba_tab_subpartitions
where table_owner in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_ind_subpartitions.log
select index_owner||','||index_name||','||PARTITION_NAME||','||SUBPARTITION_NAME||','||SUBPARTITION_POSITION||','||PCT_FREE||','||INITIAL_EXTENT||','||blevel||','||NUM_ROWS||','||LEAF_BLOCKS||','||DISTINCT_KEYS||','||CLUSTERING_FACTOR||','||to_char(LAST_ANALYZED,'YYYY-MM-DD')||','||BUFFER_POOL||','||USER_STATS||','||STATUS
from dba_ind_subpartitions
where index_owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off


spool dba_segments.log
select owner||','||segment_name||','||PARTITION_NAME||','||SEGMENT_TYPE||','||BYTES||','||BLOCKS||','||INITIAL_EXTENT||','||NEXT_EXTENT||','||PCT_INCREASE||','||TABLESPACE_NAME||','||BUFFER_POOL
from dba_segments
where owner  in ('SYSTEM','SYS','MGMT_VIEW','SYSMAN','DBSNMP','OLAPSYS','SI_INFORMTN_SCHEMA','ORDPLUGINS','XDB','WMSYS','DMSYS','OUTLN','EXFSYS','ANONYMOUS','CTXSYS','ORDSYS','MDSYS','TSMSYS','ORACLE_OCM','DIP','MDDATA','SCOTT');
spool off
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27500440/viewspace-1130208/，如需轉載，請註明出處，否則將追究法律責任。