CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Joseph', 'Zaza' FROM DUAL UNION ALL
SELECT 5, 'Jerry', 'Torchiano' FROM DUAL;
ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;
ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);
create table purchases(
  ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  customer_id   number, 
  PRODUCT_ID NUMBER, 
  QUANTITY NUMBER, 
  purchase_date timestamp
);
insert  into purchases (customer_id, product_id, quantity, purchase_date) 
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
select 1, 101,3, date '2023-03-29' + level * interval '2' day from dual
          connect by level <= 12
union all
select 2, 101,2, date '2023-01-15' + level * interval '8' hour from dual
          connect by level <= 15
union all
select 2, 102,2,date '2023-04-13' + level * interval '1 1' day to hour from dual
          connect by level <= 11
union all
select 3, 101,2, date '2023-02-01' + level * interval '1 05:03' day to minute from dual
          connect by level <= 10
union all
select 3, 101,1, date '2023-04-22' + level * interval '23' hour from dual
          connect by level <= 23
union all
select 3, 100,1,  date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
          connect by level <= 15
union all
select 4, 102,1, date '2023-01-01' + level * interval '5' hour from dual
          connect by level <= 60;
ALTER TABLE purchases 
ADD CONSTRAINT order_pk PRIMARY KEY (order_id);
ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);
/* both queries fine here */
SELECT '"' || a.owner
                    || '"."'
                    || a.table_name
                    || '"' AS full_table_name,
                a.constraint_name,
                b.status
            FROM user_constraints a
            JOIN user_constraints b
                ON ( a.r_constraint_name = b.constraint_name
                    AND a.r_owner = b.owner )
            WHERE a.constraint_type = 'R'
                AND b.status = 'ENABLED'
            ORDER BY 1
FULL_TABLE_NAME
	CONSTRAINT_NAME	STATUS
"XXX"."PURCHASES"	ITEMS_FK	ENABLED
"XXX"."PURCHASES"	CUSTOMERS_FK	ENABLED
select owner, table_name, r_constraint_name, status
 from user_constraints
where constraint_type = 'R';
OWNER	TABLE_NAME	R_CONSTRAINT_NAME	STATUS
XXX	PURCHASES	CUSTOMERS_PK	ENABLED
XXX	PURCHASES	ITEMS_PK	ENABLED
/* disable foreign keys */
BEGIN
    FOR r IN (
        SELECT '"' || a.owner
                    || '"."'
                    || a.table_name
                    || '"' AS full_table_name,
                a.constraint_name
            FROM user_constraints a
            JOIN user_constraints b
                ON ( a.r_constraint_name = b.constraint_name
                    AND a.r_owner = b.owner )
            WHERE a.constraint_type = 'R'
                AND b.status = 'ENABLED'
            ORDER BY 1 )  LOOP
            
        dbms_output.put_line ( 'Disable the constraint ' || r.constraint_name ||' (on table ' || r.full_table_name || ')' ) ;
        dbms_utility.exec_ddl_statement ( 'alter table ' || r.full_table_name || ' disable constraint ' || r.constraint_name ) ;
        
    END LOOP ;
END ;
/
Statement processed.
Disable the constraint ITEMS_FK (on table "XXX"."PURCHASES")
Disable the constraint CUSTOMERS_FK (on table "XXX"."PURCHASES")
/* status different between queries ????. Problem here!!!
*/
SELECT '"' || a.owner
                    || '"."'
                    || a.table_name
                    || '"' AS full_table_name,
                a.constraint_name,
                b.status
            FROM user_constraints a
            JOIN user_constraints b
                ON ( a.r_constraint_name = b.constraint_name
                    AND a.r_owner = b.owner )
            WHERE a.constraint_type = 'R'
                AND b.status = 'ENABLED'
            ORDER BY 1
FULL_TABLE_NAME
	CONSTRAINT_NAME	STATUS
"XXX"."PURCHASES"	ITEMS_FK	ENABLED
"XXX"."PURCHASES"	CUSTOMERS_FK	ENABLED
select owner, table_name, r_constraint_name, status
 from user_constraints
where constraint_type = 'R';
OWNER	TABLE_NAME	R_CONSTRAINT_NAME	STATUS
XXX	PURCHASES	CUSTOMERS_PK	DISABLED
XXX	PURCHASES	ITEMS_PK	DISABLED

SELECT '"' || a.owner
                    || '"."'
                    || a.table_name
                    || '"' AS full_table_name,
                a.constraint_name,
                a.status,
                b.constraint_name, 
                b.table_name, 
                b.status
            FROM user_constraints a
            JOIN user_constraints b
                ON ( a.r_constraint_name = b.constraint_name
                    AND a.r_owner = b.owner )
            WHERE a.constraint_type = 'R'
                AND b.status = 'ENABLED'
            ORDER BY 1;
"ACDC"."PURCHASES"	ITEMS_FK	  DISABLED  ITEMS_PK	    ITEMS	    ENABLED
"ACDC"."PURCHASES"	CUSTOMERS_FK. DISABLED	CUSTOMERS_PK	CUSTOMERS	ENABLED

select owner, table_name, r_constraint_name, status
 from user_constraints
where constraint_type = 'R';