[20201202]完善sosi脚本.txt

来源：这里教程网时间：2026-03-03 16:17:16 作者：

[20201202]完善sosi脚本.txt --//上午花了一点点时间改写了sosi脚本，主要我不喜欢执行时输入参数。 --//另外就是density 显示为0，明显不对。格式不合适修改如下： column DENSITY heading "Density" format 0.09999999 $ cat sosiz.sql set echo off set scan on set lines 277 set pages 9999 set verify off set feedback off set termout off column uservar new_value Table_Owner noprint select user uservar from dual; set termout on --column TABLE_NAME heading "Tables owned by &Table_Owner" format a30 --select table_name from dba_tables where owner=upper('&Table_Owner') order by 1; undefine table_name undefine owner --accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): ' --accept table_name prompt 'Please enter Table Name to show Statistics for: ' set termout off column uservar1 new_value Owner noprint column uservar2 new_value Table_name noprint select '&1' uservar1 , '&2' uservar2 from dual; set termout on set newp 0 column TABLE_NAME heading "Table|Name" format a15 column PARTITION_NAME heading "Partition|Name" format a15 column SUBPARTITION_NAME heading "SubPartition|Name" format a15 column NUM_ROWS heading "Number|of Rows" format 9,999,999,990 column BLOCKS heading "Blocks" format 999,999,990 column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990 column AVG_SPACE heading "Average|Space" format 999,990 column CHAIN_CNT heading "Chain|Count" format 999,990 column AVG_ROW_LEN heading "Average|Row Len" format 999,990 column COLUMN_NAME heading "Column|Name" format a25 column NULLABLE heading Null|able format a4 column NUM_DISTINCT heading "Distinct|Values" format 999,999,990 column NUM_NULLS heading "Number|Nulls" format 9,999,990 column NUM_BUCKETS heading "Number|Buckets" format 990 column DENSITY heading "Density" format 0.09999999 column INDEX_NAME heading "Index|Name" format a25 column UNIQUENESS heading "Unique" format a9 column BLEV heading "B|Tree|Level" format 90 column LEAF_BLOCKS heading "Leaf|Blks" format 99000 column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990 column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990 column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990 column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990 column COLUMN_POSITION heading "Col|Pos" format 990 column col heading "Column|Details" format a24 column COLUMN_LENGTH heading "Col|Len" format 9,990 column GLOBAL_STATS heading "Global|Stats" format a6 column USER_STATS heading "User|Stats" format a6 column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,990 --column to_char(t.last_analyzed,'YYYY-MM-DD') heading "Last_Analyzed|MM-DD-YYYY" format a10 --column to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS') heading "Last_Analyzed|YYYY-MM-DD" format a20 --column LAST_ANALYZED heading "Last_Analyzed|YYYY-MM-DD" format a20 prompt prompt ********************************** prompt Table Level 参数 schema tablename prompt ********************************** prompt select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, t.last_analyzed -- to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS') from dba_tables t where owner = upper(nvl('&&Owner',user)) and table_name = upper('&&Table_name') / prompt select COLUMN_NAME, decode(t.DATA_TYPE, 'NUMBER',t.DATA_TYPE||'('|| decode(t.DATA_PRECISION, null,t.DATA_LENGTH||')', t.DATA_PRECISION||','||t.DATA_SCALE||')'), 'DATE',t.DATA_TYPE, 'LONG',t.DATA_TYPE, 'LONG RAW',t.DATA_TYPE, 'ROWID',t.DATA_TYPE, 'MLSLABEL',t.DATA_TYPE, t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '|| decode(t.nullable, 'N','NOT NULL', 'n','NOT NULL', NULL) col, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, t.last_analyzed, HISTOGRAM -- to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS') from dba_tab_columns t where table_name = upper('&Table_name') and owner = upper(nvl('&Owner',user)) / prompt select INDEX_NAME, UNIQUENESS, BLEVEL BLev, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, t.last_analyzed --to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS') from dba_indexes t where table_name = upper('&Table_name') and table_owner = upper(nvl('&Owner',user)) / prompt break on index_name select i.INDEX_NAME, i.COLUMN_NAME, i.COLUMN_POSITION, decode(t.DATA_TYPE, 'NUMBER',t.DATA_TYPE||'('|| decode(t.DATA_PRECISION, null,t.DATA_LENGTH||')', t.DATA_PRECISION||','||t.DATA_SCALE||')'), 'DATE',t.DATA_TYPE, 'LONG',t.DATA_TYPE, 'LONG RAW',t.DATA_TYPE, 'ROWID',t.DATA_TYPE, 'MLSLABEL',t.DATA_TYPE, t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '|| decode(t.nullable, 'N','NOT NULL', 'n','NOT NULL', NULL) col from dba_ind_columns i, dba_tab_columns t where i.table_name = upper('&Table_name') and owner = upper(nvl('&Owner',user)) and i.table_owner=t.owner and i.table_name = t.table_name and i.column_name = t.column_name order by index_name,column_position / prompt prompt *************** prompt Partition Level prompt *************** select PARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, t.last_analyzed --to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tab_partitions t where table_owner = upper(nvl('&&Owner',user)) and table_name = upper('&&Table_name') order by partition_position / break on partition_name select PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, t.last_analyzed --to_char(t.last_analyzed,'MM-DD-YYYY') from dba_PART_COL_STATISTICS t where table_name = upper('&Table_name') and owner = upper(nvl('&Owner',user)) / break on partition_name select t.INDEX_NAME, t.PARTITION_NAME, t.BLEVEL BLev, t.LEAF_BLOCKS, t.DISTINCT_KEYS, t.NUM_ROWS, t.AVG_LEAF_BLOCKS_PER_KEY, t.AVG_DATA_BLOCKS_PER_KEY, t.CLUSTERING_FACTOR, t.GLOBAL_STATS, t.USER_STATS, t.SAMPLE_SIZE, t.last_analyzed --to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS') from dba_ind_partitions t, dba_indexes i where i.table_name = upper('&Table_name') and i.table_owner = upper(nvl('&Owner',user)) and i.owner = t.index_owner and i.index_name=t.index_name / prompt prompt *************** prompt SubPartition Level prompt *************** select PARTITION_NAME, SUBPARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, t.last_analyzed --to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS') from dba_tab_subpartitions t where table_owner = upper(nvl('&&Owner',user)) and table_name = upper('&&Table_name') order by SUBPARTITION_POSITION / break on partition_name select p.PARTITION_NAME, t.SUBPARTITION_NAME, t.COLUMN_NAME, t.NUM_DISTINCT, t.DENSITY, t.NUM_BUCKETS, t.NUM_NULLS, t.GLOBAL_STATS, t.USER_STATS, t.SAMPLE_SIZE, t.last_analyzed --to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS') from dba_SUBPART_COL_STATISTICS t, dba_tab_subpartitions p where t.table_name = upper('&Table_name') and t.owner = upper(nvl('&Owner',user)) and t.subpartition_name = p.subpartition_name and t.owner = p.table_owner and t.table_name=p.table_name / break on partition_name select t.INDEX_NAME, t.PARTITION_NAME, t.SUBPARTITION_NAME, t.BLEVEL BLev, t.LEAF_BLOCKS, t.DISTINCT_KEYS, t.NUM_ROWS, t.AVG_LEAF_BLOCKS_PER_KEY, t.AVG_DATA_BLOCKS_PER_KEY, t.CLUSTERING_FACTOR, t.GLOBAL_STATS, t.USER_STATS, t.SAMPLE_SIZE, t.last_analyzed --to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS') from dba_ind_subpartitions t, dba_indexes i where i.table_name = upper('&Table_name') and i.table_owner = upper(nvl('&Owner',user)) and i.owner = t.index_owner and i.index_name=t.index_name / prompt clear breaks set echo off --//执行显示如下： SCOTT@book> @ sosiz scott dept ********************************** Table Level 参数 schema tablename ********************************** Table Number Empty Average Chain Average Global User Sample Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size LAST_ANALYZED --------------- -------------- ------------ ------------ -------- -------- -------- ------ ------ -------------- ------------------- DEPT 4 5 0 0 0 20 YES NO 4 2017-01-03 11:22:23 Column Column Distinct Number Number Global User Sample Name Details Values Density Buckets Nulls Stats Stats Size LAST_ANALYZED HISTOGRAM ------------------------- ------------------------ ------------ ----------- ------- ---------- ------ ------ -------------- ------------------- --------------- DEPTNO NUMBER(2,0) NOT NULL 4 0.25000000 1 0 YES YES 2017-01-18 16:05:02 NONE DNAME VARCHAR2(14) 4 0.25000000 1 0 YES YES 2017-01-18 16:05:02 NONE LOC VARCHAR2(13) 4 0.25000000 1 0 YES YES 2017-01-18 16:05:02 NONE B Average Average Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size LAST_ANALYZED ------------------------- --------- ----- ------ -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ------------------- I_DEPT_DNAME UNIQUE 0 001 4 4 1 1 1 YES NO 4 2020-03-25 10:08:55 PK_DEPT UNIQUE 0 001 4 4 1 1 1 YES NO 4 2017-01-03 11:22:23 Index Column Col Column Name Name Pos Details ------------------------- ------------------------- ---- ------------------------ I_DEPT_DNAME DNAME 1 VARCHAR2(14) PK_DEPT DEPTNO 1 NUMBER(2,0) NOT NULL *************** Partition Level *************** *************** SubPartition Level ***************