ä¸. ç¢çæ¯å¦ä½äº§çç
å½å建ä¸ä¸ªæ°æ®åºå®ä¾æ¶ï¼ä¼åæ称为表空é´ï¼tablespaceï¼çå¤ä¸ªé»è¾æ®µï¼segmentï¼ï¼å¦ç³»ç»ï¼systemï¼è¡¨ç©ºé´,临æ¶ï¼temporaryï¼è¡¨ç©ºé´çãä¸ä¸ªè¡¨ç©ºé´å¯ä»¥å
å«å¤ä¸ªæ°æ®èå´ï¼extentï¼åä¸ä¸ªæå¤ä¸ªèªç±èå´åï¼å³èªç±ç©ºé´ï¼free spaceï¼ã
表空é´ã段ãèå´ãèªç±ç©ºé´çé»è¾å
³ç³»å¦ä¸ï¼
å½è¡¨ç©ºé´ä¸çæä¸ä¸ªæ®µæ¶ï¼å°ä»è¡¨ç©ºé´ææèªç±ç©ºé´ä¸ä¸ºè¿ä¸ªæ®µçåå§èå´åé
空é´ãå¨è¿äºåå§èå´å
满æ°æ®æ¶ï¼æ®µä¼è¯·æ±å¢å å¦ä¸ä¸ªèå´ãè¿æ ·çæ©å±è¿ç¨ä¼ä¸ç´ç»§ç»ä¸å»ï¼ç´å°è¾¾å°æ大çèå´å¼ï¼æè
å¨è¡¨ç©ºé´ä¸å·²ç»æ²¡æèªç±ç©ºé´ç¨äºä¸ä¸ä¸ªèå´ã
æçæ³çç¶æå°±æ¯ä¸ä¸ªæ®µçæ°æ®å¯è¢«åå¨åä¸çä¸ä¸ªèå´ä¸ãè¿æ ·ï¼ææçæ°æ®åå¨æ¶é è¿æ®µå
å
¶å®æ°æ®ï¼å¹¶ä¸å¯»æ¾æ°æ®å¯å°ç¨ä¸äºæéãä½æ¯ä¸ä¸ªæ®µå
å«å¤ä¸ªèå´çæ
åµæ¯å¤§éåå¨çï¼æ²¡æä»»ä½æªæ½å¯ä»¥ä¿è¯è¿äºèå´æ¯ç¸é»åå¨çã å½è¦æ»¡è¶³ä¸ä¸ªç©ºé´è¦æ±æ¶ï¼æ°æ®åºä¸åå并ç¸é»çèªç±èå´ï¼é¤éå«æ éæ©ï¼ï¼ èæ¯å¯»æ¾è¡¨ç©ºé´ä¸æ大çèªç±èå´æ¥ä½¿ç¨ãè¿æ ·å°éæ¸å½¢æè¶æ¥è¶å¤ç离æ£çãåéçãè¾å°çèªç±ç©ºé´ï¼å³ç¢çã
表空é´(tableSpace) 段(segment) çåº(extent) å(block) å
³ç³»
http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4962476.aspxäº. ç¢ç对系ç»çå½±å
2.1 导è´ç³»ç»æ§è½åå¼±
å¦ä¸æè¿°ï¼å½è¦æ»¡è¶³ä¸ä¸ªç©ºé´è¦æ±æ¶ï¼æ°æ®åºå°é¦å
æ¥æ¾å½åæ大çèªç±èå´ï¼è"æ大"èªç±èå´éæ¸åå°ï¼è¦æ¾å°ä¸ä¸ªè¶³å¤å¤§çèªç±èå´å·²åå¾è¶æ¥è¶å°é¾ï¼ä»è导è´è¡¨ç©ºé´ä¸çé度éç¢ï¼ä½¿æ°æ®åºç空é´åé
æåè¿ç¦»çæ³ç¶æï¼
2.2 浪费大éç表空é´
é¨åextentæ æ³å¾ä»¥èªå¨å并ï¼æµªè´¹äºå¤§éç表空é´ã
ä¸. èªç±èå´çç¢ç计ç®
ç±äºèªç±ç©ºé´ç¢çæ¯ç±å é¨åç»æï¼å¦èå´æ°éãæ大èå´å°ºå¯¸çï¼æ们å¯ç¨FSFIï¼free space fragmentation index:èªç±ç©ºé´ç¢çç´¢å¼ï¼å¼æ¥ç´è§ä½ç°ï¼
FSFI=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))
å¯ä»¥çåºï¼FSFIçæ大å¯è½å¼ä¸º100ï¼ä¸ä¸ªçæ³çåæ件表空é´ï¼ãéçèå´çå¢å ï¼FSFIå¼ç¼æ
¢ä¸éï¼èéçæ大èå´å°ºå¯¸çåå°ï¼FSFIå¼ä¼è¿
éä¸éã
å¯ä»¥ä½¿ç¨å¦ä¸SQLå¯ä»¥ç¨æ¥è®¡ç®FSFIå¼ï¼
/* Formatted on 2011/01/27 14:12:43 (QP5 v5.115.810.9015) */
SELECT tablespace_name,
SQRT (MAX (blocks) / SUM (blocks))
* (100 / SQRT (SQRT (COUNT (blocks))))
FSFI
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY 1;
æ¯å¦ï¼å¨ææ°æ®åºè¿è¡ä¸é¢SQL,å¾å°ä»¥ä¸FSFIå¼ï¼
TABLESPACE_NAME FSFI
------------------------------ ----------
QS_OA 54.3794714
RBS 59.4603558
SYSAUX 84.0138802
SYSTEM 70.5946502
UNDOTBS1 20.0110492
USERS 100
XEZF 6.52482383 --ç¢çè¾å¤
XEZF_INDEX 32.1055286
8 rows selected.
å. ç¢çæ´ç
4.1 çåºï¼extentï¼çç¢çæ´ç
使ç¨å½ä»¤ï¼ alter tablespace temp coalesce;
/* Formatted on 2011/01/27 14:23:46 (QP5 v5.115.810.9015) */
SELECT 'alter tablespace ' || TABLESPACE_NAME || ' coalesce ;'
FROM DBA_FREE_SPACE_COALESCED
WHERE PERCENT_EXTENTS_COALESCED < 100 OR PERCENT_BLOCKS_COALESCED < 100;
4.2 段ï¼segmentï¼çç¢çæ´ç
段ç±extentç»æãå¨æäºæ
åµä¸ï¼æå¿
è¦å¯¹æ®µçç¢çè¿è¡æ´çãè¦æ¥ç段çæå
³ä¿¡æ¯ï¼å¯æ¥çæ°æ®åå
¸dba_segmentsï¼segmentçä¿¡æ¯å¯æ¥çæ°æ®åå
¸dba_extentsã
段å¯ä»¥æ表段ï¼ç´¢å¼æ®µçã æ¯ç§æ®µé½å¯ä»¥ç¢ççæ´çã
ï¼1ï¼è¡¨æ®µç¢çæ´ç
å¦æ段çç¢çè¿å¤ï¼ å°å
¶æ°æ®å缩å°ä¸ä¸ªèå´çæç®åæ¹æ³ä¾¿æ¯ç¨æ£ç¡®çåå¨åæ°å°è¿ä¸ªæ®µé建ï¼ç¶åå°æ§è¡¨ä¸çæ°æ®æå
¥å°æ°è¡¨ï¼åæ¶å é¤æ§è¡¨ãè¿ä¸ªè¿ç¨å¯ä»¥ç¨import/exportå·¥å
·æ¥å®æã
exportå½ä»¤æä¸ä¸ªï¼å缩ï¼æ å¿ï¼è¿ä¸ªæ å¿å¨è¯»è¡¨æ¶ä¼å¼åexportç¡®å®è¯¥è¡¨æåé
çç©ç空é´éï¼å®ä¼åè¾åºè½¬å¨æ件åå
¥ä¸ä¸ªæ°çåå§ååå¨åæ°--çäºå
¨é¨æåé
空é´ãè¥è¿ä¸ªè¡¨å
³éï¼ å使ç¨importå½ä»¤éæ°çæãè¿æ ·ï¼å®çæ°æ®ä¼æ¾å
¥ä¸ä¸ªæ°çãè¾å¤§çåå§æ®µä¸ãä¾å¦ï¼
exp user/password file=exp.dmp compress=y
grants=y indexes=y tables=(table1,table2);
导åºæååï¼å¨åºä¸å é¤è¿ä¸¤å¼ 表ï¼å¨å¯¼å
¥æ°æ®ï¼
imp user/password file=exp.dmp commit=y buffer=64000 tables=(table1,table2);
é¤äºç¨exp/impï¼è¿å¯ä»¥ä½¿ç¨shrink table æè
alter table tbname move æ¥å®ç°ã
å
·ä½åèï¼
Oracle 10g Shrink Table 详解
http://blog.csdn.net/tianlesoftware/archive/2009/11/03/4764254.aspxä¸ä¸ªå°èæ¬ï¼
--1.æ´ç空é´ç¢çï¼
tmp_val VARCHAR2 (500);
BEGIN
FOR REC IN (SELECT TABLE_NAME FROM USER_TABLES )
LOOP
tmp_val:='ALTER TABLE '||user.TABLE_NAME ||' MOVE';
BEGIN
EXECUTE IMMEDIATE tmp_val;
DBMS_OUTPUT.put_line (tmp_val);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error: ' || tmp_val || '!');
END;
END LOOP;
ï¼2ï¼. ç´¢å¼æ®µç¢çæ´ç
æç´¢å¼é建(ç¢çæ´çåï¼å¾å¤ç´¢å¼ä¼åæUNUSABLEç¶æï¼å¿
é¡»é建åï¼è®©å®åævoidç¶æ)
FOR REC IN (SELECT INDEX_NAME FROM USER_INDEXES ) --WHERE STATUS='UNUSABLE'
LOOP
tmp_val:='ALTER INDEX '||REC.INDEX_NAME ||' REBUILD';
BEGIN
EXECUTE IMMEDIATE tmp_val;
DBMS_OUTPUT.put_line (tmp_val);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error: ' || tmp_val || '!');
END;
END LOOP;
END;