æè¿ç¨å°Oracle导åºå¯¼å
¥æ°æ®ï¼å¨ç½ä¸çäºå«äººçä¸äºæç« ï¼æ»ç»å¦ä¸ï¼
ããOracle导åºå¯¼åºæ两ä¸æ¹å¼ï¼ä¸ãå©ç¨exp imp导åºå¯¼å
¥ï¼äºãå©ç¨Oracelæ°æ®æ³µexpdp impdp导åºå¯¼å
¥ã
ããä¸ãå©ç¨exp imp导åºå¯¼å
¥
ããexp imp è¯æ³å¦ä¸ï¼
ããexpï¼
ãã1) å°æ°æ®åºorclå®å
¨å¯¼åº
ããããexp system/manager@orcl file=d:\orcl_bak.dmp full=y
ãã2) å°æ°æ®åºä¸systemç¨æ·ç表导åº
ããããexp system/manager@orcl file=d:\system_bak.dmp owner=system
ãã3) å°æ°æ®åºä¸è¡¨table1ï¼table2导åº
ããããexp system/manager@orcl file=d:\table_bak.dmp tables=(table1,table2)
ãã4) å°æ°æ®åºä¸ç表customerä¸çå段mobile以"139"å¼å¤´çæ°æ®å¯¼åº
ããããexp system/manager@orcl file=d:\mobile_bak.dmp tables=customer query=\"where mobile like '139%' \"
ããimpï¼
ãã1) å°å¤ä»½æ件bak.dmp导åºæ°æ®åº
ããããimp system/manager@orcl file=d:\bak.dmp
ããããå¦ææ°æ®è¡¨ä¸è¡¨å·²ç»åå¨ï¼ä¼æ示é误ï¼å¨åé¢å ä¸ignore=yå°±å¯ä»¥äºããã
ãã2) å°å¤ä»½æ件bak.dmpä¸ç表table1导å
¥
ããããimp system/manager@orcl file=d:\bak.dmp tables=(table1)
ããexp imp导åºå¯¼å
¥æ°æ®æ¹å¼ç好å¤æ¯åªè¦ä½ æ¬å°å®è£
äºOracle客æ·ç«¯ï¼ä½ å°±å¯ä»¥å°æå¡å¨ä¸çæ°æ®å¯¼åºå°ä½ æ¬å°è®¡ç®æºãåæ ·ä¹å¯ä»¥å°dmpæ件ä»ä½ æ¬å°å¯¼å
¥å°æå¡å¨æ°æ®åºä¸ãä½æ¯è¿ç§æ¹å¼å¨Oracle11gçæ¬ä¸ä¼åºç°ä¸ä¸ªé®é¢ï¼ä¸è½å¯¼åºç©ºè¡¨ãOracle11gæ°å¢äºä¸ä¸ªåæ°deferred_segment_creationï¼å«ä¹æ¯æ®µå»¶è¿å建ï¼é»è®¤æ¯trueãå½ä½ æ°å»ºäºä¸å¼ 表ï¼å¹¶ä¸æ²¡ç¨åå
¶ä¸æå
¥æ°æ®æ¶ï¼è¿ä¸ªè¡¨ä¸ä¼ç«å³åé
segmentã
ãã解å³åæ³ï¼
ãã1ã设置deferred_segment_creationåæ°ä¸ºfalseåï¼æ 论æ¯ç©ºè¡¨ï¼è¿æ¯é空表ï¼é½åé
segmentã
ããå¨sqlplusä¸ï¼æ§è¡å¦ä¸å½ä»¤ï¼
ããSQL>alter system set deferred_segment_creation=false;
ããæ¥çï¼
ããSQL>show parameter deferred_segment_creation;
ãã该å¼è®¾ç½®åï¼åªå¯¹åé¢æ°å¢ç表起ä½ç¨ï¼å¯¹ä¹å建ç«ç空表ä¸èµ·ä½ç¨ï¼å¹¶ä¸æ³¨æè¦éå¯æ°æ®åºè®©åæ°çæã
ãã2ãä½¿ç¨ ALLOCATE EXTEN
ããä½¿ç¨ ALLOCATE EXTENå¯ä»¥ä¸ºæ°æ®åºå¯¹è±¡åé
Extentï¼è¯æ³å¦ä¸ï¼
ããalter table table_name allocate extent
ããæ建对空表åé
空é´çSQLå½ä»¤ï¼
ãã
ããSQL>select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
ããæ¹éçæè¦ä¿®æ¹çè¯å¥ã
ããç¶åæ§è¡è¿äºä¿®æ¹è¯å¥ï¼å¯¹ææ空表åé
空é´ã
ããæ¤æ¶ç¨expå½ä»¤ï¼å¯å°å
æ¬ç©ºè¡¨å¨å
çææ表导åºã
ãã
ããäºãå©ç¨expdp impdp导åºå¯¼å
¥
ããå¨Oracle10gä¸exp imp被éæ°è®¾è®¡ä¸ºOracle Data Pumpï¼ä¿çäºåæç exp impå·¥å
·ï¼
ãã
ããæ°æ®æ³µä¸ä¼ ç»å¯¼åºå¯¼å
¥çåºå«;
ãã1) expåimpæ¯å®¢æ·ç«¯å·¥å
·ï¼ä»ä»¬æ¢å¯ä»¥å¨å®¢æ·ç«¯ä½¿ç¨ï¼ä¹å¯ä»¥å¨æå¡ç«¯ä½¿ç¨ããã
ãã2) expdpåimpdpæ¯æå¡ç«¯å·¥å
·ï¼åªè½å¨Oracleæå¡ç«¯ä½¿ç¨ã
ãã3) impåªéç¨äºexp导åºæ件ï¼impdpåªéç¨äºexpdp导åºæ件ã
ãã
ããexpdp导åºæ°æ®ï¼
ãã1ã为è¾åºè·¯å¾å»ºç«ä¸ä¸ªæ°æ®åºçdirectory对象ã
ããããcreate or replace directory dumpdir as 'd:\';
ããããå¯ä»¥éè¿ï¼select * from dba_directories;æ¥çã
ãã2ãç»å°è¦è¿è¡æ°æ®å¯¼åºçç¨æ·ææ访é®ã
ããããgrant read,write on directory dumpdir to test_expdp;
ãã3ãå°æ°æ®å¯¼åº
ããããexpdp test_expdp/test_expdp directory=dumpdir dumpfile=test_expdp_bak.dmp logfile=test_expdp_bak.log schemas=test_expdp
ãããã注æï¼è¿å¥è¯å¨cmdçªå£ä¸è¿è¡ï¼å¹¶ä¸æåä¸è¦å åå·ï¼å¦åä¼æ示é误ãå 为è¿å¥è¯æ¯æä½ç³»ç»å½ä»¤èä¸æ¯SQLã
ããimpdp导å
¥æ°æ®ï¼
ãããã1ãç»å°è¦è¿è¡æ°æ®å¯¼å
¥çç¨æ·ææ访é®ã
ããããããgrant read,write on directory dumpdir to test_impdp;
ãããã2ãå°æ°æ®å¯¼å
¥
ããããããimpdp test_impdp/impdp directory=dumpdir dumpfile=test_expdp_bak.dmp remap_schema=test_expdp:test_impdp
ããæåï¼è¿äºå
容æ¯æå¨æ¥æ¾èµææ¶æ¾å°ï¼ç±äºæ¯è¾æä¹±ï¼ç°å¨ç¨ä½æ´çï¼æ¹ä¾¿ä»¥åæ¥é
ãåæ¶ï¼å¦æè½ç»éè¦ç人æä¾ä¸äºå¸®å©ï¼é£å°±å好ä¸è¿äºã
温馨提示:答案为网友推荐,仅供参考