oracleËéƬС½á
author£ºskate
time£º2010-05-31
ÎÒÃÇÔÚʹÓÃwindowsµÄʱºò£¬¶¼ÖªµÀÒª¶¨ÆÚÕûÀí´ÅÅÌËéƬ£¬ÒòΪ´ÅÅÌËéƬ»áÓ°ÏìÐÔÄÜ£¬¸ø¹ÜÀíÉÏ´øÀ´¶îÍâµÄ
¸ºµ£¡£ÄÇoracle¸üÊÇÈç´Ë£¬µ±Ëæ×ÅÊý¾ÝÔö¼Ó£¬oracle´¦Àíº£Á¿Êý¾Ý±¾Éí¾ÍÒѾºÜ·ÑÁ¦ÁË°¡£¬Èç¹ûÔÙÓдóÁ¿
µÄËéƬ£¬ÄǾÍÊÇÑ©ÉϼÓ˪°¡¡£ËùÒÔËéƬҪÒýÆðdbaµÄ¸ß¶ÈÖØÊÓ£¬¾¡Ôç·¢ÏÖ¾¡Ôç´¦Àí¡£
ËéƬÊÇÔõô²úÉúµÄÄØ£¿
¼òµ¥Àí½â¾ÍÊÇÓÉÓÚ¸üкÍɾ³ý²úÉúһЩËéСµÄ²»Äܱ»ÔÙ´ÎʹÓõĿռ䣬¸ù¾ÝÿÖÖ²»Í¬µÄËéƬËûÃǵIJúÉúÒ²ÊÇÓÐÇø±ðµÄ
block-levelµÄËéƬ£¬¶øblockÓÖ·ÖΪdata blockºÍindex block£¬ÔÚdata blockÖдæ·ÅµÄÊÇrowÊý¾Ý£¬ÔÚindex block
Öдæ·ÅµÄÊÇË÷Òý¼üÖµÊý¾Ý£¬ËùÒÔ°´ÉÏÃæËù˵£¬block-levelËéƬÓÐϸ·ÖΪrow-levelËéƬºÍIndex Leaf Block-levelËéƬ¡£
oracleµÄÿһ¸ö¶ÔÏó¶¼ÊÇ´æ´¢ÔÚsegmentÖУ¬¶øoracleµÄ×îС·ÖÅ䵥λÊÇextents£¨Çø£©£¬ÔÚÊý¾Ý¸üÐÂɾ³ýÖÐÒ²»á²úÉúËéƬ
ÕâÒ»¼¶±ðµÄËéƬ¾ÍÊÇsegmentËéƬ¡£segmentÓÖ´æÔÚdatafileÖУ¬¶øtablespaceÓÖÊÇ°üº¬datafileµÄÂß¼¸ÅÄî¡£ËùÒÔÕâÒ»²ã
ÊÇtablespace-levelËéƬ£»tablespaceÊÇÔÚdiskÉÏ´æ´¢£¬ËùÒÔÕâÒ»²ã¾ÍÊÇdisk-levelËéƬ¡£
¼òµ¥Í¼Ê¾ÈçÏÂ
disk-level fragmention
tablespace-level fragmentation
segment-level fragmentation
block-level fragmentation
row-level fragmentation
index leaf block-level fragmentation
˳±ãÌáÏÂoracle extents´æÔÚµÄÀíÓÉ
Ò»¸öextentsÊÇÓɶà¸öÏàÁ¬µÄblock×é³ÉµÄ£¬¶à¸öextents×ö³ÉÒ»¸ösegment£»extentÊÇoracleµÄ×îС·ÖÅ䵥λ
extentµÄÓŵ㣺
1. Ìá¸ß¿Õ¼ä·ÖÅ䣬ÊͷŵÄЧÂÊ£¬½µµÍ¹ÜÀíblockµÄ×ÊÔ´³É±¾
2. Ìá¸ßɨÃèµÄЧÂÊ£¬ÒòΪextentÊÇÓÉÏàÁ¬blocks×ö³ÉµÄÌØÐÔ£¬¿ÉÒÔÒ»´Î¶ÁÈ¡¸ü¶àµÄÄÚÈÝ£¬½ÏµÍio¶Áд´ÎÊý
extentµÄȱµã
ÈÝÒײúÉúËéƬ
ÈçºÎÈ·¶¨²úÉúÁËËéƬµÄÄØ£¿
Ò»¡£±í¿Õ¼äËéƬȷ¶¨²Î¿¼
ÓÉÓÚ×ÔÓÉ¿Õ¼äËéƬÊÇÓɼ¸²¿·Ö×é³É£¬È緶ΧÊýÁ¿¡¢×î´ó·¶Î§³ß´çµÈ£¬ÎÒÃÇ¿ÉÓà FSFI--Free Space Fragmentation Index
(×ÔÓÉ¿Õ¼äËéƬË÷Òý)ÖµÀ´Ö±¹ÛÌåÏÖ£º
¡¡¡¡FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents)))
¿ÉÒÔ¿´³ö£¬FSFI µÄ×î´ó¿ÉÄÜֵΪ 100 (Ò»¸öÀíÏëµÄµ¥Îļþ±í¿Õ¼ä)¡£Ëæ×Å·¶Î§µÄÔö¼Ó£¬ FSFI Öµ»ºÂýϽµ£¬¶øËæ×Å×î´ó·¶
Χ³ß´çµÄ¼õÉÙ£¬FSFI Öµ»áѸËÙϽµ¡£Í¨¹ýÈçÏÂÓï¾ä²éѯF
Ïà¹ØÎĵµ£º
ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y &nb ......
»Ø¹ö¶ÎÓÃÓÚ¶ÔÊý¾Ý¿âÐÞ¸Äʱ, ±£´æÔÓеÄÊý¾Ý, ÒÔ±ãÉÔºó¿ÉÒÔͨ¹ýʹÓÃROLLBACKÀ´»Ö¸´µ½ÐÞ¸ÄÇ°µÄÊý¾Ý; ÁíÍâ, »Ø¹ö¶Î¿ÉÒÔΪÊý¾Ý¿âÖеÄËùÓнø³ÌÌṩ¶ÁÒ»ÖÂÐÔ. Òò´Ë, »Ø¹ö¶ÎÉèÖõĺÏÀíÓë·ñ, Ö±½ÓÓ°Ïìµ½Êý¾Ý¿âµÄÐÔÄÜ.
»Ø¹ö¶ÎµÄά»¤¼°²éѯ
(1) ´´½¨»Ø¹ö¶Î
__CREATE ROLLBACK SEGMENT RB01
__TABLESPACE RBS1
__STORAGE (
____I ......
ת×Ô£ºhttp://ilonng.blog.sohu.com/130335741.html
oracleµÄnumberÀàÐÍÊÇÒ»ÖÖÈí¼þÊý¾ÝÀàÐÍ£¬ÔÚoracleÈí¼þ±¾ÉíÖÐʵÏÖ£¬ÎÒÃDz»ÄÜʹÓùÌÓÐÓ²¼þ²Ù×÷½«Á½¸önumberÀàÐÍÏà¼Ó£¬ÕâÒªÔÚÈí¼þÖÐÄ£Ä⣬number ÀàÐ͵ÄÄÚ²¿±àÂëÊÇ2¡£oracle´æ´¢Ò»¸öÊýµÄʱ£¬»á´æ´¢¾¡¿ÉÄÜÉÙµÄÄÚÈÝÀ´±íʾÕâ¸öÊý£¬Îª´ËËü»á´æ´¢ÓÐЧÊý×Ö¡¢ÓÃÓÚÖ¸¶ ......
1.´´½¨±í£º
a. ´´½¨xs±íÖмÆËã»úרҵѧÉúµÄ±¸·Ý
Create table xs_jsj as select * from xs where zym=’¼ÆËã»ú’;
b.ÍêÕûµÄÀý×Ó£º
¡¡¡¡¡¡¡¡¡¡ Create table test ......