Viewed 100K+ times! This question is You Asked Hi Tom,How can I know what a given user is executing if his status is ACTIVE How can I know which PL/SQL blocks or SQL statements are being run by him As to SQL statemets, I can join v$session.user# with v$sqlarea.parsing_user_id ( am I really right ), but I am not sure about PL/SQL blocks. How can I track the execution of those objects and queries (something like V$transaction for entire trasactions) Regards. and Tom said...I use the script at the bottom. It shows everyone logged in and if they are active, what they are doing and how long they've been doing it.If someone is executing PLSQL, what you will see will depend on what the plsql is currently doing. If the plsql is doing SQL, you'll see the SQL. if the plsql is doing lots of PLSQL work -- you'll see that code. What I like to do is have everyone \"instrument\" their code with calls to dbms_application_info which can fill in the client_info, action, and module columns in v$session. In this fashion, you can see where in a procedure someone is based on the values in these columns. showsql exposes this information to you as well. sqlplus uses it to show you what script someone is running for example...---------------- showsql.sql --------------------------column status format a10set feedback offset serveroutput onselect username, sid, serial#, process, statusfrom v$sessionwhere username is not null/column username format a20column sql_text format a55 word_wrappedset serveroutput on size 1000000declare x number;begin for x in ( select username'('sid','serial# ') ospid = ' process ' program = ' program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, LAST_CALL_ET from v$session where status = 'ACTIVE' and rawtohex(sql_address) '00' and username is not null order by last_call_et ) loop for y in ( select max(decode(piece,0,sql_text,null)) max(decode(piece,1,sql_text,null)) max(decode(piece,2,sql_text,null)) max(decode(piece,3,sql_text,null)) sql_text from v$sqltext_with_newlines where address = x.sql_address and piece < 4) 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( x.logon_time ' ' x.current_time ' last et = ' x.LAST_CALL_ET); dbms_output.put_line( substr( y.sql_text, 1, 250 ) ); end if; end loop; end loop;end;/column username format a15 word_wrappedcolumn module format a15 word_wrappedcolumn action format a15 word_wrappedcolumn client_info format a30 word_wrappedselect username'('sid','serial#')' username, module, action, client_infofrom v$sessionwhere moduleactionclient_info is not null; Rating (118 ratings)Is this answer out of date If it is, please let us know via a Comment Comments Comment thanksOlga, July 25, 2001 - 6:01 pm UTC
The desc command is not a SQL standard. desc is the sqlplus command.SQL Developer runs a big sql query and sql navigator a simple sql query.SQL Navigator for Oracle Version 6.2.1 does not support ORACLE version 11.2.
Hi,I had downloaded the trial version of sqlnavigator from the quest site and installed, but it is asking for authorisation key to open sqlnavigator. How to get the trial version authorisation key
10.0.5EnhancementsFilter Query Results now opens a new query results tab with the filtered resultsand original results are no longer overwrittenAdded the ability to search on keyboard shortcuts when assigning shortcutsvia the Preferences -> Keyboard Shortcuts tabQuery Results: Added the ability to drag column names to the SQL editorChangesDatabase Browser: When the system navigator is used to populate the databasebrowser, the database type is included in the top level name of the browserBug FixesCode -> Inserts -> Insert Selector throws error when editor syntax type isset to T/SQLInsert Selector and Describe Table share the same default keyboard shortcutMac: When find dialog is visible, auto complete returns focus to the finddialog instead of the editor
9.4.2EnhancementsFilter Navigator: Top Level Objects. Added a drop-downfor Include Selected and Exclude Selected to determine whetherto include top level objects in the navigator or exclude them from the navigatorAdded a Connections - View Status Log menu option. View Status Log cannow be assigned a keyboard shortcutBug FixesDB2 to MySQL conversion. DB2 graphic columns getting convertedwith a G prefix in generated insert statements in certain situations
7.4.7EnhancementsSalesforce: Added row count option to Salesforce database navigator right-click / context menuSalesforce: Added support for streaming Salesforce results to allow support for larger exportsDynamoDB and SimpleDB: Added support for AWS STS temporary credential session tokens for authenticationDynamoDB and SimpleDB: Added support for using AWS credentials files for authenticationSAP / Sybase ASE: Added ability to set NEWPASSWORD property on connections to change expired passwordsChangesAWS Secret Key needs to be masked when adding a new connection profileAthena JDBC driver download updated to latest versionMySQL: Updated reserved word list to support reserved words added in MySQL version 8Bug FixesOracle Packages with overloaded functions and / or procedures not supported correctlyCommand Line Process: Connection via SSH tunnel not supported
7.4.5EnhancementsAdded ability to drag and drop files to editor for openingAdded ability to create and open file sets (File -> Open File Set) menu option for opening a set of fileswith one operationAdded ability to use the show create commands for procedures, functions, and triggers to the database navigatorDatabase Navigator: Enabled Generate DDL option in addition to the Edit option for functions, procedures, triggers,packages, and package bodies for supported databasesBug FixesMS SQL Server: Generate View DDL / definition from a database other than the currently selected database ora schema other than dbo is limiting the view definition to the first 8000 charactersQuery results column width not wide enough in certain situations when the last row displayed is the widest valueMS Access: Generate DDL should not display a length for GUID columnsSwitching editor tabs while queries are executing can cause the query results to be displayed in the wrong tab
7.4.4EnhancementsAdded Close Tab context menu option to query results tabsChangesSQLite: RazorSQL SQLite Driver changed to legacy status. Recommended to use SQLite JDBC DriverSQL Server procedure editor - if file contains carriage return / line feed line break format,or if editor preference for line breaks is set to carriage return / line feed, the carriage returnsare kept in the procedure text when sent to the databaseFrontBase: Drop table syntax now includes restrict keyword at the endBug FixesFrontBase: With certain drivers, dropping an object will cause database navigator to not reload correctlySQLite: Generate DDL does not generate View DDL for views with spaces or special characters in the nameSQLite: Alter option for Views in context menu of database browser not generating drop and create view statementsAuto-commit setting not retained when opening a new tab that shares the existing connectionHigh Resolution Monitors: Close icon on tabs right-edge not completely straightMac: High Resolution Monitors: Close icons not working on tabs 153554b96e