如何完成Oracle数据库中的数据文件（XXX.dmp）导出、导入？ oracle单实例使用exp导出全库的数据dmp文件 怎么使...
oracle\u6570\u636e\u5e93\u5982\u4f55\u5c06\u5927\u91cf\u6570\u636e(.dmp)\u5bfc\u5165\u7684\u95ee\u9898DMP\u6587\u4ef6\u5bfc\u5165\uff1a
1.\u7528SYS\u7528\u6237\u540d\uff0c\u4ee5DBA\u7684\u8eab\u4efd\u767b\u5165\u5230\u6570\u636e\u5e93(ORACLE\u5ba2\u6237\u7aefEnterprise Manager Console)
2.\u5728\u5b89\u5168\u6027 -- \u7528\u6237 \u82e5\u6240\u8981\u5bfc\u5165\u7684\u7528\u6237\u5bf9\u8c61\u5df2\u5b58\u5728 \u5219\u79fb\u53bb \u7136\u540e\u65b0\u5efa\u4e4b(\u8fd9\u6837\u5c31\u53ef\u4ee5\u5220\u9664\u8be5\u7528\u6237\u4e0b\u6240\u6709\u5bf9\u8c61\u4e86) \u8868\u7a7a\u95f4\u9009\u62e9\u539f\u6765\u7684\u3002
3.\u5728\u8be5\u7528\u6237\u7684\u89d2\u8272\u4e2d\u52a0\u5165DBA\u89d2\u8272
4.\u5728\u547d\u4ee4\u884c\u4e0b\u6267\u884c\uff1aimp aioss/aioss@ORCL10G file=d:\aioss0819.dmp full=y ignore=y commit=y
imp\u6ca1\u6709\u64cd\u4f5c\u754c\u9762\u7684\uff0c\u5982\u679c\u4f60\u60f3\u7528\u56fe\u5f62\u754c\u9762\u7684\u8bdd\uff0c\u5c31\u53ef\u4ee5\u4f7f\u7528\u7b2c\u4e09\u65b9\u8f6f\u4ef6\uff0c\u76f8\u5bf9\u7684\uff0c\u6211\u89c9\u5f97\u65e5\u672c\u51fa\u7684\u90a3\u4e2aobject browser \u5c31\u597d\u4e00\u70b9\u4e86\uff0c\u73b0\u5728\u51fa\u523010\u7248\u672c\u7684\uff0c\u4e0a\u9762\u7684\u5bfc\u51fa\u5bfc\u5165\u529f\u80fd\u4e5f\u662f\u5f15\u7528oracle\u7684imp/exp\u6a21\u5757\u7684\uff0c\u4e0d\u8fc7\u5c31\u662f\u754c\u9762\u64cd\u4f5c\u7684\uff0c\u53ef\u4ee5\u5728\u63d0\u793a\u4e0b\u8bbe\u7f6e\u53c2\u6570\u3002
\u4e00\u822c\u6309\u7167\u5982\u4e0b\u65b9\u6cd5\u5bfc\u5165\u5373\u53ef\u3002
1\u3001win\u952e+R\uff0c\u8f93\u5165cmd\uff0c\u6253\u5f00\u547d\u4ee4\u63d0\u793a\u7b26\u3002
2\u3001\u8fdb\u5165dmp\u6587\u4ef6\u6240\u5728\u76ee\u5f55\u3002
3\u3001\u6267\u884cimp\u5bfc\u5165\u8bed\u53e5\uff1a
imp \u88ab\u5bfc\u5165\u7528\u6237\u540d/\u5bc6\u7801@\u5b9e\u4f8b\u540d file=aaa.dmp log=aaa.log fromuser=\u5bfc\u51fa\u7528\u6237\u540d\u5982\u56fe\uff1a
\u6ce8\u610f\u4e8b\u9879\uff1a\u5bfc\u5165\u7684oracle\u7248\u672c\u9700\u8981\u4e0e\u5bfc\u51fa\u7684oracle\u7248\u672c\u4e00\u81f4\u6216\u9ad8\u4e8e\u5bfc\u51fa\u7684oracle\u7248\u672c\uff0c\u5426\u5219\u5bfc\u5165\u65f6\u4f1a\u62a5\u9519\u3002
exp scott/tiger@mycon tables=(dept,emp) file=tab1.dmp
导出用户:
exp system/manager@mycon owner=scott file=usr1.dmp
导出数据库:
1.完全导出
exp system/manager@mycon full=y inctype=complete file=full1.dmp
2.增量导出
exp system/manager@mycon full=y inctype=incremental file=inc1.dmp
3.累积导出
exp system/manager@mycon full=y inctype=cumulative file=cum1.dmp
imp example:
导入表:
imp system/manager@mycon file=c:\tab1.dmp tables=(dept,emp) touser=scott
导入用户:
imp system/manager@mycon file=usr1.dmp fromuser=scott touser=scott
导入数据库:
1.全库导入
imp system/manager@mycon file=full1.dmp full=y
2.增量导入
1)导入数据库最新信息
imp system/manager@mycon inctype=system full=y file=inc7.dmp
2)导入最近完全导出文件
imp system/manager@mycon inctype=restore full=y file=full1.dmp
3)导入所有累积导出文件
imp system/manager@mycon inctype=restore full=y file=cum1.dmp
4)导入最近一次增量导出的文件
imp system/manager@mycon inctype=restore full=y file=inc1.dmp
你可以用客户端
建一用户
赋两个权限
一个dba
一个是connect
先建个表空间不就完了吗
分table user database三种类型的导出，exp命令执行，参数选择不同
命令行或者linux终端执行
绛旓細1.鍏ㄥ簱瀵煎叆 imp system/manager@mycon file=full1.dmp full=y 2.澧為噺瀵煎叆 1)瀵煎叆鏁版嵁搴鏈鏂颁俊鎭 imp system/manager@mycon inctype=system full=y file=inc7.dmp 2)瀵煎叆鏈杩戝畬鍏ㄥ鍑烘枃浠 imp system/manager@mycon inctype=restore full=y file=full1.dmp 3)瀵煎叆鎵鏈夌疮绉鍑烘枃浠 imp system/manag...
绛旓細鍦∣racle鏁版嵁搴撲腑锛鍙互浣跨敤DBMS_SCHEDULER鏉ュ疄鐜版瘡澶╄嚜鍔ㄦ墽琛屽瓨鍌ㄨ繃绋嬬殑鍔熻兘銆侱BMS_SCHEDULER鏄疧racle鎻愪緵鐨勪竴绉嶄换鍔¤皟搴﹀伐鍏凤紝鍙互鐢ㄦ潵瀹氭椂鎵цSQL鑴氭湰銆佸瓨鍌ㄨ繃绋嬬瓑銆備笅闈㈡槸浣跨敤DBMS_SCHEDULER瀹炵幇姣忓ぉ鑷姩鎵ц瀛樺偍杩囩▼鐨勫熀鏈楠わ細鍒涘缓瀛樺偍杩囩▼锛氶鍏堥渶瑕佸垱寤轰竴涓渶瑕佸畾鏃舵墽琛岀殑瀛樺偍杩囩▼銆傚垱寤轰綔涓氾細浣跨敤DBMS_SCHEDUL...
绛旓細String sql_g4d_select = " select t3.xm xm,t3.xb xb,t3.qslc qslc,t3.zzlc,t3.妫鏌ユ棩鏈 妫鏌ユ棩鏈,to_char(t3.涓婁紶鏃堕棿,'yyyy-MM-dd') 涓婁紶鏃堕棿,t3.涓婁紶鍗曚綅,t3.璺眬鍚,5 sclx from v$g4d_line t3 where 1=1 ";//娉ㄦ剰涓夊紶瑙嗗浘鐨鏁版嵁绫诲瀷瑕佽浆鎹竴鑷达紝鑰屼笖鍒楃殑鏁伴噺瑕佷竴...
绛旓細鏁版嵁搴撶鐞嗗憳鍙互閫氳繃鏌ョ湅v$sysstat鏁版嵁瀛楀吀涓淐PU used by this session鈥濈粺璁￠」寰楃煡ORACLE鏁版嵁搴浣跨敤鐨凜PU鏃堕棿,鏌ョ湅鈥淥S User level CPU time鈥濈粺璁￠」寰楃煡鎿嶄綔绯荤粺鐢ㄦ埛鎬佷笅鐨凜PU鏃堕棿,鏌ョ湅鈥淥S System call CPU time鈥濈粺璁￠」寰楃煡鎿嶄綔绯荤粺绯荤粺鎬佷笅鐨凜PU鏃堕棿,鎿嶄綔绯荤粺鎬荤殑CPU鏃堕棿灏辨槸鐢ㄦ埛鎬佸拰绯荤粺鎬佹椂闂翠箣鍜,濡傛灉ORACL...
绛旓細涓绉嶆槸鐢╩usqldump鍔犲弬鏁拌繘琛.sql鐨勫鍑猴紝鐒跺悗鍐嶇敤oracle鎵ц銆傝繕鏈変竴涓槸鐢╡tl宸ュ叿寤哄伐绋嬪幓鍋氳繖涓簨鎯吢仿仿
绛旓細锛1锛 鍏抽棴鏁版嵁搴 sqlplus /nolog sql>;connect /as sysdba sql>;shutdown normal;锛2锛 鐢ㄦ嫹璐濆懡浠ゅ浠藉叏閮ㄧ殑鏃堕棿鏂囦欢銆侀噸鍋氭棩蹇楁枃浠躲佹帶鍒舵枃浠躲佸垵濮嬪寲鍙傛暟鏂囦欢 {$PageTitle=sql>;cp ; ;} 锛3锛 閲嶅惎Oracle鏁版嵁搴 sql>;startup 涓夈 鐑浠 鐑浠芥槸鍦ㄦ暟鎹簱杩愯鐨勬儏鍐典笅锛岄噰鐢╝rchivelog mode...
绛旓細[鏈嶅姟鍣╙閫夋嫨瑕佸鍑烘暟鎹殑鏈嶅姟鍣紝濡傛灉鏄湰鏈猴紝閫夋嫨锛坙ocal锛 [鏁版嵁搴]閫夋嫨瑕佸鍑虹殑鏁版嵁鎵鍦ㄧ殑搴擄紝杩欓噷閫夋嫨Northwind 3锛庝笅涓姝ワ紝閫夋嫨鐩殑 [鐩殑]閫夋嫨Microsoft ODBC for Oracle [DSN]閫夋嫨鐢ㄦ埛/绯荤粺DSN涓椤癸紝鐒跺悗鍦ㄤ笅鎷夊垪琛ㄦ涓壘涓涓凡缁忚繛鎺ュ埌浜哠cott鐢ㄦ埛鐨凞SN鍚嶇О銆 濡傛灉涓嬫媺鍒楄〃涓病鏈夛紝鐐逛笅鎷夊垪琛...
绛旓細1銆侀氳繃鍦ㄥ紑濮嬭緭鍏md锛岃繘鍏os鍛戒护绐楀彛锛2銆佽緭鍏qlplus system/system瀵嗙爜@鏁版嵁搴鍚嶇О锛岀偣鍥炶溅锛屼粠鑰岃繛閫氭暟鎹簱锛3銆佸湪涓婅堪绐楀彛涓緭鍏elect username from dba_users锛岀偣鍥炶溅锛屼互鏄剧ず褰撳墠鏁版嵁搴撳疄渚涓殑鐢ㄦ埛鍚嶏紱4銆侀夋嫨瑕佸垹闄ょ殑鐢ㄦ埛锛屽湪褰撳墠绐楀彛涓緭鍏ワ細drop user USERNAME cascade锛涚偣鍥炶溅銆5銆佺◢绛夌墖鍒...
绛旓細姝ｅ父鎯呭喌涓嬶紝濡傛灉鍒涘缓dblink鎴愬姛锛屽彲閲囩敤璇ユ柟寮忚闂埌杩滅▼鏁版嵁搴鐨勮〃.3.閫氳繃merge璇彞瀹屾垚琛ㄦ暟鎹悓姝 姝や緥涓渶瑕佸皢center搴撲腑鐨tb_sys_sqlscripe琛ㄥ悓姝ュ埌branch锛岀畝鍗曠殑璇硶濡備笅:merge into tb_sys_sqlscripe@branch b using tb_sys_sqlscripe c on (b.pk=c.pk) --浠巆enter灏嗚〃merge鍒癰ranch,...
绛旓細1銆1銆佽皟鏁存暟鎹粨鏋勭殑璁捐銆傝繖涓閮ㄥ垎鍦ㄥ紑鍙戜俊鎭郴缁熶箣鍓瀹屾垚锛岀▼搴忓憳闇瑕佽冭檻鏄惁浣跨敤ORACLE鏁版嵁搴撶殑鍒嗗尯鍔熻兘锛屽浜庣粡甯歌闂殑鏁版嵁搴撹〃鏄惁闇瑕佸缓绔嬬储寮曠瓑銆2銆2銆佽皟鏁村簲鐢ㄧ▼搴忕粨鏋勮璁°傝繖涓閮ㄥ垎涔熸槸鍦ㄥ紑鍙戜俊鎭郴缁熶箣鍓嶅畬鎴愶紝绋嬪簭鍛樺湪杩欎竴姝ラ渶瑕佽冭檻搴旂敤绋嬪簭浣跨敤浠涔堟牱鐨勪綋绯荤粨鏋勶紝鏄娇鐢ㄤ紶缁熺殑Client/Server涓...
