Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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±í¿Õ¼ä¼°Æäά»¤

»ù±¾¸ÅÄî
ORACLEÊý¾Ý¿â±»»®·Ö³É³Æ×÷Ϊ±í¿Õ¼äµÄÂß¼­ÇøÓò——ÐγÉORACLEÊý¾Ý¿âµÄÂß¼­½á¹¹¡£Ò»
¸öORACLEÊý¾Ý¿âÄܹ»ÓÐÒ»¸ö»ò¶à¸ö±í¿Õ¼ä,¶øÒ»¸ö±í¿Õ¼äÔò¶ÔÓ¦×ÅÒ»¸ö»ò¶à¸öÎïÀíµÄÊý¾Ý¿â
Îļþ¡£±í¿Õ¼äÊÇORACLEÊý¾Ý¿â»Ö¸´µÄ×îСµ¥Î»,ÈÝÄÉ×ÅÐí¶àÊý¾Ý¿âʵÌå,Èç±í¡¢ÊÓͼ¡¢Ë÷Òý
¡¢¾Û´Ø¡¢»ØÍ˶κÍÁÙʱ¶ÎµÈ¡£
ÿ¸öORACLEÊý¾ ......

дORACLE SQL

Ò».°´Ó¢ÎÄ×ÖĸÅÅÐò:
        select * from table order by nlssort(name,'NLS_SORT=schinese_pinyin_M');
 ¶þ. дһ¸öSQLÓï¾ä£¬²éѯѡÐÞÁË5Ãſγ̵ÄѧÉúѧºÅºÍÐÕÃû£¨9·ÖÖÓ£©
´ð£ºSQLÓï¾äÈçÏ£º
select stu.sno, stu.sname
from student stu
where (select count(*) from s ......

ORACLE PL/SQL ¶ÔÏó(object)ѧϰ±Ê¼Ç(¶þ)

4¡¢¶ÔÏóÒÀÀµÐÔ
 
CREATE OR REPLACE TYPE Obj1 AS OBJECT (
f1 NUMBER,
f2 VARCHAR2(10),
f3 DATE
);
/

CREATE OR REPLACE TYPE Obj2 AS OBJECT (
f1 DATE,
f2 CHAR(1)
);
/

CREATE OR REPLACE TYPE Obj3 AS OBJECT (
a Obj1,
b Obj2
);
/
  
 
OBJ3ÒÀÀµÓÚOBJ ......

ORACLE PL/SQL ¶ÔÏó(object)ѧϰ±Ê¼Ç(Èý)

7¡¢¶ÔÏóÀàÐͼ̳Ð
 
¶ÔÏóÀàÐÍʵÏÖÔÊÐíÎÒÃÇ´´½¨Ò»¸ö»ùÀàÐÍ£¬»ò½Ð¸¸ÀàÐÍ£¬ÕâÖÖÀàÐ͵ÄÊôÐÔ»ò·½·¨¿ÉÒÔ±»ÁíÒ»¸ö¶ÔÏóÀàÐͼ̳С£È»ºó¿ÉÒÔ´´½¨Ò»¸ö×ÓÀàÐÍ£¬»ò½Ðº¢×ÓÀàÐÍ£¬Ö±½ÓʹÓü̳йýÀ´µÄÊôÐÔ»ò·½·¨£¬»òÕßÓÃ×Ô¼ºµÄÊôÐԺͷ½·¨ÖØÐ´¸¸ÀàÐ͵ÄÊôÐÔ»ò·½·¨¡£
 
INSTANTIABLE¹Ø¼ü×Ö±íʾÎÒÃÇ¿ÉÒÔ´Ó¸ÃÀàÐÍÖÐʵÀý»¯»òÕß´´½¨¶ÔÏ ......

ROLLUPºÍCUBEÓï¾ä¡£ ORACLE·Ö×éͳ¼Æ


ROLLUPºÍCUBEÓï¾ä¡£
OracleµÄGROUP
BYÓï¾ä³ýÁË×î»ù±¾µÄÓï·¨Í⣬»¹Ö§³ÖROLLUPºÍCUBEÓï¾ä¡£Èç¹ûÊÇROLLUP(A, B, C)µÄ»°£¬Ê×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºó¶Ô(A¡¢B)½øÐÐGROUP BY£¬È»ºóÊÇ(A)½øÐÐGROUP BY£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£Èç¹ûÊÇGROUP BY
CUBE(A, B, C)£¬ÔòÊ×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºóÒÀ´ÎÊÇ( ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