'인덱스 상세조회'에 해당되는 글 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

/


블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,