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: [EMAIL PROTECTED]
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
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: [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).
