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
Ïà¹ØÎĵµ£º
Q£ºÎҵıíÖÐÓÐÒ»¸ö×Ö¶ÎÊÇvarchar2(4000) £¬ÆäÖд洢Á˲»ÉÙÖÐÎÄ£¬ÏÖÔÚÓÉÓÚÅÅ°æÐèÒª£¬ÎÒÏëÕÒ³öº¬Óлسµ·ûµÄ¼Ç¼ɾ³ý»Ø³µ·û£¬ÇëÎʸßÊÖSQLÓï·¨Ôõôд£¿
A£ºOracleÖлسµ·ûÊÇchr(10),¿ÉÒÔʹÓú¯Êýreplace(×Ö¶Î,chr(10),'')
È磺 update x set a=replace(a,chr(10),'')¡£
º¯Êý£º
1.CHR
¸ø³öÕûÊ ......
CmdÃüÁîÐÐÊäÈëlsnrctl½øÈë¼àÌýģʽ£¬Í¨¹ýset passwordÉèÖÃÃÜÂë¡£
ͨ¹ý change_password ÐÞ¸ÄÃÜÂë¡£
LSNRCTL> help
ÒÔϲÙ×÷¿ÉÓÃ
ÐǺŠ(*) ±íʾÐ޸ķû»òÀ©Õ¹ÃüÁ
start stop &nbs ......
½ñÌìÅöµ½Ò»¸öÎÊÌ⣬ͨ¹ýÒ»¸öSQLÓï¾ä²éѯʱ£¬³öÈçÏÂÎÊÌ⣺
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
ÎÊÌâ³öÏÖÔÚͨ¹ýWMSYS. ......
±¾ÎÄÀ´×ÔCSDN²©¿Í£¬×ªÔØÇë±êÃ÷³ö´¦£ºhttp://blog.csdn.net/cosio/archive/2009/03/11/3978747.aspx
ÓÐÁ½ÖÖº¬ÒåµÄ±í´óС¡£Ò»ÖÖÊÇ·ÖÅä¸øÒ»¸ö±íµÄÎïÀí¿Õ¼äÊýÁ¿£¬¶ø²»¹Ü¿Õ¼äÊÇ·ñ±»Ê¹Ó᣿ÉÒÔÕâÑù²éѯ»ñµÃ×Ö½ÚÊý£º
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
»òÕß
Sel ......
select distinct id
from table t
where rownum < 10
order by t.id desc;
ÉÏÊöÓï¾äµÄ¹ýÂËÌõ¼þÖ´ÐÐ˳Ðò ÏÈwhere --->order by --->distinct
Èç¹ûÓÐgroup byµÄ»° group by ÔÚorder byÇ°ÃæµÄ ......