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 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). > -- 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).
