课程第59次（2017-11-25星期六上午）

botang

1Z0-052第17章
1Z0-052共19章（上完17章），1Z0-053共21章（上完17章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的34章
外部表:
文本的外部表：只有“导入”（迅速识别文本文件的第一行，不用等待全部导入大的文本文件所有行)
datapump的外部表：既有“导入"也有“导出”。“导出”时，用的是直接识别行和列的技术。
                                expdp导出的时候用的是直接路径加载+直接识别行和列的技术。因此datapump的外部表的“导出”产出物和expdp导出的产出物是格式不通用的。
                                前者的dumpfile不能用后者 impdp进行导入。
                                datapump的外部表“导出”具有expdp所不能做的优点：The results of a complex query can be unloaded to an external file。

1. CREATE TABLE hr.extab_employees
   
2.                   (employee_id       NUMBER(4),
   
3.                    first_name        VARCHAR2(20),
   
4.                     last_name         VARCHAR2(25),
   
5.                      hire_date         DATE)
   
6. ORGANIZATION EXTERNAL
   
7.      ( TYPE ORACLE_LOADER DEFAULT DIRECTORY mydir
   
8.        ACCESS PARAMETERS
   
9.        ( records delimited by newline
   
10.          badfile mydir2:'empxt%a_%p.bad'
    
11.          logfile mydir2:'empxt%a_%p.log'
    
12.          fields terminated by ','
    
13.          missing field values are null
    
14.      ( employee_id, first_name, last_name,
    
15.                         hire_date char date_format date mask "dd-mon-yyyy"))
    
16.      LOCATION ('empxt1.dat', 'empxt2.dat') )
    
17.      PARALLEL  REJECT LIMIT UNLIMITED;
    

复制代码

“records delimited by newline“都要写。
“missing field values are null“相当于sqlloader控制文件的"trailing nullcols" 。
hire_date char date_format date mask "dd-mon-yyyy“相当于sqlloader控制文件的hire_date date(20) "dd-mon-yyyy“



empxt1.dat:
100,SAN,ZHANG,01-jan-2001
empxt2.dat：
200,SI,LI,02-feb-2002

1. select  * from dba_directories;
   
2. 
   
3. 
   
4. CREATE TABLE hr.extab_employees
   
5.                   (employee_id       NUMBER(4),
   
6.                    first_name        VARCHAR2(20),
   
7.                     last_name         VARCHAR2(25),
   
8.                      hire_date         DATE)
   
9. ORGANIZATION EXTERNAL
   
10.      ( TYPE ORACLE_LOADER DEFAULT DIRECTORY mydir
    
11.        ACCESS PARAMETERS
    
12.        ( records delimited by newline
    
13.          badfile mydir2:'empxt%a_%p.bad'
    
14.          logfile mydir2:'empxt%a_%p.log'
    
15.          fields terminated by ','
    
16.          missing field values are null
    
17.      ( employee_id, first_name, last_name,
    
18.                         hire_date char date_format date mask "dd-mon-yyyy"))
    
19.      LOCATION ('empxt1.dat', 'empxt2.dat') )
    
20.      PARALLEL  REJECT LIMIT UNLIMITED;
    
21.          
    
22.          
    
23.          select  * from dba_tables t
    
24.          where table_name='EXTAB_EMPLOYEES';
    
25.          
    
26.          select * from dba_external_tables t
    
27.          where t.OWNER='HR';
    
28. 
    
29.          select  * from hr.extab_employees;

复制代码

利用图形界面，自动产生控制文件：







[oracle@station26 mydir]$ sqlldr   

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Dec 15 10:23:16 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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
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)
no_index_errors -- abort load on any index errors  (Default FALSE)
partition_memory -- direct path partition memory limit to start spilling (kb)  (Default 0)
     table -- Table for express mode load
date_format -- Date format for express mode load
timestamp_format -- Timestamp format for express mode load
terminated_by -- terminated by character for express mode load
enclosed_by -- enclosed by character for express mode load
optionally_enclosed_by -- optionally enclosed by character for express mode load
characterset -- characterset for express mode load
degree_of_parallelism -- degree of parallelism for express mode load and external table load
      trim -- trim type for express mode load and external table load
       csv -- csv format data files for express mode load
    nullif -- table level nullif clause for express mode load
field_names -- field names setting for first record of data files for express mode load
dnfs_enable -- option for enabling or disabling Direct NFS (dNFS) for input data files  (Default FALSE)
dnfs_readbuffers -- the number of Direct NFS (dNFS) read buffers  (Default 4)

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@station26 mydir]$

1. sqlldr  hr/oracle_4U   control=passwd.ctl   external_table=GENERATE_ONLY  log=passwd.sql
   
2. 
   
3. SQL*Loader: Release 12.1.0.2.0 - Production on Fri Dec 15 10:25:28 2017
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Path used:      External Table
   
8. [oracle@station26 mydir]$ ls
   
9. empxt1.dat  empxt2.dat  passwd.ctl  passwd.dat  passwd.sql
   
10. [oracle@station26 mydir]$ vim
    

复制代码

1. root:x:0:0:root:/root:/bin/bash
   
2. bin:x:1:1:bin:/bin:/sbin/nologin
   
3. daemon:x:2:2:daemon:/sbin:/sbin/nologin
   
4. adm:x:3:4:adm:/var/adm:/sbin/nologin
   
5. lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
   
6. sync:x:5:0:sync:/sbin:/bin/sync
   
7. shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
   
8. halt:x:7:0:halt:/sbin:/sbin/halt
   
9. mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
   
10. uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
    
11. operator:x:11:0:operator:/root:/sbin/nologin
    
12. games:x:12:100:games:/usr/games:/sbin/nologin
    
13. gopher:x:13:30:gopher:/var/gopher:/sbin/nologin
    
14. ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
    
15. nobody:x:99:99:Nobody:/:/sbin/nologin
    
16. dbus:x:81:81:System message bus:/:/sbin/nologin
    
17. usbmuxd:x:113:113:usbmuxd user:/:/sbin/nologin
    
18. rtkit:x:499:499:RealtimeKit:/proc:/sbin/nologin
    
19. avahi-autoipd:x:170:170:Avahi IPv4LL Stack:/var/lib/avahi-autoipd:/sbin/nologin
    
20. vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin
    
21. abrt:x:173:173::/etc/abrt:/sbin/nologin
    
22. ntp:x:38:38::/etc/ntp:/sbin/nologin
    
23. apache:x:48:48:Apache:/var/www:/sbin/nologin
    
24. saslauth:x:498:76:Saslauthd user:/var/empty/saslauth:/sbin/nologin
    
25. postfix:x:89:89::/var/spool/postfix:/sbin/nologin
    
26. haldaemon:x:68:68:HAL daemon:/:/sbin/nologin
    
27. gdm:x:42:42::/var/lib/gdm:/sbin/nologin
    
28. pulse:x:497:496:PulseAudio System Daemon:/var/run/pulse:/sbin/nologin
    
29. sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
    
30. tcpdump:x:72:72::/:/sbin/nologin
    
31. student:x:600:600::/home/student:/bin/bash
    
32. visitor:x:601:601::/home/visitor:/bin/bash
    
33. rpc:x:32:32:Rpcbind Daemon:/var/cache/rpcbind:/sbin/nologin
    
34. rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
    
35. nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
    
36. oracle:x:602:602::/home/oracle:/bin/bash
    
37. vtl:x:603:608::/home/vtl:/bin/bash
    

复制代码

变出来的外部表语法：

1. CREATE TABLE hr.t_passwd
   
2. (
   
3.   "A_USERNAME" VARCHAR2(100),
   
4.   "A_PASSWD" CHAR(1),
   
5.   "A_UID" NUMBER,
   
6.   "A_GID" NUMBER,
   
7.   "A_COMMENT" VARCHAR2(200),
   
8.   "A_HOME" VARCHAR2(80),
   
9.   "A_SHELL" VARCHAR2(50)
   
10. )
    
11. ORGANIZATION external
    
12. (
    
13.   TYPE oracle_loader
    
14.   DEFAULT DIRECTORY MYDIR
    
15.   ACCESS PARAMETERS
    
16.   (
    
17.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
    
18.     BADFILE 'MYDIR':'passwd.bad'
    
19.     LOGFILE 'passwd.sql_xt'
    
20.     READSIZE 1048576
    
21.     FIELDS TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"' LDRTRIM
    
22.     REJECT ROWS WITH ALL NULL FIELDS
    
23.     (
    
24.       "A_USERNAME" CHAR(255)
    
25.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
26.       "A_PASSWD" CHAR(255)
    
27.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
28.       "A_UID" CHAR(255)
    
29.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
30.       "A_GID" CHAR(255)
    
31.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
32.       "A_COMMENT" CHAR(255)
    
33.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
34.       "A_HOME" CHAR(255)
    
35.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
    
36.       "A_SHELL" CHAR(255)
    
37.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"'
    
38.     )
    
39.   )
    
40. location
    
41.   (
    
42.     'passwd.dat'
    
43.   )
    
44. )REJECT LIMIT UNLIMITED
    
45. ;
    

复制代码

固定列位置的外部表：
先看控制文件（ulcase2.ctl）：

1. -- Copyright (c) 1991 by Oracle Corporation
   
2. --   NAME
   
3. --     ulcase2.ctl - <one-line expansion of the name>
   
4. --   DESCRIPTION
   
5. --     <short description of component this file declares/defines>
   
6. --   RETURNS
   
7. --
   
8. --   NOTES
   
9. --     <other useful comments, qualifications, etc.>
   
10. --   MODIFIED   (MM/DD/YY)
    
11. --    ksudarsh   04/08/94 -  merge changes from branch 1.3.710.1
    
12. --    ksudarsh   02/21/94 -  quote dat file
    
13. --    ksudarsh   03/11/93 -  make filename lowercase
    
14. --    ksudarsh   11/06/92 -  infile is ulcase2
    
15. --    cheigham   08/28/91 -  Creation
    
16. --
    
17. -- $Header: ulcase2.ctl,v 1.4 1994/04/08 13:42:44 ksudarsh Exp $ case2.ctl
    
