Re: SQL Statements

From: Michel Cadot <micadot_at_altern.org>
Date: Tue, 3 Aug 1999 16:43:40 +0200
Message-ID: <7o6v7p$6e7$1@oceanite.cybercable.fr>

Puneet Khanna a écrit dans le message <7n7e0v$7hb$1_at_nntp3.uunet.ca>...
>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
>
>

1. The following query gives you the answer in seconds:

select sum(decode(key, 'STARTUP TIME - JULIAN',

                          (to_number(to_char(sysdate, 'J'))-value)*86400,
                       'STARTUP TIME - SECONDS',
                          to_number(to_char(sysdate, 'SSSSS'))-value,
                       0)) "Elapsed (s)"

from V$INSTANCE
where key in ('STARTUP TIME - JULIAN', 'STARTUP TIME - SECONDS');

2. select count(*) "Nb User" from v$session where type='USER';

3. I don't know what you mean by "any locking problem". The following query gives the list of the locks hold and waiting. Actually, it displays the user, his session id, the status of the session, the OS process id, the lock type and the lock mode, if it is waiting and the object locked or requested:
Column user_ FORMAT a11 HEADING User Column sid_ FORMAT a10 HEADING Sid Column status FORMAT a08 HEADING Status

Column process  FORMAT a06  HEADING "OS Pid"      TRUNC
Column LK       FORMAT a02

Column mode_ FORMAT a03 HEADING Mode Column wait_ FORMAT a01 HEADING Wait Column objet_ FORMAT a29 HEADING Object select s.sid||', '||s.serial# sid_, s.status status, s.username user_,

       p.spid process, l.type LK,
       decode(l.lmode+l.request, 0, 'NO'  /* no lock requested   */,
                                 1, ' '   /* null                */,
                                 2, 'RS'  /* row share           */,
                                 3, 'RX'  /* row exclusive       */,
                                 4, 'S'   /* share               */,
                                 5, 'SRX' /* share row exclusive */,
                                 6, 'X'   /* exclusive           */,
                                 to_char(l.lmode+l.request)) mode_,
       decode(l.request, 0, ' ', 'W') wait_,
       decode(l.type||to_char(l.lmode+l.request),
              'TX6', decode(l.id2, 0, decode(o.object_name, NULL,
l.id1||'/'||l.id2,
                                      o.owner||'.'||o.object_name),
                                   decode(r.name, NULL, l.id1||'/'||l.id2,
r.name)),
              'TS6', 'Temporaire
'||TRUNC(l.id1/16777216)||'.'||mod(l.id1,16777216),
              decode(o.object_name, NULL, l.id1||'/'||l.id2,
                                    o.owner||'.'||o.object_name)) objet_

from dba_objects o, v$rollname r, v$process p, v$session s, v$lock l

where o.object_id (+) = l.id1
  and r.usn (+) = TRUNC(l.id1/65536)
  and p.addr = s.paddr
  and s.sid = l.sid
  and l.type in ('RW', 'TM', 'TS', 'TX', 'UL');

Received on Tue Aug 03 1999 - 09:43:40 CDT