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方案里
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工具生成创建外部表的语句。
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, 'sqll