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