Oracle 12cr2 資料庫之間傳輸表，分割槽或子分割槽
在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匯入後分別成為了一張非分割槽表
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2140347/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle分割槽表和分割槽表exchangeOracle
- oracle分割槽表和非分割槽表exchangeOracle
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- MySql資料分割槽操作之新增分割槽操作MySql
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- ORACLE刪除-表分割槽和資料Oracle
- 移動分割槽表和分割槽索引的表空間索引
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- Oracle12c：建立主分割槽、子分割槽，實現自動分割槽插入效果Oracle
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- ORACLE分割槽表梳理系列Oracle
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- oracle 更改分割槽表資料 ora-14402Oracle
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- PostgreSQL：傳統分割槽表SQL
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- 分割槽表之自動增加分割槽（11G）
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- Oracle查詢Interval partition分割槽表內資料Oracle
- oracle將表配置為分割槽表Oracle
- oracle 普通表-分割槽表改造流程Oracle
- 非分割槽錶轉換成分割槽表
- PG的非分割槽表線上轉分割槽表
- 增加表分割槽時，為local分割槽索引指定不同表空間的方法索引
- MySQL資料表分割槽手記MySql
- zabbix上對mysql資料庫做分割槽表MySql資料庫
- Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲Oracle資料庫
- 【MYSQL】 分割槽表MySql
- 對oracle分割槽表的理解整理Oracle
- Oracle SQL調優之分割槽表OracleSQL
- ORACLE 19c 新特性之混合分割槽表Oracle
- Oracle分割槽表基礎運維-07增加分割槽(4 RANGE_HASH)Oracle運維