RE: Deleting database

As far as I can understand your question you are copying your production environment to test. So, test should be a copy, and not an export/import logical represantation of prod. Otherwise your tables/indexes will be reorganized every time you create the new test database. This means re-analyzing statistics (see another HOT topic today), and ending up with a non-represntative version of your production database, at least from the SQL-tuning/optimizing point of view.

So, I would suggest to take rman, or the old-fashioned well working alter tablespace begein/end backup scenario, and clone your production database. This will give you the physical copy. I've done this many times on unix flavours, never on M$ OS's I like to keep it that way ;-). I guess you might find some nice articles about this on metalink, searching with the keywords clone database.

Regards, Carel-Jan

===
If you think education is expensive, try ignorance. (Derek Bok) ===

At 12:14 30-12-03 -0800, you wrote:
>That's right - you would have to drop all schema owners. In my
>opinion it is simpler and easier task to automate dropping of
>all owners followed by one full import compared to task of
>automating database deletions followed by database creations
>then doing full import in very last step.
>
>DOS environment offers weak (better word is miserable) error
>handling, so unless you resort to non M$ scripting languages
>to do this task for you I believe you'd be better off to stick
>with simpler of two approaches.
>
>Another reason to try avoiding tearing/re-creating databases
>lies in the fact that when you create new database on WinXYZ
>platform, newly created data files are always fragmented at
>file level (which may not be overly fact important for your
>test database but it is an ugly site to look at nevertheless).
>
>Branimir
>
> >
> > Branimir,
> >
> > Correct me if I am wrong, but if I used your approach of
> > dropping schema
> > owner then if I have 25 schemas on my test db, I would have
> > to drop ALL
> > of them? I would think that dropping ALL schemas would equal removing
> > entire database.
> >
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Branimir Petrovic
> INET: BranimirP_at_cpas.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: Carel-Jan Engel
  INET: cjpengel.dbalert_at_xs4all.nl

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).