dbawiki

User Tools

Site Tools

You are here: start » database_sessions
database_sessions

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
database_sessions [2019/03/29 11:33] – external edit 127.0.0.1database_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',
 +                             1, 'Null (NULL)',
 +                             2, 'Row-S (SS)',
 +                             3, 'Row-X (SX)',
 +                             4, 'Share (S)',
 +                             5, 'S/Row-X (SSX)',
 +                             6, '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 ==== ==== Setting sessions and transactions parameters using processes ====
 If building a new database,.. If building a new database,..
Line 20: Line 138:
 from   dual; from   dual;
 undef processes 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> </code>
  
Line 180: Line 303:
 order  by s.sid 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> </code>
  
Line 258: Line 423:
 and    a.sql_address = b.address; and    a.sql_address = b.address;
 </code> </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 ==== ==== Show SQL transaction usage ====
Line 317: Line 510:
 SYS             [email protected] (TNS V1-V3)                  Select SYS             [email protected] (TNS V1-V3)                  Select
 </code> </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>
 +
 ==== Show blocking sessions using Oracle standard script ==== ==== Show blocking sessions using Oracle standard script ====
 Run this if never run before (to create needed tables) Run this if never run before (to create needed tables)
Line 329: Line 684:
 ==== Show blocking sessions ==== ==== Show blocking sessions ====
 <code> <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> </code>
  
Line 382: Line 736:
  
 ==== Blocking sessions in tree layout ==== ==== Blocking sessions in tree layout ====
-from www.guyharrison.net+from www.guyharrison.net. this one seems to be the best!
 <code> <code>
 set wrap off set wrap off
Line 424: Line 778:
     ,      row_wait_obj#                row_wait_obj#     ,      row_wait_obj#                row_wait_obj#
     ,      sql_id                       sql_id     ,      sql_id                       sql_id
-    ,      'alter system disconnect session **|| sid || ',' || serial# || ** immediate;' killer+    ,      'alter system disconnect session '''|| sid || ',' || serial# || ''' immediate;' killer
     from   v$session     from   v$session
     )     )
Line 439: Line 793:
 start  with blocking_session  is null 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> </code>
  
Line 531: Line 891:
 order  by ses.status 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> </code>
 ==== Display any long operations ==== ==== Display any long operations ====
database_sessions.1553859229.txt.gz · Last modified: 2019/03/29 11:33 by 127.0.0.1
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki