If that's one of my scripts, RCS says it's from 1997.
Here's a newer one. It gets *everything* in the database, so
a little tweaking may be in order.
Jared
===============================
-- showspace_all.sql
-- use dbms_space to show exact amount
-- of space consumed by tables/indexes/clusters
-- jared still
set verify off
set echo off feed off
prompt
prompt Working...
prompt
declare
object_blocks number;
unused_blocks number;
object_bytes number;
unused_bytes number;
total_blocks number default 0;
total_bytes number default 0;
last_used_extent_file_id number;
last_used_extent_block_id number;
last_used_block number;
cursor c_objects
is
select owner, object_name, object_type
from dba_objects
where object_type in ('CLUSTER','INDEX','TABLE');
begin
for objrec in c_objects
loop
dbms_space.unused_space(
objrec.owner,objrec.object_name,objrec.object_type,
object_blocks, object_bytes,
unused_blocks, unused_bytes,
last_used_extent_file_id ,
last_used_extent_block_id,
last_used_block
);
dbms_output.put_line(
'Total space used by ' || objrec.owner || '.' ||
objrec.object_name
);
-- comment out these 2 lines if you get buffer overflow on dbms_output
dbms_output.put_line(' BLOCKS: ' || to_char( object_blocks -
unused_blocks ));
dbms_output.put_line(' BYTES : ' || to_char( object_bytes -
unused_bytes ));
total_blocks := total_blocks + ( object_blocks - unused_blocks );
total_bytes := total_bytes + ( object_bytes - unused_bytes );
end loop;
dbms_output.put_line(' ' );
dbms_output.put_line('TOTAL BLOCKS: ' || to_char( total_blocks ));
dbms_output.put_line('TOTAL BYTES : ' || to_char( total_bytes ));
end;
/
set feed on
==========================-
ù
On Sunday 23 June 2002 19:28, Ferenc Mantfeld wrote:
> Hi All
> Jared notified me that the attachment did not come through, so I am
> attaching again. I am also enclosing it in the body of the email.
>
> Credits go to Jared who wrote the shell of this some time back (98 was it
> Jared ?). I just modified an excellent foundation.
>
> <<tblindspace.sql>>
>
> set linesize 80
> set verify off
> set echo off
> set feed off
>
> undef tab_name;
> undef object_type;
> undef schema_name;
>
> prompt Enter the schema and table name, and you are shown the space that
> the table
> prompt and each associated index uses in the database
> prompt
>
> accept schema_name prompt 'Enter Schema: '
> accept tab_name prompt 'Enter TABLE: '
> prompt
>
> set serverout on size 1000000
>
> declare
> ind_name varchar2(30);
> total_blocks number;
> unused_blocks number;
>
> total_bytes number;
> unused_bytes number;
>
> last_used_extent_file_id number;
> last_used_extent_block_id number;
> last_used_block number;
> cursor find_ind is
> select index_name from all_indexes where
> owner=upper('&&schema_name') and
> table_name=upper('&&tab_name') ;
>
> begin
>
> dbms_space.unused_space(upper('&&schema_name'),upper('&&tab_name'),
> 'TABLE',total_blocks, total_bytes,unused_blocks,
> unused_bytes,
>
> last_used_extent_file_id,last_used_extent_block_id,last_used_block);
> dbms_output.put_line('Total space used by TABLE '||
> upper('&&schema_name')||'.' ||upper('&&tab_name'));
>
> dbms_output.put_line('TOTAL BLOCKS USED_BLOCKS FREE BLOCKS');
> dbms_output.put_line('============ =========== ===========');
> dbms_output.put_line(to_char(total_blocks) ||' '||
> to_char(total_blocks - unused_blocks)||' '||
> to_char(unused_blocks));
> dbms_output.put_line(' ');
> open find_ind ;
> loop
> fetch find_ind into ind_name ;
> exit when find_ind%NOTFOUND or find_ind%NOTFOUND is null ;
>
>
> dbms_space.unused_space(upper('&&schema_name'),upper(ind_name),'INDEX',
> total_blocks, total_bytes,
> unused_blocks, unused_bytes,
> last_used_extent_file_id ,
> last_used_extent_block_id,
> last_used_block );
>
> dbms_output.put_line('Total space used by INDEX '||
> upper('&&schema_name')||'.' ||upper(ind_name)||'
> '||
> to_char(total_blocks) ||'
> '||to_char(total_blocks - unused_blocks)||
> ' '|| to_char(unused_blocks));
> end loop ;
> if find_ind%ISOPEN then close find_ind;
> end if;
>
> end;
> /
>
> set feed on
>
>
> Regards:
> Ferenc Mantfeld
> Senior Performance Engineer
> Siebel Performance Engineering
> Melbourne, 3000, VIC, Australia
> Only Robinson Crusoe had all his work done by Friday
----------------------------------------
Content-Type: application/octet-stream; charset="iso-8859-1"; name="tblindspace.sql"
Content-Transfer-Encoding: 7bit
Content-Description:
----------------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
