oracle impdp目录,oracle expdp/impdp

1\ expdp

1)确认dump路径：

select * from dba_directoies;中的data_pump_dir

可用以下方式更改：

create or replace directory data_pump_dir as '/backup'

2)用以下方式EXPDP

expdp schemas=(a_user,b_user) dumpfile=xxx.dmp

logfile=xx.log;

2\ impdp

1)确认dump文件放的路径：

确认dump文件放在select * from dba_directoies;中的data_pump_dir路径下；

2)用以下方式IMPDP

impdp schema=a_user dumpfile=xx.dmp logfile=xx.log

(无需先创建用户，可直接导入)

数据库备份\恢复

备份脚本：

1\ backup parameter file;

windows:copy $ORACLE_HOME\database\spfilesid.ora

aix/linux: cp $ORACLE_HOME/dbs/spfilesid.ora

2\ backup tablespace scripts;

CREATE SMALLFILE TABLESPACE SYWG

LOGGING

DATAFILE

'/webdbdata1/SYWG_01.dbf' SIZE 50M AUTOEXTEND ON NEXT  10M MAXSIZE UNLIMITED,

'/webdbdata2/SYWG_02.dbf' SIZE 50M AUTOEXTEND ON NEXT  10M MAXSIZE UNLIMITED,

'/webdbdata3/SYWG_03.dbf' SIZE 50M AUTOEXTEND ON NEXT  10M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT  AUTO

3\ backup data:

expdp system/sywg1234 directory=test dumpfile=webdb.dmp logfile=webdb.log schemas=SYWG,WEBCALL,HQ,SJCK,SJPZ

恢复脚本：

1\ install oracle software and fix the newest patch;

2\ dcca create database and paste backuped parameter file;

3\ create tablespace;

4\ impdp system/sywg1234 directory=test dumpfile=webdb.dmp logfile=webdb_imp.log SCHEMAS=SYWG exclude=statistics

partition表

a.导出

exp system/pwd@service FILE=F\:dump_filename.dmp TABLES=ks.his_done:P201107 ROWS=Y statistics=none LOG=F:\exp.log

PAUSE

b.导入

imp system/pwd@service FILE=D:\dump_filename.dmpTABLES=ks.his_done:P201107 IGNORE=Y INDEXES=N STATISTICS=NONE LOG=D:\imp.log

PAUSE

c.删除

ALTER TABLE ks.his_done DROP PARTITION p201107;