18. --
    
19. LOAD DATA
    
20. INFILE 'ulcase2.dat'
    
21. INTO TABLE EMP
    
22. 
    
23. ( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
    
24.   ENAME    POSITION(06:15) CHAR,
    
25.   JOB      POSITION(17:25) CHAR,
    
26.   MGR      POSITION(27:30) INTEGER EXTERNAL,
    
27.   SAL      POSITION(32:39) DECIMAL EXTERNAL,
    
28.   COMM     POSITION(41:48) DECIMAL EXTERNAL,
    
29.   DEPTNO   POSITION(50:51) INTEGER EXTERNAL)
    

复制代码

1. sqlldr  hr/oracle_4U   control=ulcase2.ctl   external_table=GENERATE_ONLY  log=emp.sql

复制代码

1. CREATE TABLE hr.emp
   
2. (
   
3.   "EMPNO" NUMBER(4),
   
4.   "ENAME" CHAR(10),
   
5.   "JOB" CHAR(9),
   
6.   "MGR" NUMBER(4),
   
7.   "SAL" NUMBER(7,2),
   
8.   "COMM" NUMBER(7,2),
   
9.   "DEPTNO" NUMBER(2)
   
10. )
    
11. ORGANIZATION external
    
12. (
    
13.   TYPE oracle_loader
    
14.   DEFAULT DIRECTORY MYDIR
    
15.   ACCESS PARAMETERS
    
16.   (
    
17.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
    
18.     BADFILE 'MYDIR':'ulcase2.bad'
    
19.     LOGFILE 'emp.sql_xt'
    
20.     READSIZE 1048576
    
21.     FIELDS LDRTRIM
    
22.     REJECT ROWS WITH ALL NULL FIELDS
    
23.     (
    
24.       "EMPNO" (1:4) INTEGER EXTERNAL(4),
    
25.       "ENAME" (6:15) CHAR(10),
    
26.       "JOB" (17:25) CHAR(9),
    
27.       "MGR" (27:30) INTEGER EXTERNAL(4),
    
28.       "SAL" (32:39) INTEGER EXTERNAL(8),
    
29.       "COMM" (41:48) INTEGER EXTERNAL(8),
    
30.       "DEPTNO" (50:51) INTEGER EXTERNAL(2)
    
31.     )
    
32.   )
    
33.   location
    
34.   (
    
35.     'ulcase2.dat'
    
36.   )
    
37. )REJECT LIMIT UNLIMITED
    
38. ;
    

复制代码

二进制外部表，1Z0-052书17-34的改进语句：

1. CREATE TABLE hr.ext_emp_query_results
   
2.   ORGANIZATION EXTERNAL
   
3.   (
   
4.     TYPE ORACLE_DATAPUMP
   
5.     DEFAULT DIRECTORY mydir
   
6.     LOCATION ('emp1.exp',mydir2:'emp2.exp',mydir2:'emp3.exp')
   
7.   )
   
8. PARALLEL 4
   
9. AS
   
10. SELECT e.first_name,e.last_name,d.department_name,
    
11.   decode (mod(e.salary,1000), 1 ,'*', 2,'**', 3,'***', 4,'****' ,'------')  salsim
    
12. FROM   hr.employees e, hr.departments d
    
13. WHERE  e.department_id = d.department_id AND
    
14.        d.department_name in
    
15.                      ('Marketing', 'Purchasing');

复制代码

1. CREATE TABLE hr.ext_emp_query_results
   
2.   ORGANIZATION EXTERNAL
   
3.   (
   
4.     TYPE ORACLE_DATAPUMP
   
5.     DEFAULT DIRECTORY mydir
   
6.     LOCATION ('emp1.exp',mydir2:'emp2.exp',mydir2:'emp3.exp')
   
7.   )
   
8. PARALLEL 4
   
9. AS
   
10. SELECT e.first_name,e.last_name,d.department_name,
    
11.   decode (mod(e.salary,1000), 1 ,'*', 2,'**', 3,'***', 4,'****' ,'------')  salsim
    
12. FROM   hr.employees e, hr.departments d
    
13. WHERE  e.department_id = d.department_id AND
    
14.        d.department_name in
    
15.                      ('Marketing', 'Purchasing');
    
16. 
    
17.                                          
    
18.                                          
    
19.                                          
    
20.                                          
    
21.         select  * from hr.ext_emp_query_results;
    
22.         
    
23.         drop table hr.ext_emp_query_results;
    
24.                                          
    
25.                
    
26.                 CREATE TABLE hr.ext_emp_query_results
    
27.                 (  first_name  varchar2(30) ,   last_name   varchar2(30) ,   department_name  varchar2(30),  salsim   varchar2(10)  )
    
28.   ORGANIZATION EXTERNAL
    
29.   (
    
30.     TYPE ORACLE_DATAPUMP
    
31.     DEFAULT DIRECTORY mydir
    
32.     LOCATION ('emp1.exp',mydir2:'emp2.exp',mydir2:'emp3.exp')
    
33.   )
    
34. PARALLEL 4;
    
35.         
    
36.         
    
37.                 select  * from hr.ext_emp_query_results;                                 

复制代码