利用oracle dbms_scheduler执行windows的bat脚本加载数据

利用oracle dbms_scheduler执行windows的bat脚本加载数据

1.windows 配置dbms_scheduler 运行external job需要做以下配置

A.确保Windows服务启动 -- OracleJobSchedulerORACLESID,我的数据库SID为rwadb 所以为OracleJobSchedulerRWADB，如下图所示

2.创建一个操作系统用户，我这里创建用户rwa_test

3.配置用户归属组，将这个用户归属到ora_dba组

4.配置externaljob.ora文件，我的安装目录为C:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN，这个根据实际情况而定，更改如下

# $Header: externaljob.ora 16-dec-2005.20:47:13 rramkiss Exp $
#
# Copyright (c) 2005, Oracle. All rights reserved.  
# NAME
#   externaljob.ora
# FUNCTION
#   This configuration file is used by dbms_scheduler when executing external
#   (operating system) jobs. It contains the user and group to run external
#   jobs as. It must only be writable by the owner and must be owned by root.
#   If extjob is not setuid then the only allowable run_user
#   is the user Oracle runs as and the only allowable run_group is the group
#   Oracle runs as.
#
# NOTES
#   For Porters: The user and group specified here should be a lowly privileged
#                user and group for your platform. For Linux this is nobody
#                and nobody.
# MODIFIED
#     rramkiss   12/09/05 -  Creation
#
##############################################################################
# External job execution configuration file externaljob.ora
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site.  Important system parameters
# are discussed, and default settings given.
#
# This configuration file is used by dbms_scheduler when executing external
# (operating system) jobs. It contains the user and group to run external
# jobs as. It must only be writable by the owner and must be owned by root.
# If extjob is not setuid then the only allowable run_user
# is the user Oracle runs as and the only allowable run_group is the group
# Oracle runs as.


run_user = RWA_TEST
run_group = ora_dba

6.创建加载数据bat脚本

a.创建bat脚本内容如下，因为我是把这个脚本放到C:\rwa_file\perl目录，脚本名称为load_data.bat

sqlldr.exe userid=rwa_dev/rwa_dev@rwadb control=C:\rwa_file\control\F_MUREX_GL_0001.ctl data=C:\rwa_file\data\F_MUREX_GL_0001_20150531_001.txt

b.创建控制文件脚本F_MUREX_GL_0001.ctl，内容如下，目录为C:\rwa_file\control\

load data
TRUNCATE into table F_MUREX_GL
fields terminated by X'01' 
trailing nullcols
(DATA_DT DATE,AREANO,CURRENCY,APCODE,ORGCDE,DAMOUNT,CAMOUNT,REMARK)

c.创建数据文件F_MUREX_GL_0001_20150531_001.txt，目录为C:\rwa_file\data\，内容如下

2014-06-30   00350AED0232    6114                  0.000        1000000.000                      
2014-06-30   00350AUD0148    6107            4538300.000              0.000                      
2014-06-30   00350AUD0110    6107            1526300.000              0.000                      
2014-06-30   00350AUD0971    6107            8006100.000              0.000                      
2014-06-30   00350AUD0158    6107                154.430              0.000                      
2014-06-30   00350AUD5497    6108                  0.000          15200.000                      
2014-06-30   00350AUD0155    6108            1000000.000              0.000                      
2014-06-30   00350CAD0239    6107                  0.000         950000.000                      
2014-06-30   00350CAD0247    6107                  0.000         950000.000                      
2014-06-30   00350CAD9317    6107             262222.000              0.000                      
2014-06-30   00350CAD0123    6114            1000000.000              0.000                      
2014-06-30   00350CHF0971    6107            2383200.000              0.000                      
2014-06-30   00036CNY9867    6118             572590.240              0.000                      

d.创建表结构

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

7.新增环境变量，此处设置变量可以不用在脚本指定环境变量，使脚本更简洁，重启服务器

