ORA-02297: cannot disable constraint
(SCHEMANAME.PK_TABLENAME) - dependencies exist
ISSUE:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies
exist
Restore TABLE backup
Schema Status
-------------
select username from dba_users where username=upper('&username');
Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;
TRUNCATE TABLE
---------------------------
spool TARGET_TABLENAME_TRUNCATE.log
set echo on term on feed on timing on
truncate table SCHEMANAME.TABLENAME;
truncate table SCHEMANAME.TABLENAME
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints
where TABLE_NAME='&TABLE_NAME';
CONSTRAINT_NAME C
TABLE_NAME
STATUS
------------------------------ - ------------------------------ -------
SYS_CONSTRAINT1 C
TABLENAME
ENABLED
PK_TABLENAME P
TABLENAME
ENABLED
alter table SCHEMANAME.TABLENAME DISABLE constraint SYS_CONSTRAINT1;
alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME;
ERROR at line 1:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) -
dependencies exist
SOLUTION:
alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME cascade;
Table altered.
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints
where TABLE_NAME='TABLE_NAME';
Steps for TABLE REFRESH:-
-----------------------
1. Capture the SOURCE database TABLE ROW COUNT which are to be refreshed.
2.Take EXPORT of required TABLE(s) at SOURCE Database.
3. Copy the EXPORT DUMPFILE(s) to DESTINATION Database Server.(SCP)
4. Take EXPORT of required TABLE(s) at DESTINATION Database.(Recommended)
5. TRUNCATE the required TABLE(s) to be restored at DESTINATION Database.(Recommended)
6. IMPORT the Copied SOURCE EXPORT DUMPFILE(s) to the DESTINATION Database.
7. THE TARGET TABLE ROW COUNT SHOULD BE SAME AS THE SOURCE TABLE ROW COUNT
CAPTURED.
SOURCE
------
Hostname :
Database Name :
Table(s) Name :
DESTINATION
------
Hostname :
Database Name :
Table(s) Name :
Check for the space availability for taking backup
--------------------------------------------------
df -h or df -gt
df -h /source/backup/location/
cd /source/backup/location/
mkdir EXPORT_REF_NO
chmod 755 EXPORT_REF_NO
cd EXPORT_REF_NO
pwd
/source/backup/location/EXPORT_REF_NO
Schema Status
-------------
select username from dba_users where username=upper('&username');
Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;
EXPDP
-----
col DIRECTORY_PATH for a80;
select * from dba_directories;
create or replace directory DATAPUMP_DIR as '/source/backup/location/';
grant read,write ON DIRECTORY DATAPUMP_DIR to <system>;
alter user <username> quota unlimited on <tablespace_name>; --->
If required
col DIRECTORY_PATH for a80;
select * from dba_directories;
PAR file
--------
vi SOURCE_TABLENAME_EXPDP.par
userid="/ as sysdba"
directory=DATAPUMP_DIR
dumpfile=SOURCE_TABLENAME_EXPDP_%U.dmp
logfile=SOURCE_TABLENAME_EXPDP.log
tables=SOURCE_SCHEMANAME.TABLENAME1,SOURCE_SCHEMANAME.TABLENAME2
JOB_NAME=SOURCE_TABLE_EXPORT
parallel=6
COMPRESSION=ALL
STATUS=60
EXCLUDE=CONSTRAINT,INDEX,TRIGGER
filesize=4G
:wq
nohup expdp parfile=SOURCE_TABLENAME_EXPDP.par &
Check the status of EXPORT
--------------------------
EXP
---
If the size is less better to go for EXP/IMP
nohup exp userid=\'/ as sysdba \' file=SOURCE_TABLENAME_EXP.dmp
log=SOURCE_TABLENAME_EXP.log tables=OWNER.TABLENAME &
-----------------------------------------------------------------------------------------------------
SCP SOURCE DUMP FILES TO DESTINATION
----------------------------------------------------------
@ Source
--------
scp source_tablename_expdp*.dmp oracle@target_hostname:/target/backup/location/EXPORT_REF_NO/
cd /target/backup/location/EXPORT_REF_NO/
scp oracle@source_hostname:/source/backup/location/EXPORT_REF_NO
/source_tablename_expdp*.dmp .
pwd
/target/backup/location/EXPORT_REF_NO/
ls -lrth source_tablename_expdp*.dmp
-----------------------------------------------------------------------------------------------------
Check for the space availability for taking backup
--------------------------------------------------
df -h
df -h /target/backup/location/
cd /target/backup/location/
mkdir EXPORT_REF_NO
chmod 777 EXPORT_REF_NO
cd EXPORT_REF_NO
pwd
/target/backup/location/EXPORT_REF_NO
Schema Status
-------------
select username from dba_users where username=upper('&username');
Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;
EXPDP
-----
col DIRECTORY_PATH for a80;
select * from dba_directories;
create or replace directory DATAPUMP_DIR as
'/target/backup/location/EXPORT_REF_NO/';
grant read,write ON DIRECTORY DATAPUMP_DIR to system;
cd /target/backup/location/EXPORT_REF_NO/
ls -lrth SOURCE_TABLENAME_EXP_%U.dmp --------> scp files are here
-----------------------------------------------------------------------------------------------------------
@ Target
--------
TAKE BACKUP (EXPORT) OF TABLES BEING RESTORED AT DESTINATION
DATABASE
PAR file
--------
vi SOURCE_TABLENAME_EXPDP.par -----> same as above PAR file at SOURCE
DATABASE
:wq
nohup expdp parfile=SOURCE_TABLENAME_EXPDP.par &
Check the status of EXPORT
--------------------------
EXP
---
If the size is less better to go for EXP/IMP
nohup exp userid=\'/ as sysdba \' file=TARGET_TABLENAME_EXP.dmp
log=TARGET_TABLENAME_EXP.log tables=OWNER.TABLENAME &
------------------------------------------------------------------------------------------------------------
@ Target
--------
PAR file
--------
vi IMPORT_SOURCE_TABLENAME_EXP.par
userid="/ as sysdba"
directory=DATAPUMP_DIR
dumpfile=SOURCE_TABLENAME_EXPDP_%U.dmp logfile=IMPORT_SOURCE_TABLENAME_EXP.log
tables=SOURCE_SCHEMANAME.TABLENAME1, SOURCE_SCHEMANAME.TABLENAME2
SCHEMAS=SOURCE_SCHEMANAME1,SOURCE__SCHEMANAME2
JOB_NAME=SOURCE_TABLE_IMPORT
PARALLEL=6
IF REQUIRED:
-----------
TABLE_EXISTS_ACTION=TRUNCATE
REMAP_SCHEMA=SOURCE_SCHEMANAME1:DESTINATION_SCHEMANAME1,
SOURCE_SCHEMANAME2:DESTINATION_SCHEMANAME2
REMAP_TABLESPACE=SOURCE_SCHEMA_TABLESPACE1: DESTINATION_SCHEMA_TABLESPACE1,
SOURCE_SCHEMA_TABLESPACE2: DESTINATION_SCHEMA_TABLESPACE2
EXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX
EXCLUDE=TABLE:in('<TABLE_NAME1>','<TABLE_NAME2>')
:wq
nohup impdp parfile = IMPORT_SOURCE_TABLENAME_EXP.par &
Check the status of IMPORT
--------------------------
Validation of Import of tables
------------------------------
set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from
dba_objects where object_name in ('&object_name') and owner='&owner'
order by object_type, object_name desc
/
NOTE:
THE TARGET TABLE ROW COUNT SHOULD BE SAME AS THE SOURCE TABLE ROW COUNT
CAPTURED
-----------------------------------------------------------------------
No comments:
Post a Comment