oracle10g SQL ¸ú×Ù
1.¼¤»îSQL¸ú×Ù
´Óoracle10gÒÔºóÌṩÁËÐµķ½·¨¼¤»îSQLµÄ¸ú×Ù£¬¼´ÊÇʹÓÃdbms_monitor°üÀ´¿ªÆô»ò¹Ø±ÕSQL¸ú×Ù¡£
֮ǰ¾É·½·¨ÊÇʹÓà alter session set events .. »òÕßʹÓÃdbms_system.set_ev·½Ê½
ʹÓÃdbms_monitor°ü¿ÉÒÔÔڻỰ£¬¿Í»§¶Ë£¬×é¼þÒÔ¼°Êý¾Ý¿âËĸö²ã¼¶¿ªÆôSQLµÄ¸ú×Ù¡£
Ö»ÓÐÓµÓÐdba½ÇÉ«µÄÓû§²ÅÄÜÔÊÐíÖ´ÐÐdbms_monitor°ü
4¸ö²ã´Î¼¶±ðµÄ¼¤»îµ÷Ó÷½·¨£º
»á »° ¼¶:exec dbms_monitor.session_trace_enable(session_id =>1574,
serial_num=>36749,waits=>true,binds=>false);
×¢Òâ:ʹÓÃselect * from v$session where sid=sys_context('userenv','sid');
»ñµÃsession_idºÍserial
¹Ø±ÕÓÃ:
exec dbms_monitor.session_trace_disable(session_id=>1574,serial_num=>36749)
¿Í»§¶Ë¼¶:exec dbms_monitor.cliend_id_trace_enable(
client_id=''JXXXT:Administrator',
waits =>true,binds=>false)
×¢Òâ:client_id¼´v$session±íÀïÃæµÄclient_identifier×ֶΣ¬Ä¬ÈÏ´Ë×Ö¶ÎΪ¿Õ,
ͨ¹ýdba_enabled_traces±í²é¿´ÄÇЩ¿Í»§¶ËÆôÓÃÁËSQL¸ú×Ù
&nb
Ïà¹ØÎĵµ£º
Oracle lessons list:
Title ......
create table tree_table
(
id number,
pId number,
orderNumber number,
name varchar2(255)
)
select tt.*
from tree_table tt
start with tt.pId = 0
connect by prior tt.id = ttd.pId
order siblings by orderNumber ......
ÒÔÍù´ÓÀ´Ã»ÓÐÏëÈ¥ÈÏÕæ¶ÁÒ»´ÎOracleµÄ°æÈ¨ÉùÃ÷£¬½ñÌì´ò¿ªOracle10gR2µÄÎĵµ£¬ÓÚÊǾÍË÷ÐÔ´ÓÍ·¿ªÊ¼¶ÁÒ»ÏÂÕâÆªÎÄÕ¡£
һЩÎÒÏë´ó¼Ò¶¼Ó¦¸ÃÈ¥ÖªµÀµÄ£¬ËäÈ»¹úÄڵİæÈ¨¸ÅÄîʵÔÚÊDZȽϵ±¡£¬µ«ÏëÀ´»áÔ½À´Ô½ºÃµÄ¡£
Oracle10g·ÖΪ4¸ö°æ±¾£¬·Ö±ðÊÇ£º
1¡£Oracle Database Standard Edition One£¬×î»ù±¾µÄÉÌÒµ°æ± ......
±ê׼˳ÐòµÄ SQL Óï¾äΪ:
Select ¿¼ÉúÐÕÃû, max(×ܳɼ¨) as max×ܳɼ¨
from tb_Grade
where ¿¼ÉúÐÕÃû is not null
group by ¿¼ÉúÐÕÃû
having max(×ܳɼ¨) > 600
order by max×ܳɼ¨
ÔÚÉÏÃæµÄʾÀýÖÐ SQL Óï¾äµÄÖ´ÐÐ˳ÐòÈçÏÂ:
(1). Ê×ÏÈÖ´ÐÐ from ×Ó¾ä, ´Ó tb_G ......
̽ÌÖÈçºÎÔÚÓÐ×Å1000ÍòÌõÊý¾ÝµÄMS SQL SERVERÊý¾Ý¿âÖÐʵÏÖ¿ìËÙµÄÊý¾ÝÌáÈ¡ºÍÊý¾Ý·ÖÒ³¡£ÒÔÏ´úÂë˵Ã÷ÁËÎÒÃÇʵÀýÖÐÊý¾Ý¿âµÄ“ºìÍ·Îļþ”Ò»±íµÄ²¿·ÖÊý¾Ý½á¹¹£º
CREATE TABLE [dbo].[TGongwen] ( --TGongwenÊǺìÍ·Îļþ±íÃû
[Gid] [int]&nb ......