13 - 移动数据

第十三课

13、 移动数据

Data Pump有以下三个部分组成：
客户端工具：
 expdp/impdp
 Data Pump API (即DBMS_DATAPUMP)
 Metadata API（即DMBS_METADATA)
一个Data Pump job由以下三部分组成：
主进程(master process）：控制整个job，是整个job的协调者。
主表(master table）：记录dumpfile里数据库对象的元信息。
工作进程(worker processes）：执行导出导入工作


数据泵基础结构是通过DBMS_DATAPUMP PL/SQL 序包调用的。

13.1创建directories目录

select * from dba_directories;
DATA_PUMP_DIR目录

create directory mydump as '/u01/mydump';
grant read,write on directory mydump to system|public;

每个数据泵操作的核心为主表(MT)，这是在运行数据泵作业的用户方案中创建的表。MT中保存着作业的各个方面。
数据泵作业正常完成后，MT 即会删除。
网络模式:源库可以是只读库，数据不落地。

expdp help=y（参数查看）
parfile参数
expdp ouzy/ouzy directory=mydump estimate_only=yes（仅做估计）
保证导出数据的一致性:
exp用consistent=Y参数
expdp用flashback_scn或者flashback_time=sysdate参数

expdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp logfile=ouzy.log flashback_time=sysdate
select * from dba_datapump_jobs;（查看job 运行视图）
可以对JOB进行execute、stop、suspend。
1）Ctrl+C组合键：在执行过程中，可以按Ctrl+C组合键退出当前模式，退出之后，导出操作不会停止
2）Export> status --查看当前JOB的状态及相关信息
3）Export> stop_job --暂停JOB（暂停job后会退出expor模式）
4）重新进入export模式下:
expdp begin/begin attach=begin.SYS_EXPORT_SCHEMA_01（重新挂载运行job）
5）Export> start_job --打开暂停的JOB
6）Export> kill_job --取消当前的JOB并释放相关客户会话（将job删除同时删除dmp文件）
7）Export> exit --通过此命令退出export模式（通过4）可再进入export模式下）

DECLARE
  h1 NUMBER;
BEGIN
  h1 := dbms_datapump.open(operation => 'EXPORT'
                          ,job_mode => 'SCHEMA'
                          ,job_name => 'EXPDP001'
                          ,version => 'COMPATIBLE');
  dbms_datapump.set_parallel(handle => h1, degree => 1);
  dbms_datapump.add_file(handle => h1
                        ,filename => 'EXPDAT.LOG'
                        ,directory => 'MYDUMP'
                        ,filetype => 3);
  dbms_datapump.set_parameter(handle => h1
                             ,NAME => 'KEEP_MASTER'
                             ,VALUE => 0);
  dbms_datapump.metadata_filter(handle => h1
                               ,NAME => 'SCHEMA_EXPR'
                               ,VALUE => 'IN(''OUZY'')');
  dbms_datapump.add_file(handle => h1
                        ,filename => 'EXPDAT%U.DMP'
                        ,directory => 'MYDUMP'
                        ,filetype => 1);
  dbms_datapump.set_parameter(handle => h1
                             ,NAME => 'INCLUDE_METADATA'
                             ,VALUE => 1);
  dbms_datapump.set_parameter(handle => h1
                             ,NAME => 'DATA_ACCESS_METHOD'
                             ,VALUE => 'AUTOMATIC');
  dbms_datapump.set_parameter(handle => h1
                             ,NAME => 'ESTIMATE'
                             ,VALUE => 'BLOCKS');
  dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
  dbms_datapump.detach(handle => h1);
END;
/

通过网络导入(源库可以是只读库):
目标库:

create public database link ouzylink connect to ouzy identified by "ouzy" using '192.168.230.101:1521/prod';
select count(*) from t@ouzylink;
--通过dblink把ouzy用户从网路导入目标库（102）
impdp system/Oracle123 network_link=ouzylink schemas=ouzy logfile=import.log

 --将dump中的T表导入成T2表 
impdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp remap_table=t:t2 tables=t

13.2 SQL*Loader

SQL*Loader可将外部文件中的数据加载到Oracle DB 的表中。

create table t as select owner,object_id,created from dba_objects;
ouzy1`100`2019/09/12 14:46:55
ouzy2`200`2019/09/12 14:46:58

dos2unix工具：将回车换行转为linux下的换行

–编辑控制文件ouzy.ctl

LOAD DATA
INFILE 'ouzy.dat' --需导入的文件名
BADFILE 'data.BAD'
DISCARDFILE 'data.DSC'
APPEND INTO TABLE OUZY.T  --需导入数据的表
Fields terminated by "`"
TRAILING NULLCOLS
(
  OWNER,
  OBJECT_ID,
  CREATED DATE "YYYY/MM/DD HH24:MI:SS"
)

sqlldr ouzy/ouzy control=ouzy.ctl 常规路径
sqlldr ouzy/ouzy control=ouzy.ctl direct=true 直接路径

express模式:
sqlldr ouzy/ouzy table=t

常规路径加载使用SQL 处理和数据库COMMIT 操作来保存数据。
直接路径加载使用数据保存将数据块写入Oracle 数据文件。这就是为什么直接路径加载比常规路径加载快很多的原因。

外部表为只读表，因此无法执行DML 操作，也不能对其创建索引。
外部表使用两种访问驱动程序。ORACLE_LOADER 访问驱动程序只能用于读取外部表中的表数据并将其载入数据库。它使用文本文件作为数据源。
ORACLE_DATAPUMP 访问驱动程序既可以将表数据从外部文件载入数据库中，也可以将数据从数据库卸载到外部文件中。它使用二进制文件作为外部文件。

–创建控制文件ouzy.ctl：
sqlldr ouzy/ouzy control=ouzy.ctl external_table=generate_only

使用oracle_loader:

create directory mydump as '/u01/mydump';
grant read,write on directory mydump to public;

–根据创建控制文件时生成的日志文件，整理创建外部文本ouzy.dat脚本（sql下执行）

CREATE TABLE "SYS_SQLLDR_X_EXT_T" 
(
  "OWNER" VARCHAR2(128),
  "OBJECT_ID" NUMBER,
  "CREATED" DATE
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY MYDUMP
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'MYDUMP':'data.BAD'
    DISCARDFILE 'MYDUMP':'data.DSC'
    LOGFILE 'ouzy.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "`" LDRTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "OWNER" CHAR(255)
        TERMINATED BY "`",
      "OBJECT_ID" CHAR(255)
        TERMINATED BY "`",
      "CREATED" CHAR(255)
        TERMINATED BY "`"
        DATE_FORMAT DATE MASK "YYYY/MM/DD HH24:MI:SS"
    )
  )
  location 
  (
    'ouzy.dat'
  )
)REJECT LIMIT UNLIMITED;

将数据从外部表附加到内部表:

INSERT /*+ APPEND */ INTO t2 SELECT * FROM t;

使用oracle_datapump:
–将表数据导出

CREATE TABLE ouzy.ext_t
(owner,object_id,created)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY mydump
LOCATION ('ouzy_ext.dmp')
)
AS
SELECT owner,object_id,created from ouzy.t;

–将ouzy_ext.dmp导入并创建表ouzy.ext_t2

CREATE TABLE ouzy.ext_t2
(owner varchar2(100),object_id number ,created date)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY mydump
LOCATION ('ouzy_ext.dmp')
);

相关视图:

--查询外部表
select * from dba_external_tables;
查询位置信息，目录
select * from dba_external_locations;