Friday, September 11, 2009

ANALYZE INFORMATION

CREATE OR REPLACE PROCEDURE halim_gather_stat_schema IS
BEGIN
DBMS_STATS.gather_schema_stats
(ownname => '&STLBAS',
options => 'GATHER',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all columns size auto',
CASCADE => TRUE,
DEGREE => 5
);
END;
/


SELECT a.owner, a.table_name, a.avg_row_len, a.blocks, a.last_analyzed,
a.num_rows, a.tablespace_name, a.row_movement, a.sample_size
FROM all_tables a
WHERE last_analyzed IS NOT NULL AND owner = 'SCOTT'
ORDER BY a.last_analyzed DESC


select *
from dba_indexes
where owner='SCOTT'
AND


select * from all_tables

No comments: