Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Admin Question
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;
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities