1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Script enables all the constraints of a schema in any instance

DECLARE
CURSOR c_pk
IS
SELECT a.table_name, a.constraint_name
FROM user_constraints a
WHERE a.constraint_type IN ('P')
AND a.constraint_name NOT IN (SELECT b.constraint_name
FROM constraints_disabled_tmp b);

CURSOR c_fk
IS
SELECT table_name, constraint_name
FROM user_constraints a
WHERE a.constraint_type IN ('R')
AND a.constraint_name NOT IN (SELECT b.constraint_name
FROM constraints_disabled_tmp b);

nom_table user_constraints.table_name%TYPE;
nom_const user_constraints.constraint_name%TYPE;
ignore INTEGER;
csql INTEGER;
csql1 INTEGER;
csql2 INTEGER;
nom_ts_idx VARCHAR2 (30);
BEGIN
SELECT tablespace_name
INTO nom_ts_idx
FROM user_tablespaces
WHERE tablespace_name LIKE '%INDX%';

csql := DBMS_SQL.open_cursor;

OPEN c_pk;

LOOP
FETCH c_pk
INTO nom_table, nom_const;

EXIT WHEN c_pk%NOTFOUND;
DBMS_SQL.parse (csql,
'ALTER TABLE '
|| nom_table
|| ' ENABLE CONSTRAINT '
|| nom_const
|| ' USING INDEX TABLESPACE '
|| nom_ts_idx,
DBMS_SQL.v7
);
ignore := DBMS_SQL.EXECUTE (csql);
END LOOP;

CLOSE c_pk;

DBMS_SQL.close_cursor (csql);
csql2 := DBMS_SQL.open_cursor;

OPEN c_fk;

LOOP
FETCH c_fk
INTO nom_table, nom_const;

EXIT WHEN c_fk%NOTFOUND;
DBMS_SQL.parse (csql2,
'ALTER TABLE '
|| nom_table
|| ' ENABLE CONSTRAINT '
|| nom_const,
DBMS_SQL.v7
);
ignore := DBMS_SQL.EXECUTE (csql2);
END LOOP;

CLOSE c_fk;

DBMS_SQL.close_cursor (csql2);
csql1 := DBMS_SQL.open_cursor;
DBMS_SQL.parse (csql1, 'DROP TABLE CONSTRAINTS_DISABLED_TMP', DBMS_SQL.v7);
ignore := DBMS_SQL.EXECUTE (csql1);
DBMS_SQL.close_cursor (csql1);
END;
/

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy