——————————- 使用SPOOL命令把结果输出到文本中 ——————————-

1. SQL*Loader:
   1. 概念:
      1. 可以在服务器端和客户端操作,数据可以存放在服务器端或者客户端;
      2. 可以导入固定格式的TXT文件的数据;
   2. INSERT INTO … SELECT …的两种插入数据的方法:
      1. conventional INSERT操作:通用的插入方法,数据库会优先重新利用HWM(High Water Mark)以下的数据块,然后再使用HWM以上的数据块,使得新旧数据交叉存放,同时还要维护数据的完整性(主外键,CHECK约束等);
      2. direct-path INSERT操作:直接路径插入方法,数据库把数据直接插入到高水位之后的数据块中,数据不经过buffer cache,直接写入数据文件,忽略数据的完整性,使得性能最大的提升;
         1. 顺序插入分区/非分区表:只有一个线程把数据写到HWM之后;
         2. 并行插入分区表:每个进程可以被分配到一个或者多个分区,但是一个分区最多只有一个进程负责,推荐使用跟分区数量相同的并行度;数据同样只在每个分区的HWM之后的数据块写入;
         3. 并行插入非分区表:每个进程分配一个临时的段,然后把所有临时段合并;
      3. SQL*Loader的convention-path load方式和direct-path load方式跟这个相似;
      4. 可以通过使用/*+ APPEND */的hint,来使用直接导入数据,例子;
      5. conventional方式使用会产生redo日志,而direct-path方式始终不产生日志,与表本身是否是LOGGING模式无关,如果数据库是FORCE LOGGING模式,则无论那种方式都会记录日志,例子;
   3. SQL*Loader的介绍;
      1. Input data files:要导入的数据文件;
         1. 以文本形式存在;
         2. 可以有多个数据文件;
         3. 支持三种数据格式:
            1. Fixed-Record Format:每个列有固定的长度,效率高单灵活性差,如:数据文件中每11个字节是一个字段infile ‘example.dat’ “fix 11”;
            2. Variable-Record Format:每个列长度不固定,在最前面n位来记录它的长度,如:前三个字符表示记录的长度infile ‘example.dat’ “var 3”;
            3. Stream-Record Format:流记录,以某个符号区分列,最常用的方式;
         4. 记录的种类:
            1. Physical Record:即数据文本中的一行记录;
            2. Logical Record:在数据库中表的一行记录,可以合并几行物理记录组成一个逻辑记录;
      2. Loader control file:导入数据的控制文件;
         1. 指定导入数据的目录;
         2. 数据的格式化;
         3. 配置信息:内存,拒绝记录的规则,异常终止后的处理;
         4. 如何操作数据;
         5. 注释使用[–]符号;
         6. 不能使用CONSTANT和ZONE关键字;
      3. Parameter file:可选,可以把sqlldr命令后面的参数保存起来,只需要指定参数文件即可;
      4. Discard file:可选,被抛弃的数据,不满足过滤器条件的记录;
         1. 可以控制打开或者关闭此功能;
         2. 可以在控制文件中定义记录选择的标准;
         3. 可以规定当discard file中记录超过一定数量的话就终止;
      5. Bad file:可选,被拒绝的数据;
         1. SQL*Loader拒绝的数据,比如输入数据的格式不对;
         2. Oracle数据库拒绝的数据,比如违反约束的记录;
      6. Log file:可选,日志信息,如果没有指定就会生成以.log结尾的跟数据文件同名的文件;
         1. 头信息;
         2. 全局信息;
         3. 表信息;
         4. 数据文件信息;
         5. 表加载的信息;
         6. 统计信息;
         7. 附加信息,比如花费的时间等待;
      7. 架构图;                                                                                 
   4. Conventional和Direct-Path Load方法的对比:
      1. Conventional Load:
         1. 读取一条记录,解析,插入,提交;
         2. 总是会产生REDO日志;
         3. 强制检查所有约束;
         4. 会激活触发器;
         5. 可以插入到簇表;
         6. 其它用户可以修改表;
      2. Direct-Path Load:
         1. 把记录构造成块,直接进行块拷贝;
         2. 只有在数据库为FORCE LOGGING模式下才会产生REDO日志;
         3. 只检查主键,唯一键,NOT NULL约束;
         4. 忽略触发器;
         5. 不能对簇表加载数据;
         6. 其它用户不能修改表;
      3. 可以使用并行操作来加快速度,但是需要手动指定并行度;
   5. SQL*Loader的语法:sqlldr keyword=value,命令后面是键值对的组合;
      1. userid:username/password;
      2. control:控制文件名称;
      3. log:日志文件名称;
      4. bad:bad文件名称;
      5. data:数据文件名称;
      6. discard:discard文件名称;
      7. discardmax:允许最大的discard的记录数,默认为所有记录;
      8. skip:跳过的逻辑记录数,默认为0;
      9. load:加载的逻辑记录数,默认为所有记录;
      10. errors:允许错误的记录数,默认为50;
      11. silent:不提示header/feedback/errors/discards/partitions等信息,默认为FALSE;
      12. direct:使用直接路径,默认为FALSE;
      13. multithreading:直接数据导入时使用多线程;
      14. parfile:使用的参数文件的名称;
      15. parallel:使用并行加载,默认是FALSE;
      16. skip_unusable_indexes:跳过unusable的索引,默认为FALSE;
      17. skip_index_maintenance:不维护索引,标记索引为unusable,默认为FALSE;
      18. commit_discontinued:当加载数据终止时,提交已经加载了的数据,默认为FALSE;
      19. streamsize:使用直接路径加载时的缓存区大小,默认是256000;
      20. external_table:使用外部表导入数据;
          1. NOT_USED:不使用外部表;
          2. GENERATE_ONLY:只生成外部表的创建语法,生成在log文件中;
          3. EXECUTE:使用外部表导入数据;
      21. date_cache:导入数据的cache大小;
      22. _display_exitcode:显示sqlloader退出时的退出码;
   6. SQL*Loader的退出码,用在SHELL编程中:
      1. EX_SUCC(0):所有的记录加载成功;
      2. EX_WARN(2):一些记录被放到bad file/discard file/异常终止;
      3. EX_FAIL(1):语法错误,操作错误;
      4. 如果状态码大于0的话,就有问题;
   7. SQL*Loader的例子:
      1. 在9i的数据库的$ORACLE_HOME/rdbms/demo/目录下ul*的11个例子(.ctl是控制文件,.sql是修改表的脚本,.dat是数据,在scott用户下完成);
      2. EXAMPLE1:加载变长的数据,数据列之间以逗号分隔,可能还以引号包围,数据和控制文件在一起;
      3. EXAMPLE2:加载固定格式的数据,数据和控制文件分开;
      4. EXAMPLE3:加载有定界符的,随意格式的文件,数据与控制文件在一起;
      5. EXAMPLE4:加载合并的物理记录,合并多个物理记录到一个逻辑记录中;
      6. EXAMPLE5:加载数据到多个表中;
      7. EXAMPLE6:使用直接路径的方式加载数据;
      8. EXAMPLE7:从一个格式化的报表中抽取数据并加载;
      9. EXAMPLE8:加载分区表;
      10. EXAMPLE9:加载CLOB列;
      11. EXAMPLE10:加载REF列和ARRAYs列;
      12. EXAMPLE11:转换字符集,使用Unicode编码加载数据;
