Re: clear unused table space

On 19 feb, 12:39, "Steve Robin" <ocma..._at_gmail.com> wrote:
> On Feb 19, 4:25 pm, "frank.van.bor..._at_gmail.com"
>
> <frank.van.bor..._at_gmail.com> wrote:
> > On 19 feb, 11:58, "Steve Robin" <ocma..._at_gmail.com> wrote:
>
> > > How to clear unused table space without dropping the table.
> > > Truncate table is not clearing unused table space.
>
> > How did you come to that
> > conclusion?
> > The space should be marked as free
>
> This is the reason.
>
> SQL> DECLARE
> 2 op1 NUMBER;
> 3 op2 NUMBER;
> 4 op3 NUMBER;
> 5 op4 NUMBER;
> 6 op5 NUMBER;
> 7 op6 NUMBER;
> 8 op7 NUMBER;
> 9 BEGIN
> 10
> Dbms_Space.Unused_Space('MCC_NCR','AUDIT_ERP_CUSTOMER_ADDRESS','TABLE',op1,op2,op3,op4,op5,op6,op7);
> 11 Dbms_Output.Put_Line('AUDIT_ERP_CUSTOMER_ADDRESS' ||
> 12 LPad(op3,15,' ') ||
> 13 LPad(op1,15,' ') ||
> 14 LPad(Trunc(op1-op3-1),15,' '));
> 15 END;
> 16 /
> AUDIT_ERP_CUSTOMER_ADDRESS 4223 4224
> 0
>
> PL/SQL procedure successfully completed.
>
> SQL> SELECT COUNT(1) FROM MCC_NCR.AUDIT_ERP_CUSTOMER_ADDRESS;
>
> COUNT(1)
> ----------
> 0
>
> SQL> TRUNCATE TABLE MCC_NCR.AUDIT_ERP_CUSTOMER_ADDRESS;
>
> Table truncated.
>
> SQL> DECLARE
> 2 op1 NUMBER;
> 3 op2 NUMBER;
> 4 op3 NUMBER;
> 5 op4 NUMBER;
> 6 op5 NUMBER;
> 7 op6 NUMBER;
> 8 op7 NUMBER;
> 9 BEGIN
> 10
> Dbms_Space.Unused_Space('MCC_NCR','AUDIT_ERP_CUSTOMER_ADDRESS','TABLE',op1,op2,op3,op4,op5,op6,op7);
> 11 Dbms_Output.Put_Line('AUDIT_ERP_CUSTOMER_ADDRESS' ||
> 12 LPad(op3,15,' ') ||
> 13 LPad(op1,15,' ') ||
> 14 LPad(Trunc(op1-op3-1),15,' '));
> 15 END;
> 16 /
> AUDIT_ERP_CUSTOMER_ADDRESS 4223 4224
> 0
>
> PL/SQL procedure successfully completed.
>
> SQL> select bytes/1024/1024 from dba_segments where
> segment_name='AUDIT_ERP_CUSTOMER_ADDRESS' and owner='MCC_NCR';
>
> BYTES/1024/1024
> ---------------
> 33

According to the 10G Release 1 SQL reference manual:

"By default, Oracle Database also deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter...."

Could that be the cause - a (pretty large) minextents size? Received on Mon Feb 19 2007 - 07:14:14 CST