Oracle 특정 테이블 통계정보 확인 스크립트

 

set echo off set lines 132 set pages 5000 set scan on set verify off set feedback off set head on   prompt accept owner prompt 'Table owner: ' accept table_name prompt 'Table name: '   col owner for a15 col synonym_name for a15 col table_owner for a15 col db_link for a15 col table_name for a30 col tablespace_name for a15 col mb for 999,990 col num_rows for 999,999,990 col blocks for 999,999,990 col empty_blocks for 999,990 col avg_space for 9,990 col chain_cnt for 990 col avg_row_len for 999,990 col avg_col_len for 990 col column_name for a30 col nullable for a4 col num_distinct for 999,999,990 col density for 9.999999 col index_name for a30 col uniq for a4 col blev for 90 col leaf_blocks for 9,999,990 col distinct_keys for 999,999,990 col key_lblocks for 99,990 col key_dblocks for 9,999,990 col clustering_factor for 999,999,990 col column_position format 999 col col for a25 col column_length for 990 col num_buckets for 990 col num_nulls for 999,999,990     select  t.table_name,         t.tablespace_name,         t.num_rows,         t.blocks,         s.bytes/1048576 mb,         t.empty_blocks,         t.chain_cnt,         t.avg_row_len,         t.last_analyzed from    dba_tables t, dba_segments s where   t.owner=upper(NVL('&&owner',USER)) and t.table_name=upper('&&table_name')   and   s.owner=t.owner and s.segment_name=t.table_name   and   s.segment_type <> 'TABLE PARTITION' union all select  p.partition_name||' (P)' table_name,         p.tablespace_name,         p.num_rows,         p.blocks,         s.bytes/1048576 mb,         p.empty_blocks,         p.chain_cnt,         p.avg_row_len,         p.last_analyzed from    dba_segments s, dba_tab_partitions p where   s.segment_type='TABLE PARTITION'   and   p.table_owner=upper(NVL('&&owner',USER)) and p.table_name=upper('&&table_name')   and   s.owner=p.table_owner and s.segment_name=p.table_name and s.partition_name=p.partition_name order by table_name /   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||')'),                 'DATA',t.data_type,'LONG',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,num_nulls,density,num_buckets,avg_col_len,last_analyzed from    dba_tab_columns t where   table_name = upper('&table_name') and owner=upper(nvl('&owner',user)) order   by column_name;     select  i.index_name,i.tablespace_name,substr(i.uniqueness,1,4) uniq,i.blevel blev,i.leaf_blocks,i.distinct_keys,         i.clustering_factor,i.last_analyzed from    dba_indexes i, dba_segments s where   i.table_name = upper('&table_name') and i.owner=upper(nvl('&owner',user))   and   i.owner=s.owner and i.index_name=s.segment_name   and   s.segment_type <> 'INDEX PARTITION' union all select  p.partition_name||' (P)' index_name,p.tablespace_name,substr(i.uniqueness,1,4) uniq,p.blevel blev,p.leaf_blocks,p.distinct_keys,         p.clustering_factor,p.last_analyzed from    dba_ind_partitions p, dba_segments s, dba_indexes i where   i.table_name = upper('&table_name') and i.owner=upper(nvl('&owner',user))   and   s.segment_name=i.index_name and s.owner=i.owner   and   p.index_owner=s.owner and p.index_name=s.segment_name and p.partition_name=s.partition_name   and   s.segment_type = 'INDEX PARTITION' order   by index_name /   break on index_name   select  i.index_name,i.column_name,i.column_position,t.num_distinct,t.num_nulls,t.num_buckets from    dba_ind_columns i, dba_tab_columns t where   t.owner=upper(nvl('&owner',user)) and i.table_name=upper('&table_name')   and   i.index_owner=t.owner and i.table_name=t.table_name and t.column_name=i.column_name order   by index_name,column_position;   clear breaks set feedback on