Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Admin Question

Re: Oracle Admin Question

From: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/03/29
Message-ID: <3343a455.20721425@newshost>#1/1

On Fri, 28 Mar 1997 15:40:15 GMT, Rick Gibson <wrickg_at_geocities.com> wrote:

>I am new to Oracle, but I have Administered MS SQL and Faircom CTree.
>I was wondering if there was a to for Oracle (On Solaris) that would
>enable me to view the connections/processes attached to oracle. The
>database frequently becomes unresponsive and we have to restart it,
>and it would be nice if we could see if there was a process locking a
>table. Logs do not indicate any problem during the time of the lockup.
>
>Thanks,
>Rick Gibson

The following SQL*plus script will show you who is logged on and what they are currently executing SQL-Wise.

Also, you might want to install catblock.sql found in $ORACLE_HOME/rdbms/admin using svrmgrl (connect internal in server mangager and run @catblock from that dir to install the locking views). Then, if you suspect a blocking issue, you can run utllockt found in the same directory in svrmgrl as sys to get a lock graph (shows blockers and waiters in a tree structure...)

column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, status
from v$session
where username is not null
/

column username format a20
column sql_text format a55 word_wrapped

begin

    for x in
    ( select username||'('||sid||','||serial#||') ospid = ' || process || ' program = ' || program username,

             sql_address
        from v$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null ) loop
        for y in ( select sql_text 
                     from v$sqlarea 
                    where address = x.sql_address ) loop
            if ( y.sql_text not like '%listener.get_cmd%' and
                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;

    end loop;
end;
/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Mar 29 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US