非常好用的sql语句(日常整理)

本文给大家分享几个比较好用的sql语句,在数据库中经常会用到,需要的朋友可以参考下本篇文章。

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语句,希望大家喜欢。

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/27177/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 9月 29日 4:29:37
下一篇 2022年 9月 29日 4:29:43

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!