'오라클 테이블 통계정보 조회'에 해당되는 글 1건

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