This type of activity is intended to be done only when there is no DML activity on the table. If that isn't feasible, then I'm afraid you'd have to do conventional deletes.
-----Original Message----- From: Naveen, Nahata (IE10) [mailto:[EMAIL PROTECTED] Sent: Tue 12/23/2003 1:00 AM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Delete vs. truncate to free up spaces. 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).
<<winmail.dat>>
