Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id hBGFxqx26688 for ; Tue, 16 Dec 2003 09:59:52 -0600 X-ClientAddr: 66.27.56.210 Received: from ns3.fatcity.com (rrcs-west-66-27-56-210.biz.rr.com [66.27.56.210]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id hBGFxpo26682 for ; Tue, 16 Dec 2003 09:59:51 -0600 Received: from ns3.fatcity.com (localhost.localdomain [127.0.0.1]) by ns3.fatcity.com (8.12.8/8.12.8) with ESMTP id hBGFo0rj030636 for ; Tue, 16 Dec 2003 07:54:47 -0800 Received: (from root@localhost) by ns3.fatcity.com (8.12.8/8.12.5/Submit) id hBGFAsui010035 for oracle-l@orafaq.com; Tue, 16 Dec 2003 07:10:57 -0800 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005DA16C; Tue, 16 Dec 2003 07:14:38 -0800 Message-ID: Date: Tue, 16 Dec 2003 07:14:38 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Bobak, Mark" Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Bobak, Mark" Subject: RE: How to refresh Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit Ron, Of course, your milage may vary, but this is what I do: 1.) exp userid=system@prod_db 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 userid=system@test_db 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: rlsmith@kmg.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@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: Bobak, Mark INET: Mark.Bobak@il.proquest.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@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).