Re: Delete vs. truncate to free up spaces.

> "Nguyen, David M" wrote:
>
> I am using delete command to delete million records in several tables
> to free up space in tablespace. I understand delete command does not
> release unused spaces as truncate command but I could not use truncate
> to delete ALL records in table as I need to keep one month old of
> records in table. Please advise a better method I can use to free up
> spaces.
>
> Thanks,
> David

David,

Several solutions. If you have paid for the partitioning option, you can truncate partitions.
Otherwise, it may be faster to do a CREATE TABLE AS SELECT with nologging, parallel, blahblah, to select all the lines you want to keep, truncate the table, and INSERT ... SELECT back in the same manner. Better to have your database in restricted mode then. I used this method in a (euro-zone) bank to delete everything but CHF, GBP and the like when we waved farewell to national currencies, worked pretty efficiently.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).