Oracle 加载数据的脚本

嗨，团队，

我正在寻找一个脚本，它执行以下操作


1.禁用约束
--------------------------

开始
对于cur in (选择所有者，constraint_name，table_name
从所有 _ 约束
其中所有者 = 'hri1_dumpstag' 和
表名称 = 'BILL') 循环
立即执行 'ALTER TABLE '| | cur.owner | |'.'| cur.table_name | |' 修改约束 '| | cur.Constraintt_name | |' "禁用NOVALIDATE ';
结束循环;
结束;




2.备份表
---------------------------
创建表ownername.tablename_bkp作为select * 从ownername.tablename;

3.截断表数据
--------------------------
截断表所有者名称。tablename;

4.使用SQL加载器实用程序加载数据 (对于Windows)
-----------------------------------------
sqlldr <用户>/<密码> @ SRVC_NAME控件 = TABLENAME.ctl数据 = TABLENAME.Txt日志 = TABLENAME.log bad = TABLENAME.bad错误 = 1000000 &


5.启用约束条件
-------------------------
alter table 。 启用约束 ;

我希望所有这些都可以在Sqlplus或oracle sql developer中运行的单个文件中，该文件将仅将Tablename作为输入表。

像这样的东西？

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./@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




which spits out something like this:


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/@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;



您可能想要进行大量增强 (设置索引不可用，不包括空约束等)