Re: find allocated and used space for a table

On Monday 11 June 2001 01:10, Rahul wrote:
> manoj,

> Also, a while back someone else (Jared ?) posted a similar procedure.. you
> might
> find it on his website

No, it's not on the website, so I'll post it again.

Jared

set verify off
set echo off feed off

undef object_name;
undef object_type;
undef schema_name;

prompt After enter the object name, object type and schema, prompt you will be shown the amount of space that this object prompt uses in the database
prompt

col otype noprint new_value objtype

accept object_type prompt 'Type of object - ( TABLE/INDEX/CLUSTER ) : '

set term off
select upper('&&object_type') otype from dual; set term on

accept schema_name prompt 'Schema: '
accept object_name prompt 'Name of &&objtype: ' prompt

set serverout on size 1000000

declare

        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;

begin

        dbms_space.unused_space(
                upper('&&schema_name'),upper('&&object_name'),upper('&&objtype'),
                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 ' || 
                upper('&&schema_name') || '.' || 
                upper('&&object_name')
        );
        dbms_output.put_line('  TOTAL BLOCKS: ' || to_char( total_blocks ));
        dbms_output.put_line('  TOTAL BYTES : ' || to_char( total_bytes ));
        dbms_output.put_line('  USED  BLOCKS: ' || to_char( total_blocks - 
unused_blocks ));
        dbms_output.put_line('  USED  BYTES : ' || to_char( total_bytes - 
unused_bytes ));
        dbms_output.put_line('  FREE  BLOCKS: ' || to_char( unused_blocks ));
        dbms_output.put_line('  FREE  BYTES : ' || to_char( unused_bytes ));

end;
/

set feed on
HùÔHùÔ

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).