* [[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) ====
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
/
==== List logged on users with O/S info ====
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
/
==== Check if Session Tracing is On ====
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'
/
==== Enable Session Tracing ====
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(&sid,&serial);
==== Disable Session Tracing ====
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(&sid,&serial);
==== Lists all Locked Objects ====
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
/
==== Setting sessions and transactions parameters using processes ====
If building a new database,..
"SESSIONS parameter is the maximum number of sessions that can be created in the system. Every login requires a session.
It is calculated from PROCESSES parameter:
10g -> ((1.1 * PROCESSES) + 5)
11g -> ((1.5 * PROCESSES) + 22)
12.2-> ((1.5 * PROCESSES) + 22)
You may leave SESSIONS and let Database choose its value automatically."
TRANSACTIONS = (1.1 * SESSIONS)
or if updating an existing database...
select (&&processes*1.5)+22 sessions_11g_upwards
, ((&processes*1.5)+22)*1.1 transactions_11g_upwards
from dual;
undef processes
Then again, you could just set processes and let the database work out what to set sessions and transactions to...
alter system reset sessions scope=spfile sid='*';
alter system reset transactions scope=spfile sid='*';
==== Sessions sorted by login time ====
set lines 200
set pages 999
col killer format a15
col osuser format a15
col login_time format a15
select s.username
, s.osuser
, s.sid || ',' || s.serial# "Killer"
, p.spid "OS PID"
, s.status
, to_char(s.logon_time, 'dd-mon-yy hh24:mi') login_time
, s.last_call_et
from v$session s
, v$process p
where 1=1
and s.paddr = p.addr
and s.username is not null
order by login_time;
==== Free space in TEMP tablespaces ====
select tablespace_name temp_name
, tablespace_size/1024/1024 temp_size
, allocated_space/1024/1024 temp_alloc
, free_space/1024/1024 temp_free
from dba_temp_free_space
/
==== Live TEMP free space monitoring ====
select a.tablespace_name tablespace
, d.mb_total mb_total
, sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_used
, d.mb_total - sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_free
from v$sort_segment a
, (
select b.name
, c.block_size
, sum (c.bytes) / 1024 / 1024 mb_total
from v$tablespace b
, v$tempfile c
where b.ts# = c.ts#
group by b.name
, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name
, d.mb_total
/
==== Sessions currently holding segments in TEMP tablespace ====
select b.tablespace
, b.segfile#
, b.segblk#
, b.blocks
, a.sid
, a.serial#
, a.username
, a.osuser, a.status
from v$session a
, v$sort_usage b
where a.saddr = b.session_addr
order by b.tablespace
, b.segfile#
, b.segblk#
, b.blocks
/
==== More detailed sessions currently holding segments in TEMP tablespace ====
set lines 1000 pages 1000
col sid_serial for a10
col username for a15
col osuser for a10
col spid for a10
col module for a35
col program for a25
col tablespace for a10
select s.sid || ',' || s.serial# sid_serial
, s.username
, s.osuser
, p.spid
, s.module
, s.program
, sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used
, t.tablespace
, count(*) sort_ops
from v$sort_usage t
, v$session s
, dba_tablespaces tbs
, v$process p
where t.session_addr = s.saddr
and s.paddr = p.addr and t.tablespace = tbs.tablespace_name
group by s.sid
, s.serial#
, s.username
, s.osuser
, p.spid
, s.module
, s.program
, tbs.block_size
, t.tablespace
order by sid_serial
/
or
SET PAGESIZE 50
SET LINESIZE 300
COLUMN tablespace FORMAT A20
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A50
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.inst_id as Instance,
a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program,
a.status,
a.sql_id
FROM gv$session a,
gv$sort_usage b,
gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
AND a.inst_id=b.inst_id
AND a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks
/
==== SQL statements used by TEMP sessions ====
select s.sid || ',' || s.serial# sid_serial
, s.username
, t.blocks * tbs.block_size / 1024 / 1024 mb_used
, t.tablespace
, t.sqladdr address
, q.hash_value
, q.sql_text
from v$sort_usage t
, v$session s
, v$sqlarea q
, dba_tablespaces tbs
where t.session_addr = s.saddr
and t.sqladdr = q.address (+)
and t.tablespace = tbs.tablespace_name
order by s.sid
/
==== 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.
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;
or same thing in SQL*Plus
==== Show a users current sql ====
select sql_text
from v$sqlarea
where 1=1
and (address, hash_value) in
(
select sql_address
, sql_hash_value
from v$session
where 1=1
and username like '&username'
);
==== Top 10 SQL statements ====
Lists all SQL statements in the library cache with CPU time > &1 &1 % of the total of all statements
define CPUTimePct = '&1'
@sqlplus_settings
@nls_settings
@header_title 155 DC 'All statements in the library cache with a total CPU time >= &CPUTimePct % of all statements together'
COLUMN cpu_time_total FORMAT 9G999G990 HEADING "CPU time|total"
COLUMN sql_text FORMAT A60 HEADING "Sql text"
COLUMN pct FORMAT 990D9 HEADING "%"
COLUMN buffer_gets_per_exec FORMAT 99G999G999G999 HEADING "Buffer gets|per exec"
COLUMN wait_per_exec FORMAT 999G990D9 HEADING "Wait time|per exec"
COLUMN cpu_per_exec FORMAT 999G990D9 HEADING "CPU time|per exec"
COLUMN disk_reads_per_exec FORMAT 999G999G999 HEADING "Disk reads|per exec"
COLUMN executions FORMAT 99G999G999 HEADING "Executions"
COLUMN sql_id FORMAT A13 HEADING "Sql id"
SET FEEDBACK ON
SET ARRAYSIZE 1
SET LONG 2000
SET RECSEP WRAPPED
SET PAGESIZE 100
WITH total_cpu_time
AS ( SELECT sum(cpu_time) total_cpu FROM v$sqlarea )
SELECT cpu_time/1000000 cpu_time_total,
(cpu_time*100)/total_cpu pct,
sql_text,
executions,
cpu_time/(decode(executions,0,1,executions)*1000000) cpu_per_exec,
(elapsed_time-cpu_time)/(decode(executions,0,1,executions)*1000000) wait_per_exec,
buffer_gets/decode(executions,0,1,executions) buffer_gets_per_exec,
disk_reads/decode(executions,0,1,executions) disk_reads_per_exec,
sql_id
FROM v$sqlarea,
total_cpu_time
WHERE cpu_time >= &CPUTimePct*total_cpu/100
ORDER BY cpu_time desc;
undefine CPUTimePct
SET ARRAYSIZE 20
SET LONG 200
==== Show what SQL statements are being run by all user sessions ====
col username for a12
col sid for 99999
col sql_text for a200
set lines 1000 pages 1000 long 100000
select a.username
, to_char(a.logon_time,'DD-MON-YY HH24:MI:SS') dt
, a.sid
, a.serial#
, b.sql_text
from v$session a
, v$sqlarea b
where 1=1
and a.sql_address = b.address;
==== Incative sessions still holding lots of memory can probably be killed (after consulting the relevant user) ====
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;
==== 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.
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
/
or a little more detailed...
select s.username username,
s.program program,
decode(s.command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'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
/
produces
USERNAME PROGRAM COMMAND USED_UBLK
--------------- -------------------------------------------------- ---------------------- ----------
SYS sqlplus@hn5205.cln.be (TNS V1-V3) Update 3751
SYS sqlplus@hn5205.cln.be (TNS V1-V3) Select
==== Other useful tables to look at for blocking objects ====
V$ACCESS
DBA_DDL_LOCKS
V$LOCKED_OBJECT
==== List Database Sessions including any blocking ====
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
/
==== 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
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
/
==== Blocking locks (verbose) ====
-- 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
/
==== Show blocking sessions using Oracle standard script ====
Run this if never run before (to create needed tables)
@?/rdbms/admin/catblock
then to show locking processes in a tree structure (non indented process is blocking indented sessions)
@?/rdbms/admin/utllockt
==== Show blocking sessions ====
select s1.username||'@'||s1.machine||'('||s1.sid||','||s1.serial#||') is blocking '||
s2.username||'@'||s2.machine||'('||s2.sid||','||s2.serial#||')' blocking_status
from v$lock l1
, v$session s1
, v$lock l2
, v$session s2
where 1=1
and s1.sid = l1.sid
and s2.sid = l2.sid
and l1.block = 1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
/
==== Show blocking sessions with lock type ====
select nvl(s.username,'Internal') username
, nvl(s.terminal,'None') terminal
, l.sid||','||s.serial# killer
, u1.name||'.'||substr(t1.name,1,20) tab
, decode ( l.lmode
,1, 'No Lock'
,2, 'Row Share'
,3, 'Row Exclusive'
,4, 'Share'
,5, 'Share Row Exclusive'
,6, 'Exclusive'
, null) lmode
, decode ( l.request
,1, 'No Lock'
,2, 'Row Share'
,3, 'Row Exclusive'
,4, 'Share'
,5, 'Share Row Exclusive'
,6, 'Exclusive'
, null) request
from v$lock l
, v$session s
, sys.user$ u1
, sys.obj$ t1
where l.sid = s.sid
and t1.obj# = decode(l.id2,0,l.id1,l.id2)
and u1.user# = t1.owner#
and s.type != 'BACKGROUND'
and S.sid in ( select blocking_session from v$session where blocking_session is not null )
order by 1,2,5
/
==== Blocking sessions in tree layout ====
from www.guyharrison.net. this one seems to be the best!
set wrap off
column sid format a8
column object_name format a20
column sql_text format a150
set echo on
WITH sessions AS
(SELECT /*+materialize*/
sid, blocking_session, row_wait_obj#, sql_id
FROM v$session)
SELECT LPAD(' ', LEVEL ) || sid sid, object_name,
substr(sql_text,1,240) sql_text
FROM sessions s
LEFT OUTER JOIN dba_objects
ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql
USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions)
OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
START WITH blocking_session IS NULL
/
... and prepare the killer statement
set wrap off lines 2000 echo on
column sid for a8
column username for a12
column terminal for a12
column object_name for a20
column sql_text for a150
column killer for a60
with sessions as
(
select /*+materialize*/ sid sid
, nvl(username,'Internal') username
, serial# serial#
, nvl(terminal,'None') terminal
, blocking_session blocking_session
, row_wait_obj# row_wait_obj#
, sql_id sql_id
, 'alter system disconnect session '''|| sid || ',' || serial# || ''' immediate;' killer
from v$session
)
select lpad(' ', level)||sid sid
, object_name object_name
, substr(sql_text,1,240) sql_text
, decode(level,1,s.killer,null) killer
from sessions s
left outer join dba_objects on (object_id = row_wait_obj#)
left outer join v$sql using (sql_id)
where sid in (select blocking_session from sessions)
or blocking_session is not null
connect by prior sid = blocking_session
start with blocking_session is null
/
==== Kill a session with a force timeout! ====
Saw this flashing by in a database upgrade log. Maybe a useful hidden trick.
ALTER SYSTEM KILL SESSION '626,37999' force timeout 0;
==== Show all ddl locks in the system ====
set lines 1000
col object for a40
select ses.username
, ddl.session_id
, ses.serial#
, owner || '.' || ddl.name object
, ddl.type
, ddl.mode_held
from dba_ddl_locks ddl
, v$session ses
where owner like '%&userid%'
and ddl.session_id = ses.sid;
==== Show all locks, internal also ====
select nvl(b.username,'SYS') username
, session_id
, lock_type
, mode_held
, mode_requested
, lock_id1
, lock_id2
from sys.dba_lock_internal a
, sys.v_$session b
where 1=1
and a.session_id = b.sid
and b.username like '%&username%'
and b.sid = &session_id
/
==== Show object locks ====
set linesize 150;
set head on;
col sid_serial form a13
col ora_user for a15;
col object_name for a35;
col object_type for a10;
col lock_mode for a15;
col last_ddl for a8;
col status for a10;
break on sid_serial;
SELECT l.session_id||','||v.serial# sid_serial,
l.ORACLE_USERNAME ora_user,
o.object_name,
o.object_type,
DECODE(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR(l.locked_mode)
) lock_mode,
o.status,
to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
and l.SESSION_ID=v.sid
order by 2,3;
==== Generate kill statement for ddl locking sessions ====
col killer for a65
select 'alter system disconnect session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;' killer
from dba_ddl_locks ddl
, v$session ses
where 1=1
and ddl.session_id = ses.sid;
and owner like upper('%&userid%')
==== Generate killer statements for a particular schema / user ====
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
/
==== Generate killer statements from PL/SQL for INACTIVE sessions ====
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;
==== Display any long operations ====
set lines 100
set pages 100
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'dd-mon-yy hh24:mi') started
, time_remaining remaining
, message
from v$session_longops
where 1=1
and time_remaining != 0
order by time_remaining desc
==== Show waits for (blocking) datapump sessions ====
set pages 100 lines 1000
col p1 for 999999999999
col p2 for 999999999999
col p3 for 999999999999
select sid
, event
, p1
, p2
, p3
, seconds_in_wait
from v$session_wait
where sid in
(
select sid
from v$session vs
, v$process vp
, dba_datapump_sessions dp
where vp.addr = vs.paddr(+)
and vs.saddr = dp.saddr
)
==== Show current sessions that perform a lot of hard parses ====
set pages 1000 lines 1000
col username for a15
select vss.sid
, vs.username
, vsn.name
, vss.value
, round((sysdate-vs.logon_time)*24) hours_connected
from v$sesstat vss
, v$statname vsn
, v$session vs
where 1=1
and vss.sid = vs.sid
and vss.statistic# = vsn.statistic#
and vss.value > 0
and vsn.name like '%parse count%'
==== All active sql ====
set serveroutput on
set feedback off
column username format a20
column sql_text format a55 word_wrapped
begin
dbms_output.enable(null);
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
, sql_hash_value
from v$session
where 1=1
and status = 'ACTIVE'
and rawtohex(sql_address) != '00'
and username is not null
) loop
for y in
(
select sql_text
from v$sqlarea
where 1=1
and address = x.sql_address
) 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 || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
==== Identify the number of SQL statements in the library cache that are not using bind variables ====
These SQL statements cause expensive hard parse events in the shared pool
select count('x') num_sql
, sum(decode(executions, 1, 1, 0)) one_use_sql
, sum(sharable_mem)/1024/1024 meg_used
, sum(decode ( executions
, 1, sharable_mem
, 0
)
)/1024/1024 mb_per
from v$sqlarea
where sharable_mem > 0;
==== List number of open cursors by user ====
set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where 1=1
and sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value;
==== Show SQL statements generating the high number of cursors ====
Use SID from query above.
select sid
, sql_text
, count('e') open_crsrs
, user_name
from v$open_cursor
where 1=1
and sid = &SID
group by sid
, sql_text
, user_name
order by 3;
==== Show current setting for max open cursors and highest number used since startup ====
select max(a.value) highest_open_crsrs
, p.value max_open_crsrs
from v$sesstat a
, v$statname b
, v$parameter p
where 1=1
and a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name = 'open_cursors'
group by p.value;