共计 18645 个字符，预计需要花费 47 分钟才能阅读完成。
在 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 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-06/144579.htm
