Because there is a lot that could be overlooked, I prefer to do it the other way around:
create table tmp_tbl nologging as select * from big_table where (rows you want to keep); truncate table big_table; alter trigger trigger_name disable; (for each trigger on big_table) alter constraint constraint_name disable; (for each constraint) alter index index_name unusable; (for each index) alter table big_table nologging; insert /*+ APPEND */ select * from tmp_tbl; commit; alter table big_table logging; alter index index_name rebuild nologging; alter constraint constraint_name enable; (consider novalidate where appropriate) alter trigger trigger_name enable; @?/rdbmsa/admin/utlrp.sql That way, you're a lot less likely to overlook a grant or synonym. -Mark -----Original Message----- From: anu [mailto:[EMAIL PROTECTED] Sent: Mon 12/22/2003 6:59 PM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Delete vs. truncate to free up spaces. And synonyms will have to be re-created. (drop and create). Grants will have to be given. Jared Still <[EMAIL PROTECTED]> wrote: .. and if your table is not partitioned, consider using 'CREATE TABLE AS' with WHERE clause that eliminates the rows you wish to delete, recreate indexes and constraints on the new table, drop the old table, rename the new to the old. Keep in mind that stored procedures and triggers that reference the table will need to be recompiled. Jared On Mon, 2003-12-22 at 14:44, Jacques Kilchoer wrote: > This is one of the cases where a partitioned table can be of great use. What version > of Oracle? Standard or Enterprise Edition? > With a partitioned table you can say > alter table ... drop partition ... ; > to easily get rid of a large chunk of data and release the space. > > See > Oracle9i Database Concepts Release 2 (9.2) > Part Number A96524-01 > Chapter 11 > Partitioned Tables and Indexes > http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767 > > or > > http://tinyurl.com/362ba > > > -----Original Message----- > Nguyen, David M > > 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 > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jacques Kilchoer > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _____ Do you Yahoo!? Free Pop-Up Blocker - Get it now <http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/>
<<winmail.dat>>
