Tool used - Oracle SQl Developer
Process
=======
1. Make DBLink
2. Take backup of existing prod table, verify same
3. truncate prod table, verify same
4. transfer data from dev to prod table through DB Link
Planned Commands for same
=========================
--make a DBLInk
create database link linkname_value connect to Schemaname_value identified by username_value using 'SID_name';
--source - SCHEMA1.ABC
--target - SCHEMA1.ABC_BACKUP (make the backup table of same specifications as that of master table, better take the same query as SQl DDL to make the table)
INSERT INTO SCHEMA1.ABC_BACKUP SELECT * FROM SCHEMA1.ABC;
--Please confirm that these two tables are identical by following 2 commands (that they have same number of rows)
SELECT COUNT(*) FROM SCHEMA1.ABC_BACKUP;
SELECT COUNT(*) FROM SCHEMA1.ABC;
--Output of above two commands should be equal, this will confirm that backup of the table has been taken successfully
--truncate source_table
TRUNCATE TABLE SCHEMA1.ABC;
-- confirm that the table is truncated
select * from SCHEMA1.ABC;
--this should show zero rows in the table
--Now in next command, we have to import the data from development server to production server
insert into SCHEMA1.ABC select * from SCHEMA1.ABC@linkname_value ;
Practical Problem in REAL SCHNERIO
=============================
--source - ABC
--target - ABC_BACKUP
INSERT INTO SCHEMA1.ABC_BACKUP SELECT * FROM SCHEMA1.ABC;
--Please confirm that these two tables are identical by following 2 commands (that they have same number of rows)
SELECT COUNT(*) FROM SCHEMA1.ABC_BACKUP;
SELECT COUNT(*) FROM SCHEMA1.ABC;
--Output of above two commands should be equal, this will confirm that backup of the table has been taken successfully
--truncate source_table
TRUNCATE TABLE SCHEMA1.ABC;
Error starting at line 12 in command:
TRUNCATE TABLE SCHEMA1.ABC
PROBLEM 1
=========
Error report:
SQL Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
02266. 00000 - "unique/primary keys in table referenced by enabled foreign keys"
*Cause: An attempt was made to truncate a table with unique or
primary keys referenced by foreign keys enabled in another table.
Other operations not allowed are dropping/truncating a partition of a
partitioned table or an ALTER TABLE EXCHANGE PARTITION.
*Action: Before performing the above operations the table, disable the
foreign key constraints in other tables. You can see what
constraints are referencing a table by issuing the following
command:
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
SOLUTION 1
==========
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "ABC";
It shows 3 constraints:
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
SYS_C005497 Check "ID" IS NOT NULL
SYS_C005498 Check "ABC_NAME" IS NOT NULL
SYS_C005499 Primary_KEY (null)
run these commands TO DISABLE THESE CONSTRAINTS
ALTER TABLE SCHEMA1.ABC DISABLE CONSTRAINT SYS_C005497;
ALTER TABLE SCHEMA1.ABC DISABLE CONSTRAINT SYS_C005498;
ALTER TABLE SCHEMA1.ABC DISABLE CONSTRAINT SYS_C005499;
--same constaraints can also be seen oracle sql developer constraints tab of table information. confirm that the constraints are disabled, you can do that but looking into the constraints tabs in the table. When the constraint will be disabled it will show "disabled" in the status.
PROBLEM 2
=========
ALTER TABLE SCHEMA1.ABC DISABLE CONSTRAINT SYS_C005499
Error report:
SQL Error: ORA-02297: cannot disable constraint (SCHEMA1.SYS_C005499) - dependencies exist
02297. 00000 - "cannot disable constraint (%s.%s) - dependencies exist"
*Cause: an alter table disable constraint failed becuase the table has
foriegn keys that are dpendent on this constraint.
*Action: Either disable the foreign key constraints or use disable cascade
SOLUTION 2.1
===========
-- find all the constraints in which the primary key of this table is used as foreign key, following command is adopted from other site)
SELECT
p.table_name "Parent Table",
c.table_name "Child Table",
p.constraint_name "Parent Constraint",
c.constraint_name "Child Constraint"
FROM user_constraints p
JOIN user_constraints c
ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.table_name = UPPER('&table_name');
This will prompt for the table name, please enter "SCHEMA1.ABC" in it.
--Suppose the output is XYZ table names with constraint name
All the constraints in resulting list should be disabled
ALTER TABLE SCHEMA1.XYZ DISABLE CONSTRAINT SYS_C007163;
SOLUTION 2.2
============
--OR simply go with following query but this will not allow you to enable constraints with same fashion
ALTER TABLE SCHEMA1.ABC DISABLE CONSTRAINT SYS_C005499 CASCADE
-- confirm that the constraints are disabled, you can do that but looking into the constraints tabs in the table (see following screen shot). When the constraint will be disabled it will show "disabled" in the status.
TRUNCATE TABLE SCHEMA1.ABC;
-- confirm that the table is truncated
select * from SCHEMA1.ABC;
--this should show zero rows in the table
--enable all the constraints disabled by you
ALTER TABLE SCHEMA1.ABC ENABLE CONSTRAINT SYS_C005497;
ALTER TABLE SCHEMA1.ABC ENABLE CONSTRAINT SYS_C005498;
ALTER TABLE SCHEMA1.ABC ENABLE CONSTRAINT SYS_C005499;
ALTER TABLE SCHEMA1.XYZ ENABLE CONSTRAINT SYS_C007163;
PROBLEM 3
=========
ALTER TABLE SCHEMA1.XYZ ENABLE CONSTRAINT SYS_C007163
Error report:
SQL Error: ORA-02298: cannot validate (SCHEMA1.SYS_C007163) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
*Action: Obvious
SOLUTION 3
==========
This problem is due to mismatch of the value set of primary key and the foreign key, it can solved by either altering the primary key value set of foreign key value set.
--Now in next command, we have to import the data from development server to production server
insert into SCHEMA1.ABC select * from SCHEMA1.ABC@linkname_value;
ALTER TABLE SCHEMA1.XYZ ENABLE CONSTRAINT SYS_C007163;
