11 Replies Latest reply: Apr 6, 2012 12:38 PM by jgarry RSS

    Not able to reclaim the space from table

    712089
      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
          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
            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
              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....
                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
                  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
                    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
                      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
                        nobody here prevents you from reclaiming the space
                        • 9. Re: Not able to reclaim the space from table
                          712089
                          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
                            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
                              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.