DBA Clarification

[vijaymohan]

hi,


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).

regards,
vijay

[julian]

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.

[jmodic]

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".