Re: clear unused table space

From: Steve Robin <ocmaman_at_gmail.com>
Date: 19 Feb 2007 03:39:35 -0800
Message-ID: <1171885175.529878.11840@v33g2000cwv.googlegroups.com>

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 Received on Mon Feb 19 2007 - 05:39:35 CST