RE: Delete vs. truncate to free up spaces.
EE has enterprise on the CD, but it's an extra cost option. Make sure the license fees have been paid first, otherwise it could be a messy thing to rip out. BTDT. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, December 22, 2003 7:14 PM To: Multiple recipients of list ORACLE-L Then you should have the partitioning option. Partition your table if you can. > -Original Message- > Nguyen, David M > > It's Oracle8i Enterprise Edition. > > > -Original Message- > Jacques Kilchoer > > 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/c12p > arti.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. -- 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: Goulet, Dick 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).
RE: Delete vs. truncate to free up spaces.
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 &
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/mailta
RE: Delete vs. truncate to free up spaces.
That works also. I guess it really depends on which one is faster, and that is dependent on what % of the data you are trying to remove, and how big the whole thing is. In any case, writing a script to generate grants is just one more thing on the check list, not too hard to do. Jared On Mon, 2003-12-22 at 16:19, Bobak, Mark wrote: > 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/>
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/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/> <>
RE: Delete vs. truncate to free up spaces.
Then you should have the partitioning option. Partition your table if you can. > -Original Message- > Nguyen, David M > > It's Oracle8i Enterprise Edition. > > > -Original Message- > Jacques Kilchoer > > 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/c12p > arti.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. -- 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).
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 constraintson the new table, drop the old table, rename the new tothe old.Keep in mind that stored procedures and triggers that reference the table will need to be recompiled.JaredOn 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 StillINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free Pop-Up Blocker - Get it now
RE: Delete vs. truncate to free up spaces.
It's Oracle8i Enterprise Edition. -Original Message- Jacques Kilchoer Sent: Monday, December 22, 2003 4:44 PM To: Multiple recipients of list ORACLE-L 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/c12p arti.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: Nguyen, David M 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).
RE: Delete vs. truncate to free up spaces.
... 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).
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: [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).
Re: Delete vs. truncate to free up spaces.
you can also do the following: 1. create table as and copy just the records you want to keep. 2. Drop the old table 3. rename new table to old table 4. re-create the indexes. if there are alot, issue them as jobs and do it at the same time. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 22, 2003 5:44 PM > 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: Ryan 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).
RE: Delete vs. truncate to free up spaces.
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).
Delete vs. truncate to free up spaces.
Title: Delete vs. truncate to free up spaces. 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
