Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DROP TABLESPACE efficiently??

Re: DROP TABLESPACE efficiently??

From: Joel Garry <joel-garry_at_home.com>
Date: 16 Mar 2005 14:07:04 -0800
Message-ID: <1111010824.273620.323520@o13g2000cwo.googlegroups.com>

dominica_at_gmail.com wrote:
> Hi All,
>
> I have a situation that
> I need to clean up 3 major schemas in a production database.
> Said schemas are AA, BB, CC
>
> And AA has a lot of tablespaces like
> AA_M1 AA_M2 AA_M3.. like 17+ of them
>
> And BB has tablespaces like
> BB_M1 BB_M2 BB_M3
>
> And normally I would do:
>
> drop tablespace AA_M1 include content;
> go to OS level and issue command to delete all the datafiles
> related to that tablespaces.
>
> But my work want to find a very efficient way,
> so the redo log won't get a lot of impact.
>
> Like maybe
> write an automatic script to find all the tables in
> that schema.
>
> Issue:
>
> TRUNCATE table1;
> TRUNCATE table2;
> TRUNCATE table3;
>
> .. and so on ;
>
> ANd then do
> drop tablespace AA include content;
> delete files from OS;
> drop user AA_USER cascade constraint;

You probably want to drop the user before dropping the ts.

>
> I wonder do anyone else have a better idea.
>
> I am talking about this schema own 47 GIG of data.

It might make a difference if you are DMT v. LMT, which platform you are on, which versions, whether there are others using the db while you are doing this, etc.

If you can do it with no one else on, backup, get out of archivelog mode, drop user cascade, drop tablespace including contents and datafiles, go back to archivelog mode, backup.

This takes less than 5 hours on my config with a 35G database, including an import of the data before going back to archivelog mode, and another instance on the box with production users (not including the second backup). YMMV.

jg

--
@home.com is bogus.
Often see some guy on the freeway driving a Hummer with vanity plates
"NO SPAM" and a graphic for http://www.bogosoft.com
Received on Wed Mar 16 2005 - 16:07:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US