DBA Clarification
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: DBA Clarification

  1. #1
    Join Date
    Jan 2004

    DBA Clarification


    I have one doubt related to oracle DBA concepts.

    I noticed that whenever a table is created (with data) table space used size is increased and when i truncate any table (with data) table space used size getting released.But if i use delete statement
    table space used size is not gettimg released.Please clarify....

    Note : Obseravations are made through DBA Studio(Oracle 8i).

    vijay mohan ch.

  2. #2
    Join Date
    Jun 2001
    Helsinki. Finland

    Re: DBA Clarification

    Oracle will not reset the HWM on a table after deleting (all the) rows, even if you analyze the statistics. The DDL command truncate, will do that. You will anyway have to analyze after the truncate command in order to have up to date statistics.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    Just to clarify things for the initial poster:
    It's not the HWM resetting that releases space in a tablespace when you use TRUNCATE. HWM position has nothing to do with the space allocated for the table segment.

    When you create the table and fill it with the data, oracle will add aditional extents to the initial ones. When you delete rows from table, the number of extents dos not change, ie it is not decreased, even if they become empty. That space is still allocated fo the table in question.

    When you use TRUNCATE (with the default option DROP STRORAGE) on the other side, oracle release those additional extents, that is it dealocates them from the table. So the table now consists only from the initial extent(s), while the released extents become tablespace's "free space".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width