-------
실 행
-------
SQL> @indexdesc.sql
SQL> @stats.sql
indexdesc.sql
stats.sql
강의자료.zip실 행
-------
SQL> @indexdesc.sql
SQL> @stats.sql
indexdesc.sql
--
-- tblidx.sql
--
set heading on
set pagesize 66
set linesize 500
ttitle 'Index Information'
column tab_name format a13 heading 'Table'
column num_rows format 9999999 heading '# Rows'
column ind_name format a22 heading 'Index'
column tablespace_name format a10 heading 'Table|Space'
column uniqueness format a1 heading 'U'
column col_name format a13 heading 'Column'
column pct_free format 99 heading 'Pct|Free'
column clustering_factor format 99990 heading 'Cluster|Factor'
column blevel format 99 heading 'BLevel'
column leaf_blocks format 9999 heading 'Leaf'
column num_distinct format 9999999 heading '# Dist'
column Balance format 990
break on tab_name skip 1 on ind_name on uniqueness on num_rows
spool tblidx.lst
select x.table_name tab_name,
x.num_rows num_rows,
a.index_name ind_name,
a.tablespace_name,
decode(a.uniqueness, 'UNIQUE', 'U', 'N') uniqueness,
b.column_position col_pos,
b.column_name col_name,
y.num_distinct num_distinct,
a.pct_free,
a.clustering_factor,
a.blevel,
a.leaf_blocks,
(c.del_lf_rows_len / c.lf_rows_len) * 100 as Balance
from dba_tables x, dba_tab_columns y, dba_indexes a, dba_ind_columns b, index_stats c
where x.owner = 'SCOTT'
and a.table_owner = x.owner
and a.table_owner = b.index_owner
and x.table_name = a.table_name
and a.table_name = b.table_name
and a.index_name = b.index_name
and x.owner = y.owner
and x.table_name = y.table_name
and b.column_name = y.column_name
and b.index_name = c.name(+)
order by x.table_name
/
spool off
-- tblidx.sql
--
set heading on
set pagesize 66
set linesize 500
ttitle 'Index Information'
column tab_name format a13 heading 'Table'
column num_rows format 9999999 heading '# Rows'
column ind_name format a22 heading 'Index'
column tablespace_name format a10 heading 'Table|Space'
column uniqueness format a1 heading 'U'
column col_name format a13 heading 'Column'
column pct_free format 99 heading 'Pct|Free'
column clustering_factor format 99990 heading 'Cluster|Factor'
column blevel format 99 heading 'BLevel'
column leaf_blocks format 9999 heading 'Leaf'
column num_distinct format 9999999 heading '# Dist'
column Balance format 990
break on tab_name skip 1 on ind_name on uniqueness on num_rows
spool tblidx.lst
select x.table_name tab_name,
x.num_rows num_rows,
a.index_name ind_name,
a.tablespace_name,
decode(a.uniqueness, 'UNIQUE', 'U', 'N') uniqueness,
b.column_position col_pos,
b.column_name col_name,
y.num_distinct num_distinct,
a.pct_free,
a.clustering_factor,
a.blevel,
a.leaf_blocks,
(c.del_lf_rows_len / c.lf_rows_len) * 100 as Balance
from dba_tables x, dba_tab_columns y, dba_indexes a, dba_ind_columns b, index_stats c
where x.owner = 'SCOTT'
and a.table_owner = x.owner
and a.table_owner = b.index_owner
and x.table_name = a.table_name
and a.table_name = b.table_name
and a.index_name = b.index_name
and x.owner = y.owner
and x.table_name = y.table_name
and b.column_name = y.column_name
and b.index_name = c.name(+)
order by x.table_name
/
spool off
stats.sql
-- 사용자의 모든 테이블 컬럼구조와 데이터의 유형을 분석한다.
-- 스크립트 실행전에 반드시 SQL> @Table_ANALYZE.SQL을 실행하라.
Set feed off echo off pages 100
set linesize 200
col cluster_name format a11 headi 'Cluster'
col table_name format a13 headi 'Table'
col column_id noprint
col row_type noprint
col num_rows format 9,999,990 headi 'Rows'
col blocks format 99990 headi 'Blocks'
col empty_blocks format 99990 headi 'Empty|Blocks'
col avg_row_len format 999990 headi 'Ave Row|Length'
col pct_free format 90 headi 'Pct|Free'
col pct_used format 90 headi 'Pct|Used'
col chain_cnt format 90 headi 'Chain|Count'
col column_name format a15 headi 'Column'
col num_distinct format 9,999,990 headi 'Distinct|Values'
col index_name format a10 headi 'Index'
break on cluster_name skip 1 on table_name skip 1 -
on num_rows on blocks on empty_blocks on avg_row_len on row_type on pct_free on pct_used on chain_cnt skip 1
spool stats.dat
select decode(clu.cluster_name, null, 'Unclustered',
clu.cluster_name) cluster_name,
t.table_name,
'C' row_type,
col.column_id,
t.num_rows,
t.blocks,
t.empty_blocks,
t.avg_row_len,
t.pct_free,
t.pct_used,
t.chain_cnt,
col.column_name,
col.num_distinct
from user_tables t,
user_tab_columns col,
user_clusters clu
where t.num_rows is not null
and clu.cluster_name (+) = t.cluster_name
and t.table_name = col.table_name
and t.table_name <> 'PLAN_TABLE'
/
spool off
set feed on echo on
Set feed off echo off pages 24
-- 스크립트 실행전에 반드시 SQL> @Table_ANALYZE.SQL을 실행하라.
Set feed off echo off pages 100
set linesize 200
col cluster_name format a11 headi 'Cluster'
col table_name format a13 headi 'Table'
col column_id noprint
col row_type noprint
col num_rows format 9,999,990 headi 'Rows'
col blocks format 99990 headi 'Blocks'
col empty_blocks format 99990 headi 'Empty|Blocks'
col avg_row_len format 999990 headi 'Ave Row|Length'
col pct_free format 90 headi 'Pct|Free'
col pct_used format 90 headi 'Pct|Used'
col chain_cnt format 90 headi 'Chain|Count'
col column_name format a15 headi 'Column'
col num_distinct format 9,999,990 headi 'Distinct|Values'
col index_name format a10 headi 'Index'
break on cluster_name skip 1 on table_name skip 1 -
on num_rows on blocks on empty_blocks on avg_row_len on row_type on pct_free on pct_used on chain_cnt skip 1
spool stats.dat
select decode(clu.cluster_name, null, 'Unclustered',
clu.cluster_name) cluster_name,
t.table_name,
'C' row_type,
col.column_id,
t.num_rows,
t.blocks,
t.empty_blocks,
t.avg_row_len,
t.pct_free,
t.pct_used,
t.chain_cnt,
col.column_name,
col.num_distinct
from user_tables t,
user_tab_columns col,
user_clusters clu
where t.num_rows is not null
and clu.cluster_name (+) = t.cluster_name
and t.table_name = col.table_name
and t.table_name <> 'PLAN_TABLE'
/
spool off
set feed on echo on
Set feed off echo off pages 24