This discussion is archived
14 Replies Latest reply: Jul 6, 2010 3:23 AM by 718305 RSS

Release of space after delete/truncate table

718305 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Yes,
    Yes, a hell lot of difference.
    See this
  • 6. Re: Release of space after delete/truncate table
    618702 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks a lot to all of you guys. it's clear for me now.

Legend

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