Oracle constraint Disable
Disable all table constraints in Oracle
spool disable_cons.sql
select 'alter table '||owner||'.'||table_name||' disable constraint '
||constraint_name||'CASCADE ;'
from all_constraints
where status= 'ENABLED'
and owner =upper('&&Owner')
and constraint_type ='P'
union
select 'alter table '||owner||'.'||table_name||' disable constraint '
||constraint_name||' CASCADE ;'
from all_constraints
where status = 'ENABLED'
and CONSTRAINT_TYPE ='R'
and r_constraint_name in
(select constraint_name from all_constraints
where owner =upper('&&Owner')
and CONSTRAINT_TYPE ='P'
)
/
spool off
spool disable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||' disable;'
from all_triggers
where owner =upper('&&Owner')
and status ='ENABLED'
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80
@disable_cons.sql ---- run two time
@disable_triggers.sql
check
select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'ENABLED'
and owner =upper('&&Owner')
order by OWNER, TABLE_NAME, CONSTRAINT_NAME
after diable the constraints then enable them via this script
spool enable_cons.sql
select 'alter table '||owner||'.'||table_name||' ENABLE constraint '
||constraint_name||';'
from all_constraints
where status= 'DISABLED'
and owner =upper('&&Owner')
and constraint_type ='P'
union
select 'alter table '||owner||'.'||table_name||' ENABLE constraint '
||constraint_name||';'
from all_constraints
where status = 'DISABLED'
and CONSTRAINT_TYPE ='R'
and r_constraint_name in
(select constraint_name from all_constraints
where owner =upper('&&Owner')
and CONSTRAINT_TYPE ='P'
)
/
spool off
spool enable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||' enable;'
from all_triggers
where owner =upper('&&Owner')
and status ='DISABLED'
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80
@enable_cons.sql
@enable_triggers.sql
Then check
select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'DISABLED'
and owner =upper('&&Owner')
order by OWNER, TABLE_NAME, CONSTRAINT_NAME
or
BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'ENABLED'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name||' CASCADE ;' );
END LOOP;
END;
/
BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'DISABLED'
ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
============
CREATE OR REPLACE PROCEDURE control_fkeys(
p_table_name IN dba_constraints.table_name%TYPE ,
p_owner IN dba_constraints.table_name%TYPE,
p_enable_flag IN NUMBER
,p_status IN OUT NUMERIC)
AUTHID DEFINER IS
-- constants
k_enable dba_constraints.status%TYPE := 'ENABLE';
k_disable dba_constraints.status%TYPE := 'DISABLE';
-- identify fkeys on pkey for given table
CURSOR id_fkeys (
c_table_name dba_constraints.table_name%TYPE
,c_owner dba_constraints.owner%TYPE
,c_status dba_constraints.status%TYPE
)
IS
SELECT table_name, constraint_name fkey, r_constraint_name pkey,status
FROM dba_constraints
WHERE ( constraint_type='R' and table_name = c_table_name and owner=c_owner)
--outbound fk constraints
or
( r_constraint_name IN (
--inbound fk constraints
SELECT constraint_name
FROM dba_constraints
WHERE table_name = c_table_name
and owner=p_owner
AND constraint_type='P')
)
AND status!=c_status
ORDER BY table_name, constraint_name;
/* Note: this select only gets FK constraints that are inbound, meaning
the specified table contains parent records.
*/
-- SELECT table_name, constraint_name fkey, r_constraint_name pkey,status
-- FROM user_constraints
-- WHERE constraint_type='R'
-- AND status!=c_status
-- AND r_constraint_name IN (
-- SELECT constraint_name
-- FROM user_constraints
-- WHERE table_name=c_table_name
-- AND constraint_type='P')
-- ORDER BY table_name, constraint_name;
-- record variables
--
rec_id_fkeys id_fkeys%ROWTYPE;
--
-- variables
l_status dba_constraints.status%TYPE;
l_table_name dba_constraints.table_name%TYPE;
l_owner dba_constraints.table_name%TYPE;
l_stmt VARCHAR2(255);
l_pkey_name dba_constraints.constraint_name%TYPE;
BEGIN
p_status := 0;
l_table_name := UPPER(p_table_name);
l_owner := UPPER(p_owner);
IF (p_enable_flag = 1) THEN
l_status := k_enable;
ELSIF (p_enable_flag = 0) THEN
l_status := k_disable;
ELSE
DBMS_OUTPUT.put_line(
'-- control_fkeys: enable_flag must be 1 or 0 [' || p_enable_flag
|| ']');
p_status := 1001;
END IF;
IF (p_status = 0) THEN -- validated enable flag
-- a primary key for the given table must exist
SELECT constraint_name
INTO l_pkey_name
FROM dba_constraints
WHERE table_name=l_table_name
AND constraint_type='P'
and owner=l_owner
;
DBMS_OUTPUT.put_line( '-- control_fkeys: ' || l_status || ' foreign key constraints on table ' || l_table_name || ' whose primary key is ' || l_pkey_name);
OPEN id_fkeys(l_table_name, l_owner,l_status || 'D');
LOOP -- process foreign keys
FETCH id_fkeys INTO rec_id_fkeys;
EXIT WHEN id_fkeys%NOTFOUND;
IF l_status = k_enable THEN
l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name ||' ENABLE NOVALIDATE CONSTRAINT ' ||rec_id_fkeys.fkey;
DBMS_OUTPUT.put_line(l_stmt);
--EXECUTE IMMEDIATE l_stmt;
l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name || 'MODIFY ' ||' CONSTRAINT ' || rec_id_fkeys.fkey || 'VALIDATE' ;
DBMS_OUTPUT.put_line(l_stmt);
--EXECUTE IMMEDIATE l_stmt;
ELSE
l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name ||' DISABLE CONSTRAINT ' || rec_id_fkeys.fkey;
DBMS_OUTPUT.put_line(l_stmt);
--EXECUTE IMMEDIATE l_stmt;
END IF;
END LOOP; -- process foreign keys
IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found that weren't enabled/disabled
DBMS_OUTPUT.put_line( '-- control_fkeys: No foreign keys found against table ' || l_table_name || ' to ' || l_status);
END IF; -- no rows found
CLOSE id_fkeys;
END IF; -- validated enable flag
EXCEPTION
WHEN NO_DATA_FOUND THEN -- primary key lookup failed
p_status := 1002;
DBMS_OUTPUT.put_line( '-- control_fkeys: no primary key exists for table ' ||l_table_name);
WHEN OTHERS THEN
p_status := SQLCODE;
DBMS_OUTPUT.put_line('-- control_fkeys: ' || SQLERRM(p_status));
IF (id_fkeys%ISOPEN) THEN
CLOSE id_fkeys;
END IF;
END control_fkeys;
/
=====
set serveroutput on ;
variable ret_val number;
execute sys.control_fkeys('EMP','SYS',1,:ret_val);
==============
--DISABLE constraints
for i in (select 'ALTER TABLE '||ac.owner||'.'||ac.table_name||' DISABLE CONSTRAINT '||constraint_name as sql_string,constraint_name,ac.table_name
from all_constraints ac,all_tables at
where ac.table_name = at.table_name
and ac.owner=at.owner
and ac.owner=p_owner
order by constraint_type desc
) loop
dbms_output.put_line(current_timestamp||':'||i.sql_string);
--execute immediate i.sql_string;
dbms_output.put_line( 'Constraint '||constraint_name ||' on '||table_name ||' is ' || ' Disabled.');
end loop;
-- Enable
for i in (select 'ALTER TABLE '||ac.owner||'.'||ac.table_name||' ENABLE novalidate CONSTRAINT '||
constraint_name as sql_string,constraint_name,ac.table_name
from all_constraints ac ,all_tables at
where ac.table_name = at.table_name
and ac.owner=at.owner
and ac.owner=p_owner
order by constraint_type
) loop
dbms_output.put_line(i.sql_string);
-- execute immediate i.sql_string;
end loop;
No comments:
Post a Comment