2. DIRECTORY对象:
   1. 只有SYS用户可以拥有DIRECTORY对象,但是它可以授权其它用户创建的权限;
   2. DIRECTORY对象只是一个文件系统目录的映射/别名(如果oracle用户没有这个目录的权限怎么办?);
   3. 具有DBA角色或者CREATE ANY DIRECTORY权限的用户可以创建DIRECTORY对象:CREATE DIRECTORY ext_tab_dir AS ‘/u01/datafiles’;
   4. 查看系统中存在的DIRECTORY对象:SELECT * FROM dba_directories;
   5. 创建者/DBA可以对其它用户/角色授权:GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO hr;
   6. 查看某个DIRECTORY对象的权限:SELECT * FROM dba_tab_privs WHERE table_name = ‘EXT_TAB_DIR’;
   7. 创建者和DBA自动具有RW权限;
   8. 删除DIRECTORY对象:DROP DIRECTORY ext_tab_dir;
3. 外部表(External Tables):
   1. 外部表的概念:
      1. 10g之前,外部表是只读的;10g之后,外部表可读可写;
      2. 外部表是对SQL*Loader功能的补充,可以像访问数据库中表一样访问数据库外的资源;
      3. 只能在服务器端操作,数据要存放在服务器端,因为需要使用DIRECTORY;
      4. 可以导入固定格式的TXT文件的数据;
      5. 在ETL中尽量多用外部表,而少用SQL*Loader;
   2. 创建外部表:
      1. 使用CREATE TABLE table_name(…) ORGANIZATION EXTERNAL语法,需要指定的属性:
         1. TYPE:指定外部表使用的访问引擎;
            1. ORACLE_LOADER:默认值,只能进行数据加载操作,而且数据必须是文本文件;
            2. ORACLE_DATAPUMP:可以进行数据加载/卸载的操作,数据必须是二进制的dump文件;
         2. DEFAULT DIRECTORY:指定外部表所使用的默认目录,是一个DIRECTORY对象,而不是一个路径;
         3. ACCESS PARAMETERS:指定外部表数据对应的表中的列和某一列的格式化信息,与使用的引擎有关;
         4. LOCATION:指定外部表的位置,是目录和文件名的组合(directory:file),如果没有指定目录则使用默认的目录;
      2. 创建的步骤:
         1. 创建DIRECTORY对象;
         2. 创建外部表;
         3. 其它操作:可以再创建一个表,然后使用INSERT INTO … SELECT …语法导入数据;
   3. 加载和卸载数据:
      1. 加载数据:把外部表中的数据导入到数据库的表中;
      2. 卸载数据:把数据库表中的内容导入到外部不表中,只能使用ORACLE_DATAPUMP引擎;
   4. 使用外部表时的数据类型转换,LOB字段:CREATE TABLE LONG_TAB_XT (LONG_COL CLOB) ORGANIZATION EXTERNAL…SELECT TO_LOB(LONG_COL) FROM LONG_TAB;
   5. 并行查询外部表:
      1. ORACLE_LOADER引擎:
         1. 如果加载文件时顺序指定了多个文件;
         2. 记录使用VAR格式化;
      2. ORACLE_DATAPUMP引擎:
         1. 加载数据时:
            1. 加载时使用了多个文件;
            2. 使用一个大的文件时也会并行,因为会记录文件读取的偏移量;
         2. 卸载数据时:
            1. LOCATION参数指定多个文件时可以发生并行;
            2. 如果并行度等于文件的个数的话,每个进程写一个文件;
            3. 如果并行度大于文件的个数的话,并行度降低为文件的个数;
            4. 如果并行度小于文件的个数的话,外部文件将不会被使用;
      3. 自己决定并行度;
   6. 外部表的限制:
      1. 不支持加密列;
      2. 不支持LONG类型的列;
      3. 特殊字符需要用双引号转换;
      4. 不能是临时表;
      5. 不能添加约束;
   7. SQL*Loader和外部表的不同:
      1. 在sql*loader中可以使用反斜杠来转义一个字符:FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘\”;
      2. 在外部表中使用反斜杠会报错,可以使用引号来分割字段:TERMINATED BY ‘,’ ENCLOSED BY “‘”;
   8. 外部表的例子:
      1. ORACLE_LOADER引擎:使用脚本导入的例子;
      2. ORACLE_LOADER引擎:使用sql*loader生成外部表脚本的例子;
      3. ORACLE_DATADUMP引擎:导出数据的例子;
      4. ORACLE_DATADUMP引擎:导入数据的例子;

————————- Direct-Path INSERT的例子 ————————-

— 创建一个空表;
CREATE TABLE bt AS SELECT * FROM dba_objects WHERE 1 = 0;
— 正常插入数据;

INSERT INTO bt SELECT * FROM dba_objects;

— 在提交之前就可以查询到表的数量;
SELECT COUNT(*) FROM bt;
COMMIT;

— 使用直接路径的方式;
INSERT /*+ APPEND */ INTO bt SELECT * FROM dba_objects;

— 提交之前查询表报错,从一方面说明了直接路径插入;
SELECT COUNT(*) FROM bt;
SELECT COUNT(*) FROM bt
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

————————- Direct-Path INSERT的例子 ————————-

————————- Direct-Path INSERT NOLOGGING的例子 ————————-

— 一,表处于LOGGING模式,数据库不是FORCE LOGGING模式,观察几种插入方式产生的日志;

— 1.传统插入,LOGGING模式,会产生日志;

— 2.传统插入,NOLOGGING模式,同样会产生日志;

— 3.直接插入,LOGGING模式,不会产生日志;

— 4.直接插入,NOLOGGING模式,也不会产生日志;

— 二,表处于NOLOGGING模式,数据库处于非FORCE LOGGING模式:传统方式都会产生日志,直接插入方式都不产生日志;

— 三,数据库处于FORCE LOGGING模式,无论表是什么模式,无论哪种方式都会记录日志;

————————- Direct-Path INSERT NOLOGGING的例子 ————————-

————————- SQL*Loader的例子 ————————-

— EXAMPLE1:加载变长的数据,数据列之间以逗号分隔,可能还以引号包围,数据和控制文件在一起;

— 创建测试表;

DROP TABLE EMP;

DROP TABLE DEPT;

CREATE TABLE DEPT
(
DEPTNO NUMBER(2) not null,
DNAME  VARCHAR2(14),
LOC    VARCHAR2(13)
);
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);

— 控制文件和数据;

LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,”SARATOGA”
10,”ACCOUNTING”,CLEVELAND
11,”ART”,SALEM
13,FINANCE,”BOSTON”
21,”SALES”,PHILA.

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo1.ctl;

— 验证数据;

— EXAMPLE2:加载固定格式的数据,数据和控制文件分开;

— 控制文件;

LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo2.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)

— 数据文件;

7782 CLARK      MANAGER   7839  2572.50          10
7839 KING       PRESIDENT       5500.00          10
7934 MILLER     CLERK     7782   920.00          10
7566 JONES      MANAGER   7839  3123.75          20
7499 ALLEN      SALESMAN  7698  1600.00   300.00 30
7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30
7658 CHAN       ANALYST   7566  3450.00          20

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo2.ctl;

— 验证数据;

— EXAMPLE3:加载有定界符的,随意格式的文件,数据与控制文件在一起;

— 修改表结构:alter table emp add (projno number, loadseq number);

— 控制文件;

LOAD DATA
INFILE *
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
(empno, ename, job, mgr,
hiredate DATE(20) “DD-Month-YYYY”,
sal, comm,
deptno   CHAR TERMINATED BY ‘:’,
projno,
loadseq  SEQUENCE(MAX,1))
BEGINDATA
7782, “Clark”, “Manager”, 7839, 09-June-1981, 2572.50,, 10:101
7839, “King”, “President”, , 17-November-1981, 5500.00,, 10:102
7934, “Miller”, “Clerk”, 7782, 23-January-1982, 920.00,, 10:102
7566, “Jones”, “Manager”, 7839, 02-April-1981, 3123.75,, 20:101
7499, “Allen”, “Salesman”, 7698, 20-February-1981, 1600.00, 300.00, 30:103
7654, “Martin”, “Salesman”, 7698, 28-September-1981, 1312.50, 1400.00, 30:103
7658, “Chan”, “Analyst”, 7566, 03-May-1982, 3450,, 20:101

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo3.ctl;

— 验证数据;

— EXAMPLE4:加载合并的物理记录,合并多个物理记录到一个逻辑记录中;

— 修改表结构;

DROP TABLE EMP;

CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);

CREATE UNIQUE INDEX UNQ_EMPNO ON EMP(EMPNO);

— 控制文件;

LOAD DATA
INFILE “/u01/sqlldrdemo/demo4.dat”
DISCARDFILE “/u01/sqlldrdemo/demo4.dis”
DISCARDMAX 999
REPLACE
CONTINUEIF (1) = ‘*’
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,
HIREDATE POSITION(52:60) INTEGER EXTERNAL)

— 数据文件;

*7782 CLARK      MA
NAGER   7839  2572.50   -10    2512-NOV-85
*7839 KING       PR
ESIDENT       5500.00          2505-APR-83
*7934 MILLER     CL
ERK     7782   920.00          2508-MAY-80
*7566 JONES      MA
NAGER   7839  3123.75          2517-JUL-85
*7499 ALLEN      SA
LESMAN  7698  1600.00   300.00 25 3-JUN-84
*7654 MARTIN     SA
LESMAN  7698  1312.50  1400.00 2521-DEC-85
*7658 CHAN       AN
ALYST   7566  3450.00          2516-FEB-84
*     CHEN       AN
ALYST   7566  3450.00          2516-FEB-84
*7658 CHIN       AN
ALYST   7566  3450.00          2516-FEB-84

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo4.ctl;

— 验证数据;

— 检查不满足条件的记录;

— EXAMPLE5:加载数据到多个表中;

— 创建测试表;

DROP TABLE emp;
DROP TABLE proj;
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    CHAR(10),
JOB      CHAR(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);
CREATE UNIQUE INDEX unq_empno ON EMP (EMPNO);
CREATE TABLE PROJ
(
EMPNO  NUMBER,
PROJNO NUMBER
);

— 控制文件;

LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo5.dat’
BADFILE ‘/u01/sqlldrdemo/demo5.bad’
DISCARDFILE ‘/u01/sqlldrdemo/demo5.dis’
REPLACE
INTO TABLE EMP
(EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
ENAME    POSITION(6:15)  CHAR,
DEPTNO   POSITION(17:18) CHAR,
MGR      POSITION(20:23) INTEGER EXTERNAL)
INTO TABLE PROJ
— PROJ has two columns, both not null: EMPNO and PROJNO
WHEN PROJNO != ‘   ‘
(EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
PROJNO   POSITION(25:27) INTEGER EXTERNAL)   — 1st proj
INTO TABLE PROJ
WHEN PROJNO != ‘   ‘
(EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
PROJNO   POSITION(29:31) INTEGER EXTERNAL)   — 2nd proj
INTO TABLE PROJ
WHEN PROJNO != ‘   ‘
(EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
PROJNO   POSITION(33:35) INTEGER EXTERNAL)   — 3rd proj

— 数据文件;

1234 BAKER      10 9999 101 102 103
1234 JOKER      10 9999 777 888 999
2664 YOUNG      20 2893 425 abc 102
5321 OTOOLE     10 9999 321  55  40
2134 FARMER     20 4555 236 456
2414 LITTLE     20 5634 236 456  40
6542 LEE        10 4532 102 321  14
2849 EDDS       xx 4555     294  40
4532 PERKINS    10 9999  40
1244 HUNT       11 3452 665 133 456
123 DOOLITTLE  12 9940         132
1453 MACDONALD  25 5532     200

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo5.ctl;

— 验证数据;

— 检查不满足条件的记录;

— EXAMPLE6:使用直接路径的方式加载数据;

— 创建测试表;

DROP TABLE emp;
CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    CHAR(10),
JOB      CHAR(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
)
CREATE UNIQUE INDEX unq_empno ON emp(empno);

— 控制文件;

LOAD DATA

INFILE ‘/u01/sqlldrdemo/demo6.dat’
REPLACE

INTO TABLE emp

SORTED INDEXES (unq_empno)
(empno position(1:4),
ename position(6:15),
job position(17:25),
mgr position(27:30) nullif mgr=blanks,
sal position(32:39) nullif sal=blanks,
comm position(41:48) nullif comm=blanks,
deptno position(50:51) nullif empno=blanks)

— 数据文件;

7566 JONES      MANAGER   7839  3123.75          20
7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30
7658 CHAN       ANALYST   7566  3450.00          20
7782 CLARK      MANAGER   7839  2572.50          10
7839 KING       PRESIDENT       5500.00          10
7934 MILLER     CLERK     7782   920.00          10

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo6.ctl direct=y;

— 验证数据;

— EXAMPLE7:从一个格式化的报表中抽取数据并加载;

— 创建测试表;

CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    CHAR(10),
JOB      CHAR(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
);
CREATE UNIQUE INDEX UNQ_EMPNO on EMP (EMPNO);

— 添加触发器,这一步是关键;

