Don't forget to disable any insert triggers...
Ron Thomas Hypercom, Inc [EMAIL PROTECTED] "Hit any PHB to continue..." orantdba@nets cape.net To: [EMAIL PROTECTED] Sent by: cc: root@fatcity. Subject: Re: SPACE FREE HOW? com 01/18/02 12:56 PM Please respond to ORACLE-L Hi Seema, In a word NO. From the documentation: Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and makes the space available for other segments in the tablespace. Yo can free only unused space above the high water mark (that is, the point beyound which database blocks have not yet been formatted to receive data). Another lister provided you with the best solution i know of: 1. copy data to a holding table create table holding_table nologging as select * from my_table_that_is_too_big; 2. truncate the table truncate table my_table_that_is_too_big; 3. copy the data over insert into my_table_that_is_too_big select * from holding_table; 4. drop the holding table drop table holding_table; John [EMAIL PROTECTED] wrote: Hi Can I use following command to recover the space. alter table <tablename> deallocate unused; I don't want to truncate and import. Thx -Seema From: "Ron Rogers" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 17 Jan 2002 13:55:47 -0800 Seema, The table has set it'd HWM where the old data resided. The easiest? method of recovering the space is to export the table and then truncate the table followed by importing the table data back into the table. The truncate function will remove all of the data and re-establish the size back to original. ROR mª¿ªm >>> [EMAIL PROTECTED] 01/17/02 03:57PM >>> Hi I deleted millions of rows from diffrent tables and I have not seen any impact on database size.What I have to do to get that free space? Is it necessary to shutdown the database? Thx -Seema _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: oracledbam@hotmReceived: from CONNECT-MTA by galotterail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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). _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).