Re: Truncate and free space

devjnr_at_gmail.com wrote:
> I tried to truncate a table that was 2,200 KB.
>
> Using EM and refreshing table segments to see new used space I instead
> continue to see full 2,200 KB sized table.
>
> Then I do the following:
>
> 1) Log off from EM
> 2) Log on to EM
> 3) View table segments and now I can see correctly new used space:
>
> Used: 8.00
> Allocated: 64.00
>
>
> Is this behavior correct?
>
> Thx to all.

Looks like an EM problem to me.

Truncate is a DDL statement that removes all row data from a table (an its indexes) resetting the high water mark and optionally releases excess extents over the minextents parameter value back to the tablespace(s) the table/index(es) are stored in.

If the table is in a single extent no space will be released.

Via SQLPlus you should be able to query dba_segments and/or dba_extents to immediately see the change. Note that there was a bug on one relatively recent version of Oracle where the rdbms failed to update the base tables under dba_segments after a truncate correctly, but if you can see the correct data after logging out and back in that is not likely the described problem.

HTH -- Mark D Powell -- Received on Tue Aug 29 2006 - 08:39:57 CDT