CREATE OR REPLACE PACKAGE sqlloader AS
last_deptno  NUMBER;
last_job     CHAR(9);
last_mgr     NUMBER;
END sqlloader;CREATE OR REPLACE TRIGGER sqlloader_emp_insert
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF :new.deptno IS NOT NULL THEN
sqlloader.last_deptno := :new.deptno;   — save value for later use
ELSE
:new.deptno := sqlloader.last_deptno;   — use last valid value
END IF;
IF :new.job IS NOT NULL THEN
sqlloader.last_job := :new.job;   — save value for later use
ELSE
:new.job := sqlloader.last_job;   — use last valid value
END IF;
IF :new.mgr IS NOT NULL THEN
sqlloader.last_mgr := :new.mgr;   — save value for later use
ELSE
:new.mgr := sqlloader.last_mgr;   — use last valid value
END IF;
END sqlloader_emp_insert;

— 控制文件;

LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo7.dat’

DISCARDFILE ‘/u01/sqlldrdemo/demo7.dis’

APPEND
INTO TABLE emp
WHEN (57)=’.’
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno   POSITION(1:2)  INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job      POSITION(7:14)   CHAR  TERMINATED BY WHITESPACE
NULLIF job=BLANKS  “UPPER(:job)”,
mgr      POSITION(28:31)  INTEGER EXTERNAL TERMINATED BY WHITESPACE
NULLIF mgr=BLANKS,
ename    POSITION (34:41) CHAR  TERMINATED BY WHITESPACE
“UPPER(:ename)”,
empno    INTEGER EXTERNAL  TERMINATED BY WHITESPACE,
sal      POSITION(51)  CHAR  TERMINATED BY WHITESPACE
“TO_NUMBER(:sal,’$99,999.99′)”,
comm     INTEGER EXTERNAL  ENCLOSED BY ‘(‘ AND ‘%’
“:comm * 100”
)

— 数据文件;

               Today’s Newly Hired EmployeesDept  Job       Manager   MgrNo  Emp Name  EmpNo  Salary/Commission
—-  ——–  ——–  —–  ——–  —–  —————–
20    Salesman  Blake      7698  Shepard    8061  $1,600.00 (3%)
Falstaff   8066  $1,250.00 (5%)
Major      8064  $1,250.00 (14%)

30    Clerk     Scott      7788  Conrad     8062  $1,100.00
Ford       7369  DeSilva    8063    $800.00
Manager   King       7839  Provo      8065  $2,975.00

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo7.ctl;

— 验证数据;

— 查看不满足的记录;

— EXAMPLE8:加载分区表;

— 创建测试表;

