Dear Experts,
I have a requirement where i need to disable constraint on tables for a given schema. So created a procedure allows schema name as input.
Logic is: have cursor in side procedure and first disable constraints of constraint_type='R' then remaining in another cursor with execute immediate.
More information: I created a user (say: dpimport) and assigned DATAPUMP_IMP_FULL_DATABASE role to him along with CONNECT SESSION, UNLIMITED TABLESPACE, CREATE TABLE(for creating master table) privileges. This user will be doing import data using dump files to given schema, which requires disabling constraints before import process starts.
Problem:
- From above dpimport user, if i issue alter for any constraint like: 'ALTER TABLE SCHEMA.TABLE_NAME DISABLE CONSTRAINT CONS1;' is working fine wihtout any extra privileges.
- But if i run below procedure by passing appropriate schema_name then it is not executing cursor unless i explicitly mention 'ALTER ANY TABLE' privilege to dpimport user(which is powerful privilege, which i don't think require).
- It should work from procedure when run from dpimport. ( i know this may be same as having alter any table privilege, but manual alter script works with this user but not procedure)
Code mentioned below:
CREATE OR REPLACE PROCEDURE disableconstraints (pc_schema in VARCHAR2)
IS
v_schema VARCHAR2(30):=UPPER(pc_schema);
v_cons varchar2(50);
CURSOR referenced_cons (schema_nm VARCHAR2)
IS
SELECT
owner
, table_name
, constraint_name
, status
FROM
ALL_constraints
WHERE owner = v_schema AND CONSTRAINT_TYPE='R' and status='ENABLED';
CURSOR nonreferenced_cons (schema_nm VARCHAR2)
IS
SELECT
owner
, table_name
, constraint_name
, status
FROM
ALL_constraints
WHERE owner = v_schema AND CONSTRAINT_TYPE<>'R' AND status='ENABLED';
BEGIN
BEGIN
For cur1 in referenced_cons(v_schema)
LOOP
v_cons:=cur1.constraint_name;
DBMS_OUTPUT.PUT_LINE('alter table ' || cur1.owner || '.' || cur1.table_name || ' DISABLE constraint ' || cur1.constraint_name);
EXECUTE IMMEDIATE 'alter table ' || cur1.owner || '.' || cur1.table_name || ' DISABLE constraint ' || cur1.constraint_name;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
recorderror(SCHEMA_NAME =>v_schema, proc_name => 'disableconstraints:OTHERS',cust_err_desc => 'Cur1: Exception - '||v_cons);
RAISE;
END;
BEGIN
For cur2 in nonreferenced_cons(v_schema)
LOOP
v_cons:=cur2.constraint_name;
DBMS_OUTPUT.PUT_LINE('alter table ' || cur2.owner || '.' || cur2.table_name || ' DISABLE constraint ' || cur2.constraint_name);
EXECUTE IMMEDIATE 'alter table ' || cur2.owner || '.' || cur2.table_name || ' DISABLE constraint ' || cur2.constraint_name;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
recorderror(SCHEMA_NAME =>v_schema, proc_name => 'disableconstraints:OTHERS',cust_err_desc => 'Cur2: Exception - '||v_cons);
RAISE;
END;
END;
/
Please suggest why it is behaving this way when called from procedure.
Alternatively, i can create procedure (with AUTHID DEFINER) in the respected schema and have execute grants on this procedure to dpimport user. Then execute accordingly. But deploying and maintaining the procedure in number of schemas is tedious(have no idea on how to do it as routine). And also anyways now dpimport user able to run alter table to disbale constraints statement manually (not from procedure) as he has datapump_imp_full_database role assigned. So want to do it from dpimport to disable constraints.
please suggest.
--Regards