truncate and delete

[feroz]

Hi again,

when I use delete command, does that mean it will deallocate the free space and can be use by other table within the same tablespace? I know if I use truncate command I be able to deallocate the free space. Anybody could give me some explanation regarding the space deallocation using the delete and truncate command, please?

Thank you in advance.

regards,
feroz

[Mr.Hanky]

Truncate will free up the space for the table and it's index, delte will not. Delete will also generate rollback since it is DML, truncate will not. Truncate will also be much MUCH faster than delete, especially on bigger tables.

You should use caution with truncate though, it is a one way door and there is no going back, bwoo harr harr harrr.

MH

[feroz]

Hi,

Thank you for your reply. Is there a way I could delete specific records and deallocate the free space rather than using the truncate command which will totally destroy all the records?

Thank you.

regards,
feroz

[gpsingh]

use CTAS(global temporary table) with different storage parameters ,delete records ,
drop old table , agains use CTAS with new storage parameter from global temorary table.

[feroz]

HI,

What is CTAS? Where can I read about this? Never heard of this before.

regards,
feroz

[tycho]

Hi,

You can also do a move of the table in the tablespace after the delete.

ALTER TABLE tblname MOVE TABLESPACE tblspcname;

You can also add a storage clause if you want.

Don't forget to rebuild the indexes as they become unusable.

You don't have to care about grants, triggers and constraints oracle will keep them valid.

HTH

Tycho

[Shyamis]

Hi,

I know you can release unused space. Check the ALTER TABLE syntax. I think it was ALTER TABLE table_name
DEALLOCATE UNUSED. This can be done for the indexes as well!

Thanks,
-S

[tycho]

Hi,

With ALTER TABLE table_name DEALLOCATE UNUSED; you can only release space above the high watermark.

So no big if you wanted to reorganize the table after a delete which creates space under the high water mark.

HTH,

Tycho

[feroz]

Hi,

Thanks again. "Alter table table_name deallocate unused" might do for me.

regards,
feroz