Database sessions - Revision history

Stuart: /* More detailed sessions currently holding segments in TEMP tablespace */

2018-02-15T10:19:55Z

‎More detailed sessions currently holding segments in TEMP tablespace

Stuart: /* More detailed sessions currently holding segments in TEMP tablespace */

2018-02-15T10:19:03Z

‎More detailed sessions currently holding segments in TEMP tablespace

Stuart: /* More detailed sessions currently holding segments in TEMP tablespace */

2018-02-05T18:39:39Z

‎More detailed sessions currently holding segments in TEMP tablespace

Stuart: /* Blocking sessions in tree layout */

2017-01-17T15:30:37Z

‎Blocking sessions in tree layout

Stuart: /* Generate kill statement for ddl locking sessions */

2017-01-17T13:44:46Z

‎Generate kill statement for ddl locking sessions

Stuart: /* Show blocking sessions */

2017-01-03T15:37:52Z

‎Show blocking sessions

Stuart: /* Show a users current sql */

2016-05-10T16:01:41Z

‎Show a users current sql

Stuart: /* Show all locks, internal also */

2015-11-25T16:26:36Z

‎Show all locks, internal also

Stuart: /* Show object locks */

2015-11-25T16:23:26Z

‎Show object locks

Stuart: /* All active sql */

2015-11-25T16:01:15Z

‎All active sql

@@ Line 81: / Line 81: @@
 col program    for a25
 col tablespace for a10
 select s.sid || ',' || s.serial# sid_serial
 ,      s.username
@@ Line 110: / Line 111: @@
 or
 <pre>
 SELECT b.tablespace,
         ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,

@@ Line 106: / Line 106: @@
 ,      t.tablespace
 order  by sid_serial
+/
 </pre>

@@ Line 73: / Line 73: @@
 ===More detailed sessions currently holding segments in TEMP tablespace===
 <pre>
 col sid_serial for a10
-col module     for a20
+col username   for a15
 col program    for a25
-col tablespace for a20
+col tablespace for a10
 select s.sid || ',' || s.serial# sid_serial
 ,      s.username
@@ Line 104: / Line 108: @@
+/
 </pre>
 ===SQL statements used by TEMP sessions===
 <pre>

@@ Line 284: / Line 284: @@
   CONNECT BY PRIOR sid = blocking_session
   START WITH blocking_session IS NULL
+/
 </pre>

@@ Line 356: / Line 356: @@
 ===Generate kill statement for ddl locking sessions===
 <pre>
-  select 'alter system kill session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;'
+  select 'alter system disconnect session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;'
   from   dba_ddl_locks ddl
   ,      v$session ses

@@ Line 197: / Line 197: @@
 where  1=1
 and    a.sql_address = b.address;
 </pre>
@@ Line 215: / Line 225: @@
   and    l1.id1     = l2.id1
   and    l2.id2     = l2.id2;
 ===Show blocking sessions with lock type===
 <pre>

@@ Line 136: / Line 136: @@
          and    username like '&username'
          );
 ===Show what SQL statements are being run by all user sessions===
 <pre>

@@ Line 257: / Line 257: @@
 ,      sys.v_$session        b
 where  1=1
-and    b.username like '%&username%'
+and    a.session_id = b.sid
-and    b.sid      = &session_id
+and    b.username   like '%&username%'
+/
 </pre>
 ===Show object locks===
 <pre>

@@ Line 244: / Line 244: @@
 where  owner          like '%&userid%'
 and    ddl.session_id = ses.sid;
 </pre>
 ===Show object locks===

@@ Line 351: / Line 351: @@
 set serveroutput on
 set feedback off
 column username format a20
 column sql_text format a55 word_wrapped
 begin
-   for x in
+    dbms_output.enable(null);
-−
-   select username||'('||sid||','||serial#||') ospid = '|| process ||' program = ' || program username
+    for x in
-   ,      to_char(LOGON_TIME,' Day HH24:MI') logon_time
-   ,      to_char(sysdate,' Day HH24:MI')    current_time
+    select username||'('||sid||','||serial#||') ospid = '|| process ||' program = ' || program username
-   ,      sql_address
+    ,      to_char(LOGON_TIME,' Day HH24:MI') logon_time
-   ,      sql_hash_value
+    ,      to_char(sysdate,' Day HH24:MI')    current_time
-   from   v$session
+    ,      sql_address
-   where  1=1
+    ,      sql_hash_value
-   and    status                 = 'ACTIVE'
+    from   v$session
-   and    rawtohex(sql_address) != '00'
+    where  1=1
-   and    username is not null
+    and    status                 = 'ACTIVE'
-   ) loop
+    and    rawtohex(sql_address) != '00'
-       for y in
+    and    username is not null
+    ) loop
-       select sql_text
+        for y in
-       from   v$sqlarea
-       where  1=1
+        select sql_text
-       and    address = x.sql_address
+        from   v$sqlarea
-       ) loop
+        where  1=1
-           if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
+        and    address = x.sql_address
-               dbms_output.put_line( '--------------------' );
+        ) loop
-               dbms_output.put_line( x.username );
+            if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
-               dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
+                dbms_output.put_line( '--------------------' );
-               dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
+                dbms_output.put_line( x.username );
-           end if;
+                dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
-       end loop;
+                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
-   end loop;
+            end if;
 end;
+/