oracle10g/11g 新特性十(数据移植)

Moving Data General Architecture

Oracle  Data Pump: Overview

As  server-based facility for high-speed data and metadata movement , Oracle Data Pump;
1.Is callable via DBMS_DATAPUMP

2.Provides the following tools:

   a.expdp

   b. impdp

   c.Web-based interface

3.Provides four data movement methods:

   a.Data file copying

   b.Direct path

   c.External tables

   d.Network link support

4.Detaches from and reattaches to long-runing jobs

5.Restarts Data Pump jobs

Oracle Data Pump Benefits:

1.更精确的控制数据导出导入

2.显示指明数据库的版本

3.并发执行

4.估算出占有空间的量

5.Remapping capabilities 改变导入的目标

6.Data sampling and metadata compression 数据采样和元数据压缩

7.压缩数据 在数据

8.加密

9.大数据导出作为xml方式

10. 支持import, export的参宿

Directory Objects for Data Pump

Creating Directory Objects:

Data Pump Export and Import Clients: Overview

Data Pump Utility: interfaces and Modes

1.Data Pump Export and Import interfaces:

     a.Command line

     b.Parameter file

     c.Interactive command line

     d.Enterprise Manager

2.Data Pump Export and Import modes

  a.Full

  b.Schema

  c.Table

  d.Tablespace

  e.Transportable tablespace

Data Pump Export using Database Control

Data Pump Export Example :Advanced Options

Data Pump Import Example :impdp

Data Pump Import:Transformations

You can rempa:

1.Data files by using REMAP_DATAFILE

2.Tablespaces by using REMAP_TABLESPACE

3.Schemas by using REMAP_SCHEMA

4.Tables by using REMAP_TABLE

5.Data by using REMAP_DATA

  REMAP_TABLE='EMPLOYEES':'EMP'

Migration with Data Pump Legacy Mode

  支持 exp,imp命令的参数

Managing File Locations

1.usr1 user-create table--emp1
    create table emp1 as select * from user_tables;
2. usr1 user-create index--emp1
    create index emp1_table_name_idx on emp1(table_name);    
	
SQL> select segment_name, tablespace_name from user_segments;             
SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
LOG                  APP1
T2                   APP2
T3                   APP1
T4                   APP3
TA                   APP1
T                    APP1
EMP1                 APP1
EMP1_TABLE_NAME_IDX  APP1

3.create directory-test_dir--grant
   授权：sys 用户： 
   grant create any directory to usr1; 
   创建目录
   create directory test_dir as '/u01/back';
4.show directory
   看这个目录只能通过 dba的身份来看：(sys用户)
    select * from dba_directories where directory_name='TEST_DIR';
	
5.expdp-emp1
   /u01/app/oracle/product/10.2.0/db_1/bin/expdp usr1/usr1 dumpfile=emp1.dmp DIRECTORY=test_dir tables =tmp1 logfile=emp1.log
    在导的过程中 会在导出 用户的 表空间中 建一个表：  SYS_EXPORT_TABLE_01
    导出结束 即删除
6.破坏
  drop table emp1 perge;
7.impdp
  /u01/app/oracle/product/10.2.0/db_1/bin/impdp usr1/usr1 dumpfile=emp1.dmp DIRECTORY=test_dir  logfile=emp1.log
  exp--test-emp1-app_data   //exp , imp 只能改变用户，但是不能改变原表的表空间
  imp--user1-tmp1-app_data
  impdp test/test DUMPFILE=emp1.dmp DIRECTORY=test_dir logfile=emp1.log remap_schema=usr1:test  remap_tablespace=app_data:app_test remap_table=emp1:emp2
  remap_table 时候 原表的 索引等导不过来  
  remap_data:导入的过程中改变数据
  grant read,write on directory test_dir to test; //这个权限需要

SQL*Loader 直接路径和常规路径方式

External Tables

External Table Benefits

1.数据可以直接来自文件

2.执行复杂的查询， 可以用外部表和  oracle 库里的表进行连接，也可以并行.不需要先把外部表装载到数据库

Define an external Tables with ORACLE_LOADER

Define an External Tables with ORACLE_LOADER 11G

Using External Tables

Data Dictionary