Oracle»¹ÔÊý¾Ý¶Î³£ÓùÜÀí²Ù×÷
²ÎÊý
UNDO_MANAGEMENT = AUTO --¹ÜÀíģʽ,¿ÉΪAUTO»òMANUAL.Ö»ÄÜÔÚÆôʼ²ÎÊýÎļþÀïÃæÐÞ¸Ä
UNDO_TABLESPACE = undo --ÖÆ¶¨´æ´¢»¹ÔÊý¾ÝµÄ±í¿Õ¼ä,Òà¿ÉÓÃALTER SYSTEM SET undo_tablespace = 'abc'À´¸ü¸Ä
UNDO_RETENTION = 1800 --Ö¸¶¨Êý¾ÝÌá½»ºó»¹Ô¶Î¼ÌÐø±£´æ¶à¾ÃµÄʱ¼ä,ÃëÖÓ. Òà¿ÉÓÃALTER SYSTEM SET undo_retention = 900À´¸ü¸Ä
UNDO_SUPRESS_ERRORS = true --ÔÚ×Ô¶¯Ä£Ê½ÏÂÊÖ¶¯¹ÜÀí»¹Ô¶ÎÊÇÊÇ·ñ±¨´í,TRUEΪºöÂÔ´íÎó.²»»áÓиºÃæÓ°Ïì. Òà¿ÉÓÃALTER SESSION SET UNDO_SUPRESS_ERRORS = flaseÀ´±ä¸ü ´´½¨»¹Ô±í¿Õ¼ä
CREATE UNDO TABLESPACE abc_undo DATAFILE 'c:\abc_undo.dbf' SIZE 20M; ÆäËû±í¿Õ¼ä²Ù×÷ÓëÆäËû±í¿Õ¼äÏàͬ,ΪÁ˿ռ乻ÓÃ×îºÃ½«»¹Ô±í¿Õ¼äÉèΪ×Ô¶¯ÍØÕ¹. Çл»»¹Ô±í¿Õ¼ä
ALTER SYSTEM SET UNDO_TABLESPACE = 'abc_undo' ɾ³ý»¹Ô±í¿Õ¼ä,×¢Òâ²»ÄÜɾ³ýµ±Ç°»¹Ô±í¿Õ¼ä
DROP TABLESPACE abc_undo; ²é¿´µ±Ç°»¹Ô¶Î×´¿ö
SELECT name, value from v$parameter WHERE name LIKE '%undo%'; »ñÈ¡»¹ÔÊý¾ÝÐÅÏ¢
a.) »ñÈ¡»¹ÔÊý¾Ýͳ¼ÆÐÅÏ¢
SELECT TO_CHAR(begin_time, 'HH:MM:SS') begin_time, TO_CHAR(end_time, 'HH:MM:SS') end_time, undoblks, txncount, maxquerylen from v$undostat;
ÆäÖÐundoblksΪ¸Ãʱ¼ä¶ÎÄÚÏûºÄµÄ»¹ÔÊý¾Ý¿éÊýÁ¿,txncountΪ¸Ãʱ¼ä¶ÎÖÐÊÂÎñµÄ×ÜÊý, maxquerylenΪ¸Ãʱ¼ä¶ÎÖÐÖ´ÐÐ×µÄ²éѯ(ÃëÊý).
b.)»¹¿ÉÒÔʹÓÃÒÔϸ÷ÊÓͼ»ñÈ¡ÓÐÓÃÐÅÏ¢
dba_tablespaces, dba_data_files, dba_rollback_segs, v$rollname, v$rollstat, v$session, v$transaction
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
·½°¸1 ÊÊÓÃÓÚoracle9iÒÔÉÏ£¡
select * from
(select row_number() over(order by sendid desc) rn,m.* from xxt_msgreceive m )
where rn <1010 and rn>=1000
·½°¸2
SELECT * from (SELECT A.*, ROWNUM RN from (SELECT * from xxt_msg where sendstatus=1 order by msgid desc) A WHERE ROWNUM < ......
OracleÊý¾Ý¿âÔÚʹÓùý³ÌÖУ¬Ëæ×ÅÊý¾ÝµÄÔö¼ÓÊý¾Ý¿âÎļþÒ²Öð½¥Ôö¼Ó£¬ÔÚ´ïµ½Ò»¶¨´óСºóÓпÉÄÜ»áÔì³ÉÓ²Å̿ռ䲻×ã;ÄÇôÕâʱÎÒÃÇ¿ÉÒÔ°ÑÊý¾Ý¿âÎļþÒÆ¶¯µ½ÁíÒ»¸ö´óµÄÓ²ÅÌ·ÖÇøÖС£ÏÂÃæÎÒ¾ÍÒÔOracle for Windows°æ±¾ÖаÑCÅ̵ÄÊý¾Ý¿âÎļþÒÆ¶¯µ½DÅÌΪÀý½éÉÜOracleÊý¾Ý¿âÎļþÒÆ¶¯µÄ·½·¨ºÍ²½Öè¡£
¡¡¡¡1.ÔÚsqlplusÖÐÁ¬½Óµ½ÒªÒƶ¯ÎļþµÄOr ......
Óï·¨£º
select *
from ±íÃû
where Ìõ¼þ1
start with Ìõ¼þ2
connect by prior µ±Ç°±í×Ö¶Î=¼¶Áª±í×Ö¶Î
start withÓëconnect by priorÓï¾äÍê³ÉµÝ¹é¼Ç¼£¬ÐγÉÒ»¿ÃÊ÷Ðνṹ£¬Í¨³£¿ÉÒÔÔÚ¾ßÓвã´Î½á¹¹µÄ±íÖÐʹÓá£
start with±íʾ¿ªÊ¼µÄ¼Ç¼
connect by prior Ö¸¶¨Ó뵱ǰ¼Ç¼¹ØÁªÊ±µÄ×ֶιØÏµ
´úÂ룺
--´´½¨²¿Ãű ......
--Ãû´Ê˵Ã÷£ºÔ´——±»Í¬²½µÄÊý¾Ý¿â
Ä¿µÄ——Ҫͬ²½µ½µÄÊý¾Ý¿â
ǰ6²½±ØÐëÖ´ÐÐ,µÚ6ÒÔºóÊÇһЩ¸¨ÖúÐÅÏ¢.
--1¡¢ÔÚÄ¿µÄÊý¾Ý¿âÉÏ£¬´´½¨dblink
drop public database link dblink_orc92_182;
Create public DATABASE LINK dbl ......