- 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.
在指令碼目錄裡，你會找到prod_master.dat指令碼和prod_master.ctl指令碼，使用在這些檔案裡找到的資訊，建立一個外部表格，名字叫PROD_MASTER，在PROD資料庫的SH方案裡
[oracle@ocm1 ~]$ cd /home/oracle/script/[oracle@ocm1 script]$ lltotal 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 datainfile '/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 12 2 rose 23 1 jone 24 3 jack 35 2 jacky 4
[oracle@ocm1 script]$
參考聯機文檔：Utilities ==> 13 The ORACLE_LOADER Access Driverhttp://docs.oracle.com/cd/B19306_01/server.102/b14215/et_params.htm#i1012274
[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 statementresumable_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 byposition or by keywords. An example of the former case is 'sqlldrscott/tiger foo'; an example of the latter is 'sqlldr control=foouserid=scott/tiger'. One may specify parameters by position beforebut 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 theposition 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.ctlData File: /home/oracle/script/prod_master.dat Bad File: prod_master.bad Discard File: none specified (Allow all discards)
Number to load: ALLNumber to skip: 0Errors allowed: 50Continuation: none specifiedPath used: External Table
Table SH.EXM1, loaded from every logical record.Insert option in effect for this table: INSERTTRAILING 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_00000DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
------以上是刪除暫存資料表的語句
Run began on Thu Mar 27 12:04:44 2014Run ended on Thu Mar 27 12:04:45 2014
Elapsed time was: 00:00:00.91CPU time was: 00:00:00.23[oracle@ocm1 script]$
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/script/'
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 sysdbaConnected.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>
