RE: Actual table size (Has been posted/discussed before)

Thanks Kirti. I'll give that a go.

RC

> -----Original Message-----
> From: Deshpande, Kirti [mailto:kirti.deshpande_at_verizon.com]
> Sent: Thursday, 17 October 2002 14:29
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Actual table size (Has been posted/discussed before)
>
>
> You can try this:
>
> REM == From Oracle-l posting
> set echo off term on feed on pause off verify off
> prompt Username to check space for:
> col cobjuser noprint new_value uobjuser
> set term off feed off
> select upper('&1') cobjuser from dual;
> set term on feed on
>
> prompt Object to check space on ( for &&uobjuser user ):
> col cobjname noprint new_value uobjname
> set term off feed off
> select upper('&2') cobjname from dual;
> set term on feed on
>
> prompt Type of object: - (T)able (I)ndex
> col cobjtype noprint new_value uobjtype
> set term off feed off
> select ('&3') cobjtype from dual;
> set term on feed on
>
> set serverout on size 1000000
>
> prompt
> prompt
> prompt
>
> set feed off
>
> declare
> op1 number;
> op2 number;
> op3 number;
> op4 number;
> op5 number;
> op6 number;
> op7 number;
> objname varchar2(30);
> objtype varchar2(10);
> objuser varchar2(30);
> free_blocks number :=0;
> begin
>
> select upper('&&uobjuser') into objuser from dual;
>
> select
> decode(upper('&&uobjtype'),
> 'T','TABLE',
> 'I','INDEX',
> NULL
> ) into objtype from dual;
>
> select upper('&&uobjname') into objname from dual;
>
> dbms_space.unused_space(objuser,
> objname, objtype,
> op1,op2,op3,op4,op5,op6,op7);
>
> dbms_output.put_line('schema = ' || objuser);
> dbms_output.put_line('object name = ' || objname);
> dbms_output.put_line('object type = ' || objtype);
> dbms_output.put_line('-----------------------------');
> dbms_output.put_line(' total_blocks = '||op1);
> dbms_output.put_line(' total_bytes = '||op2);
> dbms_output.put_line(' unused_blocks = '||op3);
> dbms_output.put_line(' unused_bytes = '||op4);
> dbms_output.put_line(' last_used_extent_file_id = '||op5);
> dbms_output.put_line(' last_used_extent_block_id = '||op6);
> dbms_output.put_line(' last_used_block = '||op7);
> end ;
> /
>
> prompt
> prompt
> prompt
> set feed on
> undef 1 2
>
> -----Original Message-----
> Sent: Wednesday, October 16, 2002 10:39 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi All,
>
> It has previously been discussed in this list regarding the
> actual size of a
> table. Can someone re-post the SQL to get this?
> I've got a table that has been ANALYZED. It has only 1
> extent (INITIAL
> 50M). There's been a lot of deletes and insert on it. I
> wanted to know how
> much of the 50M has data in it?
>
> On 817.
>
> Rgds,
> Ross
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ross Collado
> INET: Ross.Collado_at_techpac.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ross Collado
  INET: Ross.Collado_at_techpac.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).