SQL> delete from ANUJ.DEPT where deptno=10;
delete from ANUJ.DEPT where deptno=10
*
ERROR at line 1:
ORA-02292: integrity constraint (ANUJ.FK_DEPTNO) violated - child record found
set heading off echo off pages 999 long 90000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
select dbms_metadata.get_ddl('TABLE','DEPT','ANUJ') output from dual;
-- Index
select dbms_metadata.get_dependent_ddl('INDEX','DEPT','ANUJ') output from dual;
-- Constraint
select dbms_metadata.get_dependent_ddl('CONSTRAINT','DEPT','ANUJ') output from dual;
-- REF_CONSTRAINT
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','DEPT','ANUJ') output from dual;
-- Trigger
select dbms_metadata.get_dependent_ddl('TRIGGER','DEPT','ANUJ') output from dual;
set linesize 300 col OWNER for a15 col R_OWNER for a15 col TABLE_NAME for a25 col SEARCH_CONDITION for a15 col constraint_type for a15 SELECT owner , table_name, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='XXX' AND constraint_name='FK_DEPTNO'
;
-- to enable the PK of the given table
define owner='ANUJ'
define table_name='DEPT'
select 'alter table '||a.OWNER||'.'||a.TABLE_NAME||' enable constraint '||a.CONSTRAINT_NAME||';'
from all_constraints a
where a.TABLE_NAME=nvl(upper('&table_name'),a.table_name)
and a.OWNER=nvl(upper('&owner'),a.owner)
and a.CONSTRAINT_TYPE in ('P','U');
-- to enable FK referencing the PK of the given table
define owner='ANUJ'
define table_name='DEPT'
select 'alter table '||b.OWNER||'.'||b.TABLE_NAME||' enable constraint '||b.CONSTRAINT_NAME||';'
from all_constraints a, all_constraints b
where a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME
and a.TABLE_NAME=nvl(upper('&table_name'),a.table_name)
and a.OWNER=nvl(upper('&owner'),a.owner)
and b.CONSTRAINT_TYPE='R'
;
set linesize 300
col PARENT_OWNER for a15
col PARENT_TABLE for a15
col PARENT_CONSTRAINT for a15
col CHILD_OWNER for a15
col CHILD_TABLE for a15
col CHILD_CONSTRAINT for a15
SELECT
pk.owner AS parent_owner,
pk.table_name AS parent_table,
pk.constraint_name AS parent_constraint,
fk.owner AS child_owner,
fk.table_name AS child_table,
fk.constraint_name AS child_constraint
FROM
dba_constraints pk
JOIN
dba_constraints fk ON pk.constraint_name = fk.r_constraint_name -- Join condition: FK references PK
WHERE
pk.constraint_type = 'P' -- Select only primary keys for the parent
and pk.table_name='DEPT'
and pk.owner='ANUJ'
and fk.owner='ANUJ'
AND fk.constraint_type = 'R' -- Select only foreign keys for the child
;
=====================================================================
define 1='ANUJ'
define 2='EMP'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('REF_CONSTRAINT', constraint_name, owner)
FROM all_constraints
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'))
AND constraint_type = 'R';
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
ALTER TABLE "ANUJ"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "ANUJ"."DEPT" ("DEPTNO") ENABLE;
=============================================================
from Web https://connor-mcdonald.com/2018/06/25/ddl-for-constraints-subtle-things/
set pagesize 0
set long 90000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
create table anuj.tab1(id number, name varchar2(100),
constraint pk_tab1_id primary key(id));
create table anuj.tab2(id number, name varchar2(100),
constraint pk_tab2_id primary key(id));
create table anuj.tab3(id number, name varchar2(100), int_id number,
constraint pk_tab3_id primary key(id),
constraint fk_tab1_id foreign key(int_id) references anuj.tab1(id),
constraint fk_tab2_id foreign key(int_id) references anuj.tab2(id));
set serverout on
begin
for i in (
select t.table_name,
dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, owner) ddl
from dba_tables t
where table_name = 'TAB3'
)
loop
dbms_output.put_line(i.ddl);
--execute immediate i.ddl;
end loop;
end;
/
ALTER TABLE "ANUJ"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID")
REFERENCES
"ANUJ"."TAB1" ("ID") ENABLE;
ALTER TABLE "ANUJ"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY
("INT_ID")
REFERENCES "ANUJ"."TAB2" ("ID") ENABLE;
=====================
set linesize 300 pagesize 300
col XCOLUMNS for a20
col R_COLUMNS for a20
col type for a8
col TABLE_NAME for a29
col R_OWNER for a12
col status for a12
col R_CONSTRAINT_NAME for a20
col CONSTRAINT_NAME for a20
col OWNER for a20
col R_TABLE_NAME for a20
select rcon.owner as r_owner, rcon.constraint_name as r_constraint_name,rcon.status, rcon.table_name as r_table_name,
listagg (rcol.column_name, ', ') WITHIN GROUP (ORDER BY rcol.owner, rcol.table_name, rcol.constraint_name) R_COLUMNS,
rcon.constraint_type as type, con.owner, con.table_name, con.constraint_name,
listagg (col.column_name, ', ') WITHIN GROUP (ORDER BY col.owner, col.table_name, col.constraint_name) XCOLUMNS
from all_constraints rcon
join all_cons_columns rcol on rcol.owner=rcon.owner and rcol.table_name=rcon.table_name and rcol.constraint_name=rcon.constraint_name
left join all_constraints con on rcon.owner = con.r_owner and rcon.constraint_name = con.r_constraint_name
left join all_cons_columns col on col.owner=con.owner and col.table_name=con.table_name and col.constraint_name=con.constraint_name
and rcol.position = col.position
where rcon.owner = upper('ANUJ1')
and rcon.table_name = upper('DEPT')
--and rcon.constraint_type in ('P','U')
--and rcon.constraint_name = nvl(upper(p_constraint),rcon.constraint_name)
and rcon.status = 'ENABLED'
group by rcon.owner, rcon.constraint_name,rcon.status, rcon.table_name, rcon.constraint_type,con.owner, con.table_name, con.constraint_name
order by rcon.owner, rcon.constraint_name, rcon.constraint_type;
create delete script
below sql generate script
set linesize 300 pagesize 0
col XCOLUMNS for a20
col R_COLUMNS for a20
col type for a8
col TABLE_NAME for a29
col R_OWNER for a12
col status for a12
col R_CONSTRAINT_NAME for a15
col OWNER for a15
col R_TABLE_NAME for a18
col CONSTRAINT_NAME for a20
select 'delete from ' ||OWNER||'.' || TABLE_NAME ||' where '|| XCOLUMNS ||' in (select '|| R_COLUMNS|| ' from ' || R_OWNER ||'.'||R_TABLE_NAME ||' WHERE deptno=10 );'
--select 'delete from ' ||OWNER||'.' || TABLE_NAME ||' where '|| XCOLUMNS ||' in (select '|| R_COLUMNS|| ' from ' || R_OWNER ||'.'||R_TABLE_NAME ||' WHERE CREATE_DATE <''01-JAN-24'' );'
--select R_OWNER ,R_CONSTRAINT_NAME , STATUS , R_TABLE_NAME , R_COLUMNS, TYPE ,OWNER,TABLE_NAME ,CONSTRAINT_NAME , XCOLUMNS
from (select rcon.owner as r_owner, rcon.constraint_name as r_constraint_name,rcon.status, rcon.table_name as r_table_name,
listagg (rcol.column_name, ', ') WITHIN GROUP (ORDER BY rcol.owner, rcol.table_name, rcol.constraint_name) R_COLUMNS,
rcon.constraint_type as type, con.owner, con.table_name, con.constraint_name,
listagg (col.column_name, ', ') WITHIN GROUP (ORDER BY col.owner, col.table_name, col.constraint_name) XCOLUMNS
from all_constraints rcon
join all_cons_columns rcol on rcol.owner=rcon.owner and rcol.table_name=rcon.table_name and rcol.constraint_name=rcon.constraint_name
left join all_constraints con on rcon.owner = con.r_owner and rcon.constraint_name = con.r_constraint_name
left join all_cons_columns col on col.owner=con.owner and col.table_name=con.table_name and col.constraint_name=con.constraint_name
and rcol.position = col.position
where rcon.owner = upper('ANUJ')
and rcon.table_name = upper('DEPT')
--and rcon.constraint_type in ('P','U')
--and rcon.constraint_name = nvl(upper(p_constraint),rcon.constraint_name)
--and rcon.status = 'ENABLED'
group by rcon.owner, rcon.constraint_name,rcon.status, rcon.table_name, rcon.constraint_type,con.owner, con.table_name, con.constraint_name
order by rcon.owner, rcon.constraint_name, rcon.constraint_type
)
where 1=1
--and TABLE_NAME is not null
--and CONSTRAINT_NAME ='FK_DEPTNO'
;
set linesize 300 pagesize 300
===define schema_name='ANUJ1'
define u_table_name='DEPT'
col table_name format a30
col constraint_name format a30
col r_constraint_name format a30
set linesize 200 trimspool on
set pagesize 100
with fks as (
select c1.owner,c1.table_name
, c1.constraint_name
, c2.r_constraint_name
from dba_constraints c1
left join dba_constraints c2
on (
c2.owner = c1.owner
and c2.table_name = c1.table_name
and c2.constraint_type='R'
)
and c1.constraint_type in ('U','P')
where c1.owner = '&schema_name'
and c2.r_constraint_name is not null
),
pks as (
select c1.owner,c1.table_name, c1.constraint_name, null r_constraint_name
from dba_constraints c1
where c1.constraint_name in (
select r_constraint_name
from fks
)
and c1.owner = '&schema_name'
),
all_data as (
select owner
,table_name
, constraint_name
, r_constraint_name
from fks
union
select owner
,table_name
, constraint_name
, r_constraint_name
from pks
)
select distinct owner||'.'||lpad(' ', 2 * (level - 1))|| table_name table_name
, constraint_name
, r_constraint_name
, level
from all_data
start with table_name = '&u_table_name'
connect by nocycle prior constraint_name = r_constraint_name
and level <=5
order by level
/
TABLE_NAME CONSTRAINT_NAME R_CONSTRAINT_NAME LEVEL
------------------------------ ------------------------------ ------------------------------ ----------
DEPT PK_DEPT 1
EMP PK_EMP PK_DEPT 2
EMP1 PK_EMP1 PK_DEPT 2
set linesize 300 pagesize 300
col SCHEMA_NAME for a20
col COLUMN_NAME for a20
col CONSTRAINT for a20
col constraint_type_desc for a30
select ctr.owner as schema_name,
ctr.table_name,
ctr.constraint_name,
col.column_name,
ctr.search_condition as constraint,
ctr.status,
ctr.constraint_type,
decode(CONSTRAINT_TYPE,
'C', 'Check constraint on a table',
'P','Primary key',
'U', 'Unique key',
'R', 'Referential integrity',
'V', 'With check option, on a view',
'O', 'With read only on a view',
'H', 'Hash expression',
'F','Constraint that involves a REF column',
'S','Supplemental logging'
) constraint_type_desc
from sys.dba_constraints ctr
join sys.dba_cons_columns col
on ctr.owner = col.owner
and ctr.constraint_name = col.constraint_name
and ctr.table_name = col.table_name
where 1=1
-- and ctr.constraint_type = 'C'
and ctr.owner='ANUJ1'
and ctr.table_name='DEPT'
--and column_name != 'LABEL'
order by ctr.owner, ctr.table_name, ctr.constraint_name;
=======define schema_name='ANUJ1'
define u_table_name='DEPT'
col table_name format a30
col constraint_name format a30
col r_constraint_name format a30
set linesize 200 trimspool on
set pagesize 100
with fks as (
select c1.owner,c1.table_name
, c1.constraint_name
, c2.r_constraint_name
from dba_constraints c1
left join dba_constraints c2
on (
c2.owner = c1.owner
and c2.table_name = c1.table_name
and c2.constraint_type='R'
)
and c1.constraint_type in ('U','P')
where c1.owner = '&schema_name'
and c2.r_constraint_name is not null
),
pks as (
select c1.owner,c1.table_name, c1.constraint_name, null r_constraint_name
from dba_constraints c1
where c1.constraint_name in (
select r_constraint_name
from fks
)
and c1.owner = '&schema_name'
),
all_data as (
select owner
,table_name
, constraint_name
, r_constraint_name
from fks
union
select owner
,table_name
, constraint_name
, r_constraint_name
from pks
)
select distinct owner||'.'||lpad(' ', 2 * (level - 1))|| table_name table_name
, constraint_name
, r_constraint_name
, level
from all_data
start with table_name = '&u_table_name'
connect by nocycle prior constraint_name = r_constraint_name
and level <=5
order by level
/
set serverout on
begin
for i in (
select t.table_name,
dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, owner) ddl
from dba_tables t
where table_name = 'TAB3'
)
loop
dbms_output.put_line(i.ddl);
--execute immediate i.ddl;
end loop;
end;
/delete from ANUJ1.DEPT * ERROR at line 1: ORA-02292: integrity constraint (ANUJ1.FK_DEPTNO) violated - child record found
set serverout on begin for i in ( select t.table_name, dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, owner) ddl FROM all_constraints WHERE owner='ANUJ1' AND constraint_name='FK_DEPTNO' ) loop dbms_output.put_line(i.ddl); --execute immediate i.ddl; end loop; end; /ALTER TABLE "ANUJ1"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "ANUJ1"."DEPT" ("DEPTNO") ENABLE;delete from "ANUJ1"."EMP" where deptno in (select DEPTNO from "ANUJ1"."DEPT" )delete from "ANUJ1"."EMP" where deptno in (select DEPTNO from "ANUJ1"."DEPT" )SQL> 2 / 11 rows deleted.select distinct * from ( select 'ALTER TABLE '||UC.owner||'.'||UC.TABLE_NAME||' ENABLE CONSTRAINT '||UC.constraint_name || ';' x FROM DBA_CONSTRAINTS UC, DBA_CONS_COLUMNS UCC WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME --AND uc.constraint_type = 'R' --and UCC.table_name in ('xx') and UC.owner='ANUJ1' and uc.constraint_name='FK_DEPTNO' ORDER BY UCC.TABLE_NAME,UC.R_CONSTRAINT_NAME,UCC.TABLE_NAME,UCC.COLUMN_NAME );
SELECT owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='XXXX' AND constraint_name='FK_DEPTNO' ;
set heading off
set echo off
Set pages 999
set long 90000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
-- Constraint
select dbms_metadata.get_dependent_ddl('CONSTRAINT','TABLE','OW') output from dual;
-- REF_CONSTRAINT
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','TABLE','OW') output from dual;
SET LINESIZE 400 PAGESIZE 300
COL R_COLUMN_NAME FOR A27
COL OWNER FOR A12
COL R_OWNER FOR A12
COL r_pk FOR A15
col TABLE_NAME for a15
col COLUMN_NAME for a15
col CONSTRAINT_NAME for a15
col R_TABLE_NAME for a15
SELECT
c.owner
,a.table_name
, a.column_name
, a.constraint_name
, c.r_owner
, c_pk.table_name r_table_name
, c_pk.constraint_name r_pk
, cc_pk.column_name r_column_name
FROM DBA_cons_columns a
JOIN DBA_constraints c ON (a.owner = c.owner AND a.constraint_name = c.constraint_name )
JOIN DBA_constraints c_pk ON (c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name )
JOIN DBA_cons_columns cc_pk on (cc_pk.constraint_name = c_pk.constraint_name AND cc_pk.owner = c_pk.owner )
WHERE a.owner = 'ANUJ1'
--AND a.table_name IN ( 'XX')
--AND a.table_name IN ('vvv')
ORDER BY 1,6
;
====
to disable and enable constraint
DECLARE
/*The name of the schema that should be synchronized.*/
Schema_Name VARCHAR2(4000) :='ANUJ1';
/*The operation type:*/
/* ON — enable foreign keys;*/
/* OFF — disable foreign keys.*/
ON_OFF VARCHAR2(4000) :='OFF';
PROCEDURE CONSTRAINTS_ON_OFF
(Target_Schema_Name IN VARCHAR2, Action IN VARCHAR2:='')
IS
sql_str VARCHAR2(4000);
FK_name VARCHAR2(4000);
var_action VARCHAR2(4000);
CURSOR cCur1 IS
/*Creating the list of foreign keys that should be disabled/enabled,*/
/*with creating a command at the same time.*/
SELECT
'ALTER TABLE '||OWNER||'.'||
TABLE_NAME||' '||var_action||' CONSTRAINT '||CONSTRAINT_NAME AS sql_string,
CONSTRAINT_NAME
FROM
ALL_CONSTRAINTS
WHERE 1=1
--and CONSTRAINT_TYPE='R'
AND OWNER='ANUJ1'
and TABLE_NAME='EMP'
;
BEGIN
IF upper(Action)='ON' THEN
var_action :='ENABLE';
ELSE
var_action :='DISABLE';
END IF;
OPEN cCur1;
LOOP
FETCH cCur1 INTO SQL_str,fk_name;
EXIT WHEN cCur1%NOTFOUND;
/*Disabling/Enabling foreign keys.*/
-- EXECUTE IMMEDIATE SQL_str;
DBMS_Output.PUT_LINE(SQL_str);
-- DBMS_Output.PUT_LINE('Foreign key '||FK_name||' is '||var_action||'d');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_Output.PUT_LINE(SQLERRM);
END;
CLOSE cCur1;
END;
BEGIN
CONSTRAINTS_ON_OFF(Schema_Name,ON_OFF);
/*specify additional calls if necessary*/
END;
/
ALTER TABLE ANUJ1.EMP DISABLE CONSTRAINT FK_DEPTNO
ALTER TABLE ANUJ1.EMP DISABLE CONSTRAINT PK_EMP
PL/SQL procedure successfully completed.
==============
constraint to table metadata delete from anuj1.dept where deptno=20 * ERROR at line 1: ORA-02292: integrity constraint (ANUJ1.FK_DEPTNO) violated - child record found ===== define O='ANUJ1' define C='FK_DEPTNO' set linesize 300 col OWNER for a15 col R_OWNER for a15 col TABLE_NAME for a25 col SEARCH_CONDITION for a15 col constraint_type for a15 col CONSTRAINT_NAME for a15 col R_CONSTRAINT_NAME for a15 SELECT owner , table_name, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='&O' AND constraint_name='&C' union all SELECT owner , table_name, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='&O' AND constraint_name in ( select r_constraint_name FROM all_constraints where owner='&O' AND constraint_name='&C' ) order by constraint_type ; OWNER TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE R_OWNER R_CONSTRAINT_NA SEARCH_CONDITIO --------------- ------------------------- --------------- --------------- --------------- --------------- --------------- ANUJ1 DEPT PK_DEPT P ANUJ1 EMP FK_DEPTNO R ANUJ1 PK_DEPT Table metadata ---- for above set PAGESIZE 80 SET LONG 10000 LONGCHUNKSIZE 10000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END; / SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner) FROM all_tables WHERE 1=1 and owner = '&O' AND table_name in ( SELECT --owner , table_name --, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='&O' AND constraint_name='&C' union all SELECT --owner , table_name --, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='&O' AND constraint_name in ( select r_constraint_name FROM all_constraints where owner='&O' AND constraint_name='&C' ) --order by constraint_type ) CREATE TABLE "ANUJ1"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13), CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE TABLE "ANUJ1"."EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0), CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") <<<< this table REFERENCES "ANUJ1"."DEPT" ("DEPTNO") ENABLE >>>>> reference table ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; *******************************************************************************
define O='ANUJ1'
define T='DEPT'
define O='ANUJ1'
define T='EMP'
SET LONG 10000 LONGCHUNKSIZE 10000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT dbms_metadata.get_dependent_ddl('CONSTRAINT', table_name, owner) FROM all_tables
WHERE 1=1
and owner = UPPER('&O')
AND table_name = UPPER('&T')
union all
SELECT dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, owner) FROM all_tables
WHERE 1=1
and owner = UPPER('&O')
AND table_name = UPPER('&T')
/
ALTER TABLE "ANUJ1"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
ERROR:
ORA-31608: specified object of type REF_CONSTRAINT not found
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 6469
ORA-06512: at "SYS.DBMS_METADATA", line 9297
ORA-06512: at line 1
ALTER TABLE "ANUJ1"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
ALTER TABLE "ANUJ1"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "ANUJ1"."DEPT" ("DEPTNO") ENABLE;
define O='ANUJ1'
define T='EMP1'
define C='FK_DEPTNO'
set linesize 300 set pagesize 80
col OWNER for a15
col R_OWNER for a15
col TABLE_NAME for a25
col SEARCH_CONDITION for a15
col constraint_type for a15
col CONSTRAINT_NAME for a15
col R_CONSTRAINT_NAME for a15
col SCHEMA_NAME for a20
col COLUMNS for a20
select acc.owner as schema_name,
acc.table_name,
acc.constraint_name,
LISTAGG(acc.column_name,',')
WITHIN GROUP (order by acc.position) as columns
,con.constraint_type
from sys.dba_constraints con
join sys.dba_cons_columns acc on con.owner = acc.owner
and con.constraint_name = acc.constraint_name
where 1=1
-- and con.constraint_type = 'P'
and acc.owner=UPPER('&O')
-- and acc.table_name not like 'BIN$%'
and con.constraint_name='&C'
group by acc.owner,
acc.table_name,
acc.constraint_name
,con.constraint_type
--order by acc.owner, acc.constraint_name
union all
select acc.owner as schema_name,
acc.table_name,
acc.constraint_name,
LISTAGG(acc.column_name,',')
WITHIN GROUP (order by acc.position) as columns
,con.constraint_type
from sys.dba_constraints con
join sys.dba_cons_columns acc on con.owner = acc.owner
and con.constraint_name = acc.constraint_name
where 1=1
-- and con.constraint_type = 'P'
and acc.owner=UPPER('&O')
-- and acc.table_name not like 'BIN$%'
AND con.constraint_name in ( select r_constraint_name FROM all_constraints
where owner='&O'
AND constraint_name='&C'
)
group by acc.owner,
acc.table_name,
acc.constraint_name
,con.constraint_type
order by 5
/
SCHEMA_NAME TABLE_NAME CONSTRAINT_NAME COLUMNS CONSTRAINT_TYPE
-------------------- ------------------------- --------------- -------------------- ---------------
ANUJ1 DEPT PK_DEPT DEPTNO P
ANUJ1 EMP FK_DEPTNO DEPTNO R
all_constraints status <<<<<<<<<<<
set linesize 300 pagesize 200
col SCHEMA_NAME for a20
col CONSTRAINT for a15
col table_name for a15
select ctr.owner as schema_name,
ctr.constraint_name,
ctr.table_name,
col.column_name,
ctr.search_condition as constraint,
ctr.status
from sys.all_constraints ctr
join sys.all_cons_columns col
on ctr.owner = col.owner
and ctr.constraint_name = col.constraint_name
and ctr.table_name = col.table_name
where 1=1
--ctr.constraint_type = 'C'
and ctr.owner in ('ANUJ1')
--and ctr.table_name='DEPT'
and STATUS!='ENABLED'
order by ctr.owner, ctr.table_name, ctr.constraint_name;
SCHEMA_NAME CONSTRAINT_NAME TABLE_NAME COLUMN_NAME CONSTRAINT STATUS
-------------------- ---------------------- --------------- --------------- --------------- --------
ANUJ1 FK_DEPTNO EMP DEPTNO DISABLED
-- Enable
ALTER table table_name enable constraint constraint_name;
--Disable
ATLER table table_name disable constraint constraint_name;SQL> delete from ANUJ1.DEPT where deptno=10; delete from ANUJ1.DEPT where deptno=10 * ERROR at line 1: ORA-02292: integrity constraint (ANUJ1.FK_DEPTNO) violated - child record found
========================================================================
create script!!!!!
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
v_schema VARCHAR2(100) := 'ANUJ1';
v_root_table VARCHAR2(100) := 'DEPT';
v_filter VARCHAR2(500) := ' deptno=10';
--v_filter VARCHAR2(500) := 'CREATE_DATE < DATE ''2024-01-05''';
-- Get PK columns (supports composite PK)
FUNCTION get_pk_columns(p_table VARCHAR2) RETURN VARCHAR2 IS
v_cols VARCHAR2(2000);
BEGIN
SELECT LISTAGG(acc.column_name, ',') WITHIN GROUP (ORDER BY acc.position)
INTO v_cols
FROM all_constraints ac
JOIN all_cons_columns acc
ON ac.owner = acc.owner
AND ac.constraint_name = acc.constraint_name
WHERE ac.owner = v_schema
AND ac.table_name = p_table
AND ac.constraint_type = 'P';
RETURN v_cols;
END get_pk_columns;
-- Recursive delete printer
PROCEDURE print_delete(p_table VARCHAR2, p_filter VARCHAR2) IS
v_pk VARCHAR2(2000);
BEGIN
v_pk := get_pk_columns(p_table);
FOR c IN (
SELECT ac.table_name AS child_table,
acc.column_name AS child_column
FROM all_constraints ac
JOIN all_cons_columns acc
ON ac.owner = acc.owner
AND ac.constraint_name = acc.constraint_name
WHERE ac.owner = v_schema
AND ac.constraint_type = 'R'
AND ac.r_constraint_name = (
SELECT constraint_name
FROM all_constraints
WHERE owner = v_schema
AND table_name = p_table
AND constraint_type = 'P'
)
) LOOP
-- Recurse first
print_delete(
c.child_table,
c.child_column || ' IN (SELECT ' || v_pk ||
' FROM ' || v_schema || '.' || p_table ||
' WHERE ' || p_filter || ')'
);
-- Output delete statement
DBMS_OUTPUT.PUT_LINE(
'DELETE FROM ' || v_schema || '.' || c.child_table ||
' WHERE ' || c.child_column || ' IN (SELECT ' || v_pk ||
' FROM ' || v_schema || '.' || p_table ||
' WHERE ' || p_filter || ');' || CHR(10)
);
END LOOP;
END print_delete;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- PRINT-ONLY FK-SAFE DELETE SCRIPT ---');
DBMS_OUTPUT.PUT_LINE('Schema: ' || v_schema || ' Root Table: ' || v_root_table);
DBMS_OUTPUT.PUT_LINE('Filter: ' || v_filter || CHR(10));
print_delete(v_root_table, v_filter);
DBMS_OUTPUT.PUT_LINE(
'DELETE FROM ' || v_schema || '.' || v_root_table ||
' WHERE ' || v_filter || ';'
);
DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- SCRIPT COMPLETE ---');
END;
/
================================================================
--- PRINT-ONLY FK-SAFE DELETE SCRIPT ---
Schema: ANUJ1 Root Table: DEPT
Filter: deptno=10
DELETE FROM ANUJ1.EMP WHERE DEPTNO IN (SELECT DEPTNO FROM ANUJ1.DEPT WHERE
deptno=10);
DELETE FROM ANUJ1.DEPT WHERE deptno=10;
--- SCRIPT COMPLETE ---
PL/SQL procedure successfully completed.
SQL> DELETE FROM ANUJ1.EMP WHERE DEPTNO IN (SELECT DEPTNO FROM ANUJ1.DEPT WHERE
deptno=10);
2
3 rows deleted.
SQL> SQL> DELETE FROM ANUJ1.DEPT WHERE deptno=10;
1 row deleted.
SQL> SQL> roll;
Rollback complete.
