$vi analyze_table.sql
-- scott 사용자의 테이블과 인덱스를 분석하는 스크립트
set verify off
set echo off
set feedback off
set heading off
set pagesize 0

spool analyze.sql

-- Analyze 테이블
select 'analyze table ', owner||'.'||table_name, ' compute statitstics;'
from dba_tables
where owner='SCOTT';
spool off
prompt '>Table Analyzing....'
@analyze

-- Analyze 인덱스
select 'analyze index ', owner||'.'||index_name, ' compute statitstics;'
from dba_indexes
where owner='SCOTT';
spool off
prompt '>Index Analyzing....'
@analyze
/
--
set verify on
set echo on
set feedback on
set heading on

@analyze_table
----------------------------------------------------

$vi stats.sql

spool stats.txt

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

@stats