In such a case what will happen to the transactions that hit the table (since the triggers have been disabled)?
Regards Naveen > -----Original Message----- > From: Bobak, Mark [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 23, 2003 5:49 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Delete vs. truncate to free up spaces. > > > 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/a > 96524/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/> > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen, Nahata (IE10) 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).
