一些有用的sql

############## 查找UNIX进程对应的数据库用户运行的SQL ################
select p.SPID,vsql.SQL_TEXT,s.sid,s.SERIAL#,s.USERNAME,s.STATUS,
s.COMMAND,s.OSUSER,s.TERMINAL,s.PROgram,s.action
from v$process p,v$session s,v$sql vsql
where p.ADDR=s.pADDR
and   s.SQL_ADDRESS=vsql.ADDRESS
and   s.SQL_HASH_VALUE=vsql.HASH_VALUE
and   p.spid=2007094

########### 查找请求运行的相关UNIX和数据库信息 ################

select request_id,requested_by,
oracle_process_id oracle_unix_process_id,
oracle_session_id session_audsid,
os_process_id concurrent_unix_process_id
from fnd_concurrent_requests
where request_id=3997062
os_process_id=5619886

############# 查找某会话运行的完整SQL #####################
select vsql_newline.sql_text,vsql_newline.piece,
vsql.sorts,executions,vsql.parse_calls,
vsql.disk_reads,buffer_gets,vsql.rows_processed,
vsql.module
from v$sqltext_with_newlines vsql_newline,
v$sqlarea vsql,v$session vs
where vsql.address=vsql_newline.address
and   vsql.hash_value=vsql_newline.hash_value
--and   vsql.sql_text like 'UPDATE AP_INVOICE_DISTRIBUTIONS APID SET APID.ASSETS_ADDITION_FLAG%'
and   vs.SQL_ADDRESS=vsql.ADDRESS
and   vs.SQL_HASH_VALUE=vsql.HASH_VALUE
and   vs.SID=20313157  --填写session的SID
order by vsql.address,vsql.hash_value,vsql_newline.piece

select b.SID,
       b.SERIAL#,
       b.USERNAME,
       b.SCHEMANAME,
       b.MACHINE,
       b.TERMINAL,
       b.PROGRAM,
       b.STATUS,  --
       b.MODULE,
       b.ACTION
  from v$session b
WHERE b.paddr = (SELECT addr
                   FROM v$process c
                  WHERE c.spid = '12617')

################ 查找登陆用户和SESSION对应关系 #######################
SELECT L.USER_ID,v.spid unix_pid, vs.SID,vs.SERIAL#,
FLOOR((SYSDATE - NVL(R.START_TIME, L.START_TIME))*24*60) LOGON_MINUTES,
USR.USER_NAME, RSP.RESPONSIBILITY_NAME,vs.ACTION,vs.MODULE
FROM FND_LOGINS L,
FND_LOGIN_RESPONSIBILITIES R,
FND_USER USR,
FND_RESPONSIBILITY_TL RSP,
GV$PROCESS V,
v$session vs
WHERE L.LOGIN_ID = R.LOGIN_ID (+)
AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID (+)
AND R.RESP_APPL_ID = RSP.APPLICATION_ID (+)
AND RSP.LANGUAGE (+) = userenv('LANG')
AND L.PID = V.PID
AND L.SERIAL# = V.SERIAL#
AND L.START_TIME>SYSDATE-1 --假设用户登陆时间不超过24小时
AND (R.AUDSID IS NULL OR R.AUDSID IN
(SELECT AUDSID FROM GV$SESSION))
and  v.ADDR=vs.PADDR

######################## 查找请求运行情况 ##########################
select fcr.request_id,fu.user_name,fcp.user_concurrent_program_name,
status_code,actual_start_date,actual_completion_date,
(actual_completion_date-actual_start_date) * 86400 cost_seconds
from fnd_concurrent_requests fcr,fnd_concurrent_programs_tl fcp,
fnd_concurrent_programs fcp2, fnd_user fu
where fcr.program_application_id=fcp.application_id
and   fcr.concurrent_program_id=fcp.concurrent_program_id
--and   fcr.status_code='R'
and   fcp.application_id=fcp2.application_id
and   fcp.concurrent_program_id=fcp2.concurrent_program_id
and   fcp.language='ZHS'
and   fcr.request_id=3995503
and   fcp2.concurrent_program_name='GLXUSA' --改为并发程序的short name
and   fu.user_id=fcr.requested_by
order by fcr.actual_start_date desc

status_code:

select lookup_code,meaning
from fnd_lookups
where lookup_type = 'CP_PHASE_CODE'

C: Finished
I: inactive
R: Running
X: 已经终止
T: 正在终止
...
(select lookup_code,meaning
from fnd_lookups
where lookup_type = 'CP_CONTROL_CODE')

 

######################## 查看某数据库对象（一般是表）是否有锁 ##################
select do.object_name,vs.SID,vs.SERIAL#,vs.LOGON_TIME, vs.LOCKWAIT,
vs.ACTION,vo.xidusn, vo.object_id,  vo.locked_mode
from v$session vs,v$locked_object vo,dba_objects do
where vs.sid=session_id
and   do.object_id=vo.OBJECT_ID
and   do.object_name='GL_INTERFACE'

0,'None',
1,'Null (NULL)'
2,'Row-S (SS)'
3,'Row-X (SX)'
4,'Share (S)'
5,'S/Row-X (SSX)'
6,'Exclusive (X)'

 如果是on commit preserve rows的临时表,在一个session使用的时候,oracle会给该临时表加3 - row-X (SX)
锁的.
0：none 
1：null 空 
2：Row-S 行共享(RS)：共享表锁，sub share 
3：Row-X 行独占(RX)：用于行的修改，sub exclusive 
4：Share 共享锁(S)：阻止其他DML操作，share 
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive 
6：exclusive 独占(X)：独立访问使用，exclusive

1级锁有：Select，有时会在v$locked_object出现。
2级锁有：Select for update,Lock For Update,Lock Row Share 
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁有：Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有：Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5级锁有：Lock Share Row Exclusive 
具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

insert / update / delete ... ;             是3的锁。       
       
                没有commit之前插入同样的一条记录会没有反应,
                因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
       
                创建索引的时候也会产生3,4级别的锁

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13561774/viewspace-591085/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13561774/viewspace-591085/