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
/
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
/
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' /
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(&sid,&serial);
</code> EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(&sid,&serial); </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
/
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='*';
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;
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 /
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
/
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 /
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
/
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 /
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
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'
);
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
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;
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;
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 [email protected] (TNS V1-V3) Update 3751 SYS [email protected] (TNS V1-V3) Select
V$ACCESS DBA_DDL_LOCKS V$LOCKED_OBJECT
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
/
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
/
-- 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
/
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
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
/
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
/
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
/
Saw this flashing by in a database upgrade log. Maybe a useful hidden trick.
ALTER SYSTEM KILL SESSION '626,37999' force timeout 0;
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;
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 /
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;
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%')
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
/
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;
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
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
)
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%'
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;
/
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;
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;
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;
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;