[root@ETL admin]# su - oracle
[oracle@ETL ~]$ cd $ORACLE_HOME
[oracle@ETL dbhome_1]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@ETL dbhome_1]$ exit
logout
[root@ETL admin]# cd /u01/app/oracle/product/11.2.0/dbhome_1
[root@ETL dbhome_1]# ls -al|grep extjob
[root@ETL dbhome_1]# cd bin
[root@ETL bin]# ls -al|grep extjob
-rwsr-x--- 1 root oinstall 1249595 Jan 18 00:53 extjob
-rwx------ 1 oracle oinstall 1249595 Jan 18 00:53 extjobo
-rwxr-xr-x 1 oracle oinstall 1249958 Sep 17 2011 extjobO
-rwxr-xr-x 1 oracle oinstall 1249958 Sep 17 2011 extjoboO
(注明:extjob文件权限必须与上面保持一致)
e.创建ETL_TEST(数据库用户),并给相应权限
1.创建 ETL_TEST用户
create user etl_test identified by etl_test
default tablespace users
temporary tablespace temp;
2.赋于相关的系统和对象权限
grant connect, resource to etl_test;
grant select on sys.v_$session to etl_test;
grant select on sys.v_$process to etl_test;
grant create job to etl_test;
grant create any job to etl_test;
grant create external job to etl_test;
grant MANAGE SCHEDULER to etl_test;
grant alter system to etl_test;
grant execute on DBMS_LOCK to etl_test;
grant execute on DBMS_PIPE to etl_test;
grant execute on UTL_FILE to etl_test;
grant execute on DBMS_SCHEDULER to etl_test;
grant all on DBMS_SCHEDULER to etl_test;
grant execute on DBMS_CRYPTO to etl_test;
grant create any directory to etl_test;
grant debug any procedure, debug connect session to etl_test;
grant select on sys.dba_free_space to etl_test;
grant select on sys.dba_data_files to etl_test;
3.创建Oracle的Directory并赋权
create or replace directory RWA_FILE_DATA as '/ETL/data';
create or replace directory RWA_FILE_BAD as '/ETL/bad';
create or replace directory RWA_FILE_LOG as '/ETL/log';
create or replace directory RWA_FILE_CONTROL as '/ETL/control';
create or replace directory RWA_FILE_LOADER as '/ETL/loader';
create or replace directory RWA_FILE_SH as '/ETL/sh';
create or replace directory RWA_FILE_BACKUP as '/ETL/backup';
create or replace directory RWA_FILE_PERL as '/ETL/perl';
grant read, write on directory RWA_FILE_DATA to etl_test;
grant read, write on directory RWA_FILE_PERL to etl_test;
grant read, write on directory RWA_FILE_BAD to etl_test;
grant read, write on directory RWA_FILE_LOG to etl_test;
grant read, write on directory RWA_FILE_CONTROL to etl_test;
grant read, write on directory RWA_FILE_LOADER to etl_test;
grant read, write on directory RWA_FILE_SH to etl_test;
grant read, write on directory RWA_FILE_BACKUP to etl_test;
f.加载数据文件
1.加载数据的表
create table F_MUREX_GL
(
data_dt DATE,
areano VARCHAR2(10),
currency VARCHAR2(10),
apcode VARCHAR2(20),
orgcde VARCHAR2(20),
damount NUMBER,
camount NUMBER,
remark VARCHAR2(1000)
);
2.加载数据的控制文件,数据文件,shell脚本,perl脚本如下
a.RWA_EDW_PLEDGE_IMPAWN_INFO.ctl -- sqlldr控制文件 目录:/ETL/control
[etl@ETL control]$ more RWA_EDW_PLEDGE_IMPAWN_INFO.ctl
load data
TRUNCATE into table F_MUREX_GL
fields terminated by X'01'
trailing nullcols
(DATA_DT DATE'yyyy-mm-dd',AREANO,CURRENCY,APCODE,ORGCDE,DAMOUNT,CAMOUNT,REMARK)
b.RWA_EDW_PLEDGE_IMPAWN_INFO.sh -- 加载数据的shell文件 目录:/ETL/loader
[etl@ETL loader]$ more RWA_EDW_PLEDGE_IMPAWN_INFO.sh
#!/bin/sh
. /home/etl/.bash_profile
vOraPwd=$1
sqlldr userid=etl_test/$vOraPwd@ETL control=/ETL/control/RWA_EDW_PLEDGE_IMPAWN_INFO.ctl data=/ETL/data/RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt log=/ETL/log/RWA
_EDW_PLEDGE_IMPAWN_INFO.log bad=/ETL/bad/RWA_EDW_PLEDGE_IMPAWN_INFO.bad
c.RWA_EDW_PLEDGE_IMPAWN_INFO.pl -- 调用加载数据文件的shell脚本(RWA_EDW_PLEDGE_IMPAWN_INFO.sh)