Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to refresh

RE: How to refresh

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 16 Dec 2003 07:44:25 -0800
Message-ID: <F001.005DA175.20031216074425@fatcity.com>


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
ineyman_at_perceptron.com

-----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
ineyman_at_perceptron.com

-----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: rlsmith_at_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_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: Igor Neyman
  INET: ineyman_at_perceptron.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: Smith, Ron L.
  INET: rlsmith_at_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_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: Igor Neyman
  INET: ineyman_at_perceptron.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). Received on Tue Dec 16 2003 - 09:44:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US