What SQL and sessions are running? GUIs like TOAD and Enterprise Manager can show this better, but they’re not always to hand. Every session active now or within the last minute is shown. 1set pages 9999 lines 1322col username form a203col osuser form a204col program form a505col minutes form 9996alter session set nls_date_format = 'Dy DD-Mon-YYYY HH24:MI:SS'7/8select distinct9s.sid, s.username, s.osuser, s.program||' '||s.module program,10s.status, s.last_call_Et/60 minutes, s.logon_time, w.state, w.event, w.seconds_in_wait ,11q.sql_text12from v$session s, v$sql q, v$session_wait w13where ( s.status = 'ACTIVE' or last_call_et < 60 )14and s.sql_hash_value = q.hash_value15and s.sql_address = q.address16and s.sid = w.sid17order by s.sid18/19set pages 9999 lines 80 head on feed 6 April 20, 2007 Posted in Performance tuning, Scripts
Following are errors on 10.2.0.1.0 on WIN XP SP2- 1SQL> set pages 9999 lines 1322SQL> col username form a203SQL> col osuser form a204SQL> col program form a505SQL> col minutes form 9996SQL> alter session set nls_date_format = `Dy DD-Mon-YYYY HH24:MI:SS'7 2 /8alter session set nls_date_format = `Dy DD-Mon-YYYY HH24:MI:SS'9 *10ERROR at line 1:11ORA-00911: invalid character 1SQL> select distinct2 2 s.sid, s.username, s.osuser, s.program||' `||s.module program,3 3 s.status, s.last_call_Et/60 minutes, s.logon_time, w.state, w.event, w.seconds_in_wait ,4 4 q.sql_text5 5 from v$session s, v$sql q, v$session_wait w6 6 where ( s.status = `ACTIVE' or last_call_et < 60 )7 7 and s.sql_hash_value = q.hash_value8 8 and s.sql_address = q.address9 9 and s.sid = w.sid10 10 order by s.sid11 11 /12where ( s.status = `ACTIVE' or last_call_et < 60 )13 *14ERROR at line 6:15ORA-00923: FROM keyword not found where expected
Sorry Mukesh – was due to wordpress editor turning ‘ into ` symbol. Should be fixed now in main post.
Following are errors on 10.2.0.1.0 on WIN XP SP2-
1SQL> set pages 9999 lines 1322SQL> col username form a203SQL> col osuser form a204SQL> col program form a505SQL> col minutes form 9996SQL> alter session set nls_date_format = `Dy DD-Mon-YYYY HH24:MI:SS'72 /8alter session set nls_date_format = `Dy DD-Mon-YYYY HH24:MI:SS'9*10ERROR at line 1:11ORA-00911: invalid character1SQL> select distinct22 s.sid, s.username, s.osuser, s.program||' `||s.module program,33 s.status, s.last_call_Et/60 minutes, s.logon_time, w.state, w.event, w.seconds_in_wait ,44 q.sql_text55 from v$session s, v$sql q, v$session_wait w66 where ( s.status = `ACTIVE' or last_call_et < 60 )77 and s.sql_hash_value = q.hash_value88 and s.sql_address = q.address99 and s.sid = w.sid1010 order by s.sid1111 /12where ( s.status = `ACTIVE' or last_call_et < 60 )13*14ERROR at line 6:15ORA-00923: FROM keyword not found where expected