expdp&impdp For Oracle 10G

-----------------------------------------------------
匯出與匯入表資料 For Oracel
-----------------------------------------------------

1.建立匯出目錄

SQL> create directory dmpdir as '/archivelog/dmpdir';

Directory created.

2.查詢目錄是否建立

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR
/orahome/10.2/ccr/state

SYS DMPDIR
/archivelog/dmpdir

SYS DATA_PUMP_DIR
/orahome/10.2/rdbms/log/[@more@]

3.匯出表資料

SQL> host expdp system/xxx directory=dmpdir tables=xxxx_SN_SITE_STATISTICS dumpfile=xxxx_sn_site_statistics.dmp logfile=xxxx_sn_site_statistics.log job_name=expdp;



4.啟動到非歸檔模式,不讓其產生ARCHIVELOG

SQL> shutdown immeidate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;

5.進行資料的匯入

SQL> host impdp system/xxxxx directory=dmpdir dumpfile=xxxx_sn_site_statistics.dmp logfile=impdp_xxxx_sn_site_statistics.log job_name=impdp full=y;



6.開啟資料歸檔
SQL> shutdown immeidate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog/STCSMES
Oldest online log sequence 26560
Next log sequence to archive 26565
Current log sequence 26565
SQL> alter database open;

--------------------------

匯出、匯入使用者下所有資料

--------------------------

SQL> host expdp system/xxxx directory=dmpdir dumpfile=xxx.dmp logfile=expdp_xxx.log schemas=xxx job_name=expdp;

SQL> drop user smp cascade;

SQL> host impdp system/xxx directory=dmpdir dumpfile=xxx.dmp logfile=impdp_xxx.log job_name=impdp full=y;

-------------------------------------------------------------------

從一個匯出schema dmp檔案中，匯入一個table到另一個schema

-------------------------------------------------------------------

impdp system/system schemas=xxx directo
ry=xxx_dir remap_schema=xxx:xxxx dumpfile=xxx.dmp include = TABLE:" in ('EMP') " logfile=xxxx.log;