oracle10g_impdp工具測試學習_之一

--users,sysaux,temp,sysaux表空間不能刪除

-bash-3.00$ impdp system/system dumpfile=other:expdp_full_db.dmp full=y

Import: Release 10.2.0.2.0 - Production on Monday, 14 September, 2009 11:26:15

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 834
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"

$sqlplus "/as sysdba"
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql

---全庫匯入
expdp system/system dumpfile=other:exp_full_db.dmp full=y
impdp system/system dumpfile=other:exp_full_db.dmp full=y

---使用者匯入
expdp system/system schemas=mv dumpfile=other:exp_user_mv.dmp

drop user mv cascade  --把哪個使用者drop掉
impdp system/system schemas=mv dumpfile=other:exp_user_mv.dmp

---表空間匯入
expdp system/system tablespaces=mv dumpfile=other:exp_tablespace_mv.dmp
drop tablespace mv including contents and datafiles;
create tablespace mv datafile '/oracle/mv.dbf' size 100m;---在匯入前一定要先建表空間,不然會報錯(根據select dbms_medata.get_ddl)
impdp system/system tablespaces=mv dumpfile=other:exp_tablespace_mv.dmp

---表匯入
expdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp
conn mv/system
drop table sol purge;

impdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp
或者
impdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp content=metadata_only --只匯入表結構
impdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp content=data_only --只匯入表資料,這個適用於要匯入的表結構已經存在
impdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp content=all --這也是預設值,就是匯入表結構和表資料

 --注意以下table_exists_action選項,處理資料依據dumpfile轉儲檔案
impdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp table_exists_action=skip  --用於表物件已在存在,不處理了
impdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp table_exists_action=append ---用於在原來表資料基礎上新增資料
impdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp table_exists_action=replace ---先drop表,然後插入資料
impdp system/system tables=mv.sol  dumpfile=other:exp_table_sol.dmp table_exists_action=truncate ---先truncate表資料,然後插入資料


--sqlfile的學習,用於impdp,就是把dumpfile轉儲檔案中的ddl語句匯入到指定的檔案,另這個檔案要儲存在目錄(directory)
expdp system/system schemas=mv dumpfile=dir:export_user_mv.dmp
impdp system/system schemas=mv dumpfile=dir:export_user_mv.dmp sqlfile=dir:ddl.sql