/* ************************************************ */
/*                                                  */
/* fnctn:   Table Usage Parameters                  */
/*                                                  */
/* name:    @c:\oracle\sql\tbl_blks.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

set termout off
set head off
set feedback off
set verify off
set pages 60
truncate table table_rowids;
 
insert into table_rowids
   (owner,
    segment_name,
    segment_type,
    extents,
    bytes,
    blocks,
    actual_blocks)
  select owner,
         segment_name,
         segment_type,
         extents,
         bytes,
         blocks,
         0
    from sys.dba_segments
   where owner not in ('SYS','SYSTEM') and 
         segment_type = 'TABLE' and 
         bytes > 1048576;

spool tbl_blks.rslt
 
select 'UPDATE TABLE_ROWIDS ' nl, 
       'SET actual_blocks = 
             (select count(distinct substr(rowid,1,8)) 
               from '||owner||'.'||segment_name||') ' nl,
       'WHERE OWNER='''||owner||''' AND SEGMENT_NAME = '''|| 
            segment_name||''' ; ' 
   from table_rowids;

spool off
 
/* @@/tmp/extent_status.lst */

commit;

set head on
set feedback on
set termout on
set verify on

col tablespace_name heading "Table|Space" format a10
col owner heading "Owner" format a10
col segment_name heading "Segment|Name" format a30
col blocks heading "Blocks|Alloc" format 999,999,990
col act_blocks heading "Actual|Blocks" format 999,999,990
col bytes heading "Bytes|Alloc" format 99,999,999,990
col act_bytes heading "Actual|Bytes" format 99,999,999,990
col pct heading "Percentage|Used" format 990.99

break on tablespace_name skip 1 on report

compute sum of bytes  act_bytes on tablespace_name
compute sum of bytes  act_bytes on report

set lines 132

ttitle center "Table Usage Parameters" skip 2 

spool tab.rslt

select b.tablespace_name tablespace_name,
       a.owner owner,
       a.segment_name segment_name,
       a.blocks blocks,a.blocks*4096 bytes,
       decode(a.actual_blocks,0,2,1,2,a.actual_blocks) act_blocks,
       decode(a.actual_blocks,0,8192,1,8192,a.actual_blocks*4096) act_bytes,
       decode(a.actual_blocks,0,2,1,2,a.actual_blocks)/a.blocks*100 pct
  from table_rowids a,
       dba_tables b
 where a.owner = b.owner and 
       a.segment_name = b.table_name and 
       a.blocks > 256
order by tablespace_name,
       owner,
       pct desc;

spool off
