RE: How to refresh
Ron - You should consider refreshing the instance by cloning your production database. This way you get an exact replica in all respects, and you can test your backup as a bonus. If you are interested in this method, tell us how you backup your production database. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 16, 2003 8:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: DENNIS WILLIAMS 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).
RE: How to refresh
Ron - As far as losing the new functions, this is one reason we've moved to a 3-instance layout. Production, staging, and test. This takes off a lot of the pressure for overwriting test as you describe. Other than that, it becomes a matter of negotiation. Describe what your options are, and let them decide which option they choose. Also suggest that everything in test (except maybe data changes users have made) should be based on scripts. If they've created functions, they should have scripts to recreate those because that is what they are eventually going to have to do in production (or in staging, if you implement that). Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 16, 2003 8:59 AM To: '[EMAIL PROTECTED]' Ron - You should consider refreshing the instance by cloning your production database. This way you get an exact replica in all respects, and you can test your backup as a bonus. If you are interested in this method, tell us how you backup your production database. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 16, 2003 8:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: DENNIS WILLIAMS 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).
RE: How to refresh
Ron, Of course, your milage may vary, but this is what I do: 1.) exp [EMAIL PROTECTED] file=prod_data_exp.dmp owner=list,of,comma,separated,schemas direct=y 2.) connect to test_db. 3.) Disable all constraints. When you script this, make sure you order it so that referential constraints get disabled before PK/UK. 4.) truncate all tables. 5.) imp [EMAIL PROTECTED] file=prod_data_exp.dmp direct=y full=y ignore=y (Optionally, you could use fromuser/touser if the usernames change between instances.) Hope that helps, -Mark -Original Message- Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: Bobak, Mark 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).
RE: How to refresh
Like I said. There are new packages, procedures, functions, etc... in TEST that we do not want to lose. Ron -Original Message- Sent: Tuesday, December 16, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Ron - You should consider refreshing the instance by cloning your production database. This way you get an exact replica in all respects, and you can test your backup as a bonus. If you are interested in this method, tell us how you backup your production database. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 16, 2003 8:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: DENNIS WILLIAMS 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: Smith, Ron L. 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).
RE: How to refresh
For this kind of situation I do normally like this Store all table names in single file. Exp constraints=n triggers=n indexes=n tables=`cat tables.lst` file=table_data.dmp grants=n Import the full file or by fromuser, touser option. -Original Message- Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: Pandian, Thiru 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).
RE: How to refresh
You can always disable triggers and constraints in existing schema before running import (and then, enable them after import is done). Also, specify CONSTRAINTS=N and TRIGGERS=N when exporting. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: Igor Neyman 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).
RE: How to refresh
Seems like last time I tried to disable constraints Oracle complained and wouldn't let me due to dependant objects or something. Ron -Original Message- Sent: Tuesday, December 16, 2003 9:15 AM To: Multiple recipients of list ORACLE-L You can always disable triggers and constraints in existing schema before running import (and then, enable them after import is done). Also, specify CONSTRAINTS=N and TRIGGERS=N when exporting. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: Igor Neyman 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: Smith, Ron L. 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).
Re: How to refresh
What are all the problems with triggers and constraints? If you're talking about unique/primary/foreign keys, disable constraints and those pesky things that protect the integrity of your data will no longer be in your way. Of course, you will not know whether your data is inconsistent or not, but what we don't know doesn't hurt us. Isn't that an old platitude? If you want to move some data from one database to another, it helps if you have some mechanisms to help, like DATE_CREATED and USER_CREATED columns in each of your database tables. What you're really talking about is called snapshot based replication. You will, however, need some programming to load the data back into the base tables. Let me recommend excellent works of Randall Schwartz, Tom Christiansen, Larry Wall,Sriram Srinivasan, Damian Conway, Tim Bunce and Aligator Descartes. They have written books that can show The Only Right Way (TM) of programming. On 12/16/2003 09:54:48 AM, Smith, Ron L. wrote: I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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). Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
Re: How to refresh
Hi Ron, For just testdata you might take a look at http://www.databee.com/main.htm, this can help you. Dennis' method will work, but doesn't answer your question, because a production copy will overwrite all new functionality as well. However, his suggestion isn't as bad as it might seem on a first sight. How is the user/developer gonig to install all new functionality/triggers/functions etc? He might wan't to test his installation procedures as well, and otherwise you might want him to. So, wrap up all the new functionality in neat installations scripts, take a copy of the production database and start testing the installation scripts. It's the only way to be sure that what's tested in your test databaes will actually get installed in your production database. At 06:54 16-12-03 -0800, you wrote: I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- 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).
RE: How to refresh
Ron, here's an ugly way to do this: Empty your TEST database of data. Drop constraints Indexes. Perform and export of the TEST database with ROWS=N - this will export all Functions, packages and procedures. Import data from production. Import the stuff from step 2 above. This should overlay all functions, packages and procedures from production. Like I said, this is uuugggly. I like Dennis's suggestions - force them to keep scripts of their functions and have them reload them once your refresh the database. One other option is to empty your database, drop indexes and constraints, and bring data one table at a time using insert into table as select * from [EMAIL PROTECTED] Then apply constraints and indexes. I would attempt this approach before the ugly approach above. good Luck Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 16, 2003 10:14 AM To: Multiple recipients of list ORACLE-L Like I said. There are new packages, procedures, functions, etc... in TEST that we do not want to lose. Ron -Original Message- Sent: Tuesday, December 16, 2003 9:04 AM To: Multiple recipients of list ORACLE-L Ron - You should consider refreshing the instance by cloning your production database. This way you get an exact replica in all respects, and you can test your backup as a bonus. If you are interested in this method, tell us how you backup your production database. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 16, 2003 8:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: DENNIS WILLIAMS 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: Smith, Ron L. 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: Mercadante, Thomas F 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).
RE: How to refresh
Here is a sample of the script I run to disable FK constraints: declare lTables DBMS_SQL.VARCHAR2_TABLE; lConstraints DBMS_SQL.VARCHAR2_TABLE; nJ BINARY_INTEGER; BEGIN SELECT table_name, constraint_name BULK COLLECT INTO lTables, lConstraints FROM user_constraints WHERE owner = 'IPN_DBA' AND constraint_type = 'R'; FOR nJ IN 1..lTables.COUNT LOOP DBMS_OUTPUT.PUT_LINE(lTables(nJ) || ': ' || lConstraints(nJ)); -- just for logging EXECUTE IMMEDIATE 'ALTER TABLE ' || lTables(nJ) || ' MODIFY CONSTRAINT ' || lConstraints(nJ) || ' DISABLE'; END LOOP; END; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Seems like last time I tried to disable constraints Oracle complained and wouldn't let me due to dependant objects or something. Ron -Original Message- Sent: Tuesday, December 16, 2003 9:15 AM To: Multiple recipients of list ORACLE-L You can always disable triggers and constraints in existing schema before running import (and then, enable them after import is done). Also, specify CONSTRAINTS=N and TRIGGERS=N when exporting. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: Igor Neyman 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: Smith, Ron L. 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: Igor Neyman 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).
Re: How to refresh
Hi Ron, For just testdata you might take a look at http://www.databee.com/main.htm, this can help you. Dennis' method will work, but doesn't answer your question, because a production copy will overwrite all new functionality as well. However, his suggestion isn't as bad as it might seem on a first sight. How is the user/developer gonig to install all new functionality/triggers/functions etc? He might wan't to test his installation procedures as well, and otherwise you might want him to. So, wrap up all the new functionality in neat installations scripts, take a copy of the production database and start testing the installation scripts. It's the only way to be sure that what's tested in your test databaes will actually get installed in your production database. At 06:54 16-12-03 -0800, you wrote: I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- 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).
RE: How to refresh
Thanks! I'll try it. Ron -Original Message- Sent: Tuesday, December 16, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Here is a sample of the script I run to disable FK constraints: declare lTables DBMS_SQL.VARCHAR2_TABLE; lConstraints DBMS_SQL.VARCHAR2_TABLE; nJ BINARY_INTEGER; BEGIN SELECT table_name, constraint_name BULK COLLECT INTO lTables, lConstraints FROM user_constraints WHERE owner = 'IPN_DBA' AND constraint_type = 'R'; FOR nJ IN 1..lTables.COUNT LOOP DBMS_OUTPUT.PUT_LINE(lTables(nJ) || ': ' || lConstraints(nJ)); -- just for logging EXECUTE IMMEDIATE 'ALTER TABLE ' || lTables(nJ) || ' MODIFY CONSTRAINT ' || lConstraints(nJ) || ' DISABLE'; END LOOP; END; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 10:25 AM To: Multiple recipients of list ORACLE-L Seems like last time I tried to disable constraints Oracle complained and wouldn't let me due to dependant objects or something. Ron -Original Message- Sent: Tuesday, December 16, 2003 9:15 AM To: Multiple recipients of list ORACLE-L You can always disable triggers and constraints in existing schema before running import (and then, enable them after import is done). Also, specify CONSTRAINTS=N and TRIGGERS=N when exporting. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Smith, Ron L. Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: Igor Neyman 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: Smith, Ron L. 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: Igor Neyman 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: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
RE: How to refresh
I do that every night, but I drop the user to be refresh in the test db, is not a big DB. Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- Sent: Tuesday, December 16, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Hi Ron, For just testdata you might take a look at http://www.databee.com/main.htm, this can help you. Dennis' method will work, but doesn't answer your question, because a production copy will overwrite all new functionality as well. However, his suggestion isn't as bad as it might seem on a first sight. How is the user/developer gonig to install all new functionality/triggers/functions etc? He might wan't to test his installation procedures as well, and otherwise you might want him to. So, wrap up all the new functionality in neat installations scripts, take a copy of the production database and start testing the installation scripts. It's the only way to be sure that what's tested in your test databaes will actually get installed in your production database. At 06:54 16-12-03 -0800, you wrote: I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- 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: =?iso-8859-1?Q?Ram=F3n_Estevez?= 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).
RE: How to refresh
If the developers have code in dev they should have DDLs that generate the triggers and changes. I hate it whenDevelopers think they can get away with telling some else to own thier code...Database CODE in my mind always includes the scripts to generate the object in addition to the funtioning body of the code. If they don't then crack the whip and whip those fellows into shape. They shouldn't be just generating changes without code to effect the changesheesh... If they refuse to do thier job correctly then why not run OEM's change management db compare bewteen prod and dev to generate the object changes in a script that you can run after the produciton import. THough you are opening yourself up to being reponsible for code you didn't write. Brad -Original Message- Sent: Tuesday, December 16, 2003 8:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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: Odland, Brad 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).
RE: How to refresh
The methodology we developed for one client goes like this: There are three databases - dev, test, and prod. dev starts as a clone of prod, and fixes, changes and enhancements are developed and unit tested there. One of the REQUIRED products of development is a script that will upgrade prod to the new version. When we are ready for complete testing of a new version of the entire application, test is recreated as a clone of prod. We run the upgrade script against test. If there are any problems, errors, missing stored procedures, or other missing or incorrect versions of database objects after running this script, the script must be corrected, and we start over with a fresh clone of prod. By the way, VERY IMPORTANT: Data changes, such as new, updated or deleted rows in code or control tables are part of the upgrade, not just DDL changes. Once the upgrade script has been run, test is a version beyond prod, and can be tested thoroughly. If errors are found they are corrected and unit tested in DEV, not test, and then put into a corrected upgrade script, we may then correct test from the script, but testing is NOT complete until test is recreated as a clone of prod yet one more time, and upgraded with the script, and run through a battery of tests again. By the time we finish testing, all we should have to do is run the upgrade script against prod and bring prod live in the new version. Then we'll often recreate dev as a clone of prod. -Original Message- Sent: Tuesday, December 16, 2003 9:55 AM To: Multiple recipients of list ORACLE-L I need to do a refresh of a test database using production data. We use import for this. In the past we have always dropped the schemas (4 or 5), recreated the schemas, and then did a full import with ignore=n. This time the user does not want to lose any of the new functions and procedures that are in test, but not in prod. Instead, they would like to just refresh the table data. Last time we tried this we had all kinds of trouble with functions, triggers, constraints, etc... and ended up doing a full import. Is there a tried and true way to just refresh the table data without losing anything else and without having all the problems with triggers and constraints? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack 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).
RE: How to refresh fast a MV from another MV?
Hi DBA's,=0D I have the following problem,=0D on a node I have some tables an a MV_master (done with FAST refresh) build = over these tables.=0D I have to bribg this MV_master on the DB servers but =0D It's not possible to do this using snapshot log on MV_master and building t= he MV_slave's using the refresh FAST (you get the ORA-12015); so I could us= e only the COMPLETE refresh but it's too long for me.=0D How can I workaround this problem?=0D =0D Any suggestion will be greatly appreciated!=0D Thanks in advance to all.=0D =0D Francesco=0D Perhaps you should consider moving files around - transportable tablespaces spring to mind. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
