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.dmp  TABLES=ks.his_done:P201107  IGNORE=Y INDEXES=N STATISTICS=NONE LOG=D:\imp.log

PAUSE

 

c.删除

 

ALTER TABLE ks.his_done DROP PARTITION p201107;