oracle 數據庫導出步驟
1.connect sys/770511 as sysdba;
2.create directory dump_dir as '/usr/app/backup';
3.grant read,write on directory dump_dir to wanyan;
4.導出方案
expdp wanyan/wanyan directory=dump_dir dumpfile=wanyan_date.dmp logfile=wanyan.log schemas=wanyan job_name=exp_wanyan_schema
165 oracle 數據庫導入:(注意導入時要重新創建數據庫和表，所以要將原來的表刪除,否則將導入失敗)
1.grant connect,resource to wanyan identified by wanyan;
2 alert user wanyan default tablespace myts01 temporary tablespace temp;
3 impdp point/point directory=dpump_dir dumpfile=db165_point_0705101106.dmp logfile=point.log schemas=point job_name=imp_schema
Windows:
D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log
SCHEMAS=scott INCLUDE=TABLE:\"IN ('EMP', 'DEP')\"
Unix:
$ORACLE_HOME/bin/expdp user/passwd schemas=user dumpfile=db227_point_$APP_NAME.dmp DIRECTORY=dpump_dir INCLUDE=TABLE:\"IN \(\'PI_B_USERINFO\', \'PI_B_USERDETAIL\'\)\"
$ORACLE_HOME/bin/expdp user/passwd schemas=user dumpfile=db227_point_$APP_NAME.dmp DIRECTORY=dpump_dir EXCLUDE=TABLE:\"IN \(\'PI_D_PROP_ANSWER\'\)\"
impdp point/point directory=dpump_dir dumpfile=db227_point_070626125643.dmp logfile=point.log schemas=point job_name=imp_schema INCLUDE=TABLE:\"IN \(\'PI_B_USERDETAIL\'\)\"
上面的導出有bug long型字段輸出格式被破壞
1.單表
exp wanyan/wanyan rows=y indexes=n compress=n buffer=65536 feedback=100000 volsize=0 file=exp_wanyan_survey_20060911.dmp log=exp_wanyan_survey_20060911.log tables=wanyan.wy_b_survey
imp wanyan/wanyan fromuser=wanyan touser=wanyan rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=exp_wanyan_survey_20060911.dmp log=imp_wanyan_survey_20060911.dmp tables=wy_b_survey
2.整個用戶
exp wanyan/wanyan owner=wanyan rows=y indexes=n compress=n buffer=65536 feedback=100000 volsize=0 file=exp_wanyan_20060911.dmp log=exp_wanyan_20060911.log
imp wanyan/wanyan fromuser=wanyan touser=wanyan rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=exp_wanyan_20060911.dmp log=imp_wanyan_20060911.logimp wanyan/wanyan fromuser=wanyan touser=wanyan rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=exp_wanyan_200610261200.dmp log=imp_20061026.log tables=wy_b_user
3.用戶導出，單表導入
imp wanyan/wanyan fromuser=wanyan touser=wanyan rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=exp_wanyan_200610261200.dmp log=imp_20061026.log tables=wy_b_user