Oracle使用联机重定义来给表增加新列与分区

因为需要将一张上亿我们要记录的表修改为分区表,所以尝试使用联机重定义来给表增加新列与分区的方法来实现,下面是一个测试的例子,操作系统是Oracle Linux 7.1,数据库为12.2.0.1,原始表为emp_redef,该表存储在hr方案中:

SQL> desc hr.emp_redef
Name          Type         Nullable Default Comments
------------- ------------ -------- ------- --------
EMPLOYEE_ID   NUMBER(6)
FIRST_NAME    VARCHAR2(20) Y
LAST_NAME     VARCHAR2(25)
JOB_ID        VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)    Y

表emp_redef将按以下规则来进行联机重定义:
.增加新列mgr,hiredate,sal与bonus
.新列bonus被初始化为0
.列department_id的值由10开始增加
.表将被重定义为范围分区表,分区键为employee_id。

联机重定义操作如下:
1.用要执行联机重定义操作的用户登录数据库

SQL> conn pm/pm@jypdb
Connected.

2.验证表emp_redef是否可以执行联机重定义。在这种情况下,可以使用主键或伪主键来来进行验证。

SQL> exec dbms_redefinition.can_redef_table(uname=>'HR',tname=>'EMP_REDEF',options_flag=>dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

3.创建一个中间表hr.int_emp_redef

SQL> create table hr.int_emp_redef
  2  (
  3    employee_id   NUMBER(6) not null,
  4    first_name    VARCHAR2(20),
  5    last_name     VARCHAR2(25) not null,
  6    job_id        VARCHAR2(10) not null,
  7    department_id NUMBER(4) not null,
  8    mgr           NUMBER(5),
  9    hiredate      DATE DEFAULT(sysdate),
 10    sal           NUMBER(7,2),
 11    bonus         NUMBER(7,2) DEFAULT(0)
 12  )
 13  partition by range(employee_id)
 14  (
 15  partition emp200 values less than(200) tablespace users,
 16  partition emp400 values less than(400) tablespace users
 17  );
Table created

4.开始重定义操作

SQL> begin
  2  dbms_redefinition.start_redef_table(
  3    uname => 'hr',
  4    orig_table => 'emp_redef',
  5    int_table => 'int_emp_redef',
  6    col_mapping => 'employee_id employee_id, first_name first_name,last_name last_name, job_id job_id, department_id+10 department_id,0 bonus',
  7    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  8  end;
  9  /

PL/SQL procedure successfully completed.

5.复制依赖对象(自动对表hr.int_emp_redef创建任何触发器,索引,物化视图日志,授权与约束)

SQL> declare
  2  num_errors pls_integer;
  3  begin
  4  dbms_redefinition.copy_table_dependents(
  5    uname => 'hr',
  6    orig_table => 'emp_redef',
  7    int_table => 'int_emp_redef',
  8    copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
  9    copy_triggers => TRUE,
 10    copy_constraints => TRUE,
 11    copy_privileges => TRUE,
 12    ignore_errors => TRUE,
 13    num_errors => num_errors);
 14  end;
 15  /

PL/SQL procedure successfully completed.

注意,在调用这个过程时ignore_errors参数需要设置为TRUE。原因是中间表创建了主键约束,并且当执行copye_table_dependents过程来试图从原始表复制主键约束与索引时会发生错误。可以忽略这些错误,但必须执行下一步操作中的查询来查看是否还存在其它错误。

6.查询dba_redefinition_errors视图来查看错误信息

SQL> set long 8000
SQL> set pages 8000
SQL> column object_name heading 'object name' format a20
SQL> column base_table_name heading 'base table name' format a10
SQL> column ddl_txt heading 'ddl that caused error' format a40
SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors;

object name          base table ddl that caused error
-------------------- ---------- ----------------------------------------
SYS_C0023200         EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
                                ("LAST_NAME" CONSTRAINT "TMP$$_SYS_C0023
                                2000" NOT NULL ENABLE NOVALIDATE)

SYS_C0023201         EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
                                ("JOB_ID" CONSTRAINT "TMP$$_SYS_C0023201
                                0" NOT NULL ENABLE NOVALIDATE)


2 rows selected.

上面的错误信息是说中间表的last_name与job_id列为not null,而原因表为null,这种错误可以忽略。

7.同步中间表hr.int_emp_redef

SQL> begin
  2  dbms_redefinition.sync_interim_table(
  3    uname => 'hr',
  4    orig_table => 'emp_redef',
  5    int_table => 'int_emp_redef');
  6  end;
  7  /

PL/SQL procedure successfully completed.

8.完成重定义操作

SQL> begin
  2  dbms_redefinition.finish_redef_table(
  3   uname => 'hr',
  4   orig_table => 'emp_redef',
  5   int_table => 'int_emp_redef');
  6  end;
  7  /

PL/SQL procedure successfully completed.

表hr.emp_redef只会以排他模式被锁定很短的时间来结束重定义操作。在操作完成后,表hr.emp_redef将使用hr.int_emp_redef表的所有属性来重定义。

SQL> desc hr.emp_redef
Name          Type         Nullable Default   Comments
------------- ------------ -------- --------- --------
EMPLOYEE_ID   NUMBER(6)
FIRST_NAME    VARCHAR2(20) Y
LAST_NAME     VARCHAR2(25)
JOB_ID        VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)
MGR           NUMBER(5)    Y
HIREDATE      DATE         Y        (sysdate)
SAL           NUMBER(7,2)  Y
BONUS         NUMBER(7,2)  Y        (0)

SQL> select dbms_metadata.get_ddl(object_type =>'TABLE',name =>'EMP_REDEF',schema => 'HR') from dual;

DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'EMP_REDEF',SCHEMA=>'HR')
--------------------------------------------------------------------------------

  CREATE TABLE "HR"."EMP_REDEF"
   (    "EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE,
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) NOT NULL ENABLE,
        "DEPARTMENT_ID" NUMBER(4,0) NOT NULL ENABLE,
        "MGR" NUMBER(5,0),
        "HIREDATE" DATE DEFAULT (sysdate),
        "SAL" NUMBER(7,2),
        "BONUS" NUMBER(7,2) DEFAULT (0),
         CONSTRAINT "EMP_REDEF_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("EMPLOYEE_ID")
 (PARTITION "EMP200"  VALUES LESS THAN (200) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "EMP400"  VALUES LESS THAN (400) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" )


1 row selected.

可以看到表hr.emp_redef已经成功能联机重定义

9.等任何查询中间表的语句执行完成后将其删除,而且中间表在重定义后其结构就变成了原始表的表结构

SQL> desc hr.int_emp_redef
Name          Type         Nullable Default Comments
------------- ------------ -------- ------- --------
EMPLOYEE_ID   NUMBER(6)    Y
FIRST_NAME    VARCHAR2(20) Y
LAST_NAME     VARCHAR2(25)
JOB_ID        VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)    Y

SQL> drop table hr.int_emp_redef purge;
Table dropped

到此,联机重定义表hr.emp_redef就操作完成。

Oracle 12cr2 数据库之间跨网络传输表,分区或子分区

为了跨网络传输表,可以在执行导入操作时使用network_link参数,这样导入操作将会使用数据库链路而不用先导出dump文件。其操作步骤如下:
1.选择一组表,分区或子分区。
如果是要传输分区,那么在传输表操作中可以指定一个表的分区,并且在同一操作中没有其它的表将被传输。如果在传输表操作中中只有表分区的子集被导出,那么在导入后每个分区将变成非分区表。

2.在源数据库中,将要被传输的表,分区或子分区所在表空间设置为只读模式。为了查询表所在的表空间可以查询dba_tables视图,为了查询表空间的所有文件可以查询dba_data_files视图。

3.传输表,分区或子分区所在表空间的所有数据文件到目标数据库。如果源平台与目标平台的字节编码不一样,那么可以使用以下
任何一种方法来转换数据文件。
–使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件,它们会自动将数据文件转换为目标平台的字节编码。

–使用rman的convert命令来将数据文件转换为目标平台的字节编码。

4.在目标数据库上执行导入操作

5.可选操作,将源数据库中的表空间设置为读写模式

下面的例子将介绍如何使用跨网传输表,分区或子分区的方法来将一个数据库中的hr.emp_test与oe.orders_test表传输到另一个数
据库中。其中源平台与目标平台的字节编码相同。

1.先在源数据库中创建表hr.emp_test与oe.orders_test

SQL> create tablespace emp_test datafile '+DATADG/jyrac/datafile/emp_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created

SQL> create tablespace orders_test datafile '+DATADG/jyrac/datafile/orders_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created



SQL> create table hr.emp_test tablespace emp_test as select * from hr.employees;
Table created

SQL> create table oe.orders_test tablespace orders_test as select * from oe.orders;
Table created

2.在目标数据库中创建数据库链路连接到源数据库

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.

3.将源数据库中表hr.emp_test与oe.orders_test所在的表空间设置为只读状态

SQL> alter tablespace emp_test read only;
Tablespace altered

SQL> alter tablespace orders_test read only;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
EMP_TEST                       READ ONLY
ORDERS_TEST                    READ ONLY
11 rows selected

4.将表空间tem_test与orders_test的所有数据文件复制到目标数据库中
在源数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.


SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'emp_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'emp_test_01.dbf');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'orders_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'orders_test_01.dbf');
PL/SQL procedure successfully completed


ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt  
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  N    orders_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.303.945987633
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  N    emp_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.302.945987591
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  Y    FILE_TRANSFER.303.945987633
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  Y    FILE_TRANSFER.302.945987591
DATAFILE  UNPROT  COARSE   JUN 06 19:00:00  N    sales_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 06 19:00:00  Y    FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 05 23:00:00  Y    SYSAUX.275.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399

5.在目标数据库中执行导入操作

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/emp_test_01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/orders_test_01.dbf' tables=hr.emp_test,oe.orders_test logfile=imp_tables.log directory=tts_dump

Import: Release 12.2.0.1.0 - Production on Tue Jun 6 22:24:24 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@JYPDB_175 network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/emp_test_01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/orders_test_01.dbf tables=hr.emp_test,oe.orders_test logfile=imp_tables.log directory=tts_dump 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jun 6 22:24:57 2017 elapsed 0 00:00:30

6.可选操作,将源数据库中的表空间emp_test与orders_test设置为读写模式

SQL> alter tablespace emp_test read write;
Tablespace altered

SQL> alter tablespace orders_test read write;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
EMP_TEST                       ONLINE
ORDERS_TEST                    ONLINE
11 rows selected

Oracle 12r2 数据库之间传输表,分区或子分区

在Oracle 12cr2中,可以使用传输表功能来从一个数据库中复制一组表,分区或子分区到另一个数据库中。传输表操作将会指定表,分区或子分区的元数据移到另一个数据库中。传输表操作会自动识别所指定表所在的表空间。为了移动数据,需要将这些表所在表空间的所有数据文件复制到目标数据库。Data Pump导入会自动释放由表,分区或子分区所占有的数据块,这些数据块不是传输表操作的一部分。

可以使用以下方法来传输表,分区或子分区:
.使用导出dump文件
在执行导出时,指定tables参数并且设置transportable参数为always。在执行导入时,不需要指定transportable参数。Data Pump导入会自动识别传输表操作。

.跨网络
在执行导入时,指定tables参数并且设置transportable参数为always,并且指定network_link参数来指定数据链路

传输表操作的限制
.不能将相同方案中相同表名的表传输到目标数据库中。然而可以使用remap_table导入参数来将表中的数据导入到不同的表中。另外,在传输操作执行之前,可以重命名被传输表或目标表。

.对于加密有以下限制:
–不能传输加密表空间中的表
–不能包含加密列的表

.不能在使用不同的time zone文件版本的不同平台之间传输使用timestamp with timezone的表

使用导出dump文件方式来传输表,分区,或子分区
在数据库之间使用志出dump文件来传输表需要执行以下步骤。
1.选择一组表,分区或子分区。
如果是要传输分区,那么在传输表操作中可以指定一个表的分区,并且在同一操作中没有其它的表将被传输。如果在传输表操作中中只有表分区的子集被导出,那么在导入后每个分区将变成非分区表。

2.在源数据库中,将要被传输的表,分区或子分区所在表空间设置为只读模式。为了查询表所在的表空间可以查询dba_tables视图,为了查询表空间的所有文件可以查询dba_data_files视图。

3.执行Data Pump导出

4.传输导出的dump文件,将导出的dump文件复制到目标数据库并且让其可以访问。

5.传输表,分区或子分区所在表空间的所有数据文件到目标数据库。如果源平台与目标平台的字节编码不一样,那么可以使用以下任何一种方法来转换数据文件。
–使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件,它们会自动将数据文件转换为目标平台的字节编码。

–使用rman的convert命令来将数据文件转换为目标平台的字节编码。

6.可选操作,将源数据库中的表空间设置为读写模式

7.在目标数据库上执行导入操作

下面的例子将分区表sh.sales_test表中的部分分区(sales_test_q1_2000,sales_test_q2_2000)传输到目标数据库中。源平台与目标平台字节编码一样,都是linux 64位操作系统

1.先创建分区表sales_test

SQL> create tablespace sales_test datafile '+DATADG/jyrac/datafile/sales_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created

-- Create table
create table SH.SALES_TEST
(
  prod_id       /* NUMBER not null*/,
  cust_id       /*NUMBER not null*/,
  time_id       /*DATE not null*/,
  channel_id    /*NUMBER not null*/,
  promo_id      /*NUMBER not null*/,
  quantity_sold /*NUMBER(10,2) not null*/,
  amount_sold   /*NUMBER(10,2) not null*/
)
partition by range (TIME_ID)
(
  partition SALES_TEST_1995 values less than (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_1996 values less than (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_H1_1997 values less than (TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_H2_1997 values less than (TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q1_1998 values less than (TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_1998 values less than (TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_1998 values less than (TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_1998 values less than (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_1999 values less than (TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_1999 values less than (TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_1999 values less than (TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_1999 values less than (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2000 values less than (TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_2000 values less than (TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_2000 values less than (TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_2000 values less than (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2001 values less than (TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_2001 values less than (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_2001 values less than (TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_2001 values less than (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2002 values less than (TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q2_2002 values less than (TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q3_2002 values less than (TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q4_2002 values less than (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q1_2003 values less than (TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q2_2003 values less than (TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q3_2003 values less than (TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q4_2003 values less than (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 5
    initrans 1
    maxtrans 255
) tablespace sales_test as select * from sh.sales;
-- Add comments to the table
comment on table SH.SALES_TEST
  is 'facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys';
-- Add comments to the columns
comment on column SH.SALES_TEST.prod_id
  is 'FK to the products dimension table';
comment on column SH.SALES_TEST.cust_id
  is 'FK to the customers dimension table';
comment on column SH.SALES_TEST.time_id
  is 'FK to the times dimension table';
comment on column SH.SALES_TEST.channel_id
  is 'FK to the channels dimension table';
comment on column SH.SALES_TEST.promo_id
  is 'promotion identifier, without FK constraint (intentionally) to show outer join optimization';
comment on column SH.SALES_TEST.quantity_sold
  is 'product quantity sold with the transaction';
comment on column SH.SALES_TEST.amount_sold
  is 'invoiced amount to the customer';
-- Create/Recreate indexes
create bitmap index SH.SALES_TEST_CHANNEL_BIX on SH.SALES_TEST (CHANNEL_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_CUST_BIX on SH.SALES_TEST (CUST_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_PROD_BIX on SH.SALES_TEST (PROD_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_PROMO_BIX on SH.SALES_TEST (PROMO_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_TIME_BIX on SH.SALES_TEST (TIME_ID)
  nologging  local;
-- Create/Recreate primary, unique and foreign key constraints
alter table SH.SALES_TEST
  add constraint SALES_TEST_CHANNEL_FK foreign key (CHANNEL_ID)
  references SH.CHANNELS (CHANNEL_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_CUSTOMER_FK foreign key (CUST_ID)
  references SH.CUSTOMERS (CUST_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_PRODUCT_FK foreign key (PROD_ID)
  references SH.PRODUCTS (PROD_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_PROMO_FK foreign key (PROMO_ID)
  references SH.PROMOTIONS (PROMO_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_TIME_FK foreign key (TIME_ID)
  references SH.TIMES (TIME_ID)
  novalidate;

2.登录到源数据库,将表sh.sales_test所在的表空间设置为只读状态

SQL> alter tablespace sales_test read only;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     READ ONLY
9 rows selected

3.导出dump文件

SQL> create or replace directory tts_dump as '/tts';
Directory created
SQL> grant execute,read,write on directory tts_dump to public;
Grant succeeded


[root@jyrac1 ~]# su - oracle
[oracle@jyrac1 ~]$ expdp system/xxzx7817600 dumpfile=sales_test.dmp directory=tts_dump tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 transportable=always logfile=sales_test.log

Export: Release 11.2.0.4.0 - Production on Tue Jun 6 11:21:02 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=sales_test.dmp directory=tts_dump tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 transportable=always logfile=sales_test.log
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /tts/sales_test.dmp
******************************************************************************
Datafiles required for transportable tablespace SALES_TEST:
  +DATADG/jyrac/datafile/sales_test_01.dbf
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jun 6 11:21:22 2017 elapsed 0 00:00:17

4.将导出的dump文件传输到目标数据库

[oracle@jytest1 tts]$ scp oracle@10.138.130.151:/tts/sales_test.* /tts/
The authenticity of host '10.138.130.151 (10.138.130.151)' can't be established.
RSA key fingerprint is 92:b7:e1:f5:a4:99:5a:de:d5:d3:f2:25:f7:98:0a:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.151' (RSA) to the list of known hosts.
oracle@10.138.130.151's password:
sales_test.dmp                                                                                                                                                                                            100%  264KB 264.0KB/s   00:00
sales_test.log                                                                                                                                                                                            100% 1542     1.5KB/s   00:00
[oracle@jytest1 tts]$ ls -lrt
total 268
-rw-r----- 1 oracle oinstall 270336 Jun  6 18:49 sales_test.dmp
-rw-r--r-- 1 oracle oinstall   1542 Jun  6 18:49 sales_test.log

5.将sales_test表空间的数据文件传输到目标数据库
在源数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.


SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.


SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'sales_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'sales_test_01.dbf');
PL/SQL procedure successfully completed

ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 06 18:00:00  N    sales_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 06 18:00:00  Y    FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 05 23:00:00  Y    SYSAUX.275.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399

6.可选操作,将源数据库中的表空间sales_test设置为读写模式

SQL> alter tablespace sales_test read write;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
9 rows selected

7.在目标数据库上执行导入操作

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 dumpfile=sales_test.dmp directory=tts_dump transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/sales_test_01.dbf' tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 logfile=imp_sales_test.log

Import: Release 12.2.0.1.0 - Production on Tue Jun 6 19:23:09 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@JYPDB_175 dumpfile=sales_test.dmp directory=tts_dump transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/sales_test_01.dbf tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 logfile=imp_sales_test.log
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 13 error(s) at Tue Jun 6 19:25:06 2017 elapsed 0 00:01:46

SQL> select owner,table_name,tablespace_name from dba_tables where owner='SH';
OWNER                                                                            TABLE_NAME                                                                       TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------
SH                                                                               SALES_TEST_SALES_TEST_Q1_2000                                                    SALES_TEST
SH                                                                               SALES_TEST_SALES_TEST_Q2_2000                                                    SALES_TEST
SH                                                                               SALES_TRANSACTIONS_EXT
SH                                                                               COSTS
SH                                                                               SALES
SH                                                                               CAL_MONTH_SALES_MV                                                               EXAMPLE
SH                                                                               FWEEK_PSCAT_SALES_MV                                                             EXAMPLE
SH                                                                               DIMENSION_EXCEPTIONS                                                             EXAMPLE
SH                                                                               SUPPLEMENTARY_DEMOGRAPHICS                                                       EXAMPLE
SH                                                                               COUNTRIES                                                                        EXAMPLE
SH                                                                               CUSTOMERS                                                                        EXAMPLE
SH                                                                               PROMOTIONS                                                                       EXAMPLE
SH                                                                               PRODUCTS                                                                         EXAMPLE
SH                                                                               TIMES                                                                            EXAMPLE
SH                                                                               CHANNELS                                                                         EXAMPLE

可以看到分区sales_test_q1_2000与sale_test_q2_2000导入后分别成为了一张非分区表

Oracle 12C 跨网络传输数据库

跨网络传输数据库,可以通过使用network_link参数来执行导入操作,导入操作将使用数据库链路,不需要生成dump文件。操作步骤如下:
1.在目标数据库中创建链接到源数据库的数据链路。执行导入操作的用户必须要有datapump_imp_full_database权限,并且连接到源数据库的数据链路也必须连接到一个有datapump_exp_full_database角色的用户。在源数据库中用户不能有sysdba管理权限。

2.在源数据库上将所有用户表空间置为只读模式

3.将源数据库中所有用户表空间相关的数据文件传输到目标数据库。如果源平台与目标平台的字节编码不同,那么查询v$transportable_platform视图来进行查看。并且将可以使用以下一种方法来转换数据文件:
.使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件。这些过程会自动将数据文件的字节编码转换为目标平台的字节编码。

.使用rman的convert命令来将数据文件的字节编码转换为目标平台的字节编码。

4.在目标数据库上执行导入操作。使用Data Pump工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。确保以下参数正确设置:
.transportable=always
.transport_datafiles=list_of_datafiles
.full=y
.network_link=database_link
.version=12
如果源数据库为11.2.0.3或11g之后的版本,那么必须设置version=12。如果源数据库与目标数据库都是12c,那么version参数不用设置。

如果源数据库包含任何加密表空间或表空间包含加密列,那么你必须指定encryption_pwd_prompt=yes或指定encryption_password参数。

Data Pump跨网络导入将会复制所有用户表空间所存储对象的元数据与管理表空间中的元与用户对象的真实数据。当导入完成后,用户表空间将会置于读写模式。

5.可选操作将源数据库中的所有用户表空间置为读写模式。

下面的例子是将源数据库jyrac传输到目标数据库jypdb
1.在目标数据库中以sys用户来创建链接到源数据库的数据链路。源数据库中的用户为jy

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.

2.在源数据库上将所有用户表空间置为只读模式

SQL>  select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

SQL> alter tablespace test read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace example read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          READ ONLY
UNDOTBS2                       ONLINE
EXAMPLE                        READ ONLY
TEST                           READ ONLY

8 rows selected.

3.在目标数据库中使用dbms_file_transfer包中的get_file过程将源数据库中所有用户表空间相关的数据文件传输到目标数据库上
在源数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile(存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中执行dbms_file_transfer.get_file过程将源数据库中所有用户表空间所相关的数据文件传输到目标数据库中

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf');

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf');

PL/SQL procedure successfully completed.

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf');

PL/SQL procedure successfully completed.
ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    SYSAUX.275.939167015

4.在目标数据库上执行导入操作。使用Data Pump工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' version=12 directory=TTS_DUMP_LOG logfile=import.log

Import: Release 12.2.0.1.0 - Production on Fri Jun 2 16:30:40 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf version=12 directory=TTS_DUMP_LOG logfile=import.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
.......
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
ORA-39082: Object type PROCEDURE:"APEX_030200"."F" created with compilation warnings

ORA-39082: Object type PROCEDURE:"APEX_030200"."APEX_ADMIN" created with compilation warnings

ORA-39082: Object type PROCEDURE:"APEX_030200"."HTMLDB_ADMIN" created with compilation warnings

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1689 error(s) at Fri Jun 2 16:43:47 2017 elapsed 0 00:13:03


传输完成后我们抽查用户jy的dba_tables表的数据在传输后是否与源数据库中的数据一致。
源数据库

SQL> conn sys/xxzx7817600@jyrac as sysdba
Connected.

SQL> select count(*) from jy.dba_tables;

  COUNT(*)
----------
      2141

目标数据库

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.

SQL> select count(*) from jy.dba_tables;

  COUNT(*)
----------
      2141

查询传输后用户表空间的状态是否为online,可以看到test,example,users表空间状态为online

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TESTTB                         ONLINE
TEMP2                          ONLINE
TEMP3                          ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
UNDOTBS2                       ONLINE

12 rows selected.

5.将源数据库中的所有用户表空间设置为读写模式

SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace example read write;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

到此通过网络执行完整数据库传输的操作就完成了。

Oracle 12c full transportable export & import

传输数据库full transportable export/import
可以使用full transportable export/import功能将整个数据库从一个数据库实例复制到另一个数据库实例。可以使用Data Pump来生成一个导出dump文件,如果需要将这个dump文件传输到目标数据库,然后导入dump文件。另外也可以使用Data Pump跨网络来复制数据库。

数据库中要被传输的表空间可以是字典管理或本地管理表空间。源数据库中的表空间的块大小不必与目标数据库中标准块大小相同。

这种传输数据库的方法要求直到完成导出dump文件之前所要传输的用户创建的表空间必须设置为只读状态。如果不能满足这个条件那么可以使用备份功能来完成传输表空间。

full transportable export/import的限制
full transportable export/import有以下限制:
.对于不同字节编码的平台不能传输加密表空间,对于相同字节编码的平台为了传输加密表空间,在执行导出dump文件时需要设置encryption_pwd_prompt导出参数设置为yes,或者使用encryption_password导出参数。在导入dump文件时,使用与导出时相同的参数设置。
.当跨网络传输数据库时,如果在管理表空间(比如system或sysaux表空间)中存在包含long或long raw列的表,那么是不支持传输的。
.full transportable export/import可以使用传统的Data Pump导出/导入来导出与导入存储在管理表空间中用户创建的数据库对象,比如直接路径或外部表。管理表空间不是用户创建而是由数据库提供,比如sytem与sysaux表空间。
.full transportable export/import不能传输同时存储在管理表空间(比如system与sysaux)与用户创建表空间中的数据库对象。例如,一个分区表可能会同时存储在管理表空间与用户表空间中。如果有这样的对象,那么在传输之前应该重新定义这些对象,因此它们将整个存储在管理表空间或者用户表空间中。如果对象不能重定义,那么可以使用传统的Data Pump导出/导入。.当跨网络传输数据库时,当存储在管理表空间(比如system与sysaux)中的表它的审计跟踪住处本身存储在用户表空间中就不能启用审计。

使用导出dump文件来传输数据库
使用导出dump文件方式来传输数据库必须执行以下步骤:
1.在源数据库上,将每个用户表空间设置为只读状态。在执行导出操作时要确保设置参数transportable=always与full=y。如果源数据库的版本是11.2.0.3或11G之后的版本,那么还必须设置version=12或更高版本号。导出的dump文件包含了存储在用户表空间中对象的元数据与存储在管理表空间(比如system与sysaux)中用户创建对象的元数据与实际数据。

2.将导出的dump文件传输到目标数据库

3.将所有用户表空间的所有数据文件传输到目标数据库,如果源平台与目标平台不同,那么需要检查字节编码,可以通过查询v$transportable_platform视图进行查看。如果源平台与目标平台的字节编码不一样,那么使用以下一种方法来转换数据文件:
.使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件。这些过程会将源数据文件自动转换为目标平台的字节编码方式。
.使用rman的convert命令将源数据文件转换为目标平台的字节编码方式

4.可选操作,将源数据库中的将被传输的表空间设置为读写状态

5.在目标数据库中导入数据,当导入完成后,用户表空间将会设置为读写状态。

下面的例子将把jyrac数据库(11.2.0.4)传输到jypdb数据库(12.2的PDB),源数据库jyrac中用户表空间为test,users,example,源平台与目标平台的字节编码相同。具体操作如下:
1.将表空间test设置为只读状态

SQL> alter tablespace test read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace example read only;

Tablespace altered.



SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          READ ONLY
UNDOTBS2                       ONLINE
EXAMPLE                        READ ONLY
TEST                           READ ONLY

8 rows selected.

2.使用Data Pump导出工具执行full transportable export操作

SQL> create or replace directory tts_dump as '/tts';
Directory created
SQL> grant execute,read,write on directory tts_dump to public;
Grant succeeded

SQL> host expdp tts/tts@JYRAC dumpfile=exp_test.dmp directory=tts_dump transportable=always full=y version=12 logfile=exp_test.log

Export: Release 11.2.0.4.0 - Production on Fri May 26 17:41:33 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "TTS"."SYS_EXPORT_FULL_01":  tts/********@JYRAC dumpfile=exp_test.dmp directory=tts_dump transportable=always full=y version=12 logfile=exp_test.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 47.43 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX
ORA-39043: Object type INDEX is not supported for "SH"."SYS_IL0000088402C00006$$".
ORA-39043: Object type INDEX is not supported for "SH"."SYS_IL0000088405C00002$$".
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.976 KB      38 rows
. . exported "SYS"."AUD$"                                473.3 KB    2931 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.367 KB      10 rows
. . exported "WMSYS"."WM$ENV_VARS"                       5.921 KB       3 rows
......
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "TTS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TTS.SYS_EXPORT_FULL_01 is:
  /tts/exp_test.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  +DATADG/jyrac/datafile/example.260.930413057
Datafiles required for transportable tablespace TEST:
  +DATADG/jyrac/datafile/test01.dbf
Datafiles required for transportable tablespace USERS:
  +DATADG/jyrac/datafile/users.263.930413057
Job "TTS"."SYS_EXPORT_FULL_01" completed with 2 error(s) at Fri May 26 17:47:08 2017 elapsed 0 00:05:31

在执行导出时必须指定transportable=always,它用来判断是否使用传输选项。full参数用来指定将导出整个数据库。dumpfile参数指定dump文件名。directory参数指定目录,它可以指向操作系统或ASM磁盘组。在执行导出前必须先创建目录,并授予读写权限。在non-CDB中,会自动创建目录对象DATA_PUMP_DIR,并且会自动授予DBA角色可以对其执行读写访问。因此sys与system用户就可以对目录执行读写操作。然而在PDB中不会自动创建目录DATA_PUMP_DIR。因此在导入PDB时,需要先创建目录。logfile参数用来指定导出操作日志文件。为了对数据库版本为11.2.0.3或以后的11G版本执行full transportable导出,必须使用version参数,并且必须指定为12或更高版本。

full transportable导入操作只有在Oracle 12c中支持,因此目标数据库必须为12c

3.将导出的dump文件传输到目标平台的所选定的目录中,该目录可以被目标数据库所访问在目标数据库中创建目录tts_dump(存储dump文件),tts_datafile(存储数据文件)

SQL> create or replace directory tts_dump as '/tts';

Directory created.

SQL> grant execute,read,write on directory tts_dump to public;

Grant succeeded.


SQL> create or replace directory tts_datafile as '+test/jycs/datafile';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中执行以下命令来传输dump文件

[oracle@jytest1 tts]$ scp -r oracle@10.138.130.152:/tts/exp_test.dmp /tts/
The authenticity of host '10.138.130.152 (10.138.130.152)' can't be established.
RSA key fingerprint is 92:b7:e1:f5:a4:99:5a:de:d5:d3:f2:25:f7:98:0a:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.152' (RSA) to the list of known hosts.
oracle@10.138.130.152's password:
exp_test.dmp                                                                                                                                                                                              100%   59MB  29.5MB/s   00:02
[oracle@jytest1 tts]$

4.从源平台将所有用户表空间传的相关数据文件输到目标平台的tts_datafile文件,通过dbms_file_transfer.put_file过程来实现。
创建源数据库连接目标数据库的数据链路

SQL> create database link jycs_link
  2  connect to system identified by "xxzx7817600"
  3  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.175)(PORT = 1521)) )(CONNECT_DATA =(SERVICE_NAME = jycs)))';
Database link created

SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATADG/jyrac/datafile/users.263.930413057                                       USERS
+DATADG/jyrac/datafile/undotbs1.262.930413057                                    UNDOTBS1
+DATADG/jyrac/datafile/sysaux.258.930413055                                      SYSAUX
+DATADG/jyrac/datafile/system.259.930413057                                      SYSTEM
+DATADG/jyrac/datafile/example.260.930413057                                     EXAMPLE
+DATADG/jyrac/datafile/undotbs2.261.930413057                                    UNDOTBS2
+DATADG/jyrac/datafile/test01.dbf                                                TEST
7 rows selected

需要传输的数据文件为test01.dbf,example.260.930413057与users.263.930413057

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

在目标数据库的ASM磁盘组可以看到相关的数据文件

ASMCMD [+test/jycs/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    users01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.281.945022371
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    test01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.279.945022099
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    example01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.280.945022161
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.281.945022371
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.280.945022161
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.279.945022099
DATAFILE  MIRROR  COARSE   MAY 26 06:00:00  Y    SYSAUX.260.942323941
DATAFILE  MIRROR  COARSE   MAY 20 22:00:00  Y    UNDOTBS1.259.942323977
DATAFILE  MIRROR  COARSE   MAY 11 12:00:00  Y    SYSTEM.269.942323889
DATAFILE  MIRROR  COARSE   MAY 11 00:00:00  Y    UNDOTBS2.266.942324411
DATAFILE  MIRROR  COARSE   MAY 02 11:00:00  Y    USERS.258.942323981

5.可选操作,将源数据库中的所有用户表空间设置为读写模式

SQL> alter tablespace users read write;

Tablespace altered.

SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace example read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

5.在目标数据库上执行数据库导入

[oracle@jytest1 admin]$ impdp jy/jy@JYPDB_175 dumpfile=exp_test.dmp directory=TTS_DUMP transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' logfile=import.log

Import: Release 12.2.0.1.0 - Production on Fri May 26 20:18:03 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "JY"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "JY"."SYS_IMPORT_TRANSPORTABLE_01":  jy/********@JYPDB_175 dumpfile=exp_test.dmp directory=TTS_DUMP transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf logfile=import.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists

ORA-31684: Object type TABLESPACE:"TEMP" already exists

Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
ORA-31685: Object type USER:"SYS" failed due to insufficient privileges. Failing sql is:
 ALTER USER "SYS" IDENTIFIED BY VALUES 'S:0C82FC9FD1570D45359355071D58A402378ABB404B83306BEA34DD19216F;D50A6384B1C2A4CF' TEMPORARY TABLESPACE "TEMP"
.....

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP"                 5.976 KB      38 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYS"."AMGT$DP$AUD$"                        473.3 KB    2931 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$"           6.367 KB      10 rows
. . imported "WMSYS"."E$ENV_VARS"                        5.921 KB       3 rows
. . imported "WMSYS"."E$EVENTS_INFO"                      5.75 KB      12 rows
. . imported "WMSYS"."E$HINT_TABLE"                       9.25 KB      72 rows
. . imported "WMSYS"."E$NEXTVER_TABLE"                   6.265 KB       1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE"         5.875 KB       1 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE"                14.51 KB       1 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE"            6.851 KB       8 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$"             0 KB       0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$"               0 KB       0 rows
. . imported "SYS"."NET$_ACL"                                0 KB       0 rows
. . imported "SYS"."WALLET$_ACL"                             0 KB       0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES"           0 KB       0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE"                   0 KB       0 rows
. . imported "WMSYS"."E$CONS_COLUMNS"                        0 KB       0 rows
. . imported "WMSYS"."E$INSTEADOF_TRIGS_TABLE"               0 KB       0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO"                       0 KB       0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES"                     0 KB       0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE"           0 KB       0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE"          0 KB       0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE"                0 KB       0 rows
. . imported "WMSYS"."E$REMOVED_WORKSPACES_TABLE"            0 KB       0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE"            0 KB       0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE"                   0 KB       0 rows
. . imported "WMSYS"."E$RIC_TABLE"                           0 KB       0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE"                  0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS"               0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_INFO"                         0 KB       0 rows
. . imported "WMSYS"."E$VERSION_TABLE"                       0 KB       0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE"                     0 KB       0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE"          0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
ORA-31693: Table data object "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-22303: type "SYS"."JDM_STR_VALS" not found
ORA-21700: object does not exist or is marked for delete

. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT"       5.859 KB       2 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT"              0 KB       0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP"               0 KB       0 rows
. . imported "WMSYS"."E$EXP_MAP"                             0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
ORA-39082: Object type PACKAGE BODY:"SYS"."WWV_DBMS_SQL" created with compilation warnings
......

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_EXPR" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_FORM" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_STANDARD" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_XLIFF" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_META_CLEANUP" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_ACC_LOAD" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRMMENU_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_OLB_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_UPDATE_APX_APP" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_UTILITIES" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_RPT_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CALENDAR2" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CHART2" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_REPORT3" created with compilation warnings

ORA-39082: Object type TRIGGER:"APEX_030200"."WWV_BIU_FLOW_SESSIONS" created with compilation warnings

Job "JY"."SYS_IMPORT_TRANSPORTABLE_01" completed with 536 error(s) at Fri May 26 20:45:45 2017 elapsed 0 00:27:38

检查表空间及其状态

SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/system.274.939167015          SYSTEM
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/sysaux.275.939167015          SYSAUX
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs1.273.939167015        UNDOTBS1
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undo_2.277.939167063          UNDO_2
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf                   USERS
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb01.dbf                  TESTTB
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf                 EXAMPLE
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf                    TEST
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs2.278.945029905        UNDOTBS2
9 rows selected

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TESTTB                         ONLINE
TEMP2                          ONLINE
TEMP3                          ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
UNDOTBS2                       ONLINE
12 rows selected

对于要传输整个数据库来说,使用full transportable export /import这种方法要比传输表空间方便很多。