8.重启服务器后创建job如下，我这里用rwa_dev(数据库用户），用sys用户进行授权，并创建相应的directory

a.创建rwa_dev用户并授权

create user rwa_dev identified by rwa_dev default tablespace etldata;
grant create session to rwa_dev;
grant debug connect session to rwa_dev;
grant create table to rwa_dev;
grant create view to rwa_dev;
grant create synonym to rwa_dev;
grant create role to rwa_dev;
grant alter profile to rwa_dev;
grant create any directory to rwa_dev;
grant alter user to rwa_dev;
grant create any job to rwa_dev;
grant create job to rwa_dev;
grant create procedure to rwa_dev;
grant unlimited tablespace to rwa_dev;
grant create public synonym to rwa_dev;
grant alter any role to rwa_dev;
grant create database link to rwa_dev;
grant create cluster to rwa_dev;
grant debug any procedure to rwa_dev;
grant create profile to rwa_dev;
grant import full database to rwa_dev;
grant create trigger to rwa_dev;
grant create sequence to rwa_dev;
grant restricted session to rwa_dev;
grant create tablespace to rwa_dev;
grant alter tablespace to rwa_dev;
grant execute on dbms_crypto to rwa_dev;
grant all on dbms_crypto to rwa_dev;
grant execute on dbms_pipe to rwa_dev;
grant all on dbms_pipe to rwa_dev;
grant execute on dbms_lock to rwa_dev;
grant all on dbms_lock to rwa_dev;
grant select on v_$session to rwa_dev;
grant select on gv_$session to rwa_dev;
grant execute on dbms_scheduler to rwa_dev;
grant all on dbms_scheduler to rwa_dev;
grant select_catalog_role to rwa_dev;
grant alter system to rwa_dev;
grant create any type to rwa_dev;
grant execute on dbms_rls to rwa_dev;
grant all on dbms_rls to rwa_dev;
grant execute on dbms_session to rwa_dev;                                                                                     
grant all on dbms_session to rwa_dev; 
grant alter session to rwa_dev;


grant execute on utl_file  to rwa_dev;
grant manage scheduler to rwa_dev;
grant create external job to rwa_dev;   
grant create any context to rwa_dev;


grant CREATE JOB            to rwa_dev;
grant CREATE ANY JOB        to rwa_dev;
grant CREATE EXTERNAL JOB   to rwa_dev;
grant EXECUTE ANY PROGRAM   to rwa_dev;
grant EXECUTE ANY CLASS     to rwa_dev;
grant MANAGE SCHEDULER      to rwa_dev;


grant select on v_$statname to rwa_dev;                 
grant select on v_$sesstat to rwa_dev;                  
grant select on v_$session to rwa_dev;                  
grant select on v_$mystat to rwa_dev; 

b.创建directory

create or replace directory RWA_FILE_DATA as 'C:\rwa_file\data';
create or replace directory RWA_FILE_BAD as 'C:\rwa_file\bad';
create or replace directory RWA_FILE_LOG as 'C:\rwa_file\log';
create or replace directory RWA_FILE_CONTROL as 'C:\rwa_file\control';
create or replace directory RWA_FILE_LOADER as 'C:\rwa_file\loader';
create or replace directory RWA_FILE_SH as 'C:\rwa_file\sh';
create or replace directory RWA_FILE_BACKUP as 'C:\rwa_file\backup';
grant read, write on directory RWA_FILE_DATA to rwa_dev;
grant read, write on directory RWA_FILE_BAD to rwa_dev;
grant read, write on directory RWA_FILE_LOG to rwa_dev;
grant read, write on directory RWA_FILE_CONTROL to rwa_dev;
grant read, write on directory RWA_FILE_LOADER to rwa_dev;
grant read, write on directory RWA_FILE_SH to rwa_dev;
grant read, write on directory RWA_FILE_BACKUP to rwa_dev;

c.在rwa_dev用户下创建external job

BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
   job_name             => 'LOAD_TEST',
   job_type             => 'EXECUTABLE',
   number_of_arguments  => 0,
   job_action           => 'C:\rwa_file\perl\load_data.bat',
   auto_drop            => FALSE
   );
end;

d.执行job并查看日志

运行：

begin
   DBMS_SCHEDULER.ENABLE('LOAD_TEST');
end;

查看日志：

select * from dba_scheduler_job_run_details
where owner = 'RWA_DEV'
 and job_name = 'LOAD_TEST'

;

清除日志：

begin
dbms_scheduler.purge_log(job_name => 'LOAD_TEST');
end;

删除JOB:

begin
  dbms_scheduler.drop_job(job_name => 'LOAD_TEST');
end;

查看加载结果：

select * from f_murex_gl;