RE: Deleting database

Carel-Jan,

Thanks for your insight in the difference between export/import and copying databases. Two factors had me initially thinking of doing export/import: 1) The tables in production are not big and 2) tables are not subject to heavy changes. As it was pointed out before and although I am not in favor of analyzing stats in a regular basis, the need for analyzing statistics will be determined by the circumstances of one's environment.

My test database is more like a test to my logical backups and to have an instance for me to study the tables of this application.

Thanks Branimir, Ron, and Vaidya too for your ideas. Now I have a broader view of the various ways to delete a database.

Regards,

Julio Cesar Quijada-Reina
Programmer Analyst
Computer Services at Alfred State College

-----Original Message-----
Carel-Jan Engel
Sent: Tuesday, December 30, 2003 3:39 PM To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: QuijadaReina, Julio C
  INET: QuijadJC_at_alfredstate.edu

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