Differences

This shows you the differences between two versions of the page.

--- database_sessions [2019/02/16 13:53] – [Setting sessions and transactions parameters using processes] stuart
+++ database_sessions [2021/03/18 21:11] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+  * [[https://techgoeasy.com/enqueue-and-locks/|Oracle Locks & table locks: How it works - techgoeasy.com]]
+==== List Users Created on the Database (including dba role grantee) ====
+<code>
+CLEAR BREAKS
+CLEAR COLUMNS
+CLEAR COMPUTES
+COL username HEA "      User Name      " FOR a21
+COL default_tablespace HEA " Default Tablespace " FOR a20;
+COL temporary_tablespace HEA "Temporary Tablespace" FOR a20;
+COL grole HEA " DBA " FOR a5;
+SELECT
+   LPAD( DECODE( p.granted_role, 'DBA' , '*' ), 3 ) grole,
+   u.username, u.default_tablespace, u.temporary_tablespace
+FROM
+   dba_users u,
+   (
+   SELECT
+      grantee, granted_role
+   FROM
+      dba_role_privs
+   WHERE
+      granted_role = 'DBA'
+   ) p
+WHERE
+   u.username = p.grantee (+)
+ORDER BY
+   u.username
+/
+</code>
+==== List logged on users with O/S info ====
+<code>
+COL orauser HEA "   Oracle User   " FOR a17 TRUNC
+COL osuser HEA " O/S User " FOR a10 TRUNC
+COL ssid HEA "Sid" FOR a4
+COL sserial HEA "Serial#" FOR a7
+COL ospid HEA "O/S Pid" FOR a7
+COL slogon HEA "  Logon Time  " FOR a14
+COL sstat HEA "Status" FOR a6
+COL auth HEA "Auth" FOR a4
+COL conn HEA "Con" FOR a3
+SELECT
+   ' '||NVL( s.username, '    ????    ' ) orauser,
+   ' '||s.osuser osuser,
+   LPAD( s.sid, 4 ) ssid, LPAD( s.serial#, 6 ) sserial,
+   LPAD( p.spid, 6 ) ospid,
+   INITCAP( LOWER( TO_CHAR( logon_time, 'MONDD HH24:MI:SS' ) ) ) slogon,
+   DECODE( s.status, 'ACTIVE', ' Busy ', 'INACTIVE', ' Idle ', 'KILLED', ' Kill ', '  ??  ' ) sstat,
+   DECODE( sc.authentication_type, 'DATABASE', ' DB ', 'OS', ' OS ', ' ?? ' ) auth,
+   DECODE( s.server, 'DEDICATED', 'Dir', 'NONE', 'Mts', 'SHARED', 'Mts', '???' ) conn
+FROM
+   v$session s, v$process p,
+   (
+   SELECT
+      DISTINCT sid, authentication_type
+   FROM
+      v$session_connect_info
+   ) sc
+WHERE
+   s.paddr = p.addr AND s.sid = sc.sid
+ORDER BY
+   s.status,s.sid
+/
+</code>
+==== Check if Session Tracing is On ====
+<code>
+set lines 180
+col module for a45
+col sql_trace_waits for a20
+col sql_trace_binds for a20
+col sql_trace for a20
+select username,module,sid,sql_trace,sql_trace_waits,sql_trace_binds from v$session where sql_trace='ENABLED'
+/
+</code>
+==== Enable Session Tracing ====
+<code>
+EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(&sid,&serial);
+</code>
+==== Disable Session Tracing ====
+</code>
+EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(&sid,&serial);
+</code>
+==== Lists all Locked Objects ====
+<code>
+SET PAGESIZE 60
+SET LINESIZE 300
+COLUMN owner FORMAT A20
+COLUMN username FORMAT A20
+COLUMN object_owner FORMAT A20
+COLUMN object_name FORMAT A30
+COLUMN locked_mode FORMAT A15
+SELECT b.session_id AS sid,
+       NVL(b.oracle_username, '(oracle)') AS username,
+       a.owner AS object_owner,
+       a.object_name,
+       Decode(b.locked_mode, 0, 'None',
+, 'Null (NULL)',
+, 'Row-S (SS)',
+, 'Row-X (SX)',
+, 'Share (S)',
+, 'S/Row-X (SSX)',
+, 'Exclusive (X)',
+                             b.locked_mode) locked_mode,
+       b.os_user_name
+FROM   dba_objects a,
+       gv$locked_object b
+WHERE  a.object_id = b.object_id
+ORDER BY 1, 2, 3, 4
+/
+</code>
 ==== Setting sessions and transactions parameters using processes ====
 If building a new database,..
@@ Line 20: / Line 138: @@
 from   dual;
 undef processes
+</code>
+Then again, you could just set processes and let the database work out what to set sessions and transactions to...
+<code>
+alter system reset sessions scope=spfile sid='*';
+alter system reset transactions scope=spfile sid='*';
 </code>
@@ Line 180: / Line 303: @@
 order  by s.sid
 /
+</code>
+==== Drop a schema but drop its sessions first ====
+Sometimes dropping a user proves difficult because database processes owned by the user are still running. This script will kill all sessions owned by the schema before dropping it.
+<code>
+set serveroutput on
+variable schema2drop varchar2(50);
+execute :schema2drop := '&1';
+declare
+    open_count integer;
+begin
+    dbms_output.enable(null);
+    -- prevent any further connections
+    execute immediate 'alter user '||:schema2drop||' account lock';
+    --kill all sessions
+    for session in (
+        select sid, serial#
+        from  v$session
+        where username = ':schema2drop'
+    )
+    loop
+        dbms_output.put_line ('alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate;');
+        execute immediate 'alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate';
+    end loop;
+    -- killing is done in the background, so we need to wait a bit
+    loop
+        select count(*)
+        into open_count
+        from  v$session where username = '''||:schema2drop||''';
+        exit when open_count = 0;
+        dbms_lock.sleep(0.5);
+    end loop;
+    -- finally, it is safe to issue the drop statement
+    dbms_output.put_line ('drop user '||:schema2drop||' cascade;');
+    execute immediate 'drop user '||:schema2drop||' cascade';
+end;
+</code>
+or same thing in SQL*Plus
+<code>
 </code>
@@ Line 257: / Line 422: @@
 where  1=1
 and    a.sql_address = b.address;
+</code>
+==== Incative sessions still holding lots of memory can probably be killed (after consulting the relevant user) ====
+<code>
+SELECT
+s.sid,s.serial#,s.username
+,s.status
+,substr(s.machine,1,10)
+,s.osuser,s.module
+,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
+-- idle time
+-- days separately
+,substr('0'||trunc(last_call_et/86400),-2,2) || ':' ||
+-- hours
+substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
+-- minutes
+substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
+--seconds
+substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2) idle_time
+FROM v$session s, v$process p
+WHERE s.username IS NOT NULL
+-- use outer join to show sniped sessions in
+-- v$session that don't have an OS process
+AND p.addr(+) = s.paddr
+and s.sid=nvl(&SID,s.sid)
+ORDER BY
+idle_time desc;
+</code>
+==== Show SQL transaction usage ====
+For example after killing a session with alter system disconnect session immediate, it takes a long time to die, use this script to see how long it will take to disappear by checking how many undo blocks still remain occupied.
+<code>
+select vs.sid
+,      vs.username
+,      vt.xidusn
+,      vt.used_urec
+,      vt.used_ublk
+from   v$session     vs
+,      v$transaction vt
+where  vs.saddr = vt.ses_addr
+/
+</code>
+or a little more detailed...
+<code>
+select s.username username,
+   s.program program,
+   decode(s.command,
+,'No Command',
+,'Create Table',
+,'Insert',
+,'Select',
+,'Update',
+,'Delete',
+,'Create Index',
+,'Alter Table',
+,'Create View',
+,'Validate Index',
+,'Alter Database',
+,'Create Tablespace',
+,'Drop Tablespace',
+,'Alter Tablespace',
+,'Drop User',
+,'Analyze Table',
+,'Analyze Index',
+     s.command||': Other') command
+,      t.used_ublk
+from
+   v$session     s,
+   v$process     p,
+   v$transaction t,
+   v$rollstat    r,
+   v$rollname    n
+where s.paddr = p.addr
+and s.taddr = t.addr (+)
+and t.xidusn = r.usn (+)
+and r.usn = n.usn (+)
+and s.username='SYS'
+order by 1
+/
+</code>
+produces
+<code>
+USERNAME        PROGRAM                                            COMMAND                 USED_UBLK
+--------------- -------------------------------------------------- ---------------------- ----------
+SYS             [email protected] (TNS V1-V3)                  Update                       3751
+SYS             [email protected] (TNS V1-V3)                  Select
+</code>
+==== Other useful tables to look at for blocking objects ====
+<code>
+V$ACCESS
+DBA_DDL_LOCKS
+V$LOCKED_OBJECT
+</code>
+==== List Database Sessions including any blocking ====
+<code>
+SET HEADING ON
+SET LINESIZE 300
+SET PAGESIZE 60
+COLUMN username FORMAT A21
+COLUMN osuser FORMAT A8
+COLUMN sid FORMAT 9,999,999
+COLUMN serial# FORMAT 9,999,999
+COLUMN lockwait FORMAT A6
+COLUMN status FORMAT A8
+COLUMN module FORMAT A20
+COLUMN machine FORMAT A24
+COLUMN program FORMAT A20
+COLUMN logon_time FORMAT A20
+SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
+s.osuser,
+s.sid,
+s.serial#,
+s.lockwait,
+s.status,
+s.module,
+s.machine,
+s.program,
+TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
+FROM   v$session s
+CONNECT BY PRIOR s.sid = s.blocking_session
+START WITH s.blocking_session IS NULL
+/
+</code>
+==== Blocking Locks ====
+  * DBA_LOCKS        Show all the locks like v$lock
+  * DBA_DML_LOCKS    Shows all DML™ locks held or being requested
+  * DBA_DDL_LOCKS    Shows all DDL locks held or being requested
+  * DBA_WAITERS      Shows all sessions waiting on, but not holding waited for locks
+  * DBA_BLOCKERS     Shows non-waiting sessions holding locks being waited-on
+Sessions blocking others
+<code>
+SET LINESIZE 300
+SET PAGESIZE 60
+COLUMN username FORMAT A15
+COLUMN osuser FORMAT A8
+COLUMN sid FORMAT 99999
+COLUMN serial# FORMAT 99999
+COLUMN process_id FORMAT A5
+COLUMN wait_class FORMAT A12
+COLUMN seconds_in_wait FORMAT 9999
+COLUMN state FORMAT A17
+COLUMN blocking_session 9999
+COLUMN blocking_session_state a10
+COLUMN module FORMAT a10
+COLUMN logon_time FORMAT A20
+SELECT
+    NVL(a.username, '(oracle)') AS username,
+    a.osuser,
+    a.inst_id,
+    a.sid,
+    a.serial#,
+    a.sql_id,
+    d.spid AS process_id,
+    a.wait_class,
+    a.seconds_in_wait,
+    a.state,
+    a.blocking_instance,
+    a.blocking_session,
+    a.blocking_session_status,
+    a.module,
+    TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
+FROM
+    gv$session a,
+    gv$process d
+WHERE
+    a.paddr  = d.addr
+AND
+   a.inst_id = d.inst_id
+AND
+    a.status = 'ACTIVE'
+AND
+    a.blocking_session IS NOT NULL
+ORDER BY 1,2
+/
+</code>
+==== Blocking locks (verbose) ====
+<code>
+-- Shows blocked session, blocking session and details of the programs and machines they are running from.
+-- In addition, shows the CTIME value which details how long the blocking session has had hold of the lock
+SELECT
+    sysdate,
+    c.blocker_inst,
+    c.blocker_sid,
+    c.blocker_username,
+    c.blocker_sql_id,
+    c.blocker_program,
+    c.blocker_machine,
+    c.blocker_info,
+    c.blocker_action,
+    c.blocker_client,
+    c.blockee_inst,
+    c.blockee_sid,
+    c.blockee_username,
+    c.blockee_sql_id,
+    c.blockee_program,
+    c.blockee_machine,
+    c.blockee_action,
+    c.blockee_info,
+    c.blockee_client,
+    d.os_user_name locked_os_user_name,
+    d.oracle_username locked_username,
+    b.owner object_owner,
+    b.object_name,
+    b.object_type,
+    c.ctime "Time Locked (s)"
+FROM
+    gv$locked_object d,
+    dba_objects b,
+    (
+    SELECT
+        ( SELECT username FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_username,
+        ( SELECT sql_id FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_sql_id,
+        ( SELECT program FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_program,
+        ( SELECT machine FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_machine,
+        ( SELECT action FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_action,
+        ( SELECT client_info FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_info,
+        ( SELECT client_identifier FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_client,
+        y.sid blocker_sid,
+        y.inst_id blocker_inst,
+        ( SELECT username FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_username,
+        ( SELECT sql_id FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_sql_id,
+        ( SELECT program FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_program,
+        ( SELECT machine FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_machine,
+        ( SELECT action FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_action,
+        ( SELECT client_info FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_info,
+        ( SELECT client_identifier FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_client,
+        z.sid blockee_sid,
+        z.inst_id blockee_inst,
+        z.ctime
+    FROM
+        gv$lock y,
+        gv$lock z
+    WHERE
+        y.block = 1 AND
+        z.request > 0 AND
+        y.id1 = z.id1 AND
+        y.id2 = z.id2
+    ) c
+WHERE
+     d.OBJECT_ID = b.OBJECT_ID AND
+     d.inst_id = c.blockee_inst AND
+     d.session_id = c.blockee_sid
+/
 </code>
@@ Line 271: / Line 684: @@
 ==== Show blocking sessions ====
 <code>
- select s1.username || '@' || s1.machine||
+select s1.username||'@'||s1.machine||'('||s1.sid||','||s1.serial#||') is blocking '||
-        ' ( SID=' || s1.sid || ' )  is blocking '||
+       s2.username||'@'||s2.machine||'('||s2.sid||','||s2.serial#||')'                 blocking_status
-        s2.username || '@' || s2.machine ||
+from   v$lock l1
-        ' ( SID=' || s2.sid || ' ) '  blocking_status
+,      v$session s1
- from   v$lock l1
+,      v$lock l2
- ,      v$session s1
+,      v$session s2
- ,      v$lock l2
+where  1=1
- ,      v$session s2
+and    s1.sid     = l1.sid
- where  1=1
+and    s2.sid     = l2.sid
- and    s1.sid     = l1.sid
+and    l1.block   = 1
- and    s2.sid     = l2.sid
+and    l2.request > 0
- and    l1.block   = 1
+and    l1.id1     = l2.id1
- and    l2.request > 0
+and    l2.id2     = l2.id2
- and    l1.id1     = l2.id1
+/
- and    l2.id2     = l2.id2;
 </code>
@@ Line 324: / Line 736: @@
 ==== Blocking sessions in tree layout ====
-from www.guyharrison.net
+from www.guyharrison.net. this one seems to be the best!
 <code>
 set wrap off
@@ Line 366: / Line 778: @@
     ,      row_wait_obj#                row_wait_obj#
     ,      sql_id                       sql_id
-    ,      'alter system disconnect session **|| sid || ',' || serial# || ** immediate;' killer
+    ,      'alter system disconnect session '''|| sid || ',' || serial# || ''' immediate;' killer
     from   v$session
     )
@@ Line 381: / Line 793: @@
 start  with blocking_session  is null
 /
+</code>
+==== Kill a session with a force timeout! ====
+Saw this flashing by in a database upgrade log. Maybe a useful hidden trick.
+<code>
+ALTER SYSTEM KILL SESSION '626,37999' force timeout 0;
 </code>
@@ Line 452: / Line 870: @@
 ==== Generate kill statement for ddl locking sessions ====
 <code>
- select 'alter system disconnect session ** || ddl.session_id || ',' || ses.serial# || ** immediate;'
+col killer for a65
+ select 'alter system disconnect session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;' killer
  from   dba_ddl_locks ddl
  ,      v$session ses
@@ Line 459: / Line 878: @@
  and    owner          like upper('%&userid%')
 </code>
+==== Generate killer statements for a particular schema / user ====
+<code>
+set lines 1000 pages 100
+col username for a15
+col status   for a10
+select ses.username
+,      ses.status
+,      'alter system disconnect session ''' || ses.sid || ',' || ses.serial# || ''' immediate;' killer
+from   v$session ses
+where  1=1
+and    lower (ses.username) like lower('%&username%')
+order  by ses.status
+/
+</code>
+==== Generate killer statements from PL/SQL for INACTIVE sessions ====
+<code>
+begin
+    for i in (select * from v$session where status='INACTIVE')
+    LOOP
+        EXECUTE IMMEDIATE(q'{ALTER SYSTEM KILL SESSION '}'||i.sid||q'[,]'  ||i.serial#||q'[']'||' IMMEDIATE');
+    END LOOP;
+end;
+</code>
 ==== Display any long operations ====
 <code>
@@ Line 621: / Line 1062: @@
 group  by p.value;
 </code>