This discussion is archived
11 Replies Latest reply: Apr 6, 2012 10:38 AM by jgarry RSS

Not able to reclaim the space from table

712089 Newbie
Currently Being Moderated
we have a table which currently have no records but when i tried to reorg this table it still consuming 4GB space not sure how.Please help me on this.
also no BLOB/CLOB column in this table,no MV on this table and below are the storage parameter for this table.
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 4294967296 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DWH1_DT"


select sum(bytes)/(1024*1024*1024) from dba_segments where owner='DWH1' and segment_name='DWH_SERVICE_BILLING_FACT';
SUM(BYTES)/(1024*1024*1024)
---------------------------
4
SQL> select count(1) from DWH1.DWH_SERVICE_BILLING_FACT;

COUNT(1)
----------
0
SQL> alter table DWH1.DWH_SERVICE_BILLING_FACT move;
Table altered.

select sum(bytes)/(1024*1024*1024) from dba_segments where owner='DWH1' and segment_name='DWH_SERVICE_BILLING_FACT';

SUM(BYTES)/(1024*1024*1024)
---------------------------
4
SQL> alter table DWH1.DWH_SERVICE_BILLING_FACT move tablespace DWH2_DT;

Table altered.

SQL> alter table DWH1.DWH_SERVICE_BILLING_FACT move tablespace DWH1_DT;

Table altered.

SQL> select sum(bytes)/(1024*1024*1024) from dba_segments where owner='DWH1' and segment_name='DWH_SERVICE_BILLING_FACT';

SUM(BYTES)/(1024*1024*1024)
---------------------------
4
  • 1. Re: Not able to reclaim the space from table
    rp0428 Guru
    Currently Being Moderated
    You can truncate the table but that won't release the space either.
    Why are you trying to achieve?
  • 2. Re: Not able to reclaim the space from table
    sb92075 Guru
    Currently Being Moderated
    http://www.oracle.com/pls/db112/search?remark=quick_search&word=shrink

    Moving an object does not change the size of the object.


    Handle:     GourG
    Status Level:     Newbie (5)
    Registered:     Jul 15, 2009
    Total Posts:     42
    Total Questions:     17 (8 unresolved)

    why so many unanswered questions?

    Edited by: sb92075 on Apr 5, 2012 8:59 PM
  • 3. Re: Not able to reclaim the space from table
    712089 Newbie
    Currently Being Moderated
    still its consuming 4 GB. Is it take some time to refresh the recliam space?

    SQL> truncate table DWH1.DWH_SERVICE_BILLING_FACT;

    Table truncated.

    SQL> select sum(bytes)/(1024*1024*1024) from dba_segments where owner='DWH1' and segment_name='DWH_SERVICE_BILLING_FACT';

    SUM(BYTES)/(1024*1024*1024)
    ---------------------------
    4

    SQL> select count(1) from DWH1.DWH_SERVICE_BILLING_FACT;

    COUNT(1)
    ----------
    0
  • 4. Re: Not able to reclaim the space from table
    Aman.... Oracle ACE
    Currently Being Moderated
    If you are on 10g and above, you can use the shrink command . If it contains no records, you may want to export it, drop the original one and import it back as well.

    Just a thought.
    Aman....
  • 5. Re: Not able to reclaim the space from table
    712089 Newbie
    Currently Being Moderated
    Hi Aman,

    I am confused as there is no records in this table but still this object consuming 4 GB space as per dba_segment view
    Is this correct or i am using worng approch.

    Thanks
  • 6. Re: Not able to reclaim the space from table
    sb92075 Guru
    Currently Being Moderated
    GourG wrote:
    Hi Aman,

    I am confused as there is no records in this table but still this object consuming 4 GB space as per dba_segment view
    Is this correct or i am using worng approch.
    You are using wrong approach.
  • 7. Re: Not able to reclaim the space from table
    712089 Newbie
    Currently Being Moderated
    I used below script for finding Table Fragmentation and its giving below output. Thats why i am tring to recliam this space.

    (http://www.oracle-ckpt.com/table-fragmentation/ )

    select t.owner,t.table_name, s.tablespace_name,
    round(s.bytes/1024/1024) actual_MB,
    round((t.num_rows+1) * t.avg_row_len/1024/1024) optimal_MB,
    round(s.bytes/1024/1024) -
    round((t.num_rows+1) * t.avg_row_len/1024/1024) CLAIMABLE_MB
    from dba_tables t, dba_segments s
    where t.owner = s.owner and s.owner='&owner_name'
    and t.table_name = s.segment_name
    and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) > 200
    order by CLAIMABLE_MB desc
    /


    OWNER TABLE_NAME TABLESPACE_NAME ACTUAL_MB OPTIMAL_MB CLAIMABLE_MB
    DWH1 DWH_SERVICE_BILLING_FACT DWH1_DT 4096 0 4096

    Edited by: GourG on Apr 5, 2012 9:19 PM
  • 8. Re: Not able to reclaim the space from table
    sb92075 Guru
    Currently Being Moderated
    nobody here prevents you from reclaiming the space
  • 9. Re: Not able to reclaim the space from table
    712089 Newbie
    Currently Being Moderated
    Thanks for your responce but i got the solution.
    The initial extent for this table is 4GB .thats why we can not go below this space.
    STORAGE(INITIAL 4294967296
  • 10. Re: Not able to reclaim the space from table
    EdStevens Guru
    Currently Being Moderated
    GourG wrote:
    Thanks for your responce but i got the solution.
    The initial extent for this table is 4GB .thats why we can not go below this space.
    STORAGE(INITIAL 4294967296
    Is this a locally or dictionary managed tablespace?

    If, as it should be, the table should inherit its storage parameters from the TS. And there is probably little justification for having such a large initial extent.
  • 11. Re: Not able to reclaim the space from table
    jgarry Guru
    Currently Being Moderated
    EdStevens wrote:
    GourG wrote:
    Thanks for your responce but i got the solution.
    The initial extent for this table is 4GB .thats why we can not go below this space.
    STORAGE(INITIAL 4294967296
    Is this a locally or dictionary managed tablespace?

    If, as it should be, the table should inherit its storage parameters from the TS. And there is probably little justification for having such a large initial extent.
    It originally inherits it's storage parameters from the TS, but INITIAL can be changed for object creation. Remember what the COMPRESS parameter from exp does - the default puts the whole size into INITIAL, and N keeps the current sizing, and you cannot alter INITIAL before you export. Also remember if you are using autoallocate you can avoid having superflous smaller sized extents by using an INITIAL greater than the appropriate one of the documented four sizes of extents.

    To the OP: Truncate will allow you to give space back to the tablespace, but you would need to shrink the tablespace to see it in the OS. Whether you want to drop and recreate the table with a new INITIAL probably depends if you will ever migrate the table elsewhere or use up the 4G again. I would, just because I don't like leaving time bombs around.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points