Re: Deleting database

I think that if you use exactly the same file names and db structure in both of your
database, you can simply ( if it is possible) bring down the production database
and copy the files to the test database.

Yechiel Adar
Mehish
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 31, 2003 3:39 PM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

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