Category : Databases and related files
Archive   : UTIL91.ZIP
Filename : ACTSIZE.SQL

rem ACTSIZE.SQL - This SQL*Plus script produces a report that displays
rem the variation between what Oracle has allocated to a
rem table and the number of blocks it actually is using.
rem
rem You must have DBA privilege to run this report and
rem must have the DBA synonyms created in your account.
rem
rem Syntax:
rem $ SQLPLUS-S USERNAME/PASSWORD @ACTSIZE OWNERNAME TABLENAME
rem
rem Set up the SQL*Plus controls
rem
set echo off
set termout off
set pagesize 0
set verify off
set heading off
set recsep off
ttitle off
btitle off

define ownername = &1
define tablename = &2

create table temp_size_table
(table_name char(50),
blocks number);

spool sizetab.sql

select 'insert into temp_size_table',
' select ','&'||'temp_var.' || segment_name ||'&'||'temp_var',
', count( distinct( substr( rowid,1,8))) blocks',
' from ', segment_name, ';'
from dba_segments
where segment_type = 'TABLE'
and segment_name like upper('&tablename')
and owner = upper('&ownername');

spool off

define temp_var = ''''

start sizetab &ownername

rem Place the appropriate delete command here for your operating system
rem
rem $ del sizetab.sql;

set echo off
set termout off
set feedback off
set verify off
rem
rem Set up the standard DBA report heading
rem
start title132 "Actual vs Allocated Space Report"
rem
set linesize 132
set newpage 0
set numwidth 3
set pagesize 58
btitle skip 3 center &ownername " Account"

col segment_name format a30 heading "Segment|Name" justify center
col segment_type format a7 heading "Segment|Type" justify center
col tablespace_name format a20 heading "Tablespace Name" justify center
col owner format a15 heading "Owner Name" justify center
col extents format 999 heading "Extents" justify center
col bytes format 999,999,999 heading "Bytes" justify center
col blocks format 9,999,999 heading "Oracle|Blocks" justify center
col act_blocks format 9,999,999 heading "Actual|Used|Blocks" justify center
col pct_block format 9999.99 heading "Pct|Blocks|Used" justify center

break on report skip page

compute sum of extents on report;
compute sum of bytes on report;
compute sum of blocks on report;
compute sum of act_blocks on report;

spool actsize.lis

select segment_name, segment_type, tablespace_name, owner,
extents, bytes, a.blocks,
b.blocks act_blocks, b.blocks/a.blocks*100 pct_block
from dba_segments a, temp_size_table b
where segment_name = upper(b.table_name)
and owner = upper('&ownername');

spool off

drop table temp_size_table;

exit