{"id":51,"date":"2018-01-03T00:21:30","date_gmt":"2018-01-02T23:21:30","guid":{"rendered":"http:\/\/dbaunchained.eu\/?p=51"},"modified":"2018-01-04T20:15:13","modified_gmt":"2018-01-04T19:15:13","slug":"your-friendly-custom-session-browser","status":"publish","type":"post","link":"https:\/\/dbaunchained.eu\/?p=51","title":{"rendered":"Your friendly custom session browser"},"content":{"rendered":"<p>Every DBA sooner or later needs to do some\u00a0<em>session browsing<\/em>, trying to find out clues about blocking sessions, check execution plans, better understand resource consumption and many other useful informations. All of that quickly enough and without waiting web pages to load.<\/p>\n<p>Every DBA sooner or later start to collect useful queries and tools for his day to day work. Me aswell, of course. In many years, I&#8217;ve collected thousands, and some of them are now parts of my custom\u00a0<em>session browser<\/em>.<\/p>\n<p>I use <a href=\"http:\/\/www.oracle.com\/technetwork\/developer-tools\/sql-developer\/downloads\/index.html\">Oracle SQL Developer<\/a> since version <em>2.something<\/em> because I personally find\u00a0<a href=\"https:\/\/www.quest.com\/products\/toad-for-oracle\/\">Toad for Oracle<\/a>\u00a0too expensive for what it gives. With SQL Developer I started to build a report to relieve my day to day work, one query at a time. Many of the queries in my reports came from other great DBAs and books, and I would like to thank them all (I will try to find them to give credits).<\/p>\n<h2>Session List<\/h2>\n<p>It all start creating a new report. Under <em>Navigate<\/em> menu, choose <em>Reports<\/em> to open the related tab. Right-click <em>User Defined Reports<\/em> and select <em>New Report. <\/em>A big window will appear where you have to put a <em>Name<\/em> for the report,\u00a0 choose <em>Table<\/em> in the dropdown list <em>Style\u00a0<\/em>so the report will be shown like a table. and a query inside the text area named <em>SQL<\/em>. The following query will retrieve all the non-background non-internal sessions. When done, click <em>Apply<\/em> and the report will be shown under the tree list.<\/p>\n<pre>WITH\r\n vs AS\r\n (\r\n SELECT\r\n rownum rnum,\r\n service_name,\r\n inst_id,\r\n sid,\r\n serial#,\r\n logon_time,\r\n status,\r\n username,\r\n EVENT,\r\n WAIT_CLASS,\r\n last_call_et,\r\n command,\r\n machine,\r\n osuser,\r\n module,\r\n action,\r\n resource_consumer_group,\r\n client_info,\r\n client_identifier,\r\n type,\r\n terminal,\r\n SQL_ID,\r\n SADDR,\r\n program,\r\n PADDR,\r\n SQL_CHILD_NUMBER\r\n FROM\r\n gv$session\r\n )\r\nSELECT\r\n vs.inst_id,\r\n vs.sid ,\r\n serial# serial,\r\n vs.logon_time,\r\n vs.username \"Username\",\r\n CASE\r\n WHEN vs.status = 'ACTIVE'\r\n THEN (NUMTODSINTERVAL(last_call_et,'SECOND'))\r\n ELSE NULL\r\n END \"Seconds in Wait (+DD HH:MI:SS)\",\r\n lower(vs.status) \"Status\",\r\n EVENT,\r\n WAIT_CLASS,\r\n DECODE(vs.command, 0,NULL, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 4,\r\n 'CRE CLUSTER', 5,'ALT CLUSTER', 6,'UPDATE', 7,'DELETE', 8,'DRP CLUSTER', 9,\r\n 'CRE INDEX', 10,'DROP INDEX', 11,'ALT INDEX', 12,'DROP TABLE', 13,'CRE SEQ',\r\n 14,'ALT SEQ', 15,'ALT TABLE', 16,'DROP SEQ', 17,'GRANT', 18,'REVOKE', 19,\r\n 'CRE SYN', 20,'DROP SYN', 21,'CRE VIEW', 22,'DROP VIEW', 23,'VAL INDEX', 24,\r\n 'CRE PROC', 25,'ALT PROC', 26,'LOCK TABLE', 28,'RENAME', 29,'COMMENT', 30,\r\n 'AUDIT', 31,'NOAUDIT', 32,'CRE DBLINK', 33,'DROP DBLINK', 34,'CRE DB', 35,\r\n 'ALTER DB', 36,'CRE RBS', 37,'ALT RBS', 38,'DROP RBS', 39,'CRE TBLSPC', 40,\r\n 'ALT TBLSPC', 41,'DROP TBLSPC', 42,'ALT SESSION', 43,'ALT USER', 44,'COMMIT',\r\n 45,'ROLLBACK', 46,'SAVEPOINT', 47,'PL\/SQL EXEC', 48,'SET XACTN', 49,\r\n 'SWITCH LOG', 50,'EXPLAIN', 51,'CRE USER', 52,'CRE ROLE', 53,'DROP USER', 54,\r\n 'DROP ROLE', 55,'SET ROLE', 56,'CRE SCHEMA', 57,'CRE CTLFILE', 58,\r\n 'ALTER TRACING', 59,'CRE TRIGGER', 60,'ALT TRIGGER', 61,'DRP TRIGGER', 62,\r\n 'ANALYZE TAB', 63,'ANALYZE IX', 64,'ANALYZE CLUS', 65,'CRE PROFILE', 66,\r\n 'DRP PROFILE', 67,'ALT PROFILE', 68,'DRP PROC', 69,'DRP PROC', 70,\r\n 'ALT RESOURCE', 71,'CRE SNPLOG', 72,'ALT SNPLOG', 73,'DROP SNPLOG', 74,\r\n 'CREATE SNAP', 75,'ALT SNAP', 76,'DROP SNAP', 79,'ALTER ROLE', 79,\r\n 'ALTER ROLE', 85,'TRUNC TAB', 86,'TRUNC CLUST', 88,'ALT VIEW', 91,'CRE FUNC',\r\n 92,'ALT FUNC', 93,'DROP FUNC', 94,'CRE PKG', 95,'ALT PKG', 96,'DROP PKG', 97,\r\n 'CRE PKG BODY', 98,'ALT PKG BODY', 99,'DRP PKG BODY', 189, 'MERGE', TO_CHAR(vs.command))\r\n \"Command\",\r\n vs.machine \"Machine\",\r\n vs.osuser \"OS User\",\r\n vs.program,\r\n vs.module \"Module\",\r\n vs.action \"Action\",\r\n vs.resource_consumer_group,\r\n vs.service_name,\r\n vs.client_info,\r\n vs.client_identifier,\r\n vs.SQL_ID,\r\n vs.type,\r\n VS.SADDR AS SESSION_ADDRESS,\r\n vs.paddr as PROCESS_ADDRESS,\r\n vs.SQL_CHILD_NUMBER\r\nFROM\r\n vs\r\nWHERE\r\n vs.USERNAME IS NOT NULL\r\nAND vs.type &lt;&gt; 'BACKGROUND'\r\nORDER BY\r\n 2,3,1<\/pre>\n<p>From now on, whenever you will left-click on the report name, you will be requested to connect to a database and the query will be run showing the result in a nice table. Of course yhou need to connect with a user with sufficient privilegies.<\/p>\n<h2>Child reports<\/h2>\n<p>Now things will start to be interesting. Right-click on the report name just created and edit it. Under the <em>Master Report <\/em>tree, find the section <em>Child Reports<\/em>. After selecting it, on the right the window will change in an empty area. On the lower, find <em>Add Child&#8230;<\/em>button and click it and give it a name. You just created a child report and you can find it in a branch on the left tree under <em>Child Reports<\/em>. If you open that branch and select <em>SQL Query<\/em> you will be able to add a new query. A lower window will show you the parent query (the previous we just copied) for reference.<\/p>\n<p>Do you want to know the rowid where a specific session is stuck on? Put this query in the child box:<\/p>\n<pre> SELECT\r\n do.object_name AS TABLE_NAME,\r\n row_wait_obj# AS OBJECT_NUM,\r\n row_wait_file# AS FILE_NUM,\r\n row_wait_block# AS BLOCK_NUM,\r\n row_wait_row# AS ROW_NUM,\r\n dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) AS ROW_ID\r\n FROM\r\n gv$session s,\r\n dba_objects DO\r\n WHERE\r\n s.sid = :SID\r\n AND s.inst_id = :INST_ID\r\n AND s.ROW_WAIT_OBJ# = do.OBJECT_ID<\/pre>\n<p>As you may have already noticed, the <em>:SID <\/em>and <em>:INST_ID<\/em> (both in uppercase, remember!) reference parent query columns. This means whenever you select a session (a row in the report), the child query will be executed with <em>:SID<\/em> and <em>:INST_ID<\/em> value from the session query.<\/p>\n<p>You can add as many child reports as you want. This is a great way to compact many repetitive queries, expectially to semplify investigating issues, avoiding annoying copy&amp;paste from notes, text files, etc.<\/p>\n<p>Here some other useful queries for your child reports. Feel free to use, adapt and enhance them as you like. Please comment below if yuo want to share your findings and queries.<\/p>\n<p>Query full text<\/p>\n<pre>SELECT S.SQL_FULLTEXT\r\n FROM GV$SQLAREA S\r\n JOIN GV$SESSION GS\r\n ON GS.SQL_ID = S.SQL_ID\r\n WHERE GS.INST_ID = :INST_ID\r\n AND GS.SID = :SID\r\n AND GS.SERIAL# = :SERIAL\r\n AND ROWNUM = 1<\/pre>\n<p>Explain Plan<\/p>\n<pre>SELECT RPAD('Inst: '\r\n ||v.inst_id, 9)\r\n ||' '\r\n ||RPAD('Child: '\r\n ||v.child_number, 11) inst_child,\r\n t.plan_table_output\r\n FROM gv$sql v,\r\n TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST', 'inst_id = '\r\n ||:INST_ID\r\n ||' AND sql_id = '''||:SQL_ID||''' AND child_number = '||v.child_number)) t\r\n WHERE v.sql_id = :SQL_ID\r\n AND v.loaded_versions &gt; 0\r\n AND NVL(:SQL_ID,'X') &lt;&gt; 'X'\r\n and v.CHILD_NUMBER = :SQL_CHILD_NUMBER<\/pre>\n<p>Blocking Locks<\/p>\n<pre>SELECT (\r\n CASE\r\n WHEN S2.INST_ID IS NOT NULL\r\n THEN 'This transaction is waiting =&gt;'\r\n WHEN S3.INST_ID IS NOT NULL\r\n THEN 'Waiting for this transaction =&gt;'\r\n ELSE 'Current locks'\r\n END ) AS Concurrency ,\r\n CASE\r\n WHEN S2.INST_ID IS NOT NULL\r\n THEN S2.INST_ID\r\n ELSE S3.INST_ID\r\n END AS Instance,\r\n CASE\r\n WHEN S2.INST_ID IS NOT NULL\r\n THEN S2.USERNAME\r\n ELSE S3.USERNAME\r\n END AS UserName,\r\n CASE\r\n WHEN S2.INST_ID IS NOT NULL\r\n THEN S2.MACHINE\r\n ELSE S3.MACHINE\r\n END AS MachineName,\r\n CASE\r\n WHEN S2.INST_ID IS NOT NULL\r\n THEN s2.sql_id\r\n ELSE (\r\n CASE\r\n WHEN S3.INST_ID IS NOT NULL\r\n AND s3.sql_id IS NULL\r\n THEN 'COMMIT\/ROLLBACK?'\r\n ELSE s3.sql_id\r\n END )\r\n END AS SQL_ID,\r\n CASE\r\n WHEN S2.INST_ID IS NOT NULL\r\n THEN s2.SID\r\n ELSE s3.SID\r\n END AS SID,\r\n CASE\r\n WHEN S2.INST_ID IS NOT NULL\r\n THEN s2.SERIAL#\r\n ELSE s3.SERIAL#\r\n END AS SERIAL,\r\n AB.OWNER\r\n ||'.'\r\n ||AB.OBJECT_NAME AS BLOCKED_OBJECT,\r\n LT.NAME AS LOCK_TYPE ,\r\n LT.ID1_TAG , LT.ID2_TAG ,\r\n AB2.OWNER\r\n ||'.'\r\n ||AB2.OBJECT_NAME AS TAG_2,\r\n (\r\n CASE G.LMODE\r\n WHEN 0\r\n THEN 'NONE'\r\n WHEN 1\r\n THEN 'NULL (NULL)'\r\n WHEN 2\r\n THEN 'ROW-SS (SS)'\r\n WHEN 3\r\n THEN 'ROW-X (SX)'\r\n WHEN 4\r\n THEN 'SHARE (S)'\r\n WHEN 5\r\n THEN 'S\/ROW-X (SSX)'\r\n WHEN 6\r\n THEN 'EXCLUSIVE (X)'\r\n END) AS LOCK_MODE ,\r\n (\r\n CASE G.REQUEST\r\n WHEN 0\r\n THEN 'NONE'\r\n WHEN 1\r\n THEN 'NULL (NULL)'\r\n WHEN 2\r\n THEN 'ROW-SS (SS)'\r\n WHEN 3\r\n THEN 'ROW-X (SX)'\r\n WHEN 4\r\n THEN 'SHARE (S)'\r\n WHEN 5\r\n THEN 'S\/ROW-X (SSX)'\r\n WHEN 6\r\n THEN 'EXCLUSIVE (X)'\r\n END) AS LOCK_REQUESTS\r\n FROM GV$LOCK G\r\n JOIN V$LOCK_TYPE LT\r\n ON G.TYPE = LT.TYPE\r\n LEFT OUTER JOIN ALL_OBJECTS AB\r\n ON AB.OBJECT_ID = DECODE(LT.ID1_TAG, 'object #',G.ID1, NULL)\r\n LEFT OUTER JOIN ALL_OBJECTS AB2\r\n ON AB2.OBJECT_ID = DECODE(LT.ID2_TAG, 'table\/partition',G.ID2, NULL)\r\n LEFT OUTER JOIN GV$LOCK G2\r\n ON G.ID1 = G2.ID1\r\n AND G.ID2 = G2.ID2\r\n AND G.BLOCK &gt;= 1\r\n AND G2.REQUEST &gt; 0\r\n LEFT OUTER JOIN GV$SESSION S2\r\n ON S2.INST_ID = G2.INST_ID\r\n AND S2.SID = G2.SID\r\n LEFT OUTER JOIN GV$LOCK G3\r\n ON G.ID1 = G3.ID1\r\n AND G.ID2 = G3.ID2\r\n AND G3.BLOCK &gt;= 1\r\n AND G.REQUEST &gt; 0\r\n LEFT OUTER JOIN GV$SESSION S3\r\n ON S3.INST_ID = G3.INST_ID\r\n AND S3.SID = G3.SID\r\n WHERE G.INST_ID = :INST_ID\r\n AND G.SID = :SID<\/pre>\n<p>Transactions<\/p>\n<pre>SELECT START_DATE,\r\n NAME,\r\n USED_UBLK AS UNDO_BLOCK_USED,\r\n USED_UREC AS UNDO_RECORDS_USED,\r\n LOG_IO AS LOGICAL_IO,\r\n PHY_IO AS PHYSICAL_IO,\r\n CR_GET AS CONSISTENT_GETS,\r\n CR_CHANGE AS CONSISTENT_CHANGES\r\n FROM GV$TRANSACTION\r\n WHERE SES_ADDR = :SESSION_ADDRESS\r\n AND INST_ID = :INST_ID<\/pre>\n<p>Long Operations<\/p>\n<pre>SELECT START_TIME\r\n ,LAST_UPDATE_TIME\r\n ,OPNAME AS OPERATION_NAME\r\n ,TARGET_DESC\r\n ,SOFAR AS SO_FAR\r\n ,TOTALWORK AS TOTAL_WORK\r\n ,CASE WHEN TOTALWORK = 0 THEN 'N\/A' ELSE TO_CHAR(SOFAR\/TOTALWORK*100,'999.99')||'%' END AS PERC\r\n ,UNITS AS UNITS_OF_MEASUREMENT\r\n ,TIME_REMAINING\r\n ,ELAPSED_SECONDS\r\n ,CONTEXT\r\n ,MESSAGE\r\n FROM GV$SESSION_LONGOPS\r\n WHERE INST_ID = :INST_ID\r\n AND SID = :SID\r\n AND SERIAL# = :SERIAL\r\n ORDER BY LAST_UPDATE_TIME DESC<\/pre>\n<p>Bind Variables<\/p>\n<pre>SELECT\r\n NAME,\r\n POSITION,\r\n DATATYPE_STRING AS DATA_TYPE,\r\n PRECISION,\r\n SCALE,\r\n VALUE_STRING AS VALUE\r\n FROM v$sql_bind_capture WHERE sql_id=:SQL_ID<\/pre>\n<p>Active Session History<\/p>\n<pre>SELECT NVL(a.event, 'ON CPU') AS event,\r\n A.WAIT_CLASS ,\r\n A.SESSION_TYPE ,\r\n A.SESSION_STATE,\r\n A.PROGRAM,\r\n A.MODULE,\r\n A.ACTION,\r\n COUNT(*) AS total_wait_time\r\n FROM Gv$active_session_history a\r\n WHERE a.session_id = :SID\r\n and a.SESSION_SERIAL# = :SERIAL\r\n AND A.INST_ID = :INST_ID\r\n GROUP BY NVL(a.event, 'ON CPU'),\r\n A.WAIT_CLASS ,\r\n A.SESSION_TYPE ,\r\n A.SESSION_STATE,\r\n A.PROGRAM,\r\n A.MODULE,\r\n A.ACTION\r\n ORDER BY total_wait_time DESC<\/pre>\n<p>Isolation Level<\/p>\n<pre>SELECT\r\n *\r\n FROM\r\n V$SES_OPTIMIZER_ENV v\r\n WHERE\r\n v.sid = :SID\r\n AND NAME = 'transaction_isolation_level'\r\n ORDER BY\r\n v.name<\/pre>\n<p>Session Current Locks<\/p>\n<pre>select\r\n nvl(S.USERNAME,'Internal') username,s.SERIAL#,\r\n L.SID,\r\n nvl(S.TERMINAL,'None') terminal,\r\n decode(command,\r\n 0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,\r\n decode(command,\r\n 0,'BACKGROUND',\r\n 1,'Create Table',\r\n 2,'INSERT',\r\n 3,'SELECT',\r\n 4,'CREATE CLUSTER',\r\n 5,'ALTER CLUSTER',\r\n 6,'UPDATE',\r\n 7,'DELETE',\r\n 8,'DROP',\r\n 9,'CREATE INDEX',\r\n 10,'DROP INDEX',\r\n 11,'ALTER INDEX',\r\n 12,'DROP TABLE',\r\n 13,'CREATE SEQUENCE',\r\n 14,'ALTER SEQUENCE',\r\n 15,'ALTER TABLE',\r\n 16,'DROP SEQUENCE',\r\n 17,'GRANT',\r\n 18,'REVOKE',\r\n 19,'CREATE SYNONYM',\r\n 20,'DROP SYNONYM',\r\n 21,'CREATE VIEW',\r\n 22,'DROP VIEW',\r\n 23,'VALIDATE INDEX',\r\n 24,'CREATE PROCEDURE',\r\n 25,'ALTER PROCEDURE',\r\n 26,'LOCK TABLE',\r\n 27,'NO OPERATION',\r\n 28,'RENAME',\r\n 29,'COMMENT',\r\n 30,'AUDIT',\r\n 31,'NOAUDIT',\r\n 32,'CREATE EXTERNAL DATABASE',\r\n 33,'DROP EXTERNAL DATABASE',\r\n 34,'CREATE DATABASE',\r\n 35,'ALTER DATABASE',\r\n 36,'CREATE ROLLBACK SEGMENT',\r\n 37,'ALTER ROLLBACK SEGMENT',\r\n 38,'DROP ROLLBACK SEGMENT',\r\n 39,'CREATE TABLESPACE',\r\n 40,'ALTER TABLESPACE',\r\n 41,'DROP TABLESPACE',\r\n 42,'ALTER SESSION',\r\n 43,'ALTER USER',\r\n 44,'COMMIT',\r\n 45,'ROLLBACK',\r\n 46,'SAVEPOINT',\r\n 47,'PL\/SQL EXECUTE',\r\n 48,'SET TRANSACTION',\r\n 49,'ALTER SYSTEM SWITCH LOG',\r\n 50,'EXPLAIN',\r\n 51,'CREATE USER',\r\n 52,'CREATE ROLE',\r\n 53,'DROP USER',\r\n 54,'DROP ROLE',\r\n 55,'SET ROLE',\r\n 56,'CREATE SCHEMA',\r\n 57,'CREATE CONTROL FILE',\r\n 58,'ALTER TRACING',\r\n 59,'CREATE TRIGGER',\r\n 60,'ALTER TRIGGER',\r\n 61,'DROP TRIGGER',\r\n 62,'ANALYZE TABLE',\r\n 63,'ANALYZE INDEX',\r\n 64,'ANALYZE CLUSTER',\r\n 65,'CREATE PROFILE',\r\n 66,'DROP PROFILE',\r\n 67,'ALTER PROFILE',\r\n 68,'DROP PROCEDURE',\r\n 69,'DROP PROCEDURE',\r\n 70,'ALTER RESOURCE COST',\r\n 71,'CREATE SNAPSHOT LOG',\r\n 72,'ALTER SNAPSHOT LOG',\r\n 73,'DROP SNAPSHOT LOG',\r\n 74,'CREATE SNAPSHOT',\r\n 75,'ALTER SNAPSHOT',\r\n 76,'DROP SNAPSHOT',\r\n 79,'ALTER ROLE',\r\n 85,'TRUNCATE TABLE',\r\n 86,'TRUNCATE CLUSTER',\r\n 87,'-',\r\n 88,'ALTER VIEW',\r\n 89,'-',\r\n 90,'-',\r\n 91,'CREATE FUNCTION',\r\n 92,'ALTER FUNCTION',\r\n 93,'DROP FUNCTION',\r\n 94,'CREATE PACKAGE',\r\n 95,'ALTER PACKAGE',\r\n 96,'DROP PACKAGE',\r\n 97,'CREATE PACKAGE BODY',\r\n 98,'ALTER PACKAGE BODY',\r\n 99,'DROP PACKAGE BODY',\r\n 189,'MERGE',\r\n command||' - ???') COMMAND,\r\n decode(L.LMODE,1,'No Lock',\r\n 2,'Row Share',\r\n 3,'Row Exclusive',\r\n 4,'Share',\r\n 5,'Share Row Exclusive',\r\n 6,'Exclusive','NONE') lmode,\r\n decode(L.REQUEST,1,'No Lock',\r\n 2,'Row Share',\r\n 3,'Row Exclusive',\r\n 4,'Share',\r\n 5,'Share Row Exclusive',\r\n 6,'Exclusive','NONE') request,\r\n l.id1||'-'||l.id2 Laddr,\r\n l.type||' - '||\r\n decode(l.type,\r\n 'BL','Buffer hash table instance lock',\r\n 'CF',' Control file schema global enqueue lock',\r\n 'CI','Cross-instance function invocation instance lock',\r\n 'CS','Control file schema global enqueue lock',\r\n 'CU','Cursor bind lock',\r\n 'DF','Data file instance lock',\r\n 'DL','Direct loader parallel index create',\r\n 'DM','Mount\/startup db primary\/secondary instance lock',\r\n 'DR','Distributed recovery process lock',\r\n 'DX','Distributed transaction entry lock',\r\n 'FI','SGA open-file information lock',\r\n 'FS','File set lock',\r\n 'HW','Space management operations on a specific segment lock',\r\n 'IN','Instance number lock',\r\n 'IR','Instance recovery serialization global enqueue lock',\r\n 'IS','Instance state lock',\r\n 'IV','Library cache invalidation instance lock',\r\n 'JQ','Job queue lock',\r\n 'KK','Thread kick lock',\r\n 'MB','Master buffer hash table instance lock',\r\n 'MM','Mount definition gloabal enqueue lock',\r\n 'MR','Media recovery lock',\r\n 'PF','Password file lock',\r\n 'PI','Parallel operation lock',\r\n 'PR','Process startup lock',\r\n 'PS','Parallel operation lock',\r\n 'RE','USE_ROW_ENQUEUE enforcement lock',\r\n 'RT','Redo thread global enqueue lock',\r\n 'RW','Row wait enqueue lock',\r\n 'SC','System commit number instance lock',\r\n 'SH','System commit number high water mark enqueue lock',\r\n 'SM','SMON lock',\r\n 'SN','Sequence number instance lock',\r\n 'SQ','Sequence number enqueue lock',\r\n 'SS','Sort segment lock',\r\n 'ST','Space transaction enqueue lock',\r\n 'SV','Sequence number value lock',\r\n 'TA','Generic enqueue lock',\r\n 'TD','DDL enqueue lock',\r\n 'TE','Extend-segment enqueue lock',\r\n 'TM','DML enqueue lock',\r\n 'TO','Temporary Table Object Enqueue',\r\n 'TT','Temporary table enqueue lock',\r\n 'TX','Transaction enqueue lock',\r\n 'UL','User supplied lock',\r\n 'UN','User name lock',\r\n 'US','Undo segment DDL lock',\r\n 'WL','Being-written redo log instance lock',\r\n 'WS','Write-atomic-log-switch global enqueue lock',\r\n 'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',\r\n 'New block allocation enqueue lock (ID2=1)'),\r\n 'LA','Library cache lock instance lock (A=namespace)',\r\n 'LB','Library cache lock instance lock (B=namespace)',\r\n 'LC','Library cache lock instance lock (C=namespace)',\r\n 'LD','Library cache lock instance lock (D=namespace)',\r\n 'LE','Library cache lock instance lock (E=namespace)',\r\n 'LF','Library cache lock instance lock (F=namespace)',\r\n 'LG','Library cache lock instance lock (G=namespace)',\r\n 'LH','Library cache lock instance lock (H=namespace)',\r\n 'LI','Library cache lock instance lock (I=namespace)',\r\n 'LJ','Library cache lock instance lock (J=namespace)',\r\n 'LK','Library cache lock instance lock (K=namespace)',\r\n 'LL','Library cache lock instance lock (L=namespace)',\r\n 'LM','Library cache lock instance lock (M=namespace)',\r\n 'LN','Library cache lock instance lock (N=namespace)',\r\n 'LO','Library cache lock instance lock (O=namespace)',\r\n 'LP','Library cache lock instance lock (P=namespace)',\r\n 'LS','Log start\/log switch enqueue lock',\r\n 'PA','Library cache pin instance lock (A=namespace)',\r\n 'PB','Library cache pin instance lock (B=namespace)',\r\n 'PC','Library cache pin instance lock (C=namespace)',\r\n 'PD','Library cache pin instance lock (D=namespace)',\r\n 'PE','Library cache pin instance lock (E=namespace)',\r\n 'PF','Library cache pin instance lock (F=namespace)',\r\n 'PG','Library cache pin instance lock (G=namespace)',\r\n 'PH','Library cache pin instance lock (H=namespace)',\r\n 'PI','Library cache pin instance lock (I=namespace)',\r\n 'PJ','Library cache pin instance lock (J=namespace)',\r\n 'PL','Library cache pin instance lock (K=namespace)',\r\n 'PK','Library cache pin instance lock (L=namespace)',\r\n 'PM','Library cache pin instance lock (M=namespace)',\r\n 'PN','Library cache pin instance lock (N=namespace)',\r\n 'PO','Library cache pin instance lock (O=namespace)',\r\n 'PP','Library cache pin instance lock (P=namespace)',\r\n 'PQ','Library cache pin instance lock (Q=namespace)',\r\n 'PR','Library cache pin instance lock (R=namespace)',\r\n 'PS','Library cache pin instance lock (S=namespace)',\r\n 'PT','Library cache pin instance lock (T=namespace)',\r\n 'PU','Library cache pin instance lock (U=namespace)',\r\n 'PV','Library cache pin instance lock (V=namespace)',\r\n 'PW','Library cache pin instance lock (W=namespace)',\r\n 'PX','Library cache pin instance lock (X=namespace)',\r\n 'PY','Library cache pin instance lock (Y=namespace)',\r\n 'PZ','Library cache pin instance lock (Z=namespace)',\r\n 'QA','Row cache instance lock (A=cache)',\r\n 'QB','Row cache instance lock (B=cache)',\r\n 'QC','Row cache instance lock (C=cache)',\r\n 'QD','Row cache instance lock (D=cache)',\r\n 'QE','Row cache instance lock (E=cache)',\r\n 'QF','Row cache instance lock (F=cache)',\r\n 'QG','Row cache instance lock (G=cache)',\r\n 'QH','Row cache instance lock (H=cache)',\r\n 'QI','Row cache instance lock (I=cache)',\r\n 'QJ','Row cache instance lock (J=cache)',\r\n 'QL','Row cache instance lock (K=cache)',\r\n 'QK','Row cache instance lock (L=cache)',\r\n 'QM','Row cache instance lock (M=cache)',\r\n 'QN','Row cache instance lock (N=cache)',\r\n 'QO','Row cache instance lock (O=cache)',\r\n 'QP','Row cache instance lock (P=cache)',\r\n 'QQ','Row cache instance lock (Q=cache)',\r\n 'QR','Row cache instance lock (R=cache)',\r\n 'QS','Row cache instance lock (S=cache)',\r\n 'QT','Row cache instance lock (T=cache)',\r\n 'QU','Row cache instance lock (U=cache)',\r\n 'QV','Row cache instance lock (V=cache)',\r\n 'QW','Row cache instance lock (W=cache)',\r\n 'QX','Row cache instance lock (X=cache)',\r\n 'QY','Row cache instance lock (Y=cache)',\r\n 'QZ','Row cache instance lock (Z=cache)',\r\n 'AE','Media recovery',\r\n '????') Lockt\r\n from GV$LOCK L,\r\n GV$SESSION S,\r\n SYS.USER$ U1,\r\n SYS.OBJ$ T1\r\n where L.SID = S.SID\r\n AND L.INST_ID = S.INST_ID\r\n and T1.OBJ# = decode(L.ID2,0,L.ID1,1)\r\n and U1.USER# = T1.OWNER#\r\n and S.TYPE != 'BACKGROUND'\r\n AND L.SID = :SID\r\n AND L.INST_ID = :INST_ID\r\n order by 1,3,2,5<\/pre>\n<p>SQL Informations<\/p>\n<pre>SELECT\r\n INST_ID,\r\n LAST_ACTIVE_TIME,\r\n IS_REOPTIMIZABLE,\r\n IS_RESOLVED_ADAPTIVE_PLAN,\r\n SQL_PLAN_BASELINE,\r\n EXECUTIONS,\r\n LOADED_VERSIONS,\r\n DISK_READS,\r\n DIRECT_WRITES,\r\n BUFFER_GETS,APPLICATION_WAIT_TIME,\r\n CONCURRENCY_WAIT_TIME,\r\n CLUSTER_WAIT_TIME,\r\n USER_IO_WAIT_TIME,\r\n PLSQL_EXEC_TIME,\r\n ROWS_PROCESSED,\r\n SERVICE,\r\n FULL_PLAN_HASH_VALUE,\r\n MODULE,\r\n REMOTE,\r\n IM_SCANS,\r\n SQL_ID,\r\n SQL_FULLTEXT\r\n FROM GV$SQL\r\n where sql_id=:SQL_ID\r\n AND INST_ID = :INST_ID<\/pre>\n<p>Process Informations<\/p>\n<pre>select * from gv$process\r\n where inst_id = :INST_ID\r\n and ADDR = :PROCESS_ADDRESS<\/pre>\n<p>Optimizer Session Environment<\/p>\n<pre>SELECT * FROM GV$SES_OPTIMIZER_ENV\r\n WHERE INST_ID = :INST_ID\r\n AND SID = :SID<\/pre>\n<p>Parallel Processes<\/p>\n<pre>select * from gV$PX_SESSION gps\r\n where gps.inst_id = :INST_ID\r\n and gps.sid = :SID\r\n and gps.serial# = :SERIAL<\/pre>\n<p>Client Connection Informations<\/p>\n<pre>select * from GV$SESSION_CONNECT_INFO\r\n WHERE INST_ID = :INST_ID\r\n AND SID = :SID<\/pre>\n<p>Shared Cursors<\/p>\n<pre>SELECT *\r\n FROM GV$SQL_SHARED_CURSOR\r\n WHERE SQL_ID = :SQL_ID\r\n AND INST_ID = :INST_ID<\/pre>\n<p>Temporary Segments Usage<\/p>\n<pre>SELECT * FROM GV$TEMPSEG_USAGE\r\n WHERE SESSION_ADDR = :SADDR\r\n AND INST_ID = :INST_ID<\/pre>\n<p>Open Cursors<\/p>\n<pre>SELECT sid ,\r\n sql_text,\r\n LAST_SQL_ACTIVE_TIME,\r\n USER_NAME,\r\n SQL_ID,\r\n CURSOR_TYPE,\r\n COUNT(*) AS \"OPEN CURSORS\"\r\n FROM gv$open_cursor\r\n WHERE SID = :SID\r\n and INST_ID = :INST_ID\r\n GROUP BY sid ,\r\n sql_text,\r\n LAST_SQL_ACTIVE_TIME,\r\n USER_NAME,\r\n SQL_ID,\r\n CURSOR_TYPE<\/pre>\n<p>Cached Cursors Stats<\/p>\n<pre>SELECT\r\n cach.value cache_hits,\r\n prs.value all_parses,\r\n prs.value - cach.value sess_cur_cache_not_used\r\n FROM\r\n Gv$sesstat cach,\r\n Gv$sesstat prs,\r\n Gv$statname nm1,\r\n Gv$statname nm2\r\n WHERE\r\n cach.statistic# = nm1.statistic#\r\n AND nm1.name = 'session cursor cache hits'\r\n AND prs.statistic# = nm2.statistic#\r\n AND nm2.name = 'parse count (total)'\r\n AND prs.sid = cach.sid\r\n AND PRS.INST_ID = CACH.INST_ID\r\n AND NM1.INST_ID = NM2.INST_ID\r\n AND PRS.INST_ID = NM2.INST_ID\r\n AND cach.sid = :SID\r\n AND CACH.INST_ID = :INST_ID<\/pre>\n<p>After you are happy with your new tool, you may want to save it and copy to different computers or send it by mail to collegues. Just right-click on your report and <em>Save As&#8230;<\/em> . On the other hand, to import a new report, just right-clink on <em>User Defined Reports<\/em> and <em>Open Report<\/em> choosing the xml file saved from another SQL Developer.<\/p>\n<p>Hope you like it! \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Every DBA sooner or later needs to do some\u00a0session browsing, trying to find out clues about blocking sessions, check execution&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"footnotes":""},"categories":[8,10,9,2],"tags":[4,3],"class_list":["post-51","post","type-post","status-publish","format-standard","hentry","category-oracle","category-performance","category-sql-developer","category-tools","tag-query","tag-sql-developer"],"wppr_data":{"cwp_meta_box_check":"No"},"_links":{"self":[{"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=\/wp\/v2\/posts\/51","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=51"}],"version-history":[{"count":7,"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=\/wp\/v2\/posts\/51\/revisions"}],"predecessor-version":[{"id":58,"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=\/wp\/v2\/posts\/51\/revisions\/58"}],"wp:attachment":[{"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=51"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=51"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbaunchained.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=51"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}