Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, jayaraj.

Asked: April 10, 2017 - 8:24 pm UTC

Last updated: April 11, 2017 - 3:04 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hi Team,

I am Looking for a single script which does the following


1. Disable the constraints
--------------------------

begin
for cur in (select owner, constraint_name , table_name
from all_constraints
where owner = 'HRI1_DUMPSTAG' and
TABLE_NAME = 'BILL') loop
execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" DISABLE NOVALIDATE ';
end loop;
end;




2. Take Backup of the table
---------------------------
create table ownername.tablename_bkp as select * from ownername.tablename;

3. Truncate the table data
--------------------------
truncate table ownername.tablename;

4. Load the data using SQL Loader utility (for Windows)
-----------------------------------------
sqlldr <user>/<password>@SRVC_NAME control=TABLENAME.ctl data=TABLENAME.txt log=TABLENAME.log bad=TABLENAME.bad errors=1000000 &


5. Enable the constraints
-------------------------
alter table <schema>.<table_name> enable constraint <constraint_name>;

i want all this in a single file which can be run in Sqlplus or oracle sql developer which will table only the Tablename as input.



and Connor said...

Something like this ?

set termout off
set feedback off
set verify off
set heading off

undefine own
col x new_value own
select owner x , case when count(*) over () != 1 then 1/0 end err
from dba_tables
where table_name = upper('&1');

set termout on

select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY CONSTRAINT "'||constraint_name||'" DISABLE NOVALIDATE;'
from all_constraints
where r_owner = '&&own' 
and r_constraint_name in
  ( select constraint_name
    from   all_constraints
     where owner = '&&own' 
    and TABLE_NAME = upper('&1')    
  );
  
select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY CONSTRAINT "'||constraint_name||'" DISABLE NOVALIDATE;'
from all_constraints
where owner = '&&own' 
and TABLE_NAME = upper('&1');

select 'create table &&own..'||upper('&1')||'_bkp as select * from &&own..'||upper('&1')||';'
from dual;

select 'truncate table &&own..'||upper('&1')||';' from dual;

select 'host sqlldr &&own./<password>@SRVC_NAME control='||upper('&1')||'.ctl data='||upper('&1')||'.txt log='||upper('&1')||'.log bad='||upper('&1')||'.bad errors=1000000'
from dual;

select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY CONSTRAINT "'||constraint_name||'" enable VALIDATE;'
from all_constraints
where owner = '&&own' 
and TABLE_NAME = upper('&1');


select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY CONSTRAINT "'||constraint_name||'" enable VALIDATE;'
from all_constraints
where r_owner = '&&own' 
and r_constraint_name in
  ( select constraint_name
    from   all_constraints
     where owner = '&&own' 
    and TABLE_NAME = upper('&1')    
  );

set feedback on
set verify on
set heading on


<code>

which spits out something like this:

<code>
SQL>  @c:\temp\x.sql SALES

ALTER TABLE MCDONAC.SALES MODIFY CONSTRAINT "SYS_C0012329" DISABLE NOVALIDATE;
ALTER TABLE MCDONAC.SALES MODIFY CONSTRAINT "SYS_C0012330" DISABLE NOVALIDATE;
ALTER TABLE MCDONAC.SALES MODIFY CONSTRAINT "SYS_C0012331" DISABLE NOVALIDATE;

create table MCDONAC.SALES_bkp as select * from MCDONAC.SALES;

truncate table MCDONAC.SALES;

host sqlldr MCDONAC/<password>@SRVC_NAME control=SALES.ctl data=SALES.txt log=SALES.log bad=SALES.bad errors=1000000

ALTER TABLE MCDONAC.SALES MODIFY CONSTRAINT "SYS_C0012329" enable VALIDATE;
ALTER TABLE MCDONAC.SALES MODIFY CONSTRAINT "SYS_C0012330" enable VALIDATE;
ALTER TABLE MCDONAC.SALES MODIFY CONSTRAINT "SYS_C0012331" enable VALIDATE;


Plenty of enhancements you might want to make (setting indexes unusable, not including null constraints etc etc)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.