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

d 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 =etl run_group =etl

3.查看$ORACLE_HOME/bin/extjob文件权限

[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;

gra

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