Thanks Kirti. I'll give that a go.
RC
-Original Message-
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
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 100
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).
--
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).
