1. /* 得到trace文件路径和名称 */
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM ( SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
( SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v. NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
( SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest' ) d
|
2./* 显示产生锁定的sql语句 */
1
|
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ a.username, a.machine, a.sid,a.serial#, a.last_call_et "Seconds" , b.id1, c.sql_text "SQL" from v$session a, v$lock b,v$sqltext c where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value;
|
3./* 查看oracle隐藏参数 */
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
select name ,
value,
decode(isdefault, 'TRUE' , 'Y' , 'N' ) as "Default" ,
decode(ISEM, 'TRUE' , 'Y' , 'N' ) as SesMod,
decode(ISYM, 'IMMEDIATE' , 'I' , 'DEFERRED' , 'D' , 'FALSE' , 'N' ) as SysMod,
decode(IMOD, 'MODIFIED' , 'U' , 'SYS_MODIFIED' , 'S' , 'N' ) as Modified,
decode(IADJ, 'TRUE' , 'Y' , 'N' ) as Adjusted,
description
from ( --GV$SYSTEM_PARAMETER
select x.inst_id as instance,
x.indx + 1,
ksppinm as name ,
ksppity,
ksppstvl as value,
ksppstdf as isdefault,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE' , 'FALSE' ) as ISEM,
decode(bitand(ksppiflg / 65536, 3),
1,
'IMMEDIATE' ,
2,
'DEFERRED' ,
'FALSE' ) as ISYM,
decode(bitand(ksppstvf, 7), 1, 'MODIFIED' , 'FALSE' ) as IMOD,
decode(bitand(ksppstvf, 2), 2, 'TRUE' , 'FALSE' ) as IADJ,
ksppdesc as description
from x$ksppi x, x$ksppsv y
where x.indx = y.indx
and substr(ksppinm, 1, 1) = '_'
and x.inst_id = USERENV( 'Instance' ))
order by name ;
|
4./* 根据系统中oracle的pid来查看sql */
1
|
select /*+ ORDERED */ sql_text from v$sqltext a where (a.hash_value,a.address) IN ( select decode (sql_hash_value,0,prev_hash_value,sql_hash_value),decode (sql_hash_value,0,prev_sql_addr,sql_address) from v$session b where b.paddr =( select addr from v$process c where c.spid = '&pid' )) order by piece ASC ;
|
以上就是本文给大家分享几个比较好用sql语句,希望大家喜欢。