14 Replies Latest reply: Jul 6, 2010 5:23 AM by 718305 RSS

    Release of space after delete/truncate table

    718305
      Hello,

      How does release of space after delete/truncate table works? Is the space used before deletion released once delete is complete or not? Will I see the space occupied by deleted table as free in dba_segments or will I need to reorganize the table (drop and recreate again?). Reason why I am asking is that I can see table with 0 rows, but in dba_segment I can see it is occupying few gigabytes....

      Thank you
        • 1. Re: Release of space after delete/truncate table
          CKPT
          Hi,

          even if you delete space wont release exactly, and HWM will never come down, until you truncate the table.
          if you truncate you will loss whole data,

          so if you reorganize by using 1) move 2) ctas 3) exp/imp 4) redifination then its like new table, so the objects wil be created newly and will insert rows one by one.

          Thanks
          • 2. Re: Release of space after delete/truncate table
            asifkabirdba
            There are 3 ways to do for reclaim the space.

            1.Export/Import
            2.Alter table Move
            3.Create table as (CTAS)

            CTAS has it own draw backs which misses out so may values

            Alter table would be an ideal option but you need space for the table which will be doubled during the move and then you have truncate the old table
            Rebuild Index on that

            What is average size of the row in the table where you see Rowchaining/ migration.
            or what is the size of the row when you have values fully occupied by the coulumn.
            say varchar2(10) mean--insert with 10 characters and similiarily ofr all the cloumns and check the size of the row in KB ..If the size is more than 8KB it going to chain in mutiple blocks because your block size is 8KB

            Export /import is also an ideal method..But it all depends on your environment and other things



            Regards
            Asif Kabir
            • 3. Re: Release of space after delete/truncate table
              695836
              Hi,
              Another way is alter table shrink command.This does not need to rebuild indexes.
              • 4. Re: Release of space after delete/truncate table
                718305
                Thx a lot for quick replies. OK so if I got it right after simple delete of whole table I will not see any change in occupancy in dba_segments unless I do kind of reorganization,is that correct?
                Additional question - is there any diff from release of space point of view between delete and truncate ?
                • 5. Re: Release of space after delete/truncate table
                  695836
                  Yes,
                  Yes, a hell lot of difference.
                  See this
                  • 6. Re: Release of space after delete/truncate table
                    618702
                    Dear asifkabirdba,

                    There is one more way to claim the space;
                    alter table 'table_name' allocate extent;
                    Dear tdkuser,

                    I think truncate is the best option however if you only want to delete a group of data you have to use the delete command.

                    If you shrink the space and shrink the space with the compact option, the clustering factor on the indexes may increase and also as far as i know when you move a table their indexes may become invalid. At the end those options will release the space allocated.

                    Hope it Helps,

                    Ogan
                    • 7. Re: Release of space after delete/truncate table
                      781937
                      Reason might be at initial cause table wud have been created with large initial value. if you check the create table script, in that check for the value of initial. You wil find the cause of utilizing more space in server. Take a backup of the table script. Drop the table. Recreate with less initial value.
                      • 8. Re: Release of space after delete/truncate table
                        718305
                        Thx Hashmi. I was aware that delete is DML and truncate DDL statement which can't be rolled back. however I was not sure about space handling after particular statement. I see in link you posted following:
                        "TRUNCATE:
                        This is the DML command. This command delete the data from table. But there is one difference from ordinary delete command. Truncate command drop the storage held by this table. Drop storage can be use by this table again or some other table."

                        Does this mean that I do not need to care about reorganize after truncate? Will I see after truncate released space in dba_segments for truncated table?
                        • 9. Re: Release of space after delete/truncate table
                          618702
                          Truncate is a DDL command, not a DML command?

                          Truncate will release the space claimed by the table (datafiles).

                          Ogan

                          Edited by: Ogan Ozdogan on 06.Tem.2010 11:03

                          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1001.htm#i2099120
                          • 10. Re: Release of space after delete/truncate table
                            695836
                            Hi,
                            If you observer,in the same link another poster corrects it. Yes,with truncate whatever space is reserved by the table is freed.
                            • 11. Re: Release of space after delete/truncate table
                              718305
                              one last, probably dummy question, but just to be sure :)
                              - truncate will release all reserved space except initial extend for truncated table, correct?
                              • 12. Re: Release of space after delete/truncate table
                                695836
                                Yes,
                                That initial extent is allocated as soon as you create a table.
                                SQL> create table trunc_t (n number);
                                
                                Table created.
                                
                                SQL> select segment_name,extents,bytes,initial_extent,next_extent from user_segments where segment_name='TRUNC_T';
                                
                                SEGMENT_NAME
                                --------------------------------------------------------------------------------   EXTENTS      BYTES INITIAL_EXTENT NEXT_EXTENT
                                ---------- ---------- -------------- -----------
                                TRUNC_T
                                         1      65536          65536
                                Here you see next_extent value is null as table is empty.
                                • 13. Re: Release of space after delete/truncate table
                                  618702
                                  Here is a little illustration for you;
                                  SQL> conn ogan/password
                                  Connected.
                                  SQL> create table ogan_deneme as select * from all_objects;
                                  
                                  Table created.
                                  
                                  SQL> select count(*) from ogan_deneme;
                                  
                                    COUNT(*)
                                  ----------
                                      228470
                                  
                                  SQL> set line 1000
                                  SQL> set pagesize 1000
                                  
                                  SQL> select * from dba_segments where owner='OGAN';
                                  
                                  OWNER    SEGMENT_NAME        PARTITION_NAME           SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_
                                  ------------------------------ 
                                  OGAN      OGAN_DENEME          TABLE              SYSTEM                                 854       319981   *30408704*       *1856*         *44*          65536                       1  2147483645                       1               1          854 DEFAULT
                                  
                                  SQL> truncate table ogan_deneme;
                                  
                                  Table truncated.
                                  
                                  SQL> select * from dba_segments where owner='OGAN';
                                  
                                  OWNER    SEGMENT_NAME        PARTITION_NAME           SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_
                                  ------------------------------ 
                                  OGAN      OGAN_DENEME           TABLE              SYSTEM                                 854       319981      *65536*          *4*          *1*          65536                       1  2147483645                       1               1          854 DEFAULT
                                  
                                  SQL>
                                  Hope it Helps,

                                  Ogan
                                  • 14. Re: Release of space after delete/truncate table
                                    718305
                                    thanks a lot to all of you guys. it's clear for me now.