Category: PL/SQL

Oracle: Enable/Disable all constraints in schema at once

Here are the snippets for enabling and disabling all constraints in an oracle schema. This is useful when you create a new schema and then fill it up with the data from other databases.

For whatever reason you will need this be careful to only enable primary keys at first, otherwise all foreign key validation will fail.

Also check my post about updating sequences –

--
-- disable all
--

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);
  END LOOP;
END;
/

--
--enable all
--
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'
   --AND CONSTRAINT_NAME LIKE '%_PK%'   --first you have to enable only primary keys, otherwise foreign keys will not validate
   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;
/

08.04.2015

Oracle: Update all sequences for tables for current user

Below is procedure that updates all table sequences to match the max ID currently in a table. Useful when there is a mismatch of values.

-- Update all sequences to match actual table values
DECLARE
v_max VARCHAR2 (1000);
BEGIN

FOR rec IN (
select tabs.table_name table_name,
seqs.sequence_name seq_name,
seqs.last_number
from user_tables tabs
join user_triggers trigs
on trigs.table_name = tabs.table_name
join user_dependencies deps
on deps.name = trigs.trigger_name
join user_sequences seqs
on seqs.sequence_name = deps.referenced_name
where last_number = 1 --only sequnces that have last number 1
)
LOOP
--Info print
--dbms_output.put_line(rec.table_name);
--dbms_output.put_line(rec.seq_name);
--dbms_output.put_line(rec.last_number);

--reset sequence last number to 0
EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY -' || rec.last_number ;
EXECUTE IMMEDIATE 'SELECT ' || rec.seq_name || '.NEXTVAL FROM DUAL' ;

--Find highest id in table
EXECUTE IMMEDIATE 'select max(id)+1 from ' || rec.table_name INTO v_max;

--dbms_output.put_line(v_max);

EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY ' || v_max ;

EXECUTE IMMEDIATE 'SELECT ' || rec.seq_name || '.NEXTVAL FROM DUAL' ;

EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY 1' ;

END LOOP;
END;
/

08.04.2015