Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

Show currently executing SQL


Whenever your database is experiencing a performance problem, it is very useful to have a script that displays all of the currently executing SQL statements. The following script joins the v$sql and v$session tables to show you all of the SQL that is executing at that moment in your database.


set pagesize  24
set newpage    1
set linesize 125
column sql_text  format a100
column user_name format a12
   substr(u.username,1,12) user_name,
   v$sql s,
   v$session u
   s.hash_value = u.sql_hash_value
   sql_text not like '%from v$sql s, v$session u%'
order by


Here is the output from this script.  As we see, this is extremely useful because a runaway SQL statement could cause degradation of your whole system.  If you identify a runaway query, you can issue the ALTER SYSTEM KILL SESSION 'SID, SER#'; command to kill the query:



SQL> @cur_sql


         5              select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 an

                        d t.bitmapped=0


         6              select local_tran_id, global_tran_fmt, global_oracle_id, global_foreign_id,        state, status, he

                        uristic_dflt,        session_vector, reco_vector, 3600*24*(sysdate-reco_time),        3600*24*(sysda

                        te-nvl(heuristic_time,fail_time)), global_commit#, type#   from pending_trans$  where session_vector

                         != '00000000'


         7              BEGIN sys.dbms_ijob.remove(:job); END;

         9 READER       SELECT TO_CHAR(page_unique_id),        page_seq_nbr,        book_unique_id,        visual_page_nbr,

                               page_text FROM page WHERE  page_unique_id = TO_NUMBER('2380')


        14 READER       SELECT TO_CHAR(page_unique_id),        page_seq_nbr,        book_unique_id,        visual_page_nbr,

                               page_text FROM page WHERE  page_unique_id = TO_NUMBER('9975')






Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.