Enable and Disable constraints using Scripts

Below are scripts that we will use in our environment before import the data using impdp inorder to speed up the process by disabling the constraints and triggers.

CONSTRAINTS DISABLE
——————-

Set linesize 300

Spool COnstraints_disable.sql

select ‘alter table ‘ || owner || ‘.’ || table_name || ‘ disable constraint ‘ || constraint_name || ‘;’ from all_constraints where owner=’test_schema’;
CONSTRAINTS ENABLE
——————-

SQL> set linesize 300

SQL> spool Constraints_enable.sql

SQL> select ‘alter table ‘ || owner || ‘.’ || table_name || ‘ enable constraint ‘ || constraint_name || ‘;’ from all_constraints where owner=’test_schema’;

Trigger Disable
—————

SQL> Set linesize 300

SQL> spool trigger_disable.sql

select ‘alter trigger ‘ || owner || ‘.’ || trigger_name || ‘ disable; ‘ from all_triggers where owner=’test_schema’;
Trigger Enable
—————

SQL> Set linesize 300

SQL> spool trigger_enable.sql

select ‘alter trigger ‘ || owner || ‘.’ || trigger_name || ‘ enable; ‘ from all_triggers where owner=’test_schema’;
UNLOCK_TABLE_STATISTICS
—————————

SQL > set linesize 300

SQL> Unlock_statistics.sql

select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS(”’||owner||”’,”’||table_name||”’);’ from dba_tab_statistics where stattype_locked is not null and owner in(‘test_schema’);

Session marked for killing

Even after we killed the session using “immediate” keyword and its still in the state session marked for killing.

SQL> Alter system kill session ‘374,1005’ immediate;

ora-00031 : Session marked for killing..

In this case get the PID corresponding to the SID and kill it at the OS level using kill -9

–Use the below query to get the PID —

select
sid,
spid
from v$process p, v$session s
where paddr = addr
and s.sid in(‘374’)
order by s.sid;

then kill the corresponding spid in the os level using kill -9 spid

To find the ROW Counts of tables that belongs to a Particular Schema

select table_name,num_rows counter from dba_tables where owner=’Schema_name’ order by table_name;