Temporarily Disable and Re-enable the Constraints in Oracle

SQL command script files to disable and enable all constraints:

Disable:

set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/

Enable:

set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/

Scripts in PL/SQL to disable and enable all constraints:

Disable:

BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/

Enable:

BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE 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;
/

Software Patch Installation Batch Files

The example here is to install an Oracle database application patch. The basic structure of patch installation scripts shall include two folders and two files:

Folder 1: Logs
Folder 2: Scripts
File 1: Readme.txt
File 2: Setup.bat (or other name)

Of course, there would be some SQL script files in Scripts folder. Log files in Logs folder would be generated by these script files in Scripts folder. The working procedure is to run installer batch file (here Setup.bat), which will call other batch files (we do not have here) and/or script files stored in Scripts folder. In there example, the batch file open sqlplus.exe followed by user id, password and the SQL scripts (in file), or package to run.

Following is the example of batch file:

@echo off
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: PLEASE REVIEW AND ADJUST ENVIRONMENT VARIABLES BETWEEN < >
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CLS
set NETALIAS=
set USERID=
set PASSWORD=
set COMMONID=
set COMPASS=
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: ORACLE_VERSION are 9.2 (for 9i) or 10.2.0 (for 10g)
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set ORACLE_VERSION=
set ORACLE_HOME=C:\oracle\product\%ORACLE_VERSION%\db_1
set SQLUTIL=%ORACLE_HOME%\bin\sqlplus.exe
set FOLDERPATH=%cd%
echo #####################################################
echo.
echo You are just about to run SCRAMBLING SCRIPT on database
echo.
echo [**** %NETALIAS% ****]
echo.
echo Schema
echo.
echo [**** %USERID% ****]
echo.
echo If this is not correct Database/Schema then
echo.
echo press CTL + C to cancel the script.....
echo.
echo #####################################################
pause
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Truncate.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Create_Tables.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.spec"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.body"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Cleanup.sql"
pause

Note: NETALIAS, USERID following “set” commend are variables. %...% is the way to use these variables.

Following is the Truncate.sql:

PROMPT Truncating data. Please wait...
TRUNCATE TABLE BATCH_BUF$RECON$DETAIL;
TRUNCATE TABLE BATCH_BUF$VALU$SUMMARY_PEN;
-- Enable/Disable primary, unique and foreign key constraints alter table BATCH_BUF$VALU$SUMMARY_PEN disable constraint BATCH_BUF$VALU$SUMMARY_PEN$FK1;
EXIT;