OCM_Session4_3_Oracle_Loader External Tables
- 3. Oracle_Loader External Tables
- 3.1 In the scripts directory, you will find prod_master.dat and prod_master.ctl. Using the information found in these files, create an external table names PROD_MASTER in the SH schema of the PROD database.
- 1.首先找到这两个脚本，查看一下内容；
[oracle@ocm1 ~]$ cd /home/oracle/script/ [oracle@ocm1 script]$ ll total 12 -rw-r--r-- 1 oracle oinstall 64 Mar 19 14:22 create_bishhr.sql -rw-r--r-- 1 root root 154 Mar 27 11:01 prod_master.ctl -rw-r--r-- 1 root root 56 Mar 27 11:01 prod_master.dat [oracle@ocm1 script]$ cat prod_master.ctl load data infile '/home/oracle/script/prod_master.dat' into table sh.exm1 fields terminated by whitespace --插入表sh.exm1，以空白符为间隔 TRAILING NULLCOLS (emp_no,dept_no,name,num)
[oracle@ocm1 script]$ cat prod_master.dat 1 1 tom 1 2 2 rose 2 3 1 jone 2 4 3 jack 3 5 2 jacky 4
[oracle@ocm1 script]$
参考联机文档： Utilities ==> 13 The ORACLE_LOADER Access Driver http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_params.htm#i1012274
- 2.使用sqlldr工具生成创建外部表的语句。
[oracle@ocm1 script]$ sqlldr
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 27 11:23:24 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password control -- control file name log -- log file name bad -- bad file name data -- data file name discard -- discard file name discardmax -- number of discards to allow (Default all) skip -- number of logical records to skip (Default 0) load -- number of logical records to load (Default all) errors -- number of errors to allow (Default 50) rows -- number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) bindsize -- size of conventional path bind array in bytes (Default 256000) silent -- suppress messages during run (header,feedback,errors,discards,partitions) direct -- use direct path (Default FALSE) parfile -- parameter file: name of file that contains parameter specifications parallel -- do parallel load (Default FALSE) file -- file to allocate extents from skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) readsize -- size of read buffer (Default 1048576) external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED) columnarrayrows -- number of rows for direct path column array (Default 5000) streamsize -- size of direct path stream buffer in bytes (Default 256000) multithreading -- use multithreading in direct path resumable -- enable or disable resumable for current session (Default FALSE) resumable_name -- text string to help identify resumable statement resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200) date_cache -- size (in entries) of date conversion cache (Default 1000)
PLEASE NOTE: Command-line parameters may be specified either by position or by keywords. An example of the former case is 'sqlldr scott/tiger foo'; an example of the latter is 'sqlldr control=foo userid=scott/tiger'. One may specify parameters by position before but not after parameters specified by keywords. For example, 'sqlldr scott/tiger control=foo logfile=log' is allowed, but 'sqlldr scott/tiger control=foo log' is not, even though the position of the parameter 'log' is correct. [oracle@ocm1 script]$
sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY
- 3.执行sqlldr，生成外部表，不过这里应该先创建sh.exm1表，否则会报错
[oracle@ocm1 script]$ sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 27 12:04:00 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-941: Error during describe of table SH.EXM1 ORA-04043: object SH.EXM1 does not exist [oracle@ocm1 script]$
a.所以首先要创建一个表名为sh.exm1
SH@PROD> create table sh.exm1 (emp_no int,dept_no int ,name varchar2(30),num int);
Table created.
b.再来导入一次：
[oracle@ocm1 script]$ sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 27 12:04:44 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
[oracle@ocm1 script]$
c.查看日志：
[oracle@ocm1 script]$ cat prod_master.log
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 27 12:04:44 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: prod_master.ctl Data File: /home/oracle/script/prod_master.dat Bad File: prod_master.bad Discard File: none specified (Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: External Table
Table SH.EXM1, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMP_NO FIRST * WHT CHARACTER DEPT_NO NEXT * WHT CHARACTER NAME NEXT * WHT CHARACTER NUM NEXT * WHT CHARACTER
CREATE DIRECTORY statements needed for files ------------------------------------------------------------------------ CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script' CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'
CREATE TABLE statement for external table: ------------------------------------------------------------------------ CREATE TABLE "SYS_SQLLDR_X_EXT_EXM1" ( "EMP_NO" NUMBER(38), "DEPT_NO" NUMBER(38), "NAME" VARCHAR2(30), "NUM" NUMBER(38) ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.bad' LOGFILE 'prod_master.log_xt' READSIZE 1048576 FIELDS TERMINATED BY WHITESPACE LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( "EMP_NO" CHAR(255) TERMINATED BY WHITESPACE, "DEPT_NO" CHAR(255) TERMINATED BY WHITESPACE, "NAME" CHAR(255) TERMINATED BY WHITESPACE, "NUM" CHAR(255) TERMINATED BY WHITESPACE ) ) location ( 'prod_master.dat' ) )REJECT LIMIT UNLIMITED
-------以上红色部分是创建外部表的语句
INSERT statements used to load internal tables: ------------------------------------------------------------------------ INSERT /*+ append */ INTO SH.EXM1 ( EMP_NO, DEPT_NO, NAME, NUM ) SELECT "EMP_NO", "DEPT_NO", "NAME", "NUM" FROM "SYS_SQLLDR_X_EXT_EXM1"
------以上是加载数据的语句
statements to cleanup objects created by previous statements: ------------------------------------------------------------------------ DROP TABLE "SYS_SQLLDR_X_EXT_EXM1" DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
------以上是删除临时表的语句
Run began on Thu Mar 27 12:04:44 2014 Run ended on Thu Mar 27 12:04:45 2014
Elapsed time was: 00:00:00.91 CPU time was: 00:00:00.23 [oracle@ocm1 script]$
- 4.SH用户下创建目录，创建外部表
a.创建目录
SH@PROD> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'; CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/' * ERROR at line 1: ORA-01031: insufficient privileges
SH@PROD> conn /as sysdba Connected. SYS@PROD> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/' 2 ;
Directory created.
SYS@PROD> grant read,write on directory SYS_SQLLDR_XT_TMPDIR_00000 to sh;
Grant succeeded.
SYS@PROD>
或者直接是sys授权创建目录给sh用户，sh用户再创建 SYS@PROD>grant create any directory to sh; SH@PRODCREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'
b.创建外部表语句
CREATE TABLE "PROD_MASTER" ---修改成题目要求的名字 ( "EMP_NO" NUMBER(38), "DEPT_NO" NUMBER(38), "NAME" VARCHAR2(30), "NUM" NUMBER(38) ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.bad' LOGFILE 'prod_master.log_xt' READSIZE 1048576 FIELDS TERMINATED BY WHITESPACE LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( "EMP_NO" CHAR(255) TERMINATED BY WHITESPACE, "DEPT_NO" CHAR(255) TERMINATED BY WHITESPACE, "NAME" CHAR(255) TERMINATED BY WHITESPACE, "NUM" CHAR(255) TERMINATED BY WHITESPACE ) ) location ( 'prod_master.dat' ) )REJECT LIMIT UNLIMITED
c.使用SH用户在SQLPLUS命令下执行
SH@PROD> CREATE TABLE "PROD_MASTER" 2 ( 3 "EMP_NO" NUMBER(38), 4 "DEPT_NO" NUMBER(38), 5 "NAME" VARCHAR2(30), 6 "NUM" NUMBER(38) 7 ) 8 ORGANIZATION external 9 ( 10 TYPE oracle_loader 11 DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 12 ACCESS PARAMETERS 13 ( 14 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 15 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'prod_master.bad' 16 LOGFILE 'prod_master.log_xt' 17 READSIZE 1048576 18 FIELDS TERMINATED BY WHITESPACE LDRTRIM 19 MISSING FIELD VALUES ARE NULL 20 REJECT ROWS WITH ALL NULL FIELDS 21 ( 22 "EMP_NO" CHAR(255) 23 TERMINATED BY WHITESPACE, 24 "DEPT_NO" CHAR(255) 25 TERMINATED BY WHITESPACE, 26 "NAME" CHAR(255) 27 TERMINATED BY WHITESPACE, 28 "NUM" CHAR(255) 29 TERMINATED BY WHITESPACE ) 30 31 ) 32 location 33 ( 34 'prod_master.dat' 35 ) 36 )REJECT LIMIT UNLIMITED 37 ;
Table created.
SH@PROD> desc PROD_MASTER Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_NO NUMBER(38) DEPT_NO NUMBER(38) NAME VARCHAR2(30) NUM NUMBER(38)
SH@PROD> select * from PROD_MASTER;
EMP_NO DEPT_NO NAME NUM ---------- ---------- ------------------------------ ---------- 1 1 tom 1 2 2 rose 2 3 1 jone 2 4 3 jack 3 5 2 jacky 4
5 rows selected.
SH@PROD>
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。