CREATE TABLE LINEITEM
(
L_ORDERKEY      NUMBER,
L_PARTKEY       NUMBER,
L_SUPPKEY       NUMBER,
L_LINENUMBER    NUMBER,
L_QUANTITY      NUMBER,
L_EXTENDEDPRICE NUMBER,
L_DISCOUNT      NUMBER,
L_TAX           NUMBER,
L_RETURNFLAG    CHAR(1),
L_LINESTATUS    CHAR(1),
L_SHIPDATE      DATE,
L_COMMITDATE    DATE,
L_RECEIPTDATE   DATE,
L_SHIPINSTRUCT  CHAR(17),
L_SHIPMODE      CHAR(7),
L_COMMENT       CHAR(43)
)
PARTITION by RANGE (L_SHIPDATE)
(
PARTITION SHIP_Q1 VALUES LESS THAN (TO_DATE(‘1996-04-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
PARTITION SHIP_Q2 VALUES LESS THAN (TO_DATE(‘1996-07-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
PARTITION SHIP_Q3 VALUES LESS THAN (TO_DATE(‘1996-10-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)),
PARTITION SHIP_Q4 VALUES LESS THAN (TO_DATE(‘1997-01-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’))
);

— 控制文件;

LOAD DATA
INFILE ‘/u01/sqlldrdemo/demo8.dat’ “fix 129”

BADFILE ‘/u01/sqlldrdemo/demo8.bad’

TRUNCATE
INTO TABLE lineitem
PARTITION (ship_q1)
(l_orderkey      position    (1:6) char,
l_partkey       position   (7:11) char,
l_suppkey       position  (12:15) char,
l_linenumber    position  (16:16) char,
l_quantity      position  (17:18) char,
l_extendedprice position  (19:26) char,
l_discount      position  (27:29) char,
l_tax           position  (30:32) char,
l_returnflag    position  (33:33) char,
l_linestatus    position  (34:34) char,
l_shipdate      position  (35:43) char,
l_commitdate    position  (44:52) char,
l_receiptdate   position  (53:61) char,
l_shipinstruct  position  (62:78) char,
l_shipmode      position  (79:85) char,
l_comment       position (86:128) char)

— 数据文件;

     1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN PERSON
TRUCK  iPBw4mMm7w7kQ zNPL i261OPP
1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN
MAIL   5wM04SNyl0AnghCP2nx lAi
1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN
REG AIRSQC2C 5PNCy4mM
1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE
AIR    Om0L65CSAwSj5k6k
1 6564  6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN PERSON
MAIL   CB0SnyOL PQ32B70wB75k 6Aw10m0wh
1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE
FOB    C2gOQj OB6RLk1BS15 igN
2 8819 82012441659.44  0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD
AIR    O52M70MRgRNnmm476mNm
3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN
FOB    6wQnO0Llg6y
3 9717  1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN
SHIP   LhiA7wygz0k4g4zRhMLBAM
3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN
REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297

— 导入数据:sqlldr scott/tiger control=/u01/sqlldrdemo/demo8.ctl;

— 验证数据;

— EXAMPLE9:加载CLOB列,为每个CLOB列准备一个文本文件;

— 创建测试表;

CREATE TABLE EMP
(
EMPNO    NUMBER(4) not null,
ENAME    CHAR(10),
JOB      CHAR(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2),
  RESUME   CLOB
)

— EXAMPLE10:加载REF列和ARRAYs列;

— EXAMPLE11:转换字符集,使用Unicode编码加载数据;

————————- SQL*Loader的例子 ————————-

————————- 外部表 ORACLE_LOADER引擎的例子 ————————-
— 手动编写外部表语法;

1.创建DIRECTORY对象;

CREATE DIRECTORY ext_tab_dir AS ‘/u01/datafiles’;
SELECT * FROM dba_directories WHERE directory_name = ‘EXT_TAB_DIR’;

2.对用户授权;

GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO hr;
SELECT * FROM dba_tab_privs WHERE table_name = ‘EXT_TAB_DIR’;

3.测试数据/u01/datafiles/emp.dat;

“56”,   “baker”,        “mary”, “f”,    “01-09-2004”, “15-11-1980”
“87”,   “roper”,        “lisa”, “m”,    “01-06-1999”, “20-12-1970”

4.创建外部表;

CREATE TABLE ext_tab_emp
(
ID            INTEGER,
first_name    VARCHAR2(20),
last_name     VARCHAR2(20),
male          CHAR(1),
birthday      DATE,
hire_date     DATE
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE ‘EXT_TAB_DIR’:’emp.bad’
DISCARDFILE ‘EXT_TAB_DIR’:’emp.dis’
LOGFILE ‘EXT_TAB_DIR’:’emp.log’
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
ID            CHAR(20) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘,
first_name    CHAR(20),
last_name     CHAR(20),
male          CHAR(1),
birthday      CHAR(20) date_format DATE mask “DD-MM-YYYY”,
hire_date     CHAR(20) date_format DATE mask “DD-MM-YYYY”
)
)
LOCATION (’emp.dat’, ’emp1.dat’)
) PARALLEL 2;
5.查看数据;

SELECT * FROM ext_tab_emp;

6.查看日志发现,如果使用并行的话会有多个线程同时工作,提高效率;

— 使用SQL*LOADER工具生成创建外部表的语法;

1.测试数据/u01/datafiles/car.dat;

Talbot,8/18,4,01-MAR-1923,ohv,8,295.00
Talbot,10/23,4,12-MAR-1923,ohv,8.9,375.00
Talbot,12/30,6,23-JAN-1924,ohv,13.4,550.00
Sunbeam,14/40,4,06-MAR-1924,ohv,13.9,895.00
Sunbeam,12/30,4,08-FEB-1924,ohv,11.5,570.00
Sunbeam,20/60,6,24-FEB-1924,ohv,20.9,950.00
Sunbeam,Twin Cam,6,11-MAR-1926,ohv,20.9,1125.00
Sunbeam,20,6,15-JUN-1927,ohv,20.9,750.00
Sunbeam,16,6,10-SEP-1927,ohv,16.9,550.00
Peugeot,172,4,28-SEP-1928,sv,6.4,165.00
Austin,7,4,29-JAN-1922,sv,7.2,225.00
Austin,12,4,01-JAN-1922,sv,12.8,550.00
Austin,20,4,04-JAN-1916,sv,22.4,616.00
Lanchester,40,6,08-JAN-1919,ohv,38.4,1875.00
Lanchester,21,6,16-JAN-1924,ohv,20.6,950.00
Vauxhall,30/98,4,18-JAN-1919,sv,23.8,1475.00
Vauxhall,23/60,4,27-JAN-1919,sv,22.4,1300.00

2.hr用户下的表结构;

3.控制文件/u01/datafiles/car.ctl;

LOAD DATA
INFILE ‘/u01/datafiles/car.dat’
BADFILE ‘/u01/datafiles/car.bad’
DISCARDFILE ‘/u01/datafiles/car.dsc’
APPEND
INTO TABLE car
FIELDS TERMINATED BY “,”
TRAILING NULLCOLS
(maker,
model,
no_cyl,
first_built_date,
engine,
hp,
price)

4.生成创建外部表的脚本;

sqlldr hr/hr control=/u01/datafiles/car.ctl external_table=GENERATE_ONLY;

5.适当修改即可;

conn hr/hr
CREATE TABLE car_ext
(
“MAKER” VARCHAR2(20),
“MODEL” VARCHAR2(20),
“NO_CYL” NUMBER,
“FIRST_BUILT_DATE” DATE,
“ENGINE” VARCHAR2(20),
“HP” NUMBER(10,1),
“PRICE” NUMBER(10,2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY EXT_TAB_DIR — 必须大写

  ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE ‘EXT_TAB_DIR’:’car.bad’

    DISCARDFILE ‘EXT_TAB_DIR’:’car.dsc’

    LOGFILE ‘EXT_TAB_DIR’:’car.log’

    READSIZE 1048576
FIELDS TERMINATED BY “,” LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
“MAKER” CHAR(255) TERMINATED BY “,”,
“MODEL” CHAR(255) TERMINATED BY “,”,
“NO_CYL” CHAR(255) TERMINATED BY “,”,
“FIRST_BUILT_DATE” CHAR(255) TERMINATED BY “,”,
“ENGINE” CHAR(255) TERMINATED BY “,”,
“HP” CHAR(255) TERMINATED BY “,”,
“PRICE” CHAR(255) TERMINATED BY “,”
)
)
location
(
‘car.dat’
)
)REJECT LIMIT UNLIMITED;

————————- 外部表 ORACLE_LOADER引擎的例子 ————————-

————————- 外部表 ORACLE_DATAPUMP引擎的例子 ————————-

— 卸载数据;

1.目标:卸载执行查询结果集的数据,并创建外部表;

conn hr/hr;

SELECT e.first_name, e.last_name, e.email, e.phone_number, d.department_name FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.department_id = d.department_id;

2.创建目录并授权;

CREATE DIRECTORY EXT_TAB_DIR AS ‘/u01/datafiles’;

GRANT READ, WRITE ON DIRECTORY EXT_TAB_DIR TO hr;

3.把数据导出为EMP_DEPT.dmp文件,创建外部表;

conn hr/hr;

CREATE TABLE emp_dept_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_TAB_DIR LOCATION (‘EMP_DEPT.dmp’)

)
AS

SELECT e.first_name, e.last_name, e.email, e.phone_number, d.department_name FROM EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.department_id = d.department_id;

— 导入数据;

目标:把导出的数据导入到hr用户下t1表中;

conn hr/hr;

