Data de Inclusão:
02/09/2006 | Última Alteração:
02/09/2006
Tipo:Scripts | Enviada por:
Moderador Oracle_br (moderador@oraclebr.com.br)
Listing Free Space
This tip comes from Krishna Sarabu, an Oracle database administrator with Reuters in Hauppauge, New York.
This script lists the free space by objects in the schema using DBMS_SPACE package.
REM ******************************************************************************************
REM * Script to List free space by object_name and object_type. *
REM * Developed by Krishna Sarabu. *
REM * *
REM * Version# Date Author *
REM * ========================================= *
REM * Version 1.0 04/23/01 Krishna Sarabu *
REM ******************************************************************************************
undef Owner
undef Grpid
undef Inst
undef DBs
Column Inst new_val Grpid
column B new_val DBs
Accept Owner char Prompt 'Please Enter the Schema Owner name to list free space report information for all objects : '
set verify off heading off serveroutput on linesize 132 pagesize 5000 feedback off
select value Inst from v$parameter where name='instance_number' ;
select value B from v$parameter where name='db_block_size' ;
Declare
Cursor C1 is
Select table_name object_name, 'TABLE' object_type from dba_tables where owner=upper('&Owner')
union
select index_name object_name, 'INDEX' object_type from dba_indexes where owner=upper('&Owner')
union
select cluster_name object_name, 'CLUSTER' object_type from dba_clusters where owner=upper('&Owner')
order by object_type, object_name ;
F_blks Integer := 0; Sz Number ;
begin
dbms_output.enable(9999999999999);
dbms_output.put_line('===================================================================================');
dbms_output.put_line('- Free Space Report List of &Owner ' ) ;
dbms_output.put_line('===================================================================================');
dbms_output.put_line(Rpad('OBJECT NAME',40)||' '||Rpad('OBJECT TYPE',15)||' '||RPAD('FREE SPACE in KB',25) );
dbms_output.put_line('===================================================================================');
For I in C1 LOOP
F_blks := 0;
dbms_space.free_blocks(SEGMENT_OWNER => upper('&Owner') ,
SEGMENT_NAME => Rtrim(I.Object_name) ,
SEGMENT_TYPE => Rtrim(I.Object_Type) ,
FREELIST_GROUP_ID => &Grpid , FREE_BLKS => F_blks ) ;
Sz := NVL(F_blks,0) * ( &DBs / 1024 );
If Sz > 0 Then
dbms_output.put_line(Rpad(I.object_name,40)||' '||Rpad(I.object_type,15)||' '||Rpad(Sz,10) );
End If;
End Loop;
dbms_output.put_line('===================================================================================');
End;
--------------------------------------------------------------------------------