Re: SQL Statements

From: Alex Shnir <shnira_at_staff.juno.com>
Date: Thu, 22 Jul 1999 12:13:17 -0400
Message-ID: <3797431C.7041BCF7@staff.juno.com>

2.

select s.username,osuser,sid,
decode(status,'ACTIVE','Act','INACTIVE','Inact','KILLED','Kill',status) stat, decode(type,'BACKGROUND','Back','USER','User',type) type,p.spid, s.terminal,

decode(command,0,'',1,'Create Table',2,'Insert',3,'Select',4,'Create Clust',
5,'Alter Clustr',6,'Update',7,'
Delete',8,'Drop',9,'Create Index',
10,'Drop Index',11,'Alter Index',12,'Drop Table',15,'Alter Table',
17,'Grant',18,'Revoke',19,'Create Syn',20,'Drop Synonym',
21,'Create View',22,'Drop View',26,'Lock Table',27,'nop',28,'Rename',
29,'Comment',30,'Audit',31,'Noaudit',32,'Cre Ext Data',
33,'Drop Ext Dat',34,'Create Data',35,'Alter Data',36,'Cre Roll Seg',
37,'Alt Roll Seg',38,'Drp Roll Seg',39,'Cre Tablesp',40,'Alt Tablesp',

41,'Drop Tablesp',42,'Alt Session',43,'Alter User',44,'Commit', 45,'Rollback',46,'Save Point',47,'PL/SQL',to_char(command)) command, decode(lockwait,'','','Y') lockwait
from v$session s,v$process p
where s.paddr = p.addr
order by type desc,username,osuser,sid

3.

select osuser,p.spid,
s.username,s.sid,s.serial# ser#,
decode(l.type,
'MR','Media Reco',
'RT','Redo Thred',
'UN','User Name',
'TX','Trans',
'TM','DML',
'UL','PL/SQL Usr',
'DX','Dist. Tran',
'CF','Cntrl File',
'IS','Inst State',
'FS','File Set',
'IR','Inst Reco',
'ST','Disk Space',
'TS','Temp Seg',
'IV','Cache Inv',
'LS','Log Switch',
'RW','Row Wait',
'SQ','Seq Number',
'TE','Extend Tbl',
'TT','Temp Table',

l.type) locktyp,
' ' object_name,

decode(lmode,1,Null,2,'Row Share',3,'Row Excl',4,'Share', 5,'Sh Row Ex',6,'Exclusive',' ') held,
decode(request,1,Null,2,'Row Share',3,'Row Excl',4,'Share', 5,'Sh Row Ex',6,'Exclusive',' ') request from v$lock l,v$session s,v$process p
where s.sid = l.sid and

s.username <> ' ' and
s.paddr = p.addr and
l.type <> 'TM' and

(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6) union
select osuser,p.spid,
s.username,s.sid,s.serial#,
decode(l.type,
'MR','Media Reco',
'RT','Redo Thred',
'UN','User Name',
'TX','Trans',
'TM','DML',
'UL','PL/SQL Usr',
'DX','Dist. Tran',
'CF','Cntrl File',
'IS','Inst State',
'FS','File Set',
'IR','Inst Reco',
'ST','Disk Space',
'TS','Temp Seg',
'IV','Cache Inv',
'LS','Log Switch',
'RW','Row Wait',
'SQ','Seq Number',
'TE','Extend Tbl',
'TT','Temp Table',

l.type) locktype,
object_name,
decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share', 5,'Sh Row Ex',6,'Exclusive',NULL) held, decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share', 5,'Sh Row Ex',6,'Exclusive',NULL) request from v$lock l,v$session s,v$process p,sys.dba_objects o where s.sid = l.sid and

o.object_id = l.id1 and
l.type = 'TM' and
s.username <> ' ' and
s.paddr = p.addr

union
select osuser,p.spid,
s.username,s.sid,s.serial#,
decode(l.type,
'MR','Media Reco',
'RT','Redo Thred',
'UN','User Name',
'TX','Trans',
'TM','DML',
'UL','PL/SQL Usr',
'DX','Dist. Tran',
'CF','Cntrl File',
'IS','Inst State',
'FS','File Set',
'IR','Inst Reco',
'ST','Disk Space',
'TS','Temp Seg',
'IV','Cache Inv',
'LS','Log Switch',
'RW','Row Wait',
'SQ','Seq Number',
'TE','Extend Tbl',
'TT','Temp Table',

l.type) locktype,
'(Rollback='||rtrim(r.name)||')' object_name,
decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share', 5,'Sh Row Ex',6,'Exclusive',NULL) held, decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share', 5,'Sh Row Ex',6,'Exclusive',NULL) request from v$lock l,v$session s,v$process p,v$rollname r where s.sid = l.sid and
l.type = 'TX' and
l.lmode = 6 and
trunc(l.id1/65536) = r.usn and
s.username <> ' ' and
s.paddr = p.addr
order by 8,9

Puneet Khanna wrote:

> I want to know SQL statements for -
>
> 1. Checking how long database instance is up.
> 2. How many active users are in the system.
> 3. Are there any locking problems.
>
> Thanks in advance,
> Puneet

Received on Thu Jul 22 1999 - 11:13:17 CDT