Oracle¶Î¸ßˮλ(HWM, high water mark)ÎÊÌâ
Oracle¶Ô±í×öÈ«±íɨÃèµÄʱºò
£¬»áɨÃèÍêHWMÒÔÏÂ
µÄÊý¾Ý¿é¡£Èç¹ûij¸ö±ídelete(delete²Ù×÷²»»á½µµÍ¸ßˮλ)ÁË´óÁ¿Êý¾Ý£¬ÄÇôÕâʱ¶Ô±í×öÈ«±íɨÃè¾Í»á×öºÜ¶àÎÞÓù¦£¬É¨ÃèÁËÒ»´ó¶ÑÊý¾Ý¿é£¬×îºó·¢ÏÖ¿éÀïÃæ¾ÓȻûÓÐÊý¾Ý¡£
ͨ³££¬ÔÚ¶Ô±í×öÁË´óÅúÁ¿delete²Ù×÷Ö®ºó£¬¾ÍÓ¦¸ÃÂíÉϽµµÍ±íµÄ¸ßˮ룬¿ÉÒÔʹÓÃshrink ÃüÁî»òÕßalter table table_name move½µµÍ±íµÄ¸ßˮλ¡£ÔÚ½µµÍ±íµÄ¸ßˮλ֮ºó£¬±íÉÏÃæµÄË÷Òý»áʧЧ£¬ÒòΪ±íµÄrowid¸ü¸ÄÁË£¬Õâ¸öʱºòÐèÒªrebuildË÷Òý¡£
ÈçºÎÇó³ö¶ÎµÄ¸ßˮλ£¿ÆäʵºÜ¼òµ¥£¬Ê×ÏȶԱíÊÕ¼¯Í³¼ÆÐÅÏ¢£¬È»ºó²éѯDBA_TABLESµÄblocks£¬ÒÔ¼°empty_blocks×ֶΣ¬blocks±íʾÒѾÓÃÁ˶àÉÙ¸öblocks,empty_blocks±íʾ´ÓÀ´Ã»ÓÐʹÓùýµÄblocks¡£ÄÇôblocks¾Í±íʾ¶ÎµÄ¸ßˮλ¡£
¿ÉÒÔʹÓÃÏÂÃæµÄÓï¾ä²é¿´±íµ½µ×ÓÃÁ˶àÉÙ¸öblocks
select count( distinct dbms_rowid.rowid_block_number(rowid)) from table_name;
È»ºóÔÙ¶Ô±Èdba_tables±íÖеÄblocksÁУ¬Èç¹ûÇó³öµÄblocksÊýÓëdba_tablesÏà²îÔÚ10×óÓÒ£¬ÄÇô±íʾÕâ¸ö±í²»ÐèÒªshrink,ÓÃÉÏÃæµÄ½Å±¾Çó³öµÄblocksÊýû¼ÆËã¶ÎÍ·£¬Î»Í¼¹ÜÀí¿é¡£Èç¹ûÏà²îºÜ´ó£¬ÄÇô±íʾÕâ¸ö±íÐèÒªshrinkÁË£¬²»¹ýÕâÑù×ö±È½ÏÂé·³£¬²»ÊÇÂð£¿
Æäʵ»¹¿ÉÒÔ¼à¿Ø±íµÄDML²Ù×÷£¬¸ù¾Ý¼à¿ØµÄ½á¹û£¬ÎÒÃǾͿÉÒÔÅжÏÄÄЩ±íÐèÒª½µµÍ¸ßˮλ¡£ÔÚOracle10gÖÐDBA_TAB_MODIFICATIONSÕâ¸öÊÓͼ¼Ç¼ÁË×ÔÉÏ´ÎÊÕ¼¯Í³¼ÆÐÅÏ¢ÒÔÀ´±íµÄDML²Ù×÷ÐÅÏ¢¡£µ±ÎÒÃÇÔٴζԱíÊÕ¼¯Í³¼ÆÐÅÏ¢£¬¸ÃÊÓͼµÄ¼Ç¼¾Í»á±»Çå¿Õ¡£
¶ÔÓÚ·Ç·ÖÇø±í£¬¿ÉÒÔʹÓÃÏÂÃæ½Å±¾³õ²½¼ì²âÄÄЩ±íÐèÒª½µµÍ¸ßˮλ
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;
col table format a35
select a.owner || '.' || a.table_name "Table",a.num_rows,a.avg_row_len,b.inserts,b.deletes,a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0) total_rows,
round(a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0))/1024/1024,4) "Calculate_Sizle(Mb)",c.bytes/1024/1024 "Segment_Size(Mb)"
from dba_tables a left join all_tab_modifications b
on a.owner=b.table_owner and a.table_name=b.table_name inner join dba_segments c&n
Ïà¹ØÎĵµ£º
author£ºskate
time£º2010-05-25
OracleÊý¾Ý¿â¹ÜÀí½Å±¾ËµÃ÷
Oracle ÖеÄÊý¾Ý×ÖµäÊÇÊý¾Ý¿âÖеķdz£ÖØÒªµÄÒ»²¿·Ö£¬ÌṩÁËÏà¹ØÊý¾Ý¿âµÄÐÅÏ¢£¬ËüÊÇËùÓÐOracleÓû§£¨´ÓÓ¦ÓõÄ×îÖÕÓû§¡¢Ó¦ÓõÄÉè¼Æ¿ª·¢Õßµ½Êý¾Ý¿â¹ÜÀíÔ±£©µÄ·Ç³£ÖØÒªµÄÐÅÏ¢À´Ô´¡£Êý¾Ý×ÖµäµÄ´´½¨£¬ÊÇÔÚOracleÊý¾Ý¿â´´½¨Íê±Ïºó£¬ÔËÐÐһЩÏà¹ØµÄÊý¾Ý¿â¹ÜÀí½Å±¾À´Íê ......
ÓÃjavaµ÷ÓÃoracle´æ´¢¹ý³Ì×ܽá
1¡¢Ê²Ã´ÊÇ´æ´¢¹ý³Ì¡£´æ´¢¹ý³ÌÊÇÊý¾Ý¿â·þÎñÆ÷¶ËµÄÒ»¶Î³ÌÐò£¬ËüÓÐÁ½ÖÖÀàÐÍ¡£Ò»ÖÖÀàËÆÓÚSELECT²éѯ£¬ÓÃÓÚ¼ìË÷Êý¾Ý£¬¼ìË÷µ½µÄÊý¾ÝÄܹ»ÒÔÊý¾Ý¼¯µÄÐÎʽ·µ»Ø¸ø¿Í»§¡£ÁíÒ»ÖÖÀàËÆÓÚINSERT»òDELETE²éѯ£¬Ëü²»·µ»ØÊý¾Ý£¬Ö»ÊÇÖ´ÐÐÒ»¸ö¶¯×÷¡£ÓеķþÎñÆ÷ÔÊÐíͬһ¸ö´æ´¢¹ý³Ì¼È¿ÉÒÔ·µ»ØÊý¾ÝÓÖ¿ÉÒÔÖ´Ðж¯×÷¡£
......
Oracle 9iÐÔÄܵ÷Õû
/* *
×÷Õß:Å·³¯¾´
QQ:35712069
ÊÖ»ú:13873195792
Çë×ªÔØÕß²»Òª¸ü¸ÄÔ°æÄÚÈÝ
*/
1¡¢ÉèÖúÏÊʵÄSGA
¡¡¡¡³£³£ÓÐÈ˱§Ô¹·þÎñÆ÷Ó²¼þºÜºÃ£¬µ«ÊÇOracle¾ÍÊǺÜÂý¡£ºÜ¿ÉÄÜÊÇÄÚ´æ·ÖÅä²»ºÏÀíÔì³ÉµÄ¡£
¡¡¡¡(1)¼ÙÉèÄÚ´æÓÐ512M£¬Õâͨ³£ÊÇСÐÍÓ¦Ó᣽¨ÒéOracleµÄSGA´óÔ¼240M£¬ÆäÖУº¹²Ïí³Ø£¨SHARED_POOL ......
----±¾Óû§ËùÓµÓеÄϵͳȨÏÞ:
select * from user_sys_privs;
---±¾Óû§¶ÁÈ¡ÆäËûÓû§¶ÔÏóµÄȨÏÞ:
¡¡select * from user_tab_privs;
-----Ìí¼ÓȨÏÞ
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE&nbs ......
¿ÉÇ¨ÒÆ±í¿Õ¼ätransport tablespace
¿ÉÇ¨ÒÆ±í¿Õ¼ä
ʹÓÿÉÇ¨ÒÆ±í¿Õ¼ä(Transportable Tablespaces)µÄÌØÐÔÔÚÊý¾Ý¿âÖ®¼äÒÆ¶¯´óÁ¿Êý¾Ý£¬ÐÔÄܱÈexport/importºÍunload/loadÒª¿ìºÜ¶à£¬ÒòΪËüÇ¨ÒÆ±í¿Õ¼äÖ»ÐèÒª¸´ÖÆÊý¾ÝÎļþºÍ²åÈë±í¿Õ¼äÔªÊý¾Ýµ½Ä¿±êÊý¾Ý¿âÖС£
Ç¨ÒÆ±í¿Õ¼ä¶ÔÒÔÏÂÓ¦ÓÃÌØ±ðÓÐÓãº
·Ö½×¶Î½«OLTPµÄÊý¾ÝÒÆÈëÊý¾Ý²Ö¿â
¸ ......