oracle ËøÎÊÌâµÄ½â¾ö
¿ÉÒÔÓÃSpotlightÈí¼þ¶ÔÊý¾Ý¿âµÄÔËÐÐ״̬½øÐÐ¼à¿Ø¡£
µ±³öÏÖsessionËøÊ±£¬ÎÒÃÇÒª¼°Ê±½øÐд¦Àí.
1. ²é¿´ÄÄЩsessionËø:
SQLÓï¾ä£ºselect 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);
SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '132,731';
alter system kill session '275,15205';
alter system kill session '308,206';
alter system kill session '407,3510';
2. ²é¿´sessionËø.
sqlÓï¾ä£ºselect s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;
SQL> select s.sid,q.sql_text from v$sqltext q, v$session s where q.address = s.sql_address and s.sid in (select sid from v$lock where block = 1) order by piece;
SID SQL_TEXT
---------- ----------------------------------------------------------------
77 UPDATE PROFILE_USER SET ID=1,COMPANY_ID=2,CUSTOMER_ID=3,NAMED
77 _INSURED_ID=4,LOGIN=5,ROLE_ID=6,PASSWORD=7,EMAIL=8,TIME_ZON
77 E=9 WHERE PROFILE_USER.ID=:34
3 rows selected.
3. killËøµÄ½ø³Ì.
SQLÓï¾ä£ºalter system kill session '77,22198';
SQL> alter system kill session '391,48398';
System altered.
4. ²é¿´ËËøÁËË¡£
select s1.username || [email='@']'@'[/email] || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || [email='@']'@'[/email] || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
&nb
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
Êý¾Ý¿âÁ¬½ÓÊÇÒ»ÖֹؼüµÄÓÐÏ޵ݺ¹óµÄ×ÊÔ´£¬ÕâÒ»µãÔÚ¶àÓû§µÄÍøÒ³Ó¦ÓóÌÐòÖÐÌåÏÖµÃÓÈΪͻ³ö¡£¶ÔÊý¾Ý¿âÁ¬½ÓµÄ¹ÜÀíÄÜÏÔÖøÓ°Ïìµ½Õû¸öÓ¦ÓóÌÐòµÄÉìËõÐԺͽ¡×³ÐÔ£¬Ó°Ïìµ½³ÌÐòµÄÐÔÄÜÖ¸±ê¡£Êý¾Ý¿âÁ¬½Ó³ØÕýÊÇÕë¶ÔÕâ¸öÎÊÌâÌá³öÀ´µÄ¡£
Êý¾Ý¿âÁ¬½Ó³Ø¸ºÔð·ÖÅä¡¢¹ÜÀíºÍÊÍ·ÅÊý¾Ý¿âÁ¬½Ó£¬ËüÔÊÐíÓ¦ÓóÌÐòÖØ¸´Ê¹ÓÃÒ»¸öÏÖÓеÄÊý¾Ý¿ ......
Ò»£¬PL/SQL¿éµÄ½á¹¹ºÍ×é³ÉÔªËØ
PL/SQL³ÌÐòÓÉÉùÃ÷²¿·Ö£¬Ö´Ðв¿·Ö£¬Òì³£´¦Àí²¿·ÖÈý¸ö²¿·Ö×é³É¡£½á¹¹ÈçÏ£º
DECLARE
/*ÉùÃ÷²¿·Ö£ºÔÚ´ËÉùÃ÷PL/SQL±äÁ¿£¬ÀàÐͼ°Óα꣬ÒÔ¼°¾Ö²¿µÄ´æ´¢¹ý³ÌºÍº¯Êý*/
BEGIN
/*Ö´Ðв¿·Ö£º¹ý³Ì¼°sqlÓï¾ä£¬³ÌÐòÖ÷Òª²¿·Ö£¬ÊDZØÐëµÄ*/
EXCEPTION
/*Òì³£´¦Àí²¿·Ö£º´íÎó´¦Àí*/
END
PL/SQL¿ ......
1¡¢´¥·¢Æ÷µÄ¸ÅÄî
´¥·¢Æ÷Ò²ÊÇÒ»ÖÖ´øÃûµÄPL/SQL¿é¡£´¥·¢Æ÷ÀàËÆÓÚ¹ý³ÌºÍº¯Êý£¬ÒòΪËüÃǶ¼ÊÇÓµÓÐÉùÃ÷¡¢Ö´ÐкÍÒì³£´¦Àí¹ý³ÌµÄ´øÃûPL/SQL¿é¡£Óë°üÀàËÆ£¬´¥·¢Æ÷±ØÐë´æ´¢ÔÚÊý¾Ý¿âÖв¢ÇÒ²»Äܱ»¿é½øÐб¾µØ»¯ÉùÃ÷¡£
¶ÔÓÚ´¥·¢Æ÷¶øÑÔ£¬µ±´¥·¢Ê¼þ·¢ÉúµÄʱºò¾Í»áÏÔʽµØÖ´Ðиô¥·¢Æ÷£¬²¢ÇÒ´¥·¢Æ÷²»½ÓÊܲÎÊý¡£
´´½¨´¥·¢Æ÷µÄÓï·¨È ......
OracleµÄ·ÖÒ³²éѯÓï¾ä»ù±¾ÉÏ¿ÉÒÔ°´ÕÕ±¾Îĸø³öµÄ¸ñʽÀ´½øÐÐÌ×Óá£
·ÖÒ³²éѯ¸ñʽ£º
SELECT * from
(
SELECT A.*, ROWNUM RN
from (SELECT * from TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
ÆäÖÐ×îÄÚ²ãµÄ²éѯSELECT * from TABLE_NAME±íʾ²»½øÐзҳµÄÔʼ²éѯÓï¾ä¡£ROWNUM <= 40ºÍRN ......