本篇文章给出Oracle数据库运维时常用的查询语句：

1. 查询数据库的参数与属性：

SELECT * FROM database_properties;

SELECT * FROM v$version;

2.查询当前运行的数据库SESSION和SID：

SELECT sid,

serial#

FROM v$session

WHERE audsid=sys_context('USERENV','SESSIONID');

3. 查询指定SID对应的操作系统进程ID：

SELECT p.spid "OS Thread" ,

b.name "Name User" ,

s.program ,

s.sid ,

s.serial# ,

s.osuser ,

s.machine

FROM v$process p,

v$session s,

v$bgprocess b

WHERE p.addr=s.paddr

AND p.addr =b.paddr

AND (s.sid =

&1

OR p.spid=

&1)

UNION ALL

SELECT p.spid "OS Thread" ,

s.username "Name User" ,

s.program ,

s.sid ,

s.serial# ,

s.osuser ,

s.machine

FROM v$process p,

v$session s

WHERE p.addr=s.paddr

AND (s.sid =

&1

OR p.spid=

&1)

AND s.username IS NOT NULL;

4.根据SID获取sql语句：

SELECT

/*+ PUSH_SUBQ */

command_type ,

sql_text ,

sharable_mem ,

persistent_mem ,

runtime_mem ,

sorts ,

version_count ,

loaded_version ,

open_versions ,

users_opening ,

executions ,

users_executing ,

loads ,

first_load_time ,

invalidations ,

parse_calls ,

disk_reads ,

buffer_gets ,

rows_processed ,

sysdate start_time ,

sysdate finish_time ,

'>'

||address sql_address ,

'N' status

FROM v$sqlarea

WHERE address=

(SELECT sql_address FROM v$session WHERE sid=&sid

);

5.查询某个数据库SESSION使用了指定的数据库对象：

SELECT p.spid ,

s.sid ,

s.serial# ,

s.username ,

a.type ,

s.osuser ,

a.owner ,

a.object ,

DECODE(SIGN(48-command),1,TO_CHAR(command),'Action Code #'

||TO_CHAR(command)) Action ,

p.program ,

s.terminal ,

s.program ,

s.status

FROM v$session s,

v$access a,

v$process p

WHERE s.paddr=p.addr

AND s.type ='USER'

AND a.sid =s.sid

--and a.object='&obj'

ORDER BY s.username,

s.osuser;

6.查询当前那些用户正在使用数据库：

SELECT s.osuser ,

DECODE(SIGN(48-command),1,TO_CHAR(command),'Action Code #'

||TO_CHAR(command)) Action ,

p.program "oracle process" ,

status "session status" ,

s.terminal ,

s.program ,

s.username ,

s.fixed_table_sequence "Activity Meter" ,

'' Query ,

0 Memory ,

0 "Max Memory" ,

0 "CPU Usage" ,

s.sid ,

s.serial#

FROM v$session s,

v$process p

WHERE s.paddr=p.addr

AND s.type ='USER'

ORDER BY s.username,

s.osuser;

7.查询消耗数据库资源前10的数据库SESSION：

SELECT s.schemaname "Schema Name" ,

DECODE(SIGN(48-command),1,'Action Code #'

||TO_CHAR(command)) Action ,

status "Session Status" ,

s.osuser "OS User Name" ,

s.sid ,

p.spid ,

s.serial# ,

NVL(s.username,'[Oracle Process]') "User Name" ,

s.terminal ,

s.program ,

st.value "Criteria Value"

FROM v$sesstat st,

v$session s,

v$process p

WHERE st.sid =s.sid

AND st.statistic#=to_number('38')

AND ('ALL' ='ALL'

OR s.status ='ALL')

AND p.addr =s.paddr

AND s.schemaname ='DM_DWH'

ORDER BY st.value DESC,

p.spid ASC,

s.username ASC,

s.osuser ASC;

8.查询数据库的被锁对象：

SELECT

/*+ RULE */

ls.osuser OS_USER_NAME ,

ls.username USER_NAME ,

DECODE(ls.type,'RW','Row wait enqueue lock', 'TM','DML enqueue lock', 'TX','Transaction enqueu lock', 'UL','User supplied lock') LOCK_TYPE ,

o.object_name OBJECT ,

DECODE(ls.lmode,1,NULL, 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exlusive') LOCK_MODE ,

o.owner ,

ls.sid ,

ls.serial# ,

ls.id1 ,

ls.id2

FROM dba_objects o,

(SELECT s.osuser,

s.username,

l.type,

l.lmode,

s.sid,

s.serial#,

l.id1,

l.id2

FROM v$session s,

v$lock l

WHERE s.sid=l.sid

) ls;