Re: SQL Statements

From: Á¶°æÁø <creamino_at_channeli.net>
Date: Fri, 23 Jul 1999 18:37:43 +0900
Message-ID: <7n9de8$5ha$1@b5nntp2.channeli.net>

Hi!
I hope These scripts will help you...

Oracle Enterprise Manager is very helpful tools.. but it also dependent on Oracle internal functions...
I think REAL Oracle DBAs must know the Internal Oracle Architecture and freely deal with them...

May the Force be with you!!
From Cho..
creamino_at_lgsoft.com

--
--   who.sql
--

SELECT substr(nvl(S.OSUSER,s.type),1,7)     OS_User,
       substr(nvl(S.USERNAME,'Bg'),1,7)     DBUser,
       to_char(S.sid,'9999')                SID,
       to_char(S.serial#,'999')             Ser,
       to_char(P.pid,'99999')               ORAPID,
--     substr(s.program,1,15)               PGM,
       S.process                            F_Ground,
       P.spid                               B_Ground,
       S.status
FROM   V$SESSION S,
       V$PROCESS P

WHERE s.paddr = p.addr
order by s.sid
/

--
-- lock.sql
--
--

clear screen

col un format a11 heading 'Ora User'
col ou format a11 heading 'OS User'
col ob format a19 heading 'On object'

col sid format 999 heading 'SID'
col ser format 99990 heading 'Serial#'

col id1 format 9999999 heading 'Obj ID'
col ty format a4  heading 'Type'
col mh format a4 heading  'Held'

col mr format a4 heading 'Req'
col pt format a10 heading 'p2text'

select username un, osuser ou, s.sid sid , s.serial# ser, l.type ty,

 decode(lmode,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') mh,
 o.name ob, id1,
 decode(request,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') mr

from v$lock l, v$session s, obj$ o
where l.sid = s.sid
and l.id1 = o.obj#(+)
and username is not null
order by sid
/

Doc

     Name: lockhldr.sql

     This script lists the users that are currently holding locks that
     are currently blocking users as well as the users that are being
     blocked. It also lists the offending objects (tables, etc).

#

set heading on
ttitle 'User Blocking and Waiting for Other Users'

select distinct o.object_name,

       sh.username||'('||sh.sid||')' "Holder",
       sw.username||'('||sw.sid||')' "Waiter",
       decode(lh.lmode, 1, 'null',
                        2, 'row share',
                        3, 'row exclusive',
                        4,  'share',
                        5, 'share row exclusive' ,
                        6, 'exclusive')  "Lock Type"

from all_objects o, v$session sw, v$lock lw, v$session sh, v$lock lh where lh.id1 = o.object_id

  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM'

/

Puneet Khanna <7n7dvq$7gc$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
>
>
Received on Fri Jul 23 1999 - 04:37:43 CDT