ããmoveä¸ä¸ªè¡¨å°å¦å¤ä¸ä¸ªè¡¨ç©ºé´æ¶,ç´¢å¼ä¸ä¼è·çä¸èµ·moveï¼èä¸ä¼å¤±æãï¼LOBç±»åä¾å¤ï¼è¡¨moveï¼æ们å为ï¼
ãã*æ®é表move
ãã*ååºè¡¨move
ãã*LONG,LOB大å段类åmoveæ¥è¿è¡æµè¯å说æã
ããç´¢å¼çmoveï¼æ们éè¿rebuildæ¥å®ç°
ããä¸ï¼moveæ®é表ãç´¢å¼åºæ¬è¯æ³ï¼
ããalter table tab_name move tablespace tbs_name;moveè¿çæ®é表ï¼å¨ä¸ç¨å°å¤±æçç´¢å¼çæä½è¯å¥ä¸ï¼è¯å¥æ§è¡æ£å¸¸ï¼ä½å¦ææä½çè¯å¥ç¨å°äºç´¢å¼ï¼ä¸»é®å½åå¯ä¸ç´¢å¼ï¼ï¼åæ¤æ¶æ¥åç¨å°çç´¢å¼å¤±æï¼è¯å¥æ§è¡å¤±è´¥ï¼å
¶ä»å¦å¤é®ï¼é空约æï¼ç¼ºçå¼çä¸ä¼å¤±æã
ããæ们éè¦éæ°å建主é®æç´¢å¼ï¼åºæ¬è¯æ³ä¸ºï¼
ãã1
ãã2
ããalter index index_name rebuild;
ããalter index pk_name rebuild;
ããå¦ææ们éè¦moveç´¢å¼ï¼å使ç¨rebuildè¯æ³ï¼
ãã1
ãã2
ããalter index index_name rebuild tablespace tbs_name;alter index pk_name rebuild tablespace tbs_name;æ示ï¼æ¥è¯¢è¡¨æå
·æçç´¢å¼ï¼å¯ä»¥ä½¿ç¨user_indexesè§å¾ï¼ç´¢å¼å主é®é½å¨è¿ä¸ªè§å¾éå¯æ¾å°ï¼ã
ããäºï¼moveååºè¡¨åç´¢å¼åæ®é表ä¸æ ·ï¼ç´¢å¼ä¼å¤±æï¼åºå«çä»
ä»
æ¯è¯æ³èå·²ã
ããååºåºæ¬è¯æ³ï¼ç¹å«æé注æï¼å¦ææ¯å级ååºï¼å使ç¨å
³é®åPARTITIONï¼å¦ææ¯å¤çº§ååºï¼å使ç¨SUBPARTITIONæ¿ä»£PARTITIONã
ããå¦æååºæååºç´¢å¼æ¯è¾å¤§ï¼å¯ä»¥ä½¿ç¨å¹¶è¡moveærebuildï¼PARALLEL (DEGREE 2);å¦ï¼
ãã1
ãã2
ãã3
ããALTER TABLE PART_ALARMTEXTDATA move SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);ALTER INDEX GLOBAL_ALARMTEXTDATA REBUILD tablespace users PARALLEL (DEGREE 2);ALTER INDEX LOCAL_ALARMTEXTDATA REBUILD SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);移å¨è¡¨çæ个ååºï¼
ãã1
ããALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;é建å
¨å±ç´¢å¼ï¼
ãã1
ãã2
ããALTER INDEX global_index REBUILD;æ
ããALTER INDEX global_index REBUILD tablespace tbs_name;注: ååºæä½æ¶å¯ä»¥å¸¦ä¸with update global indexesé项æ´æ°å
¨å±ç´¢å¼é建å±é¨ç´¢å¼ï¼
ãã1
ããALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;1
ããæ
ãã1
ããALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name;æ示ï¼
ããUSER_PART_TABLES
ããUSER_IND_PARTITIONS
ããUSER_IND_SUBPARTITIONS
ããUSER_LOB_PARTITIONS
ããUSER_LOB_SUBPARTITIONS
ããUSER_PART_INDEXES
ããUSER_PART_LOBSå¯æ¥è¯¢ååºç¸å
³å
容ï¼åæ¶ï¼ååºå¯¹è±¡ï¼ä¹æ¯segmentï¼æ以ä¹å¯å¨dba_segmentséæ¥çå°ã
ããä¸ï¼move LONG,LOBç±»åï¼æ®è¯´DBMS_REDEFINITIONå
å¯ä»¥æä¾ä¸äºæ¹ä¾¿ï¼æ²¡ç¨è¿ãï¼I:LONGç±»å
ããLONGç±»åä¸è½éè¿MOVEæ¥ä¼ è¾ç¹å«æ示ï¼å°½éä¸è¦ç¨LONGç±»åï¼ç¹é¾ç®¡çãåèï¼http//
www.anysql.net/2005/12/long_vs_lob.htmlLONGä¸è½ä½¿ç¨insert into ⦠select â¦ç带selectç模å¼ãå¦1
ãã2
ãã3
ãã4
ãã5
ãã6
ãã7
ãã8
ãã9
ãã10
ãã11
ãã12
ãã13
ãã14
ããcreate table t123 (id int,en long);å
ããinsert into t123(id,en) select * from t123;æ¥åé误ï¼å¯ä»¥ç¨pl/sqlæ¥å¸®å©è§£å³ï¼å¦ï¼
ããdeclare
ããcursor cur_t123 is select * from t123;
ããuse_t123 cur_t123%rowtype;
ããbegin
ããopen cur_t123;
ããloop
ããfetch cur_t123 into use_t123;
ããexit when cur_t123%notfound;
ããinsert into t123(id,en) values (use_t123.id,use_t123.en);end loop;
ããclose cur_t123;
ããend;
ãã对æLONGç±»åå段ç表ç转移ï¼å¯ä»¥ä½¿ç¨ï¼
ããcreateæ°è¡¨çæ¹æ³ã
ãã* createä¸ä¸ªæ°ç表ï¼åå¨å¨éè¦è½¬ç§»ç表空é´ã
ãã* å建æ°çç´¢å¼ï¼ä½¿ç¨tablespace åå¥æå®æ°ç表空é´ï¼ã
ãã* ææ°æ®è½¬ç§»è¿æ¥
ããæ¹æ³ä¸ï¼ç¨COPYçæ¹æ³ï¼
ãã1
ããcopy from bigboar/bigboar@bigboar_sid insert t123(id,en) using select id,en from t123;æ¹æ³äºï¼PL/SQLï¼å¦ä¸ï¼
ããæ¹æ³ä¸ï¼ç´æ¥å°±æLONG转æ¢æCLOBç±»å
ãã1
ãã2
ããcreate table t321(id int,en clob) tablespace users;insert into t321(id,en) select id,to_lob(en) from t123;æ¹æ³åï¼exp/imp
ãã1
ãã2
ããexp bigboar/bigboar file=a.dat tables=t123imp bigboar/bigboar file=a.dat full=y IGNORE =y* dropææ§è¡¨ã
ãã* rename æ°è¡¨ä¸ºæ§è¡¨è¡¨åã
ããII:LOBç±»åå¨å»ºç«å«ælobå段ç表æ¶ï¼oracleä¼èªå¨ä¸ºlobå段建ç«ä¸¤ä¸ªåç¬çsegment,ä¸ä¸ªç¨æ¥åæ¾æ°æ®ï¼segment_type=LOBSEGMENTï¼ï¼å¦ä¸ä¸ªç¨æ¥åæ¾ç´¢å¼ï¼segment_type=LOBINDEXï¼ãé»è®¤å®ä»¬ä¼åå¨å¨å表ä¸èµ·ç表空é´ãæ们对表MOVEæ¶ï¼LOGç±»åå段å该å段çç´¢å¼ä¸ä¼è·çMOVEï¼å¿
é¡»è¦åç¬æ¥è¿è¡MOVEï¼è¯æ³å¦ä¸å¦ï¼
ãã1
ãã2
ããalter table t321 move tablespace users;
ããalter table t321 move lob(en) store as (tablespace users);