Computers - http://phatbowling.com/sqlandstuff/all

spool all_stat.rslt;

/* ****************************************************** */
/* */
/* Name: all_stat.sql */
/* Purpose: Show the analyze statistics of all tables and */
/* indexes */
/* Creator: R.Kupcunas */
/* Date: 01-Jan-98 */
/* */
/* ****************************************************** */

set pagesize 100;
set linesize 132;
break on "OWNER" on "VIEW";

set echo on;

/* ********************* */
/* statistics for tables */
/* ********************* */

set echo off;

select substr(owner,1,8) "OWNER",
substr(table_name,1,25) "TABLE",
substr(to_char(pct_used, '99'),1,3) "% USED",
substr(to_char(pct_free, '99'),1,3) "% FREE",
substr(to_char(num_rows, '99,999'),1,7) "ROWS",
substr(to_char(blocks, '99,999'),1,7) "BLKS",
substr(to_char(empty_blocks, '99,999'),1,7) "EMPTY BLKS",
substr(to_char(avg_space, '99,999'),1,7) "AVG SPACE",
substr(to_char(chain_cnt, '9,999'),1,5) "CHAINING",
substr(to_char(avg_row_len, '999'),1,4) "ROW LEN",
cache "CACHE"
from sys.all_tables
where owner <> 'SYS' and
owner <> 'SYSTEM'
order by 1, 2;

set echo on;

/* ********************* */
/* statistics for indexes */
/* ********************* */

set echo off;

select substr(owner,1,8) "OWNER",
substr(table_name,1,25) "TABLE",
substr(index_name,1,25) "INDEX",
decode(uniqueness,'UNIQUE', 'Yes', 'No') "UNQ?",
substr(to_char(distinct_keys, '999,999'),1,8) "#Keys",
substr(to_char(avg_leaf_blocks_per_key, '999'),1,4) "Leaf/Key",
substr(to_char(avg_data_blocks_per_key, '999'),1,4) "Blks/Key",
substr(to_char(clustering_factor, '99,999'),1,7) "Clust Fct"
from sys.all_indexes
where owner <> 'SYS' and
owner <> 'SYSTEM'
order by 1, 2;

set echo on;
spool off;
commit;

Sign Guestbook

View Guestbook

tomday2@mail.com

Visitors: 00005

Page Updated Wed Feb 16, 2000 1:33pm EST