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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).