On Feb 19, 6:14 pm, "frank.van.bor..._at_gmail.com"
<frank.van.bor..._at_gmail.com> wrote:
> 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?- Hide quoted text -
>
> - Show quoted text -
Thanks a lot Frank.
It was my mistake, I forgot to see this parameter. I future I will try
to not ask this kind of stupid question.
Thanks again
Received on Tue Feb 20 2007 - 05:00:22 CST
