|
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。
- CREATE TABLE hr.extab_employees
- (employee_id NUMBER(4),
- first_name VARCHAR2(20),
- last_name VARCHAR2(25),
- hire_date DATE)
- ORGANIZATION EXTERNAL
- ( TYPE ORACLE_LOADER DEFAULT DIRECTORY mydir
- ACCESS PARAMETERS
- ( records delimited by newline
- badfile mydir2:'empxt%a_%p.bad'
- logfile mydir2:'empxt%a_%p.log'
- fields terminated by ','
- missing field values are null
- ( employee_id, first_name, last_name,
- hire_date char date_format date mask "dd-mon-yyyy"))
- LOCATION ('empxt1.dat', 'empxt2.dat') )
- 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
- select * from dba_directories;
- CREATE TABLE hr.extab_employees
- (employee_id NUMBER(4),
- first_name VARCHAR2(20),
- last_name VARCHAR2(25),
- hire_date DATE)
- ORGANIZATION EXTERNAL
- ( TYPE ORACLE_LOADER DEFAULT DIRECTORY mydir
- ACCESS PARAMETERS
- ( records delimited by newline
- badfile mydir2:'empxt%a_%p.bad'
- logfile mydir2:'empxt%a_%p.log'
- fields terminated by ','
- missing field values are null
- ( employee_id, first_name, last_name,
- hire_date char date_format date mask "dd-mon-yyyy"))
- LOCATION ('empxt1.dat', 'empxt2.dat') )
- PARALLEL REJECT LIMIT UNLIMITED;
-
-
- select * from dba_tables t
- where table_name='EXTAB_EMPLOYEES';
-
- select * from dba_external_tables t
- where t.OWNER='HR';
- 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]$
- sqlldr hr/oracle_4U control=passwd.ctl external_table=GENERATE_ONLY log=passwd.sql
- SQL*Loader: Release 12.1.0.2.0 - Production on Fri Dec 15 10:25:28 2017
- Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
- Path used: External Table
- [oracle@station26 mydir]$ ls
- empxt1.dat empxt2.dat passwd.ctl passwd.dat passwd.sql
- [oracle@station26 mydir]$ vim
复制代码- root:x:0:0:root:/root:/bin/bash
- bin:x:1:1:bin:/bin:/sbin/nologin
- daemon:x:2:2:daemon:/sbin:/sbin/nologin
- adm:x:3:4:adm:/var/adm:/sbin/nologin
- lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
- sync:x:5:0:sync:/sbin:/bin/sync
- shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
- halt:x:7:0:halt:/sbin:/sbin/halt
- mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
- uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
- operator:x:11:0:operator:/root:/sbin/nologin
- games:x:12:100:games:/usr/games:/sbin/nologin
- gopher:x:13:30:gopher:/var/gopher:/sbin/nologin
- ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
- nobody:x:99:99:Nobody:/:/sbin/nologin
- dbus:x:81:81:System message bus:/:/sbin/nologin
- usbmuxd:x:113:113:usbmuxd user:/:/sbin/nologin
- rtkit:x:499:499:RealtimeKit:/proc:/sbin/nologin
- avahi-autoipd:x:170:170:Avahi IPv4LL Stack:/var/lib/avahi-autoipd:/sbin/nologin
- vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin
- abrt:x:173:173::/etc/abrt:/sbin/nologin
- ntp:x:38:38::/etc/ntp:/sbin/nologin
- apache:x:48:48:Apache:/var/www:/sbin/nologin
- saslauth:x:498:76:Saslauthd user:/var/empty/saslauth:/sbin/nologin
- postfix:x:89:89::/var/spool/postfix:/sbin/nologin
- haldaemon:x:68:68:HAL daemon:/:/sbin/nologin
- gdm:x:42:42::/var/lib/gdm:/sbin/nologin
- pulse:x:497:496:PulseAudio System Daemon:/var/run/pulse:/sbin/nologin
- sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
- tcpdump:x:72:72::/:/sbin/nologin
- student:x:600:600::/home/student:/bin/bash
- visitor:x:601:601::/home/visitor:/bin/bash
- rpc:x:32:32:Rpcbind Daemon:/var/cache/rpcbind:/sbin/nologin
- rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
- nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
- oracle:x:602:602::/home/oracle:/bin/bash
- vtl:x:603:608::/home/vtl:/bin/bash
复制代码
变出来的外部表语法:
- CREATE TABLE hr.t_passwd
- (
- "A_USERNAME" VARCHAR2(100),
- "A_PASSWD" CHAR(1),
- "A_UID" NUMBER,
- "A_GID" NUMBER,
- "A_COMMENT" VARCHAR2(200),
- "A_HOME" VARCHAR2(80),
- "A_SHELL" VARCHAR2(50)
- )
- ORGANIZATION external
- (
- TYPE oracle_loader
- DEFAULT DIRECTORY MYDIR
- ACCESS PARAMETERS
- (
- RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
- BADFILE 'MYDIR':'passwd.bad'
- LOGFILE 'passwd.sql_xt'
- READSIZE 1048576
- FIELDS TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"' LDRTRIM
- REJECT ROWS WITH ALL NULL FIELDS
- (
- "A_USERNAME" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "A_PASSWD" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "A_UID" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "A_GID" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "A_COMMENT" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "A_HOME" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "A_SHELL" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"'
- )
- )
- location
- (
- 'passwd.dat'
- )
- )REJECT LIMIT UNLIMITED
- ;
复制代码 固定列位置的外部表:
先看控制文件(ulcase2.ctl):
- -- Copyright (c) 1991 by Oracle Corporation
- -- NAME
- -- ulcase2.ctl - <one-line expansion of the name>
- -- DESCRIPTION
- -- <short description of component this file declares/defines>
- -- RETURNS
- --
- -- NOTES
- -- <other useful comments, qualifications, etc.>
- -- MODIFIED (MM/DD/YY)
- -- ksudarsh 04/08/94 - merge changes from branch 1.3.710.1
- -- ksudarsh 02/21/94 - quote dat file
- -- ksudarsh 03/11/93 - make filename lowercase
- -- ksudarsh 11/06/92 - infile is ulcase2
- -- cheigham 08/28/91 - Creation
- --
- -- $Header: ulcase2.ctl,v 1.4 1994/04/08 13:42:44 ksudarsh Exp $ case2.ctl
- --
- LOAD DATA
- INFILE 'ulcase2.dat'
- INTO TABLE EMP
- ( EMPNO POSITION(01:04) INTEGER EXTERNAL,
- ENAME POSITION(06:15) CHAR,
- JOB POSITION(17:25) CHAR,
- MGR POSITION(27:30) INTEGER EXTERNAL,
- SAL POSITION(32:39) DECIMAL EXTERNAL,
- COMM POSITION(41:48) DECIMAL EXTERNAL,
- DEPTNO POSITION(50:51) INTEGER EXTERNAL)
复制代码- sqlldr hr/oracle_4U control=ulcase2.ctl external_table=GENERATE_ONLY log=emp.sql
复制代码- CREATE TABLE hr.emp
- (
- "EMPNO" NUMBER(4),
- "ENAME" CHAR(10),
- "JOB" CHAR(9),
- "MGR" NUMBER(4),
- "SAL" NUMBER(7,2),
- "COMM" NUMBER(7,2),
- "DEPTNO" NUMBER(2)
- )
- ORGANIZATION external
- (
- TYPE oracle_loader
- DEFAULT DIRECTORY MYDIR
- ACCESS PARAMETERS
- (
- RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
- BADFILE 'MYDIR':'ulcase2.bad'
- LOGFILE 'emp.sql_xt'
- READSIZE 1048576
- FIELDS LDRTRIM
- REJECT ROWS WITH ALL NULL FIELDS
- (
- "EMPNO" (1:4) INTEGER EXTERNAL(4),
- "ENAME" (6:15) CHAR(10),
- "JOB" (17:25) CHAR(9),
- "MGR" (27:30) INTEGER EXTERNAL(4),
- "SAL" (32:39) INTEGER EXTERNAL(8),
- "COMM" (41:48) INTEGER EXTERNAL(8),
- "DEPTNO" (50:51) INTEGER EXTERNAL(2)
- )
- )
- location
- (
- 'ulcase2.dat'
- )
- )REJECT LIMIT UNLIMITED
- ;
复制代码
二进制外部表,1Z0-052书17-34的改进语句:
- CREATE TABLE hr.ext_emp_query_results
- ORGANIZATION EXTERNAL
- (
- TYPE ORACLE_DATAPUMP
- DEFAULT DIRECTORY mydir
- LOCATION ('emp1.exp',mydir2:'emp2.exp',mydir2:'emp3.exp')
- )
- PARALLEL 4
- AS
- SELECT e.first_name,e.last_name,d.department_name,
- decode (mod(e.salary,1000), 1 ,'*', 2,'**', 3,'***', 4,'****' ,'------') salsim
- FROM hr.employees e, hr.departments d
- WHERE e.department_id = d.department_id AND
- d.department_name in
- ('Marketing', 'Purchasing');
复制代码- CREATE TABLE hr.ext_emp_query_results
- ORGANIZATION EXTERNAL
- (
- TYPE ORACLE_DATAPUMP
- DEFAULT DIRECTORY mydir
- LOCATION ('emp1.exp',mydir2:'emp2.exp',mydir2:'emp3.exp')
- )
- PARALLEL 4
- AS
- SELECT e.first_name,e.last_name,d.department_name,
- decode (mod(e.salary,1000), 1 ,'*', 2,'**', 3,'***', 4,'****' ,'------') salsim
- FROM hr.employees e, hr.departments d
- WHERE e.department_id = d.department_id AND
- d.department_name in
- ('Marketing', 'Purchasing');
-
-
-
-
- select * from hr.ext_emp_query_results;
-
- drop table hr.ext_emp_query_results;
-
-
- CREATE TABLE hr.ext_emp_query_results
- ( first_name varchar2(30) , last_name varchar2(30) , department_name varchar2(30), salsim varchar2(10) )
- ORGANIZATION EXTERNAL
- (
- TYPE ORACLE_DATAPUMP
- DEFAULT DIRECTORY mydir
- LOCATION ('emp1.exp',mydir2:'emp2.exp',mydir2:'emp3.exp')
- )
- PARALLEL 4;
-
-
- select * from hr.ext_emp_query_results;
复制代码
|
|