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.
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)