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