1 2 Previous Next 17 Replies Latest reply: Jul 16, 2008 8:30 AM by Sven W. RSS

    shrink table after delete

    650063
      Hi,
      I want to remove some data from a Oracle table, too free up some space on hard drive.
      I use delete clause with where condition to delete data, but then after i would need some shrink clause to shrink up the table as i understand.
      I cant use truncate because it doesn't allow to use where clause. I need something like:

      delete from table 1 where condition1;
      shrink table1; --rfree up space                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
        • 1. Re: shrink table after delete
          Etbin
          see alter table deallocate unused and/or shrink space clause (never did myself)

          Regards

          Etbin
          • 2. Re: shrink table after delete
            531871
            Hi

            ALTER TABLE <BBBBB> MOVE;

            It will "free space", since it moves the high water mark.
            If indexes remember to rebuild.

            It can make the world in performance difference if you had 1 mio rows and deleted the 990.000 :-)

            Mette
            • 3. Re: shrink table after delete
              Etbin
              Thanks for the info. Not very familiar with this DBA stuff (at least here is considered like such). As a (rather underprivileged) developer I would just try CTAS if warned about such situation (probably others would see to it without previous notice). Seeing a not yet answered post it was just an indication to the OP.

              Regards

              Etbin
              • 4. Re: shrink table after delete
                Billy~Verreynne
                > I want to remove some data from a Oracle table, too free up some space on
                hard drive.

                Deleting from a table will not free up harddisk space.

                Have a look at the [url http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#sthref35]Oracle® Database Concepts guide.

                In fact, deleting rows from a table may not even free up any freespace at all for Oracle itself to use, as those datablocks may not be moved to the freelist due to the PCTUSED and PCTFREE storage parameters set for that table.
                • 5. Re: shrink table after delete
                  William Robertson
                  Had to check to remind myself, but
                  ALTER TABLE testspace DEALLOCATE UNUSED;
                  trims unused blocks from the end of the table, i.e. lowers the high water mark to the last used block,
                  ALTER TABLE testspace SHRINK SPACE;
                  moves rows around within the table to make better use of the space available, without invalidating indexes (for this the table must have row movement enabled), and of course
                  ALTER TABLE testspace MOVE;
                  rebuilds the table, invalidating the index. While you are at it you could change the PCTFREE and COMPRESS settings e.g.
                  ALTER TABLE testspace MOVE PCTFREE 0 COMPRESS;
                  ALTER INDEX testspace_rn_ix REBUILD COMPRESS;
                  • 6. Re: shrink table after delete
                    Karthick_Arp
                    "too free up some space on hard drive"...

                    experts correct me if iam wrong.

                    When i create a datafile of 1 GB. Even thought I have not yet put any data into it it will be showing a size of 1GB right? Which means a data file of 1 GB has been allocated in the hard drive for oracle to use.

                    So my question is will all your suggestions increase the space in hard drive?

                    Thanks,
                    Karthick.
                    • 7. Re: shrink table after delete
                      Etbin
                      Thanks. I checked all that too, just trying do to my daily good action after seeing a question not answered yet (left out move because of index invalidation and under the assumption there might be a real lack of space - remembering the ... you must delete something else first! messages from ages ago). It makes no harm seeing it repeated and it might be of some use in the future.

                      Regards

                      Etbin
                      • 8. Re: shrink table after delete
                        531871
                        Hello William.

                        Try this line of code with sqltrace active. Look at the number of gets of count(*) after each step.

                        The get count drops after the ALTER TABLE MOVE command - not after the ALTER TABLE DEALLOCATE. How come? If it moved the HWM I would have suspected a lowering af GETS ?? Or did the HWM not move at all?

                        regards
                        Mette

                        drop table mette;

                        create table mette as select * from all_objects;

                        begin
                        for i in 1..20 loop
                        insert into mette select * from all_objects;
                        end loop;
                        end;

                        select count(*) from mette; -- gets 10.000 -- approx. 700.000 rows

                        delete from mette;

                        select count(*) from mette; -- gets 10.000

                        ALTER TABLE mette DEALLOCATE UNUSED;

                        select count(*) from mette; -- gets 10.000

                        alter table mette move;

                        select count(*) from mette; -- gets 3 !!
                        • 9. Re: shrink table after delete
                          Sven W.
                          What I like about the ALTER TABLE ... MOVE action is that this is able to change the storage parameters for the initial extent. Something that all other things besides dropping and recreating the table, won't do. Useful for example when you try to change the INI_TRANS parameter (one of my personal favourites).
                          • 10. Re: shrink table after delete
                            Etbin
                            Thanks for the info. Will be forwarded to the DBA's attention as there are quite frequent reorganizations happening (mainly transparent for the developers, but we are being asked from time to time whether certain applications should be shut down or not before their actions take place)

                            Regards

                            Etbin
                            • 11. Re: shrink table after delete
                              Billy~Verreynne
                              > When i create a datafile of 1 GB. Even thought I have not yet put any data into it it will be showing a
                              size of 1GB right? Which means a data file of 1 GB has been allocated in the hard drive for oracle to
                              use.

                              Yes, with the follow caveats.

                              This does not apply to temporary files - only data files. Temporary files are created as "stubs" and then grow to the specified size. The space is not pre-allocated. Not sure if this behaviour changed with Oracle 11g.

                              Automated extending. This is by default enable. So when enabled, your 1GB datafile can grow, very unexpectedly, to a larger size.
                              • 12. Re: shrink table after delete
                                William Robertson
                                Simply deleting rows doesn't make blocks eligible for deallocation. Try this:
                                truncate table mette reuse storage;

                                CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'mette');

                                SELECT t.blocks used, s.blocks allocated
                                FROM   user_tables t JOIN user_segments s ON s.segment_name = t.table_name
                                WHERE  t.table_name = 'METTE';

                                   USED ALLOCATED
                                ------ ---------
                                      0     20480


                                alter table mette deallocate unused;

                                CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'mette');

                                SELECT t.blocks used, s.blocks allocated
                                FROM   user_tables t JOIN user_segments s ON s.segment_name = t.table_name
                                WHERE  t.table_name = 'METTE';

                                   USED ALLOCATED
                                ------ ---------
                                      0         8


                                alter table mette allocate extent;

                                -- ...same query...

                                   USED ALLOCATED
                                ------ ---------
                                      0        16

                                alter table mette deallocate unused;

                                -- ...same query...

                                   USED ALLOCATED
                                ------ ---------
                                      0         8
                                • 13. Re: shrink table after delete
                                  650063
                                  This way can be done also:

                                  1. DELETE from Table1 where cond1
                                  2. CREATE TABLE Table1_Copy as SELECT * from Table1
                                  3. TRUNCATE Table1
                                  4. INSERT INTO Table1 SELECT * from Table1_Copy
                                  5. DROP TABLE Table1_Copy
                                  • 14. Re: shrink table after delete
                                    Sven W.
                                    Bad solution. You do an expensive delete just to do a truncate after that.

                                    Furthermore you temporarily double the needed tablespace. And this while the primary concern is to shrink the space.

                                    For optimal performance this is possible (not for optimal space)

                                    1. CREATE TABLE Table1_Copy as SELECT * from Table1 where condition1;
                                    2. TRUNCATE Table1
                                    3. INSERT INTO Table1 SELECT * from Table1_Copy
                                    4. DROP TABLE Table1_Copy

                                    Message was edited by:
                                    Sven W.
                                    1 2 Previous Next