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:anu_77_d_at_yahoo.com]
> 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 <jkstill_at_cybcon.com> 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: Jacques.Kilchoer_at_quest.com
> >
> > 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: ListGuru_at_fatcity.com (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: jkstill_at_cybcon.com
>
> 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: ListGuru_at_fatcity.com (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: Jared Still
  INET: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).