DBMS_SCHEDULER执行PERL脚本加载数据 - 数据库编程

体与Oracle用户保持一致即可 # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export ORACLE_SID=ETL export ORACLE_TERM=xterm export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" export ORA_NLS33=$ORACLE_HOME/common/nls/admin/data LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib export LD_LIBRARY_PATH export PATH=$PATH:$ORACLE_HOME/bin # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export ORACLE_SID=ETL export ORACLE_TERM=xterm export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" export ORA_NLS33=$ORACLE_HOME/common/nls/admin/data LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib export LD_LIBRARY_PATH export PATH=$PATH:$ORACLE_HOME/bin PATH=$PATH:$HOME/bin

c.配置脚本及数据文件相关路径

[root@ETL /]# mkdir /ETL

[root@ETL /]# cd ETL

[root@ETL ETL]# mkdir bad

[root@ETL ETL]# mkdir log

[root@ETL ETL]# mkdir loader

[root@ETL ETL]# mkdir control

[root@ETL ETL]# mkdir data

[root@ETL ETL]# mkdir backup

[root@ETL ETL]# mkdir sh

[root@ETL ETL]# mkdir perl

[root@ETL ETL]# cd ..

[root@ETL /]# chown -R etl:etl /ETL

[root@ETL /]# chmod -R 777 /ETL

(目录说明:bad(sqlldr加载数据文件被拒的记录),log(sqlldr加载数据文件日志),loader(加载数据文件的perl脚本),control(sqlldr加载数据文件所用到的控制文件),data(sqlldr加载的数据文件,backup(数据文件的备份目录),sh(shell脚本目录),per(perl脚本目录).

d.因为此次实验是用ETL_TEST(数据库用户)调用dbms_schduler包以ETL用户身份加载数据(sqlldr加载),以下为执行'EXECUTABLE'的job相关配置

1.查看$ORACLE_HOME/rdbms/admin/externaljob.ora 权限

[root@ETL ~]# 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 ~]# cd /u01/app/oracle/product/11.2.0/dbhome_1

[root@ETL dbhome_1]# cd rdbms

[root@ETL rdbms]# cd admin

[root@ETL admin]# ls -al|grep externaljob.ora

-rw-r----- 1 root oinstall 1536 Jan 30 13:28 externaljob.ora

(其中文件权限必须和上面一致)

2.配置$ORACLE_HOME/rdbms/admin/externaljob.ora,将run_user=etl run_group=etl 具体如下:

[root@ETL admin]# vi externaljob.ora

# $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 an

DBMS_SCHEDULER执行PERL脚本加载数据(三)