CREATE TABLE t1
(
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
DEPARTMENT_NAME VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_TAB_DIR
LOCATION (‘EMP_DEPT.dmp’)

————————- 外部表 ORACLE_DATAPUMP引擎的例子 ————————-

EXP/IMP和EXPDP/IMPDP工具的使用

1. exp/imp:
   1. 概念:
      1. 早期的oracle数据导入导出工具,效率比较低,可以用在小数据量的情况下;
      2. 可以在服务器端或者客户端操作,数据可以存放在服务器端或者客户端;
      3. 只能导出导出二进制的dump文件;
   2. 使用exp/imp工具的前提:
      1. 运行catexp.sql或者catalog.sql脚本;
      2. 保证有足够的空间;
      3. 保证有足够的权限;
         1. 对于exp:必须有CREATE SESSION权限,如果要导出其它用户下的表,还需要有EXP_FULL_DATABASE角色;
         2. 对于imp:必须有CREATE SESSION权限,如果要导入其它用户创建的dump文件,还需要有IMP_FULL_DATABASE角色;
   3. 调用exp/imp的方法:
      1. 命令行接口,常用;
      2. 交互模式;
      3. 使用参数文件方式,常用;
      4. 使用OEM调用;
   4. EXP工具:
      1. 用途:
         1. 归档历史数据;
         2. 保存表的结构信息;
         3. 可以在不同的服务器,不同的平台上传输数据;
         4. 传输表空间时使用;
      2. 导出的四种模式:
         1. Table Mode:包括表的定义,数据,用户拥有的表和索引,表上的约束;
         2. User Mode:用户下所有的对象;
         3. Tablespace Mode:某一个表空间中所有的对象,主要用于传输表空间;
         4. Full Database Mode:不推荐的方式,可以使用rman替代;
      3. 语法:exp keyword=value1,value2,…,valuen;
         1. USERID=user/pwd:用户名和密码;
         2. BUFFER:指定数据缓冲区的大小;
         3. FILE:导出文件的名称;
         4. FILESIZE:每个导出文件的最大大小,如果导出文件很大,可以限制每个文件的大小,使用多个文件来保存数据;
         5. COMPRESS:是否使用压缩,默认为Y;
         6. GRANTS:是否导出用户的权限,默认为Y;
         7. ROWS:是否导出表的记录,默认为Y;
         8. INDEXES:是否导出索引,默认为Y;
         9. TRIGGERS:是否导出触发器,默认为Y;
         10. CONSTRAINTS:是否导出表上的约束,默认为Y;
         11. DIRECT:是否使用直接路径导出,默认为N,只能使用命令行方式,BUFFER参数无效;
         12. LOG:指定日志文件;
         13. PARFILE:指定参数文件;
         14. FULL:导出整个数据库,全库导出时使用,不常用;
         15. OWNER:列出要导出的用户,导出用户模式;
         16. TABLES:列出要导出的表的名称(schema.table:partition),支持通配符,导出表模式;
         17. TABLESPACES:列出要导出的表空间,导出表空间模式;
         18. TRANSPORT_TABLESPACE:是否导出表空间的元信息,默认为N,传输表空间时使用;
         19. FEEDBACK=n:显示导出的进度,n条记录打一个点;
         20. QUERY:使用查询语句来过滤要导出的记录,eg:exp scott/tiger TABLES=emp QUERY=\”WHERE job=\’SALESMAN\’ and sal \<1600\”;
         21. CONSISTENT:是否在导出开始时设置SET TRANSACTION READ ONLY语句来保证整个导出期间的数据一致性,默认为N,推荐为Y;
         22. OBJECT_CONSISTENT:保证导出某个对象期间,这个对象是只读的,默认为N,推荐为Y;
         23. FLASHBACK_SCN:从指定的SCN开始导出数据;
         24. FLASHBACK_TIME:从指定的时间开始导出数据,内部会转化为SCN号码,可以使用TO_TIMESTAMP函数来转化字符串格式的时间;
         25. STATISTICS:当导出的对象被导入时,是否分析对象,默认为ESTIMATE;
         26. RESUMABLE:当空间不足时是否会挂起,等待分配空间后继续导出,默认是N;
         27. RESUMABLE_NAME:指定挂起语句的标识符;
         28. RESUMABLE_TIMEOUT:等待的时间;
         29. RECORDLENGTH:记录的长度;
         30. INCTYPE:增量导出类型;
         31. RECORD:跟踪增量导出,默认为Y;
   5. IMP工具:
      1. 用途:
         1. 可以执行完全导入或者累计导入;
         2. 主要用于恢复用户错误;
      2. 导入介绍:
         1. 先创建表的定义;
         2. 然后从exp的dmp文件中抽取数据;
         3. 创建索引;
         4. 导入触发器;
         5. 开启数据完整性约束检查;
         6. 创建位图索引,序列等对象;
      3. 导入的模式:
         1. 表模式;
         2. 用户模式;
         3. 表空间模式,用户可传输表空间;
         4. 全库模式,不常用;
      4. 语法:imp keyword=value1,value2,…,valuen;
         1. USERID=user/pwd:用户名和密码;
         2. BUFFER:指定数据缓冲区的大小;
         3. FILE:要导入的文件的名称;
         4. FILESIZE:每个导入文件的最大大小;
         5. SHOW:查看要导入的dmp文件中的内容,并不进行导入操作;
         6. IGNORE:是否忽略创建对象时的错误,默认为N,表示出错时输出错误日志;
         7. GRANTS:是否导入用户的权限,默认为Y;
         8. ROWS:是否导入表的记录,默认为Y;
         9. INDEXES:是否导入索引,默认为Y;
         10. TRIGGERS:是否导入触发器,默认为Y;
         11. CONSTRAINTS:是否导入表上的约束,默认为Y;
         12. LOG:指定日志文件;
         13. PARFILE:指定参数文件;
         14. FULL:导出整个数据库,全库导出时使用,不常用;
         15. FROMUSER:在用户模式下使用,指定要导入的用户;
         16. TOUSER:与FROMUSER连用,指定要转变的用户列表,如fromuser=u1 touser=u2,即把u1下的对象创建在u2下;这个用户必须预先存在,否则会报错;
         17. TABLES:列出要导入的表的名称(schema.table:partition);
         18. TABLESPACES:列出要导入的表空间,导出表空间模式;
         19. TRANSPORT_TABLESPACE:是否导入表空间的元信息,默认为N,传输表空间时使用;
         20. FEEDBACK=n:显示导入的进度,n条记录打一个点;
         21. STATISTICS:导入对象时,是否分析对象,默认为AWAYS;
         22. RESUMABLE:当空间不足时是否会挂起,等待分配空间后继续导出,默认是N;
         23. RESUMABLE_NAME:指定挂起语句的标识符;
         24. RESUMABLE_TIMEOUT:等待的时间;
         25. RECORDLENGTH:记录的长度;
         26. INCTYPE:增量导入类型;
         27. COMMIT:指定是否导入一组数据后就提交,默认为N,表示每导入一个表提交一次;
         28. DESTROY:是否覆盖表空间的数据文件,默认为N;
         29. INDEXFILE:把表和索引写入指定的文件中;
         30. SKIP_UNUSABLE_INDEXES:跳过不可用索引维护;
         31. TOID_NOVALIDATE:跳过指定类型ID的验证;
         32. COMPILE:是否马上编译导入的包,存储过程和函数,默认为Y;如果为N,表示第一次使用时编译;
         33. STREAMS_CONFIGURATION:导入stream的一般元数据,默认为Y;
         34. STREAMS_INSTANTIATION:导入stream的实例元数据,默认为N;
         35. DATAFILES:当TRANSPORT_TABLESPACE=Y时,使用此参数传输导入与数据库的数据文件;
   6. 字符集转换:
      1. 数据从源数据库导出,传输到目标服务器(ftp/scp/tape),然后导入到目标数据库一共有四个环境的字符集:
         1. 源数据库的字符集和NCHAR字符集;
         2. 源数据库服务器导出时的字符集(通过NLS_LANG环境变量设置);
         3. 目标数据库服务器导入时的字符集(通过NLS_LANG环境变量设置);
         4. 目标数据库的字符集和NCHAR字符集;
         5. 源数据库和目标数据库的字符集相同,可能会发生3次字符集转换(导出,传输,导入),如果不相同,一定会发生转换或者导入错误;
      2. 查看数据库的字符集:select * from nls_database_parameters,可以看得出语言是AMERICAN,区域是AMERICA,字符集是WE8ISO8859P1,NCHAR是AL16UTF16;            
      3. 设置服务器的环境变量:export NLS_LANG=NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET;                    
      4. 查看现有的dmp文件的字符集:首先设置当前服务器的字符集与数据库字符集一致,然后使用imp show=y命令,可以查看到导出时的字符集;                      
      5. 字符集换换的拓扑图;                                                      
   7. 传输表空间的原理:
      1. 如果把数据导出再导出效率太低;
      2. 传输表空间的做法是:只导出表空间的元信息,应用到目标数据库,表空间的数据使用数据块拷贝的方式;
2. expdp/impdp:
   1. 概念:
      1. 带有数据泵的oracle数据导入导出工具,比较高效;
      2. 只能在服务器端操作,因为需要使用DIRECTORY,数据只能存放在服务器;
      3. 只能导出导出二进制的dump文件;
      4. 主要是调用DBMS_DATAPUMP的包;
   2. expdp:
      1. 语法:expdp user/pwd key=value;
         1. COMPRESSION:压缩数据;
            1. METADATA_ONLY:默认值,只压缩元数据;
            2. NONE:不压缩;
         2. CONTENT:指定要导出的内容;
            1. ALL:导出对象的定义和数据;
            2. DATA_ONLY:导出对象的数据;
            3. METADATA_ONLY:导出对象的定义;
         3. DIRECTORY:用于存放dump文件和日志文件的目录对象;
         4. DUMPFILE:导出到dump文件的列表,格式为:[DIRECTORY:]file_name,如果不指定目录,则使用DIRECTORY参数指定的目录对象;
         5. FILESIZE:导出文件的最大大小,如果导出对象比较大,可以分成多个文件保存;
         6. LOGFILE:指定日志文件的名称;
         7. NOLOGFILE:默认为N,禁止生成日志文件;
         8. PARFILE:指定参数文件;
         9. ENCRYPTION_PASSWORD:指定加密列的密码;
         10. ESTIMATE:指定估算被导出表所占磁盘空间的方法;
             1. BLOCKS:默认值,按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间;
             2. STATISTICS:据最近统计值估算对象占用空间;
         11. ESTIMATE_ONLY:默认值为N,如果为Y,则只估算导出作业占用的磁盘空间而不会执行;
         12. INCLUDE:指定要包含的对象类型和对象,格式:object_type[:name_clause];
             1. name_clause:必须是用双引号引起来的,因为里面的每个对象需要使用单引号;
             2. 使用LIKE子句:include=TABLE:”LIKE ‘B%'”,但是不能使用NOT LIKE;
             3. 使用IN子句:include=TABLE:”IN (‘T1’, ‘T2’)”;
             4. 使用<>子句:include=TABLE:”<> (‘T1’)”,但是不能使用[=];
             5. 只导出存储过程:indlude=PROCEDURE;
         13. EXCLUDE:排除指定的对象类型或者对象,格式与INCLUDE相似;
         14. FLASHBACK_SCN:指定SCN以后的数据;
         15. FLASHBACK_TIME:指定某个时间以后的数据,内部转化为最近的SCN号;
         16. JOB_NAME:把导出的任务创建一个JOB,指定作业的名称;
         17. ATTACH:使当前会话与已存在的导出作业建立联系,然后执行交互命令,eg:expdp user/pwd ATTACH=[schema_name.]job_name;
         18. NETWORK_LINK:指定目标数据库到源数据库的Database Link,用于将远程数据对象导出到本地的情况;
             1. 因为expdp是服务器端工具,如果expdp user/pwd@net_name的话导出的文件存放在服务器端;
             2. 如果expdp user/pwd NETWORK_LINK=dblink_name的话,则数据导出到本地;
             3. 导出的日志文件不能放到ASM上,只能存放数据文件;
         19. PARALLEL:指定导出的并行度,默认为1,推荐并行度等于给定的文件个数的整数倍,而且要小于CPU个数*2;
         20. QUERY:指定过滤导出数据的条件,格式:[schmea].[table_name:]query_clause;
         21. SAMPLE:导出数据的百分比;
         22. FULL:默认为N,数据库全库导出;
         23. SCHEMAS:导出数据的schema列表,默认为当前schema;
         24. TABLES:指定要导出的表的列表,格式为:[schema_name.]table_name[:partition_name];
         25. TABLESPACES:指定要导出的表空间;
         26. TRANSPORT_FULL_CHECK:默认为Y,检查要传输的表空间是否是自包含的;
         27. TRANSPORT_TABLESPACES:指定要要导出元数据的表空间的列表,主要用于传输表空间的情况;
         28. VERSION:指定导出对象的数据库的版本;
             1. COMPATIBLE:默认值;
             2. LATEST:会根据数据库的实际版本生成对象元数据;
             3. version_string用于指定数据库版本字符串;
         29. STATUS:用于指定显示当前导出作业执行状态的间隔,eg:STATUS[=interval],默认为0;
      2. 交互式命令:
         1. ADD_FILE:添加一个导出文件;
         2. FILESIZE:指定添加文件的文件大小;
         3. HELP:帮助;
         4. PARALLEL:改变导出作业的并行度,PARALLEL=<number of workers>;
         5. 进入expdp的作业会话:expdp user/pwd attch=schema.job_name;
         6. START_JOB:开始/恢复作业的执行;
         7. STOP_JOB:暂停作业,使用STOP_JOB=IMMEDIATE表示立即停止;
         8. EXIT_CLIENT(CTRL+C):退出当前session,让作业继续运行;
         9. CONTINUE_CLIENT:切换到日志模式,作业在空闲时会重新运行;
         10. KILL_JOB:断开与作业的连接,并停止作业,同时删除dmp文件;
         11. STATUS:用于指定显示当前导出作业执行状态的间隔,eg:STATUS[=interval],默认为0;
      3. 导出的步骤:
         1. 创建目录对象:CREATE DIRECTORY ext_tab_dir as ‘/u01/datafiles/’;                          
         2. 授权:GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO hr;                                   
         3. 执行导出:expdp hr/hr directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;(如果不指定JOB_NAME的话,会生成一个系统的名称,推荐指定;)                 
      4. 导出的各种场景:
         1. 按表模式导出:expdp hr/hr directory=ext_tab_dir tables=jobs,countries dumpfile=expdp_table1.dmp,expdp_table2.dmp logfile=expdp_table.log parallel=2 job_name=’job_expdp_table’;
         2. 按查询条件导出:expdp hr/hr directory=ext_tab_dir tables=jobs query='”where rownum<11″‘ dumpfile=expdp_hr_jobs.dmp logfile=expdp_hr_jobs.log job_name=’job_expdp_hr_jobs’;
         3. 按表空间导出:expdp system/oracle directory=ext_tab_dir tablespaces=USERS dumpfile=expdp_ts_users1.dmp,expdp_ts_users2.dmp logfile=expdp_ts_users.log parallel=2 job_name=’job_expdp_ts_users’;
         4. 导出方案:expdp hr/hr directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;
         5. 导出整个数据库:expdp hr/hr directory=ext_tab_dir dumpfile=full.dmp full=y logfile=full.log job_name=’job_expdp_full’;
   3. impdp:
      1. 语法:impdp user/pwd key=value;
         1. REMAP_DATAFILE:重新定义DDL语句中引用的数据文件,用于将源数据文件转变为目标数据文件名,在不同平台之间搬移表空间时使用,eg:REMAP_DATAFIEL=source_datafie:target_datafile;
         2. REUSE_DATAFILES:默认是N,指定是否覆盖已存在的数据文件;
         3. REMAP_SCHEMA:将源schema的对象转换到目标schema中,eg:REMAP_SCHEMA=source_schema:target_schema;
         4. REMAP_TABLESPACE:将源表空间的对象导入到目标表空间中,eg:REMAP_TABLESPACE=source_tablespace:target_tablespace;
         5. SKIP_UNUSABLE_INDEXES:跳过不可使用的索引,默认为N;
         6. SQLFILE:把导出的dump文件中所有对象的DDL语句写到sql脚本中,eg:SQLFILE=[directory_object:]file_name;
         7. STREAMS_CONFIGURATION:是否导入流元数据库,默认是Y;
         8. TABLE_EXISTS_ACTION:当表已经存在时,导入作业要执行的操作;
            1. SKIP:默认跳过;
            2. APPEND:追加数据;
            3. REPLACE:删除表重建,然后再插入;
            4. TRUNCATE:截断;
         9. TRANSFORM:指定是否修改建立对象的DDL语句,eg:TRANSFORM=transform_name:value[:object_type];
            1. Transform_name:用于指定转换名:
               1. SEGMENT_ATTRIBUTES用于标识段属性(物理属性,存储属性,表空间,日志等信息);
               2. STORAGE用于标识段存储属性;
               3. OID;
               4. PCTSPACE;
            2. value:用于指定是否包含段属性或段存储属性;
            3. object_type用于指定对象类型;
         10. TRANSPORT_DATAFILES:用户指定传输表空间时被导入表空间对应的数据文件;
         11. TRANSPORT_TABLESPACES:列出传输表空间的元数据的表空间的列表,只是在NETWORK_LINK模式下操作;
      2. 交互式命令:与expdp一致;
   4. 导入的各种场景:
      1. 按表导入,将hr用户下俩个表导入到scott用户下:impdp hr/hr directory=ext_tab_dir tables=jobs,countries dumpfile=expdp_table1.dmp,expdp_table2.dmp remap_schema=hr:scott logfile=impdp_table.log parallel=2 job_name=’job_impdp_table’;
      2. 按用户导入(如果用户存在则直接导入,要有目录对象的权限,如果不存在使用dba导入):impdp system/oracle directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=impdp_hr.log parallel=2 job_name=’job_impdp_hr’;
      3. 在客户端执行expdp命令把导出文件存放在服务器端;
      4. 在客户端执行expdp命令把导入文件存放在客户端;
      5. 源数据库的数据直接导入到目标数据库,中间不产生任何的dump文件;

——————————— 查看DIRECTORY对象的权限 ———————————

SELECT d.directory_name, d.directory_path, p.grantee, p.privilege
FROM user_tab_privs p
INNER JOIN dba_directories d ON p.table_name = d.directory_name AND
d.directory_name = ‘EXT_TAB_DIR’;

——————————— 查看DIRECTORY对象的权限 ———————————

——————————— exp的例子 ———————————

— 导出scott用户下的所有对象:exp scott/tiger owner=scott file=/u01/exp/exp_scott.dmp log=exp_scott.log feedback=1000;(导出的字符集和当前环境的字符集不同,可能发生字符集转换;)

— 导出hr用户下的jobs,countries表:exp hr/hr tables=jobs,countries file=/u01/exp/exp_hr_tables.dmp log=/u01/exp/exp_hr_tables.log feedback=100;(导出的过程中出现警告,其实是因为字符集的问题;)

— 设置字符集导出,没有提示字符集可能发送转换,也没有警告;

——————————— exp的例子 ———————————

——————————— imp的例子 ———————————

— 把某个用户下的数据导入;

— 首先删除用户下所有的对象,然后创建用户(导入时此用户必须存在):drop user scott cascade;;

— 导入数据:imp scott/tiger file=/u01/exp/exp_scott.dmp fromuser=scott touser=scott;

— 把hr用户下的exp_jobs表导入到scott用户下(必须使用dba用户才可以完成):imp system/oracle file=/u01/exp/exp_jobs.dmp fromuser=hr touser=scott tables=exp_jobs feedback=100;

——————————— imp的例子 ———————————

——————————— NETWORK_LINK参数的测试 ———————————

— 1.配置两台数据库的tnsnames.ora,其中orcl_source指向源数据库,orcl_target指向目标数据库;

— 2.在源数据库添加一个目录对象dir_source,并授权给hr用户;

CREATE DIRECTORY dir_source AS ‘/u01/dump_source’;

GRANT READ, WRITE ON DIRECTORY dir_source TO hr;

— 3.使用网络名在客户端调用expdp命令,导出的文件存放在服务器端(用户必须要有CONNECT, RESOURCE, EXP_FULL_DATABASE角色和目录对象的读写权限即可);

expdp hr/hr@orcl_source directory=dir_source schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;

— 4.在源数据库添加一个目录对象dir_target,并授权给hr用户;

CREATE DIRECTORY dir_target AS ‘/u01/dump_target’;
GRANT READ, WRITE ON DIRECTORY dir_target TO hr;

— 6.在目标端创建服务器端的Database Link;

CREATE PUBLIC DATABASE LINK dblink_source CONNECT TO hr identified by hr USING ‘ORCL_SOURCE’;

— 5.在目标服务器使用expdp命令,把导出的数据放到目标端;

expdp hr/hr network_link=dblink_source directory=dir_target schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;

— 直接从源数据库导入到目标数据库,中间不生成任何的dump文件;

impdp system/oracle network_link=dblink_source directory=dir_target schemas=hr logfile=exp2imp.log parallel=2 job_name=’job_exp2imp’

——————————— NETWORK_LINK参数的测试 ———————————

——————————— INCLUDE/SQLFILE参数的测试 ———————————

— 导出HR用户下所有的PROCEDURE,SEQUENCE,TABLE对象;

expdp hr/hr directory=dir_source schemas=hr include=PROCEDURE,SEQUENCE,TABLE dumpfile=expdp_hr_part_objects.dmp logfile=expdp_hr_part_objects.log job_name=’job_expdp_hr_objects’;

— 导出dump文件中包含对象的DDL语句;

impdp system/oracle directory=dir_source dumpfile=expdp_hr_part_objects.dmp sqlfile=hr_objects.sql;