【impdp】使用impdp工具排除特定表的匯入
在《【imp】使用imp工具遷移資料時迂迴地排除特定表的匯入》(http://space.itpub.net/519536/viewspace-672170)文中提到使用imp工具迂迴的完成排除特定表匯入問題。使用impdp工具可以非常容易的實現。
1.環境準備
1)建立使用者sec並給予
sys@ora10g> drop user sec cascade;
sys@ora10g> create user sec identified by sec default tablespace TBS_SEC_D;
sys@ora10g> grant dba to sec;
2)在sec使用者下建立三張表,為每張表中初始化一條資料
sys@ora10g> conn sec/sec
sec@ora10g> create table t1 (x int);
sec@ora10g> create table t2 (x int);
sec@ora10g> create table t3 (x int);
sec@ora10g> insert into t1 values(1);
sec@ora10g> insert into t2 values(2);
sec@ora10g> insert into t3 values(3);
sec@ora10g> commit;
sec@ora10g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T2 TABLE
T1 TABLE
T3 TABLE
2.使用expdp工具生成sec使用者的備份檔案
ora10g@asdlabdb01 /db_backup/dpump_dir$ expdp system/oracle directory=dpump_dir dumpfile=sec.dmp schemas=sec
Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 September, 2010 22:34:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dpump_dir dumpfile=sec.dmp schemas=sec
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SEC"."T1" 4.914 KB 1 rows
. . exported "SEC"."T2" 4.914 KB 1 rows
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/db_backup/dpump_dir/sec.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 02:34:36
3.刪除sec使用者
sys@ora10g> drop user sec cascade;
User dropped.
4.使用impdp工具完成匯入
1)匯入操作
ora10g@asdlabdb01 /db_backup/dpump_dir$ impdp system/oracle directory=dpump_dir dumpfile=sec.dmp exclude=table:"in('T1')"
Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 September, 2010 22:35:20
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dpump_dir dumpfile=sec.dmp exclude=table:in('T1')
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T2" 4.914 KB 1 rows
. . imported "SEC"."T3" 4.914 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 02:35:23
5.小結
在Oracle 10g時代,推薦選用expdp/impd工具完成邏輯備份及資料遷移,該工具的主要特點是:迅速、便捷!
Good luck.
secooler
10.09.01
-- The End --
1.環境準備
1)建立使用者sec並給予
sys@ora10g> drop user sec cascade;
sys@ora10g> create user sec identified by sec default tablespace TBS_SEC_D;
sys@ora10g> grant dba to sec;
2)在sec使用者下建立三張表,為每張表中初始化一條資料
sys@ora10g> conn sec/sec
sec@ora10g> create table t1 (x int);
sec@ora10g> create table t2 (x int);
sec@ora10g> create table t3 (x int);
sec@ora10g> insert into t1 values(1);
sec@ora10g> insert into t2 values(2);
sec@ora10g> insert into t3 values(3);
sec@ora10g> commit;
sec@ora10g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T2 TABLE
T1 TABLE
T3 TABLE
2.使用expdp工具生成sec使用者的備份檔案
ora10g@asdlabdb01 /db_backup/dpump_dir$ expdp system/oracle directory=dpump_dir dumpfile=sec.dmp schemas=sec
Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 September, 2010 22:34:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dpump_dir dumpfile=sec.dmp schemas=sec
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SEC"."T1" 4.914 KB 1 rows
. . exported "SEC"."T2" 4.914 KB 1 rows
. . exported "SEC"."T3" 4.914 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/db_backup/dpump_dir/sec.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 02:34:36
3.刪除sec使用者
sys@ora10g> drop user sec cascade;
User dropped.
4.使用impdp工具完成匯入
1)匯入操作
ora10g@asdlabdb01 /db_backup/dpump_dir$ impdp system/oracle directory=dpump_dir dumpfile=sec.dmp exclude=table:"in('T1')"
Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 September, 2010 22:35:20
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dpump_dir dumpfile=sec.dmp exclude=table:in('T1')
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T2" 4.914 KB 1 rows
. . imported "SEC"."T3" 4.914 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 02:35:23
5.小結
在Oracle 10g時代,推薦選用expdp/impd工具完成邏輯備份及資料遷移,該工具的主要特點是:迅速、便捷!
Good luck.
secooler
10.09.01
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25462274/viewspace-2094942/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 然後再全庫匯入排除view資料庫在impdp匯入View資料庫
- Oracle 12c expdp和impdp匯出匯入表Oracle
- Oracle 12.1.0.2 impdp匯入慢診斷Oracle
- Oracle資料泵匯出匯入(expdp/impdp)Oracle
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle 19.3資料庫impdp匯入view時hang住Oracle資料庫View
- impdp匯入報ORA-00001 ORA-04088錯誤
- IMPDP分割槽表注意事項
- Oracle 11g impdp 先資料後索引匯入方法Oracle索引
- 19c資料庫impdp匯入view時hang住資料庫View
- Oracle 28.6資料庫impdp匯入view時hang@11Oracle資料庫View
- oracle10g expdp資料泵的bug,按schema匯出,匯入impdp時無jobOracle
- Oracle OCP(57):IMPDPOracle
- IMPDP匯入沒有某個表空間的處理方法__加上引數TRANSFORM就可以了ORM
- ORACLE 資料泵impdp匯入報錯之ORA-31693 ORA-04098Oracle
- oracle資料庫的impdp,expdpOracle資料庫
- [20200620]expdp impdp exclude引數.txt
- expdp/impdp變慢 (Doc ID 2469587.1)
- 【Data Pump】expdp/impdp Job基本管理
- oracle dblink用法總結和expdp和impdp利用dblink倒入匯出到本地Oracle
- impdp導致主鍵索引的變化索引
- 使用資料泵(expdp、impdp)遷移資料庫流程資料庫
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- expdp/impdp 詳細引數解釋
- exp匯出表中特定條件的表
- 使用impdp,expdp資料泵進入海量資料遷移
- impdp和expdp用法及引數介紹
- ORACLE EXPDP IMPDP 的停止和啟動及監控Oracle
- 從Yum中排除特定包
- [20231212]impdp content=metadata_only locks the stats.txt
- impdp自動建立使用者前提條件與應用場景
- 資料泵datapump(expdp/impdp)的parfile用法,避免跳脫字元字元
- EXP、IMP、SQLLOADER、EXPDP、IMPDP、DBMS_METADATA、SQLPLUS等方面SQL
- Oracle 11g用impdp還原資料庫Oracle資料庫
- [20200904]12c invisible column impdp segment_column_id.txt
- [20181014]12Cr2 impdp使用NETWORK_LINK支援LONG欄位型別.txt型別
- impdp ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
- mysqldump 備份匯出資料排除某張表或多張表MySql
- mongodb使用自帶命令工具匯出匯入資料MongoDB