¹ØÓÚsql trace ºÍ 10046ʼþµÄ˵Ã÷
ÔÎĵØÖ·£ºhttp://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm
SQL_TRACEÊÇOracleÌṩµÄÓÃÓÚ½øÐÐSQL¸ú×ÙµÄÊֶΣ¬ÊÇÇ¿ÓÐÁ¦µÄ¸¨ÖúÕï¶Ï¹¤¾ß.ÔÚÈÕ³£µÄÊý¾Ý¿âÎÊÌâÕï¶ÏºÍ½â¾öÖУ¬SQL_TRACEÊǷdz£³£Óõķ½·¨¡£
±¾ÎľÍSQL_TRACEµÄʹÓÃ×÷¼òµ¥Ì½ÌÖ£¬²¢Í¨¹ý¾ßÌå°¸Àý¶Ôsql_traceµÄʹÓýøÐÐ˵Ã÷.
Ò»¡¢ »ù´¡½éÉÜ
(a) SQL_TRACE˵Ã÷
SQL_TRACE¿ÉÒÔ×÷Ϊ³õʼ»¯²ÎÊýÔÚÈ«¾ÖÆôÓã¬Ò²¿ÉÒÔͨ¹ýÃüÁîÐз½Ê½ÔÚ¾ßÌåsessionÆôÓá£
1£® ÔÚÈ«¾ÖÆôÓÃ
ÔÚ²ÎÊýÎļþ(pfile/spfile)ÖÐÖ¸¶¨:
sql_trace =true
ÔÚÈ«¾ÖÆôÓÃSQL_TRACE»áµ¼ÖÂËùÓнø³ÌµÄ»î¶¯±»¸ú×Ù£¬°üÀ¨ºǫ́½ø³Ì¼°ËùÓÐÓû§½ø³Ì£¬Õâͨ³£»áµ¼Ö±ȽÏÑÏÖصÄÐÔÄÜÎÊÌ⣬ËùÒÔÔÚÉú²ú»·¾³
ÖÐÒª½÷É÷ʹÓÃ.
Ìáʾ: ͨ¹ýÔÚÈ«¾ÖÆôÓÃsql_trace£¬ÎÒÃÇ¿ÉÒÔ¸ú×Ùµ½ËùÓкǫ́½ø³ÌµÄ»î¶¯£¬ºÜ¶àÔÚÎĵµÖеijéÏó˵Ã÷£¬Í¨¹ý¸ú×ÙÎļþµÄʵʱ±ä»¯£¬ÎÒÃÇ¿ÉÒÔÇåÎú
µÄ¿´µ½¸÷¸ö½ø³ÌÖ®¼äµÄ½ôÃÜе÷.
2£® ÔÚµ±Ç°session¼¶ÉèÖÃ
´ó¶àÊýʱºòÎÒÃÇʹÓÃsql_trace¸ú×Ùµ±Ç°½ø³Ì.ͨ¹ý¸ú×Ùµ±Ç°½ø³Ì¿ÉÒÔ·¢ÏÖµ±Ç°²Ù×÷µÄºǫ́Êý¾Ý¿âµÝ¹é»î¶¯(ÕâÔÚÑо¿Êý¾Ý¿âÐÂÌØÐÔʱÓÈÆäÓÐЧ)£¬
Ñо¿SQLÖ´ÐУ¬·¢ÏÖºǫ́´íÎóµÈ.
ÔÚsession¼¶ÆôÓúÍÍ£Ö¹sql_trace·½Ê½ÈçÏÂ:
ÆôÓõ±Ç°sessionµÄ¸ú×Ù:
SQL> alter session set sql_trace=true;
Session altered.
´ËʱµÄSQL²Ù×÷½«±»¸ú×Ù:
SQL> select count(*) from dba_users;
COUNT(*)
----------
34
½áÊø¸ú×Ù:
SQL> alter session set sql_trace=false;
Session altered.
3£® ¸ú×ÙÆäËûÓû§½ø³Ì
ÔںܶàʱºòÎÒÃÇÐèÒª¸ú×ÙÆäËûÓû§µÄ½ø³Ì£¬¶ø²»Êǵ±Ç°Óû§£¬Õâ¿ÉÒÔͨ¹ýOracleÌṩµÄϵͳ°üDBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION
À´Íê³É
SET_SQL_TRACE_IN_SESSION¹ý³ÌÐòÒªÌṩÈý¸ö²ÎÊý:
SQL> desc dbms_system
...
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
...
ͨ¹ýv$sessionÎÒÃÇ¿ÉÒÔ»ñµÃsid¡¢serial#µÈÐÅÏ¢:
»ñµÃ½ø³ÌÐÅÏ¢£¬Ñ¡ÔñÐèÒª¸ú×ٵĽø³Ì:
SQL> select sid,serial#,username from v$session
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
--¿ç·þÎñÆ÷²éѯÈçÏ£º
SELECT a.*,b.stor_Name
from OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=tom;UID=sa;PWD=123',
pubs.dbo.authors) AS a,stores b
ORDER BY a.au_lname, a.au_fname
--ÆäÖУ¬tomΪԶ³Ì·þÎñÆ÷Ãû£¬stores ÊDZ¾»úÊý¾Ý¿âpubsÖеıí
--ÐèҪעÒâµÄÊÇÈô¶þ¸ö±íÖÐµÄ ......
use Tempdb
go
if object_ID('fn_ACITEncryption') is not null
drop function fn_ACITEncryption
go
create function fn_ACITEncryption
(
@Str nvarchar(4000),--¼ÓÃܵÄ×Ö·û´®
@Flag bit=1,--1¡¢¼ÓÃÜ 0¡¢½âÃÜ
@Key nvarchar(50)--ÃÜÎÄ
)
returns nvarchar(4000)--這Àï¿É轉換 ......
SQL°´ÕÕÈÕ¡¢ÖÜ¡¢Ô¡¢Äêͳ¼ÆÊý¾Ý ÊÕ²Ø
ÎÄÕ²ο¼£ºhttp://www.cnblogs.com/wenbhappy/archive/2008/07/02/1233660.html
Èç:
±í:consume_record
×Ö¶Î:consume (moneyÀàÐÍ)
date (datetimeÀàÐÍ)
ÇëÎÊÔõôдËÄÌõsqlÓï¾ä·Ö±ð°´ÈÕ,°´ÖÜ,°´ÔÂ,°´¼¾Í³¼ÆÏû·Ñ×ÜÁ¿.
Èç:1ÔÂ 1200Ô ......
create table tb (ptoid int,proclassid int,proname varchar(10))
insert tb
select 1,1,'Ò·þ1'
union all
select 2,2,'Ò·þ2'
union all
select 3,3,'Ò·þ3'
union all
select 4,3,'Ò·þ4'
union all
select 5,2,'Ò·þ5'
union all
select 6,2,'Ò·þ6'
union all
select 7,2,'Ò·þ7'
union all
select 8 ......