set echo off;
clear col
set head off
set pause off
set pages 0
set verify off
set feedback off
set WRAP      on

clear breaks
clear compute

column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy hh:miam') today
       from dual;

break on instance
column instance new_value instance_name
select substr(name,1,8) instance
      from v$database;

clear breaks
set termout on

set pagesize 60 linesize 132 verify off
set space 2
ttitle left 'Date: ' format a18 dba_date -
       center 'DB Review(4) - ' format a8 instance_name -
       right 'Page: ' format 999 sql.pno skip 2

set head on
set verify on
set feedback on
col table_name format a20
col ext format 990
col used format 99,990
col empty format 99,990
col used_pct format 990.99
col next format 99,999,990

break on "OWNER" on "TABLE" on "INDEX";
spool db_revw4.rslt 

set echo on;
/* ******************************************************************* */
/*                                                                     */
/*                >>> Database Review; R.Kupcunas <<<                  */
/*                                                                     */
/*  Name:    d:\oradata\NUCP\tuning\db_revw4.sql                       */
/*  Creator: Rick Kupcunas                                             */
/*  Created: 10-Mar-97                                                 */
/*                                                                     */
/* this script will show Show the analyze statistics of all tables.    */
/*  SECTION 1 - statistics for tables                                  */
/*  SECTION 2 - statistics for indexes                                 */
/* ******************************************************************* */

break on "OWNER" on "VIEW";

set echo on;

/* ********************************************************* */
/*  SECTION 1 - statistics for tables                        */
/* ********************************************************* */

set echo off;

col tableName heading "Table Name" format a30
col indexName heading "Index Name" format a30
col tablespace_name heading "TableSpace|Name" format a30
col total_bytes heading "Size|(Kbytes)" format 999,999,990
col free_bytes heading "Free|(KBytes)" format 999,999,990
col pctUsed1 heading "Pct|Used" format 9,999
col pctFree1 heading "Pct|Free" format 9,999
col emptyBlocks heading "Free|Blks" format 99,990
col numRows heading "Num|Rows" format 9,999,990
col numBlocks heading "Num|Blks" format 99,990
col avgSpace heading "Avg|Space" format 99,990
col chaining heading "Chain" format 9,990
col rowLenght heading "Row|Len" format 9,990

select substr(owner,1,8)   				"OWNER",
       table_name  			                	tableName,
       substr(to_char(pct_used, '999'),1,5)     	pctUsed1,
       substr(to_char(pct_free, '999'),1,5)     	pctFree1,
       substr(to_char(num_rows, '9,999,999'),1,10)		numRows,
       /* substr(to_char(blocks, '99,999'),1,7)		numBlocks, */
       substr(to_char(empty_blocks, '99,999'),1,7)      emptyBlocks,
       /* substr(to_char(avg_space, '99,999'),1,7)	avgSpace,  */
       substr(to_char(chain_cnt, '9,999'),1,5)		chaining,
       substr(to_char(avg_row_len, '9,999'),1,6)		rowLenght,
       substr(cache,1,1)				"C"
  from sys.all_tables
  where owner <> 'SYS' and
        owner <> 'SYSTEM'
  order by 1, 2;

set echo on;

/* ********************************************************* */
/*  SECTION 2 - statistics for indexes                       */
/* ********************************************************* */

set echo off;

break on "OWNER" on tableName;

col tableName heading "Table Name" format a30
col indexName heading "Index Name" format a30
col leafKeys heading "Leaf|Keys" format 990
col blockKeys heading "Blks|Keys" format 990
col clustFactor heading "Clust|Fct" format 99,990

select substr(owner,1,8)      					"Owner",
       table_name    				                tableName,
       index_name     				                indexName,
       decode(uniqueness,'UNIQUE', 'Yes', 'No')         	"Unq",
       substr(to_char(distinct_keys, '9,999,999'),1,11)     	"#Keys",
       substr(to_char(avg_leaf_blocks_per_key, '999'),1,4)     	leafKeys,
       substr(to_char(avg_data_blocks_per_key, '999'),1,5)	blockKeys
--       substr(to_char(clustering_factor, '99,999'),1,7)		clustFactor
  from sys.all_indexes
  where owner <> 'SYS' and
        owner <> 'SYSTEM'
  order by 1, 2, 3;

set echo on;
spool off;
commit;
