数据库的导入导出

《数据库的导入导出》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、SQL>createdirectorydump_diras'/u01/app/oracle/admin/lsmesdb/dpdump';Directorycreated.SQL>grantread,writeondirectorydump_dirtosystem;Grantsucceeded.創建使用者createusertestidentifiedbytest;授權GRANTCONNECTTOTEST;GRANTEXP_FULL_DATABASETOTEST;GRANTIMP_FULL_DATABASETOTEST;GRANTDBATOTEST1.導出整個數據庫exptest/test@li

2、nshinefile=d:dumplinshine.dmpfull=y2.導出特定的表exptest/test@linshinetables=color_admin.emp,color.deptfile=mytable.dmp3.導出特定的用戶exptest/test@linshineowner=scottfile=myuser.dmp4.導出特定的表空間exptest/test@linshinetablespace=usersfile=mytp.dmp1.導入整個數據庫imptest/test@linshinefull=yfile=d:dumplinshine.dmpignore=y

3、2.導入特定的表imptest/test@linshine　tables=color_admin.emp　file=mytable.dmpimptest/test@linshinetables=color_admin.empfromuser=scotttouser=new_scottfile=mytable.dmp3.導入特定的用戶revokeunlimitedtablespacefrombl_admin;alteruserbl_adminquota0onMES_TABLESPACE;alteruserbl_adminquotaunlimitedonBL_MES_DATA_TS;impbl_a

4、dmin/bladmin@blmes4fromuser=mes_admintouser=bl_adminfile=e:blmes.dmplog=e:imp.log1.createuserbackup_adminidentifiedbybackupadmin;2.GRANTEXP_FULL_DATABASETObackup_admin;3.GRANTIMP_FULL_DATABASETObackup_admin;4.grantcreatesession,resourcetobackup_admin5.createdirectorypump_diras'E:dump';6.grantread

5、ondirectorypump_dirtobackup_admin導出數據庫Expdptest/test@linshinedumpfile=pump_dir:mydb.dmpschemas=color_adminjob_name=rebertnologfile=y1.導入數據庫Impdptest/test@linshinedumpfile=pump_dir:mydb.dmpremap_schema=color_admin:testjob_name=rebertnologfile=yContent=metadata_only只有表結構　　　　　Data_only只有數據　　　　　ALL所有2.使

6、用expdp和impdp来给方案（用户）转移表空间3.1導出expdpsystem/managerDIRECTORY=dump_dirDUMPFILE=schema.dmpSCHEMAS=old_userLOGFILE=export2009021601.log3.2導入impdpnew_user/passwordDIRECTORY=dump_dirDUMPFILE=schema.dmpSCHEMAS=old_userREMAP_SCHEMA=old_user:new_userREMAP_TABLESPACE=system:new_tablespaceLOGFILE=import20090216

7、01.logselectusername,sid,serial#fromv$session;altersystemkillsession'152,24357'immediate;dropusertestcascade;grantconnect,resourcetotestidentifiedbytest;alterusertestdefaulttablespacetest_tstemporaryt