Tuesday, 5 May 2015

ORA-38784: Cannot create restore point

ORA-01153: an incompatible media recovery is active

SQL> create restore point RP_15042015 guarantee flashback database ;
create restore point RP_15042015 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'RP_15042015'.
ORA-01153: an incompatible media recovery is active

Check is RMAN process running if yes------- then kill all the process !!!!!!!!!!!!!!
1.
ps -ef|grep -i rman

2.
select SID,SERIAL#,PROGRAM from v$session where PROGRAM like '%rman%';

195 35085 rman@*******db (TNS V1-V3)
629 58871 rman@*******db (TNS V1-V3)
1202 47627 rman@*******db (TNS V1-V3)

3.
check for any datafiles that is in BACKUP mode.

SQL> select * from v$backup where status = 'ACTIVE';

script to check rman process ...

set head off verify off echo off pages 1500 linesize 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
DEFINE _br=chr(10)
select
' alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' || &_br ||
'Inst id .............................................: '||s.inst_id,
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '||s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from gv$session s, gv$process p
where 1=1
and p.addr=s.paddr
and s.program like '%rman%'; -------- to check the rman process !!!!
set head on verify on echo on

alter system kill Session '50,4771,@1' immediate;
Inst id .............................................: 1
Session Id.............................................: 50
Serial Num..............................................: 4771
User Name ..............................................: SYS
Session Status .........................................: ACTIVE
Client Process Id on Client Machine ....................: *10962*
Server Process ID ......................................: 11233
Sql_Address ............................................: 00
Sql_hash_value .........................................: 0
Schema Name ..... ......................................: SYS
Program ...............................................: rman@********db (TNS V1-V3)
Module .................................................: backup incr datafile
Action .................................................: 0000267 STARTED16
Terminal ...............................................:
Client Machine .........................................: ********db
LAST_CALL_ET ...........................................: 274
S.LAST_CALL_ET/3600 ....................................: .076111111111111111111111111111111111111

alter system kill Session '536,55845,@1' immediate;
Inst id .............................................: 1
Session Id.............................................: 536
Serial Num..............................................: 55845
User Name ..............................................: SYS
Session Status .........................................: INACTIVE
Client Process Id on Client Machine ....................: *10962*
Server Process ID ......................................: 10972
Sql_Address ............................................: 00
Sql_hash_value .........................................: 0
Schema Name ..... ......................................: SYS
Program ...............................................: rman@********db (TNS V1-V3)
Module .................................................: rman@********db (TNS V1-V3)
Action .................................................: 0000010 FINISHED131
Terminal ...............................................:
Client Machine .........................................: ********db
LAST_CALL_ET ...........................................: 274
S.LAST_CALL_ET/3600 ....................................: .076111111111111111111111111111111111111

alter system kill Session '677,48685,@1' immediate;
Inst id .............................................: 1
Session Id.............................................: 677
Serial Num..............................................: 48685
User Name ..............................................: SYS
Session Status .........................................: INACTIVE
Client Process Id on Client Machine ....................: *10962*
Server Process ID ......................................: 10973
Sql_Address ............................................: 00
Sql_hash_value .........................................: 0
Schema Name ..... ......................................: SYS
Program ...............................................: rman@********db (TNS V1-V3)
Module .................................................: rman@********db (TNS V1-V3)
Action .................................................:
Terminal ...............................................:
Client Machine .........................................: ********db
LAST_CALL_ET ...........................................: 9
S.LAST_CALL_ET/3600 ....................................: .0025

set linesize 200 numf 999999999999999
col TIME format a31
col name format a25
col STORAGE_SIZE format 9999999999999999
col DATABASE_INCARNATION# format 99999999999
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------- ---------- ------------------------------- --------------------- --- -----------------
RP_15042015 692690908 13-APR-15 12.57.21.000000000 PM 3 YES 52428800

1 row selected.

Monday, 27 April 2015

pdksh-5.2.14 is missing on Centos for Oracle prerequisite

Oracle prerequisite pdksh-5.2.14 is missing

Download pdksh form following site

wget ftp://ftp.pbone.net/mirror/archive.download.redhat.com/pub/redhat/linux/6.1/en/os/i386/RedHat/RPMS/pdksh-5.2.14-1.i386.rpm

after download I tried this , I am getting following error

[root@oraudev ~]# rpm -ivh pdksh-5.2.14-1.i386.rpm

error: Failed dependencies:

ld-linux.so.2 is needed by pdksh-5.2.14-1.i386

libc.so.6 is needed by pdksh-5.2.14-1.i386

libc.so.6(GLIBC_2.0) is needed by pdksh-5.2.14-1.i386

libc.so.6(GLIBC_2.1) is needed by pdksh-5.2.14-1.i386

solution

Install this rpm as follow

ls -ltr /root/pdksh-5.2.14-1.i386.rpm

-rw-r--r-- 1 root root 173189 Apr 26 08:17 /root/pdksh-5.2.14-1.i386.rpm

[root@oraudev ~]# yum localinstall /root/pdksh-5.2.14-1.i386.rpm <<<<<<<<<<<<<<<<<<-----

Loaded plugins: fastestmirror, refresh-packagekit, security

Setting up Local Package Process

Examining /root/pdksh-5.2.14-1.i386.rpm: pdksh-5.2.14-1.i386

Marking /root/pdksh-5.2.14-1.i386.rpm to be installed

Loading mirror speeds from cached hostfile

* base: centos.openitc.uk

* extras: mirror.cov.ukservers.com

* updates: www.mirrorservice.org

Resolving Dependencies

--> Running transaction check

---> Package pdksh.i386 0:5.2.14-1 will be installed

--> Processing Dependency: ld-linux.so.2 for package: pdksh-5.2.14-1.i386

--> Processing Dependency: libc.so.6 for package: pdksh-5.2.14-1.i386

--> Processing Dependency: libc.so.6(GLIBC_2.0) for package: pdksh-5.2.14-1.i386

--> Processing Dependency: libc.so.6(GLIBC_2.1) for package: pdksh-5.2.14-1.i386

--> Running transaction check

---> Package glibc.i686 0:2.12-1.149.el6_6.7 will be installed

--> Processing Dependency: libfreebl3.so(NSSRAWHASH_3.12.3) for package: glibc-2.12-1.149.el6_6.7.i686

--> Processing Dependency: libfreebl3.so for package: glibc-2.12-1.149.el6_6.7.i686

--> Running transaction check

---> Package nss-softokn-freebl.i686 0:3.14.3-22.el6_6 will be installed

--> Finished Dependency Resolution

Sunday, 8 March 2015

Oracle Session info

Session Info ...

http://anuj-singh.blogspot.com/2016/09/oracle-session-count-info-and-kill-and.html

http://anuj-singh.blogspot.com/2021/05/sql-history.html



set linesize 300 pagesize 300
col "hr:mi:ss" for a12
col p3 for 9999999999999
col ACTIVE_SECONDS  for 9999999999
col username for a15
col kill for a17

SELECT
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
    s.username,
    s.status,
    s.last_call_et AS active_seconds,
        TO_CHAR(TRUNC(s.last_call_et/3600),'FM9900') || ':' ||
    TO_CHAR(TRUNC(MOD(s.last_call_et,3600)/60),'FM00') || ':' ||
    TO_CHAR(MOD(s.last_call_et,60),'FM00')
         "hr:mi:ss",
    s.sql_id,
        s.p1,
        s.p2,
        s.p3,
    q.sql_fulltext AS sql_text,
FINAL_BLOCKING_SESSION_STATUS
FROM
    gv$session s
JOIN
    gv$sql q ON s.sql_id = q.sql_id
             AND s.inst_id = q.inst_id
WHERE
    s.status = 'ACTIVE'
    AND s.type <> 'BACKGROUND'
   -- AND s.last_call_et > 60
ORDER BY
    s.last_call_et DESC
/






SET pagesize 50
SET linesize 155
SET wrap off
COLUMN name format a11 head "Consumer|Group"
COLUMN sid format 9999
COLUMN username format a16
COLUMN CONSUMED_CPU_TIME head "Consumed|CPU time|(s)" format 999999.9
COLUMN IO_SERVICE_TIME head "I/O time|(s)" format 9999.9
COLUMN CPU_WAIT_TIME head "CPU Wait|Time (s)" FOR 99999
COLUMN CPU_WAITS head "CPU|Waits" format 99999
COLUMN YIELDS head "Yields" format 99999
COLUMN state format a10
COLUMN osuser format a8
COLUMN machine format a16
COLUMN PROGRAM format a12
 
SELECT
          rcg.name
        , rsi.sid
        , s.username
        , rsi.state
        , rsi.YIELDS
        , rsi.CPU_WAIT_TIME / 1000 AS CPU_WAIT_TIME
        , rsi.CPU_WAITS
        , rsi.CONSUMED_CPU_TIME / 1000 AS CONSUMED_CPU_TIME
        , rsi.IO_SERVICE_TIME /1000 AS IO_SERVICE_TIME
        , s.osuser
        , s.program
        , s.machine
        , sw.event
FROM V$RSRC_SESSION_INFO rsi INNER JOIN v$rsrc_consumer_group rcg
ON rsi.CURRENT_CONSUMER_GROUP_ID = rcg.id
INNER JOIN v$session s ON rsi.sid=s.sid
INNER JOIN v$session_wait sw ON s.sid = sw.sid
WHERE rcg.id !=0 -- _ORACLE_BACKGROUND_GROUP_
and (sw.event != 'SQL*Net message from client' or rsi.state='RUNNING')
ORDER BY rcg.name, s.username,rsi.cpu_wait_time + rsi.IO_SERVICE_TIME + rsi.CONSUMED_CPU_TIME ASC, rsi.state, sw.event, s.username, rcg.name,s.machine,s.osuser
/




SET LINESIZE 250
SET PAGESIZE 100
SET TRIMSPOOL ON

COLUMN consumer_group   FORMAT A18       HEADING "Consumer|Group"
COLUMN sid              FORMAT 99999     HEADING "SID"
COLUMN username         FORMAT A16       HEADING "USERNAME"
COLUMN state            FORMAT A10       HEADING "STATE"
COLUMN cpu_time         FORMAT 999990.9  HEADING "CPU time|(s)"
COLUMN io_megabytes     FORMAT 99999999990.9  HEADING "I/O MB"
COLUMN program          FORMAT A30       HEADING "PROGRAM"
COLUMN event            FORMAT A30       HEADING "EVENT"
col kill for a17
SELECT 
s.con_id,
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
sql_id,
    s.resource_consumer_group                        AS consumer_group,
  --  s.sid,
    s.username,
    s.status                                         AS state,
    ROUND(NVL(stm.cpu_time, 0) / 1000000, 2)         AS cpu_time,
    ROUND((io.block_gets + io.consistent_gets + io.physical_reads) * 8192 / 1024 / 1024, 2) AS io_megabytes,
    s.program,
    s.event
FROM 
    gv$session s
LEFT JOIN (
    SELECT inst_id, sid, value AS cpu_time 
    FROM gv$sess_time_model 
    WHERE stat_name = 'DB CPU'
) stm ON s.inst_id = stm.inst_id AND s.sid = stm.sid
LEFT JOIN gv$sess_io io 
    ON s.inst_id = io.inst_id AND s.sid = io.sid
WHERE 
    s.status = 'ACTIVE'
    AND s.username IS NOT NULL
and USERNAME not in ('GGATE','SYS','SYSTEM','C##GGATE','DBFS_USER')
and EVENT not in ('PL/SQL lock timer')
ORDER BY 
    --cpu_time DESC
    io_megabytes desc
;



 set linesize 120 pagesize 500
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '447arykc3gcw', cursor_child_no => 0, FORMAT => 'TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));
SELECT * FROM TABLE(DBMS_XPLAN.display_awr(sql_id=>'447arykc3gcw',format=>'ALLSTATS LAST +cost +bytes'));

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set linesize 200 pagesize 300
col "Kill--" for a150
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' /* '||action||' '||sql_id||' '||event||' started on '||logon_time||'*/;' "Kill--" from gv$session 
where 1=1
-- module='DBMS_SCHEDULER' 
and USERNAME='SYS'
and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^'
;

set linesize 300 pagesize 300
col sql_text for a50 wrap
col kill for a17 
col event for a25
col osuser for a9
select /*+ gather_plan_statistics */ 
 ''''||sess.sid ||','|| sess.serial#||',@'||sess.inst_id ||'''' kill 
--serial#, sid
,osuser,  executions, sql.sql_id ,sql.child_number, event,p1,p2,sql_text
from gv$session sess join gv$sql sql 
on  (sess.sql_address = sql.address and sess.inst_id=sql.inst_id ) 
where sess.status = 'ACTIVE'
and sess.sid != (select distinct sid from sys.v_$mystat)

/

set linesize 300 pagesize 1000
 col PLAN_TABLE_OUTPUT for a100
SELECT t.* FROM gv$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')) t WHERE s.sql_id in (select sql_id from gv$session where sql_id is not null 
--and USERNAME='USER' 
and sql_id='XXXXXX'
 )  ;

==========

col username for a23

col serv_mod_action for a50

col tr for a2

col event for a32

col sql_id for a13

col sql_dT for 999999

col call_dT for 9999999

col W_dT for 9999999

col obj# for 999999999999

col kill for a16

SELECT

'''' || sid || ',' || serial# || ',@' || inst_id || '''' AS kill,

username ||

CASE

WHEN regexp_substr(program, ' \(.*') <> ' (TNS'

THEN regexp_substr(program, ' \(.*')

END AS username,

sql_id,

ROUND((SYSDATE - sql_exec_start) * 24 * 3600, 1) AS sql_dT,

last_call_et AS call_dT,

CASE

WHEN state = 'WAITING'

THEN ROUND(wait_time_micro / 1000000, 2)

ELSE ROUND(time_since_last_wait_micro / 1000000, 2)

END AS w_dT,

DECODE(state, 'WAITING', event, 'CPU') AS event,

service_name || ' ' || SUBSTR(module, 1, 20) || ' ' || action AS serv_mod_action,

NULLIF(row_wait_obj#, -1) AS obj#,

DECODE(taddr, NULL, NULL, 'NN') AS tr

FROM gv$session

WHERE 1 = 1

-- AND module = 'DBMS_SCHEDULER'

-- AND ((state = 'WAITING' AND wait_class <> 'Idle')

-- OR (state <> 'WAITING' AND status = 'ACTIVE'))

AND (machine, port) <> (

SELECT machine, port

FROM v$session

WHERE sid = SYS_CONTEXT('USERENV', 'SID')

)

AND event NOT LIKE '%message from client%'

ORDER BY inst_id, sql_id;

col kill for a70

SELECT 'ALTER SYSTEM KILL SESSION '''

|| sid || ',' || serial# || ',@' || inst_id

|| ''' IMMEDIATE;' kill ,sql_id,USERNAME

FROM gv$session

WHERE status = 'ACTIVE'

AND username IS NOT NULL;

KILL SQL_ID USERNAME

---------------------------------------------------------------------- ------------- -----------------------

ALTER SYSTEM KILL SESSION '414,1512,@1' IMMEDIATE; 8c6a13g04913a SYS

ALTER SYSTEM KILL SESSION '817,31845,@1' IMMEDIATE; SYS

SELECT

s.inst_id,

s.sid,

s.serial#,

p.spid

FROM gv$session s

JOIN gv$process p

ON s.paddr = p.addr

AND s.inst_id = p.inst_id

WHERE s.sid = 6398;

WITH x

AS (SELECT *

FROM dba_hist_active_sess_history

JOIN

dba_hist_snapshot

USING (snap_id, dbid, instance_number)

WHERE 1=1

begin_interval_time BETWEEN TO_DATE(

'28-MAY-2024 21:30'

,'DD-MON-YYYY HH24:MI'

)

AND TO_DATE(

'28-MAY-2024 23:59'

,'DD-MON-YYYY HH24:MI'

)

and begin_interval_time > sysdate -1/24

)

SELECT TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value ,session_id, event, COUNT( * )

FROM x

GROUP BY TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value ,session_id, event;

set linesize 300

col MODULE for a14

col ACTION for a24

col EVENT for a24

WITH y AS (

SELECT * FROM dba_hist_active_sess_history

WHERE 1=1

-- snap_id IN (xxxx, xxx)

--and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^'

--and action like 'ORA$AT^_%' escape '^'

--and sample_time >sysdate -30

and sample_time > sysdate - interval '15' minute

and MODULE not in ('GoldenGate','DBMS_SCHEDULER','APEX Listener','KTSJ')

)

SELECT TRUNC(sample_time) sample_time, module, action, sql_id, sql_plan_hash_value ,session_id, event, COUNT( * )

FROM y

GROUP BY TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value ,session_id, event;

SAMPLE_TI CON_ID MODULE ACTION SQL_ID SQL_PLAN_HASH_VALUE SESSION_ID EVENT COUNT(*)

--------- ---------- ------------------------ ------------------------ ------------- ------------------- ---------- ------------------------------ ----------

09-NOV-25 5 backup incr datafile 0000335 STARTED16 0 503 Backup: MML write backup piece 180

09-NOV-25 5 sqlplus@1331740-3pors03. 2u7vcdc1s2pn6 73398623 891 db file scattered read 23

corp.metrobank.plc.uk (T

09-NOV-25 5 dbfs_client@1331740-3por 0 2364 SQL*Net vector data to client 3

s03.corp.metrobank.plc.u


set linesize 300 pagesize 300
col MODULE for a24
col ACTION for a24
col EVENT for a30
WITH y   AS (
SELECT   *  FROM   gV$ACTIVE_SESSION_HISTORY
            WHERE 1=1
-- snap_id IN (xxxx, xxx)
--and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^'
--and action like 'ORA$AT^_%' escape '^'
--and sample_time >sysdate -30
and sample_time >  sysdate - interval '3' minute
and MODULE not in ('GoldenGate','DBMS_SCHEDULER','APEX Listener','KTSJ')
and event is not null                                    
 )
  SELECT   TRUNC(sample_time) sample_time,con_id, module, action, sql_id, sql_plan_hash_value ,session_id, event, COUNT( * )
    FROM   y
GROUP BY   TRUNC(sample_time),con_id, module, action, sql_id, sql_plan_hash_value ,session_id, event
 having COUNT( * ) >2
;



col inst_sid heading "INST_ID|:SID" format a7

col username format a10
col machine format a12
col sql_exec_start heading "SQL|START|D HH:MM:SS" format a11
col sql_id format a13
col sql_text format a40
col event format a33
col wait_sec heading "WAIT|(SEC)" format 99999
set linesize 200  pagesize 200
 col kill for a17
select
''''||ses.sid ||','|| ses.serial#||',@'||ses.inst_id ||'''' kill ,
-- ses.inst_id || chr(58) || ses.sid as inst_sid,
       username,
       (sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start,
       ses.sql_id,
       substr(sql.sql_text, 1, 40) sql_text,
       substr(case time_since_last_wait_micro
                when 0 then (case wait_class when 'Idle' then 'IDLE: ' || event else event end)
                else 'ON CPU'
              end, 1, 33) event,
       (case time_since_last_wait_micro
          when 0 then wait_time_micro
          else time_since_last_wait_micro
        end) / 1000000 wait_secD
  from gv$session ses,
       gv$sqlstats sql
 where ses.inst_id || chr(58) || ses.sid <> sys_context('USERENV', 'INSTANCE') || chr(58) || sys_context('USERENV', 'SID')
   and username is not null
   and status = 'ACTIVE'
   and ses.sql_id = sql.sql_id(+)
 order by sql_exec_start,
          username,
          ses.sid,
          ses.sql_id;

set linesize 300 pagesize 1000

col PLAN_TABLE_OUTPUT for a100

SELECT t.* FROM gv$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')) t WHERE s.sql_id in (select sql_id from gv$session where sql_id is not null

--and USERNAME='USER'

and sql_id='XXXXXX'

) ;

-- session.sql

DEFINE _br=chr(10)
DEFINE _trcsid_session_serial="NA"
COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial
DEFINE _trcsid_session_serial="NA"
SET TERMOUT ON VERIFY OFF HEADING OFF LINESIZE 155 SQLBL ON ECHO OFF TRIMSPOOL ON TRIMOUT ON FEEDBACK ON
--
-- Show information about the SESSION
SELECT distinct
        '    alter system kill Session  ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;'          || &_br ||
        '    INST_ID# ......... ' || RPAD(s.INST_ID           , 50, ' ') ||   'SID   ............ ' || s.sid               || &_br ||
        '    SERIAL# .......... ' || RPAD(s.serial#           , 50, ' ') ||   'USERNAME ......... ' || s.username          || &_br ||
        '    SPID  ............ ' || RPAD(p.spid              , 50, ' ') ||   'OPID ............. ' || p.pid               || &_br ||
        '    PADDR  ........... ' || RPAD(s.paddr             , 50, ' ') ||   'SADDR  ........... ' || s.saddr             || &_br ||
        '    AUDSID  .......... ' || RPAD(s.audsid            , 50, ' ') ||   'OSUSER  .......... ' || s.osuser            || &_br ||
        '    PROCESS  ......... ' || RPAD(s.process           , 50, ' ') ||   'PROGRAM  ......... ' || s.program           || &_br ||
        '    MACHINE  ......... ' || RPAD(s.machine           , 50, ' ') ||   'MODULE  .......... ' || s.module            || &_br ||
        '    HASH_VALUE. ...... ' || RPAD(s.sql_hash_value    , 50, ' ') ||   'PREV_HASH_VALUE .. ' || s.prev_hash_value   || &_br ||
        '    SQL_ID ........... ' || rpad(s.sql_id            , 50, ' ') ||   'CHILD_NUMBER# .... ' || s.sql_child_number  || &_br ||
        '    PREV_SQL_ID ...... ' || rpad(s.prev_sql_id       , 50, ' ') ||   'PREV_CHILD# ...... ' || s.prev_child_number || &_br ||
        '    WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS        , 50, ' ') ||   'WAIT_TIME   ...... ' || w.WAIT_TIME         || &_br ||
        '    STATE      ........' || rpad(s.STATE             , 50, ' ') ||   'SERVICE NAME ......' || s.SERVICE_NAME      || &_br ||
        '    LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI')        , 50, ' ')||  'PROCESS..........'||s.PROCESS|| &_br ||
        '    P1.................' ||rpad(w.p1       , 50, ' ') ||  'p1text..........'||w.p1text||&_br ||
        '    P2.................' ||rpad(w.p2       , 50, ' ') ||  'p2text..........'||w.p2text||&_br ||
        '    P3.................' ||rpad(w.p3       , 50, ' ') ||  'p3text..........'||w.p3text||&_br ||
        '    EVENT#..............'||rpad(s.EVENT#   , 50, ' ') ||  'EVENT.......... '||s.EVENT||&_br  ||
        '    Status..............'||rpad(s.status   , 50, ' ') ||&_br ||  
        '    Sql_text............'||substr(sql_text,1,80)||&_br||
        '    p1 raw..............'||rpad(s.P1RAW        , 50, ' ')||' ***last call et.......... '||s.last_call_et|| &_br ||
        --|| 'Service Name........'||s.SERVICE_NAME
        '    EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br ||
        '    EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br ||
        '    alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';'
FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq
WHERE 1=1
-- and s.sid IN (1)
and s.paddr = p.addr
AND s.sid   = w.sid
and s.sql_id is not null
-- and s.sid =714
and s.INST_ID=p.INST_ID
and w.INST_ID=s.INST_ID
and sq.INST_ID=s.INST_ID
and sq.sql_id=s.sql_id
-- and s.USERNAME='CA'
-- and s.SERVICE_NAME not in  ('SYS$USERS','SYS$BACKGROUND')
--and s.SERVICE_NAME in ('CAV')
and s.sid >9 ;

==================================================================================

DEFINE _br=chr(10)
DEFINE _trcsid_session_serial="NA"
COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial
DEFINE _trcsid_session_serial="NA"
set termout on verify off heading off linesize 155 sqlbl on echo off trimspool on trimout on feedback on
--
-- Show information about the SESSION
SELECT distinct
        '    alter system kill Session  ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;'          || &_br ||
        '    INST_ID# ......... ' || RPAD(s.INST_ID           , 50, ' ') ||   'SID   ............ ' || s.sid               || &_br ||
        '    SERIAL# .......... ' || RPAD(s.serial#           , 50, ' ') ||   'USERNAME ......... ' || s.username          || &_br ||
        '    SPID  ............ ' || RPAD(p.spid              , 50, ' ') ||   'OPID ............. ' || p.pid               || &_br ||
        '    PADDR  ........... ' || RPAD(s.paddr             , 50, ' ') ||   'SADDR  ........... ' || s.saddr             || &_br ||
        '    AUDSID  .......... ' || RPAD(s.audsid            , 50, ' ') ||   'OSUSER  .......... ' || s.osuser            || &_br ||
        '    PROCESS  ......... ' || RPAD(s.process           , 50, ' ') ||   'PROGRAM  ......... ' || s.program           || &_br ||
        '    MACHINE  ......... ' || RPAD(s.machine           , 50, ' ') ||   'MODULE  .......... ' || s.module            || &_br ||
        '    HASH_VALUE. ...... ' || RPAD(s.sql_hash_value    , 50, ' ') ||   'PREV_HASH_VALUE .. ' || s.prev_hash_value   || &_br ||
        '    SQL_ID ........... ' || rpad(s.sql_id            , 50, ' ') ||   'CHILD_NUMBER# .... ' || s.sql_child_number  || &_br ||
        '    PREV_SQL_ID ...... ' || rpad(s.prev_sql_id       , 50, ' ') ||   'PREV_CHILD# ...... ' || s.prev_child_number || &_br ||
        '    SQL EXEC ID........'||  rpad(s.SQL_EXEC_ID       , 40, ' ') ||   'Tracefile  ........' || p.TRACEFILE         || &_br ||   ----- comment 10g 
  '    WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS        , 50, ' ') ||   'WAIT_TIME   ...... ' || w.WAIT_TIME         || &_br ||
        '    STATE      ........' || rpad(s.STATE             , 50, ' ') ||   'SERVICE NAME ......' || s.SERVICE_NAME      || &_br ||
        '    LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI')        , 50, ' ')||'PROCESS..........'||s.PROCESS|| &_br ||
        '    P1.................' ||rpad(w.p1       , 50, ' ') ||  'p1text..........'||w.p1text||&_br ||
        '    P2.................' ||rpad(w.p2       , 50, ' ') ||  'p2text..........'||w.p2text||&_br ||
        '    P3.................' ||rpad(w.p3       , 50, ' ') ||  'p3text..........'||w.p3text||&_br ||
        '    EVENT#..............'||rpad(s.EVENT#   , 50, ' ') ||  'EVENT.......... '||s.EVENT ||&_br  ||
        '    Status..............'||rpad(s.status   , 50, ' ') ||&_br ||  
        '    Sql_text............'||substr(sql_text,1,80)||&_br||
        '    p1 raw..............'||rpad(s.P1RAW        , 50, ' ')||' ***last call et.......... '||to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99999.99')|| &_br ||
    --|| 'Service Name........'||s.SERVICE_NAME
 -- 'SQL EXEC ID........'||rpad(s.SQL_EXEC_ID  , 50, ' ') ||'CHILD_NUMBER# .... ' || s.sql_child_number  || &_br ||
        '    EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br ||
        '    EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br ||
        '    alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';'|| &_br ||
        '    select * from table(dbms_xplan.display_cursor('||''''||s.sql_id ||''''||',null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' || &_br ||  
        '    select * from table(dbms_xplan.display_awr('||''''||s.sql_id ||''''||',null,null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' || &_br ||  
        '    alter system set events ''trace[RDBMS.SQL_Optimizer.*][sql:'||s.sql_id ||']'';'  || &_br ||   
        '    alter system set events ''trace[RDBMS.SQL_Optimizer.*][sql:'||s.sql_id ||'] off'';'  
  FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq
where 1=1
and s.paddr    = p.addr
and s.inst_id  = p.inst_id
and w.inst_id  = s.inst_id
and sq.inst_id = s.inst_id
and sq.sql_id  = s.sql_id
and s.sid      = w.sid
-- and s.machine='S0017'
-- and s.sql_id is not null
-- nad s.sid!=sys_context('userenv','sid')
-- and sq.sql_id in ('1jwzgruw1z6s3' )
-- and s.EVENT not like 'SQL%' 
-- and s.username='SYS'
-- and s.service_name  in  ('USER')
-- and s.sid  in (23826)
-- and ROUND((SYSDATE - s.LOGON_TIME)*(24*60),1) > 60  ---- 1hr 
and s.type != 'BACKGROUND'
and s.username is not null
-- and s.status  ='INACTIVE'
 and s.USERNAME  Not  in  ( 'SYS ' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
/

==========

define sid=1473

set lines 200 pagesize 300 echo off

col sess format a10

col stime format a08

col sta format a04 head 'Sta'

col event format a24 trunc

col seq# format 99999

col wtime format 9999999

col twaited format 99999999

col p1p2p3 format a18 head ' P1:P2:P3'

col wclass format a11 trunc

col sqlid_cn format a16 head 'SqlId:ChildNo'

col blk format a01 head 'X'

col oc format 999

col cn format 99

col flags format a12 head 'CPH S PRXJBZ'

col mod format a20 head 'Module' trunc

col sqlstart format a05 head 'SQL|Start'

col sqlxid format 99999999 head 'SqlExecId'

col sidser format a15 head 'Sid,Serial<Blk'

col dur format 999999 head 'Dur|ms'

col cpu format 9999 head 'CPU|ms'

col dbtime format 999999 head 'DB Time|ms'

break on sidser skip 1 on mod on sqlid_cn on sqlxid on sqlstart

select

''''||SESSION_ID ||','|| session_serial#||',@'||inst_id ||''''||decode(substr(blocking_session_status,1,1),'V','<',' ')||lpad(blocking_session,4,' ') sidser

,to_char(sample_time, 'HH24:MI:SS') stime

,module mod

,sql_id||decode(sql_child_number,-1,' ',':'||lpad(sql_child_number,2,' ')) sqlid_cn

,sql_exec_id sqlxid

,to_char(sql_exec_start,'MI:SS') sqlstart

,in_connection_mgmt||in_parse||in_hard_parse||' '||in_sql_execution||' '||in_plsql_execution||in_plsql_rpc||in_java_execution||in_bind||in_cursor_close flags

,decode(session_state,'WAITING','Wait','ON CPU','cpu',' ? ') sta

,seq# seq#

,event event

,lpad(least(p1,9999999),7,' ')||':'||lpad(least(p2,999999),6,' ')||':'||lpad(least(p3,9),3,' ') p1p2p3

,wait_class wclass

,tm_delta_time/1000 dur

,tm_delta_cpu_time/1000 cpu

,tm_delta_db_time/1000 dbtime

from gv$active_session_history

where 1=1

and session_id = '&sid'

--and sample_time between sysdate-70/1440 and sysdate-30/1440

and sample_time > sysdate -1

and sample_time > sysdate - interval '30' minute

order by sample_time

;

=========================

set linesize 2000 pagesize 500 
col LONGOPS_MESSAGE for a20 
col PREV_SQL_TEXT for a50 wrap
col SQL_TEXT for a50 wrap 
col  P1TEXT for a10                                                                    
col  P2TEXT  for a10                                                                 
col  P3TEXT for a10
col USERNAME for a10
col PROGRAM for a10 
col EVENT for a12
col HOST_NAME for a12
col LATCHNAME for a12
col MACHINE for a12
col kill for a15
select/*+ rule*/ distinct   --because of multiple records in v$sql may exist
to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME
,''''||gv$session.sid ||','|| gv$session.serial#||',@'||gv$session.inst_id ||'''' kill
, gv$session.STATUS
, gv$session.USERNAME
, gv$session.PROGRAM
--, gv$session.INST_ID
--, gv$session.SID
--, gv$session.SERIAL#
, gv$transaction.START_TIME as TX_STIME
, gv$session.LAST_CALL_ET as LAST_CALL_ET
, gv$session_wait.EVENT
, v$latchname.NAME as LATCHNAME
, gv$session_wait.SECONDS_IN_WAIT
, dba_objects.owner||'.'||dba_objects.object_name req_object
, decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE',DBMS_ROWID.ROWID_CREATE( 1,
gv$session.ROW_WAIT_OBJ#, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#,
gv$session.ROW_WAIT_ROW# )) req_rowid
, lockhold.inst_id as BLOCKING_INSTANCE
, lockhold.sid as BLOCKING_SESSION
, gv$session.COMMAND
, gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT
, gv$session_longops.MESSAGE as LONGOPS_MESSAGE
, gv$session_wait.P1
, gv$session_wait.P1TEXT
, gv$session_wait.P2 P2
, gv$session_wait.P2TEXT
, gv$session_wait.P3
, gv$session_wait.P3TEXT
, gv$sql.ADDRESS as SQL_ADDRESS
, gv$sql.HASH_VALUE as HASH_VALUE
, gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE
, gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE
, gv$sql.sql_text as SQL_TEXT
, sql1.ADDRESS as PREV_SQL_ADDRESS
, sql1.HASH_VALUE as PREV_HASH_VALUE
, sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE
, sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE
, sql1.sql_text as PREV_sql_text
--Network connection properties
, gv$session.SERVER
, gv$session.FAILOVER_TYPE
, gv$session.FAILOVER_METHOD
, gv$session.FAILED_OVER
, gv$session.FIXED_TABLE_SEQUENCE
--OS properties
, gv$session.MACHINE
, gv$session.MODULE
, gv$session.OSUSER
, gv$session.OWNERID
, gv$session.TERMINAL
-- , 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#||''';' as KILL_SESSION
,'alter system kill session ' || '''' || gv$session.sid || ',' || gv$session.serial# ||',@'|| gv$session.inst_id || '''' || ' immediate;' as KILL_SESSION
, gv$instance.HOST_NAME
, 'kill '||gv$process.SPID as KILL_SPID
from gv$session
, gv$instance
, gv$process
, gv$session_wait
, gv$sql
, gv$sql sql1
, gv$transaction
, v$latchname
, dba_objects
, gv$session_longops
, gv$lock lockwait
, gv$lock lockhold
where gv$session.INST_ID = gv$instance.INST_ID
and gv$session.PADDR=gv$process.ADDR(+)
and gv$session.INST_ID = gv$process.INST_ID(+)
and gv$session.sql_address=gv$sql.address(+)
and gv$session.INST_ID = gv$sql.INST_ID(+)
and gv$session.sql_hash_value=gv$sql.hash_value(+)
and gv$session.PREV_SQL_ADDR = sql1.address(+)
and gv$session.PREV_HASH_VALUE = sql1.hash_value(+)
and gv$session.SID=gv$session_wait.SID(+)
and gv$session.INST_ID = gv$session_wait.INST_ID(+)
and gv$session.SADDR=gv$transaction.SES_ADDR(+)
and gv$session.INST_ID = gv$transaction.INST_ID(+)
and (gv$session.SERIAL# <> 1 or upper(gv$session.PROGRAM) like '%LGWR%'
or upper(gv$session.PROGRAM) like '%DBW%' )--System session excluded,exc. DBWR
and upper(gv$session.PROGRAM) not like '%QMNC%'--Queue Monitor Coordinator excluded
and upper(gv$session.PROGRAM) not like '%Q0%'--Queue monitor processes excluded
and gv$session_wait.P2=v$latchname.LATCH#(+)
and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+)
and gv$session.SID = gv$session_longops.sid(+)
and gv$session.INST_ID = gv$session_longops.INST_ID(+)
and gv$session.SERIAL# = gv$session_longops.SERIAL#(+)
and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+)
and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+)
and nvl(gv$session_longops.SOFAR,0) <> nvl(gv$session_longops.TOTALWORK,1)
and gv$session.LOCKWAIT = lockwait.KADDR(+)
and lockwait.id1 = lockhold.id1(+)
and lockwait.id2 = lockhold.id2(+)
and nvl(lockwait.REQUEST,1) > 0
and nvl(lockwait.LMODE,0) = 0
and nvl(lockhold.REQUEST,0) = 0
and nvl(lockhold.LMODE,1) > 0
and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1)
--and gv$session.schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')                     
order by tx_stime, status, LOGON_TIME, username, machine asc;

 =============

Session Info ....



SET    PAGES   0       -
       NEWP    0       -
       LINES   150     -
       TERM    Off     -
       HEAD    Off

Col     X form a150
--spool  all_session.log
TTitle  T Skip 2

select  ' ' x
, lpad( 'SADDR', 24, ' ')			||' : '||s.saddr x
, lpad( '_', 43, '_') x
, lpad( 'SID', 24, ' ')				||' : '||s.sid x
, lpad( 'Sid_serial', 24, ' ')			||' : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' x
, lpad( 'SERIAL#', 24, ' ')			||' : '||s.serial# x
, lpad( 'AUDSID', 24, ' ')			||' : '||s.audsid x
, lpad( 'PADDR', 24, ' ')			||' : '||s.paddr x
, lpad( 'USER#', 24, ' ')			||' : '||s.user# x
, lpad( 'USERNAME', 24, ' ')			||' : '||s.username x
, lpad( 'COMMAND', 24, ' ')			||' : '||s.command x
, lpad( 'TADDR', 24, ' ')			||' : '||s.taddr x
, lpad( 'LOCKWAIT', 24, ' ')			||' : '||s.lockwait x
, lpad( 'STATUS', 24, ' ')			||' : '||s.status x
, lpad( 'SERVER', 24, ' ')			||' : '||s.server x
, lpad( 'SCHEMA#', 24, ' ')			||' : '||s.schema# x
, lpad( 'SCHEMANAME', 24, ' ')			||' : '||s.schemaname x
, lpad( 'OSUSER', 24, ' ')			||' : '||s.osuser x
, lpad( 'Client Process', 24, ' ')		||' : '||s.process x
, lpad( 'Server Process', 24, ' ')		||' : '||p.spid x
, lpad( 'MACHINE', 24, ' ')			||' : '||s.machine x
, lpad( 'TERMINAL', 24, ' ')			||' : '||s.terminal x
, lpad( 'PROGRAM', 24, ' ')			||' : '||s.program x
, lpad( 'TYPE', 24, ' ')			||' : '||s.type x
, lpad( 'SQL_ID', 24, ' ')			||' : '||s.sql_id x
, lpad( 'PREV_SQL_ID', 24, ' ')			||' : '||s.prev_sql_id x
, lpad( 'SQL_ADDRESS', 24, ' ')			||' : '||s.sql_address x
, lpad( 'SQL_HASH_VALUE', 24, ' ')		||' : '||s.sql_hash_value x
, lpad( 'PREV_SQL_ADDR', 24, ' ')		||' : '||s.prev_sql_addr x
, lpad( 'PREV_HASH_VALUE', 24, ' ')		||' : '||s.prev_hash_value x
, lpad( 'MODULE', 24, ' ')			||' : '||s.module x
, lpad( 'MODULE_HASH', 24, ' ')			||' : '||s.module_hash x
, lpad( 'ACTION', 24, ' ')			||' : '||s.action x
, lpad( 'ACTION_HASH', 24, ' ')			||' : '||s.action_hash x
, lpad( 'CLIENT_INFO', 24, ' ')			||' : '||s.client_info x
, lpad( 'FIXED_TABLE_SEQUENCE', 24, ' ')        ||' : '||s.fixed_table_sequence x
, lpad( 'ROW_WAIT_OBJ#', 24, ' ')		||' : '||s.row_wait_obj# x
, lpad( 'ROW_WAIT_FILE#', 24, ' ')		||' : '||s.row_wait_file# x
, lpad( 'ROW_WAIT_BLOCK#', 24, ' ')		||' : '||s.row_wait_block# x
, lpad( 'ROW_WAIT_ROW#', 24, ' ')		||' : '||s.row_wait_row# x
, lpad( 'LOGON_TIME', 24, ' ')			||' : '||to_char( s.logon_time, 'dd mon yyyy hh24:mi:ss') x
, lpad( 'LAST_CALL_ET', 24, ' ')		||' : '||s.last_call_et x
, lpad( 'SQL FIRST_LOAD_TIME', 24, ' ')	        ||' : '||sql.first_load_time x
, lpad( 'FINAL_BLOCKING* ..', 24, ' ')	        ||' : '||s.final_blocking_session_status x
, lpad( 'EVENT ..', 24, ' ')			||' : '||s.event x
, lpad( 'SQL', 24, ' ')				||' : '||sql.sql_text x
from    gv$sqlarea sql,gv$process p,gv$session s
where 1=1
and     p.addr             = s.paddr
and	    p.inst_id          = s.inst_id
and     nvl( s.sql_address, 'FFFFFFFF') = sql.Address
and     s.inst_id = sql.inst_id
And     Nvl( s.SERIAL#, 0) > 1
--and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
-- 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')
/


***********************************



--- with Con_id


DEFINE _br=chr(10)
DEFINE _trcsid_session_serial="NA"
COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial
DEFINE _trcsid_session_serial="NA"
SET TERMOUT ON VERIFY OFF HEADING OFF LINESIZE 155 SQLBL ON ECHO OFF TRIMSPOOL ON TRIMOUT ON FEEDBACK ON
--
-- Show information about the SESSION
SELECT distinct
        '    alter system kill Session  ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;'          || &_br ||
        '    CON_ID..............'||rpad(s.CON_ID   , 50, ' ') ||&_br || 
        '    INST_ID# ......... ' || RPAD(s.INST_ID           , 50, ' ') ||   'SID   ............ ' || s.sid               || &_br ||
        '    SERIAL# .......... ' || RPAD(s.serial#           , 50, ' ') ||   'USERNAME ......... ' || s.username          || &_br ||
        '    SPID  ............ ' || RPAD(p.spid              , 50, ' ') ||   'OPID ............. ' || p.pid               || &_br ||
        '    PADDR  ........... ' || RPAD(s.paddr             , 50, ' ') ||   'SADDR  ........... ' || s.saddr             || &_br ||
        '    AUDSID  .......... ' || RPAD(s.audsid            , 50, ' ') ||   'OSUSER  .......... ' || s.osuser            || &_br ||
        '    PROCESS  ......... ' || RPAD(s.process           , 50, ' ') ||   'PROGRAM  ......... ' || s.program           || &_br ||
        '    MACHINE  ......... ' || RPAD(s.machine           , 50, ' ') ||   'MODULE  .......... ' || s.module            || &_br ||
        '    HASH_VALUE. ...... ' || RPAD(s.sql_hash_value    , 50, ' ') ||   'PREV_HASH_VALUE .. ' || s.prev_hash_value   || &_br ||
        '    SQL_ID ........... ' || rpad(s.sql_id            , 50, ' ') ||   'CHILD_NUMBER# .... ' || s.sql_child_number  || &_br ||
        '    PREV_SQL_ID ...... ' || rpad(s.prev_sql_id       , 50, ' ') ||   'PREV_CHILD# ...... ' || s.prev_child_number || &_br ||
        '    WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS        , 50, ' ') ||   'WAIT_TIME   ...... ' || w.WAIT_TIME         || &_br ||
        '    STATE      ........' || rpad(s.STATE             , 50, ' ') ||   'SERVICE NAME ......' || s.SERVICE_NAME      || &_br ||
        '    LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI')        , 50, ' ')||  'PROCESS..........'||s.PROCESS|| &_br ||
        '    P1.................' ||rpad(w.p1       , 50, ' ') ||  'p1text..........'||w.p1text||&_br ||
        '    P2.................' ||rpad(w.p2       , 50, ' ') ||  'p2text..........'||w.p2text||&_br ||
        '    P3.................' ||rpad(w.p3       , 50, ' ') ||  'p3text..........'||w.p3text||&_br ||
        '    EVENT#..............'||rpad(s.EVENT#   , 50, ' ') ||  'EVENT.......... '||s.EVENT||&_br  ||
        '    Status..............'||rpad(s.status   , 50, ' ') ||&_br ||  
        '    Sql_text............'||substr(sql_text,1,80)||&_br||
        '    p1 raw..............'||rpad(s.P1RAW        , 50, ' ')||' ***last call et.......... '||s.last_call_et|| &_br ||
        --|| 'Service Name........'||s.SERVICE_NAME
        '    EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br ||
        '    EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br ||
        '    alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';'
FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq
WHERE 1=1
-- and s.sid IN (1)
and s.paddr = p.addr
AND s.sid   = w.sid
and s.sql_id is not null
-- and s.sid =714
and s.INST_ID=p.INST_ID
and w.INST_ID=s.INST_ID
and sq.INST_ID=s.INST_ID
and sq.sql_id=s.sql_id
-- and s.USERNAME='CA'
-- and s.SERVICE_NAME not in  ('SYS$USERS','SYS$BACKGROUND')
--and s.SERVICE_NAME in ('CAV')
and s.CON_ID=3
and s.sid >9 ;




--- with Con_id




SET    PAGES   0       -
       NEWP    0       -
       LINES   150     -
       TERM    Off     -
       HEAD    Off

Col     X form a150
--spool  all_session.log
TTitle  T Skip 2

select  ' ' x
, lpad( 'SADDR', 24, ' ')			||' : '||s.saddr x
, lpad( '_', 43, '_') x
, lpad( 'SID', 24, ' ')				||' : '||s.sid x
, lpad( 'Sid_serial', 24, ' ')			||' : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' x
, lpad( 'Con _id', 24, ' ')			||' : '||s.Con_id
, lpad( 'SERIAL#', 24, ' ')			||' : '||s.serial# x
, lpad( 'AUDSID', 24, ' ')			||' : '||s.audsid x
, lpad( 'PADDR', 24, ' ')			||' : '||s.paddr x
, lpad( 'USER#', 24, ' ')			||' : '||s.user# x
, lpad( 'USERNAME', 24, ' ')			||' : '||s.username x
, lpad( 'COMMAND', 24, ' ')			||' : '||s.command x
, lpad( 'TADDR', 24, ' ')			||' : '||s.taddr x
, lpad( 'LOCKWAIT', 24, ' ')			||' : '||s.lockwait x
, lpad( 'STATUS', 24, ' ')			||' : '||s.status x
, lpad( 'SERVER', 24, ' ')			||' : '||s.server x
, lpad( 'SCHEMA#', 24, ' ')			||' : '||s.schema# x
, lpad( 'SCHEMANAME', 24, ' ')			||' : '||s.schemaname x
, lpad( 'OSUSER', 24, ' ')			||' : '||s.osuser x
, lpad( 'Client Process', 24, ' ')		||' : '||s.process x
, lpad( 'Server Process', 24, ' ')		||' : '||p.spid x
, lpad( 'MACHINE', 24, ' ')			||' : '||s.machine x
, lpad( 'TERMINAL', 24, ' ')			||' : '||s.terminal x
, lpad( 'PROGRAM', 24, ' ')			||' : '||s.program x
, lpad( 'TYPE', 24, ' ')			||' : '||s.type x
, lpad( 'SQL_ID', 24, ' ')			||' : '||s.sql_id x
, lpad( 'PREV_SQL_ID', 24, ' ')			||' : '||s.prev_sql_id x
, lpad( 'SQL_ADDRESS', 24, ' ')			||' : '||s.sql_address x
, lpad( 'SQL_HASH_VALUE', 24, ' ')		||' : '||s.sql_hash_value x
, lpad( 'PREV_SQL_ADDR', 24, ' ')		||' : '||s.prev_sql_addr x
, lpad( 'PREV_HASH_VALUE', 24, ' ')		||' : '||s.prev_hash_value x
, lpad( 'MODULE', 24, ' ')			||' : '||s.module x
, lpad( 'MODULE_HASH', 24, ' ')			||' : '||s.module_hash x
, lpad( 'ACTION', 24, ' ')			||' : '||s.action x
, lpad( 'ACTION_HASH', 24, ' ')			||' : '||s.action_hash x
, lpad( 'CLIENT_INFO', 24, ' ')			||' : '||s.client_info x
, lpad( 'FIXED_TABLE_SEQUENCE', 24, ' ')        ||' : '||s.fixed_table_sequence x
, lpad( 'ROW_WAIT_OBJ#', 24, ' ')		||' : '||s.row_wait_obj# x
, lpad( 'ROW_WAIT_FILE#', 24, ' ')		||' : '||s.row_wait_file# x
, lpad( 'ROW_WAIT_BLOCK#', 24, ' ')		||' : '||s.row_wait_block# x
, lpad( 'ROW_WAIT_ROW#', 24, ' ')		||' : '||s.row_wait_row# x
, lpad( 'LOGON_TIME', 24, ' ')			||' : '||to_char( s.logon_time, 'dd mon yyyy hh24:mi:ss') x
, lpad( 'LAST_CALL_ET', 24, ' ')		||' : '||s.last_call_et x
, lpad( 'SQL FIRST_LOAD_TIME', 24, ' ')	        ||' : '||sql.first_load_time x
, lpad( 'FINAL_BLOCKING* ..', 24, ' ')	        ||' : '||s.final_blocking_session_status x
, lpad( 'EVENT ..', 24, ' ')			||' : '||s.event x
, lpad( 'SQL', 24, ' ')				||' : '||sql.sql_text x
from    gv$sqlarea sql,gv$process p,gv$session s
where 1=1
and     p.addr             = s.paddr
and	    p.inst_id          = s.inst_id
and     nvl( s.sql_address, 'FFFFFFFF') = sql.Address
and     s.inst_id = sql.inst_id
And     Nvl( s.SERIAL#, 0) > 1
--and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
-- 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')
/

=====


	
  SELECT COUNT (*),
         CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END
            AS state,
         CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END
            AS sw_event
    FROM gv$session_wait
GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END,
         CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END
ORDER BY 1 DESC, 2 DESC




  SELECT COUNT (*),
         CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END
            AS state,
         CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END
            AS sw_event
    FROM gv$session
   WHERE TYPE = 'USER' 
AND status = 'ACTIVE'
GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END,
         CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END
ORDER BY 1 DESC, 2 DESC	
	


define sid='2594'
set pagesize 300 linesize 300
col sw_event    head EVENT for a40 truncate
col sw_p1transl head P1TRANSL for a42
col sw_sid      head SID for 999999

col sw_p1       head P1 for a18 justify left word_wrap
col sw_p2       head P2 for a18 justify left word_wrap
col sw_p3       head P3 for a18 justify left word_wrap

select 
    sid sw_sid, 
    CASE WHEN state != 'WAITING' THEN 'WORKING'
         ELSE 'WAITING'
    END AS state, 
    CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
         ELSE event
    END AS sw_event, 
    seq#, 
    seconds_in_wait sec_in_wait, 
    CASE state WHEN 'WAITING' THEN NVL2(p1text,p1text||'= ',null)||CASE WHEN P1 < 536870912 THEN to_char(P1) ELSE '0x'||rawtohex(P1RAW) END ELSE null END SW_P1,
    CASE state WHEN 'WAITING' THEN NVL2(p2text,p2text||'= ',null)||CASE WHEN P2 < 536870912 THEN to_char(P2) ELSE '0x'||rawtohex(P2RAW) END ELSE null END SW_P2,
    CASE state WHEN 'WAITING' THEN NVL2(p3text,p3text||'= ',null)||CASE WHEN P3 < 536870912 THEN to_char(P3) ELSE '0x'||rawtohex(P3RAW) END ELSE null END SW_P3,
    CASE state WHEN 'WAITING' THEN 
        CASE 
            WHEN event like 'cursor:%' THEN
                '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))
                    WHEN event like 'enq%' AND state = 'WAITING' THEN 
                '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||
                chr(bitand(p1, -16777216)/16777215)||
                chr(bitand(p1,16711680)/65535)||
                ' mode '||bitand(p1, power(2,14)-1)
            WHEN event like 'latch%' AND state = 'WAITING' THEN 
                  '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||(
                        select name||'[par' 
                            from v$latch_parent 
                            where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))
                        union all
                        select name||'[c'||child#||']' 
                            from v$latch_children 
                            where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))
                  )
            WHEN event like 'library cache pin' THEN
                  '0x'||RAWTOHEX(p1raw)
        ELSE NULL END 
    ELSE NULL END AS sw_p1transl
FROM 
    gv$session_wait 
WHERE 1=1
 and  sid IN ('&sid')
ORDER BY
    state,
    sw_event,
    p1,
    p2,
    p3
	

====



define sid=''

set time on timing on
set lines 500 pages 60
column sid 						format 9999
column state 					format a10 trunc
column username 				format a20 wrap
column osuser 					format a8
column seq 						for 99999
column et 						for 99999999999 heading 'Minu|Elaps'
column wait_time heading 'Sec.|Wtd' format 999
column wis heading 'Sec.|Wtng' format 9999999999999
col event 						for a25 trunc
col mod 						for a36 wrap
col  P1_P2_P3_TEXT 				for a40 wrap
col process 					for a8
col pid 						for a6 heading DBP
col sql_hash_value 				for 9999999999
col serial# heading 'Sr.|No.' 	for 99999
col inst_id     				for 99 heading 'IN|ST'
col kill1 						for a17
col kill for a17

--set feed on pause on term on
select
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill
 ,s.con_id
-- , to_char(p.spid,'9999999′) SPID 
,'kill -9 '||to_char(p.spid) kill1
, s.sql_id
,decode(w.event,'latch free', w.event||'-p2='||to_char(w.p2), substr(w.event,1,28)) event
, s.username username
,substr(s.osuser, 1,10) osuser
,w.state,w.wait_time, w.seconds_in_wait wis, w.seq# seq, s.sql_hash_value, s.last_call_et/60 et,s.module ||'~'||s.action mod,s.status,
substr(w.p1text||' '||to_char(s.P1)||'-'||w.p2text||' '||to_char(s.P2)||'-'||   w.p3text||' '||to_char(s.P3), 1,40) P1_P2_P3_TEXT
from gv$session_wait w, gv$session s, gv$process p
where s.sid=w.sid
and p.addr  = s.paddr
--and s.status in ( 'ACTIVE', 'KILLED')
and s.username is not null
and w.inst_id = s.inst_id
and s.inst_id = p.inst_id
and s.username not in ('SYS','SYSTEM')
--and s.sid = &sid
--order by 14
/
                   





set termout off  verify off trimspool on linesize 300 longchunksize 200000 long 200000 pages 0
column txt format a200

COL dummy_value NOPRINT for a300
--
SELECT 'dummy_value' dummy_value,
  'USERNAME    : ' || s.username     || CHR(10) ||
  'SCHEMA      : ' || s.schemaname   || CHR(10) ||
  'OSUSER      : ' || s.osuser       || CHR(10) ||
  'MODULE      : ' || s.program      || CHR(10) ||
  'ACTION      : ' || s.schemaname   || CHR(10) ||
  'CLIENT INFO : ' || s.osuser       || CHR(10) ||
  'PROGRAM     : ' || s.program      || CHR(10) ||
  'kill -9     : ' || p.spid      || CHR(10) ||
  --'SPID        : ' || p.spid         || CHR(10) ||
   --'KILL STRING : ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' || CHR(10) ||
'KILL STRING : ' || 'alter system kill session ' || '''' || s.sid || ',' || s.serial# ||',@'|| s.inst_id || '''' || ' immediate;'|| CHR(10) ||
  'Con Id      : ' || s.con_id || CHR(10) ||
  'MACHINE     : ' || s.machine      || CHR(10) ||
  'TYPE        : ' || s.type         || CHR(10) ||
  'TERMINAL    : ' || s.terminal     || CHR(10) ||
  'CPU         : ' || q.cpu_time/1000000     || CHR(10) ||
  'ELAPSED_TIME: ' || q.elapsed_time/1000000 || CHR(10) ||
  'BUFFER_GETS : ' || q.buffer_gets  || CHR(10) ||
  'SQL_ID      : ' || q.sql_id       || CHR(10) ||
  'CHILD_NUM   : ' || q.child_number || CHR(10) ||
  'START_TIME  : ' || TO_CHAR(s.sql_exec_start,'dd-mon-yy hh24:mi') || CHR(10) || 
  'STATUS      : ' || s.status       || CHR(10) ||
  'P1   	: ' || s.p1text || ' ' || s.p1  || chr(10)|| 
  'P2   	: ' || s.p2text || ' ' || s.p2  || chr(10)|| 
 'P3   		: ' || s.p3text || ' ' || s.p3  || chr(10)|| 
'FINAL_BLOCKING_SESSION   		: '                ||s.FINAL_BLOCKING_SESSION || chr(10)|| 
  --w'dbms_xplan :' ||'    select * from table(dbms_xplan.display_cursor('||''''||s.sql_id ||''''||',null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' ||CHR(10) ||
   'SQL_TEXT    : ' || q.sql_fulltext||CHR(10)||
  'dbms_xplan :' ||'   SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id =>'''||s.sql_id||''''||','|| 'cursor_child_no =>' ||child_number||','|| 'FORMAT =>'|| '''TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'' ));'  ||CHR(10)
FROM gv$session s,gv$process p,gv$sql q
where 1=1
and s.username IS NOT NULL 
and s.paddr  = p.addr
and s.inst_id  = p.inst_id
and s.inst_id  = q.inst_id
and s.sql_id = q.sql_id
AND NVL(q.sql_text,'x') NOT LIKE '%dummy_value%' 
--AND s.status != DECODE('&&active','n','xyz','N','xyz','INACTIVE')
and s.username IS NOT NULL
ORDER BY q.cpu_time;





set linesize 300 trimspool on pagesize 200
col event format a20
col USERNAME for a20
col SERNAME for a20
col kill for a17
col p2 for 99999999999999999
col P1TEXT for a20 
col P2TEXT for a20 
col P3TEXT for a20 
SELECT
''''||s.sid||','|| s.serial#||',@'||s.inst_id ||'''' kill
  , NVL(s.username,s.program) username
 --, s.sid                     sid
 --, s.serial#                 serial
 ,s.con_id
 ,s.sql_id
 , s.sql_hash_value          sql_hash_value
 , SUBSTR(DECODE(w.wait_time
               , 0, w.event
               , 'ON CPU'),1,15) event
 , w.p1                          p1
 , substr(w.P1TEXT,1,20) P1TEXT
 , w.p2                          p2
 ,substr(w.P2TEXT,1,20) P2TEXT
 , w.p3 p3
 ,substr(w.P3TEXT,1,20) P3TEXT
 from gv$session s, gv$session_wait  w
 where w.sid=s.sid
 and w.inst_id=s.inst_id
 and s.status='ACTIVE'
 AND s.type='USER'
 and username not in ('GGATE' ,'XXXX','SYS' ,'C##GGATE' )
 ;


set linesize 250 pagesize 60
select 	''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
s.con_id,
	s.username username,
	e.event event,
--	s.sid,
	s.sql_id,
	e.p1text,
	e.p1,
	e.p2text,
	e.p2,
	e.seq# seq,
	e.state,
	e.wait_time,
	-- time of current or most recent wait in microseconds
	--e.wait_time_micro,
	e.wait_time_micro / 1000000 wait_time_seconds,
	--e.seconds_in_wait, -- a confusing and somewhat useless column - see 11g docs for v$session
	decode(s.blocking_session, null,'',s.blocking_instance || ':' || s.blocking_session) blocking_session
from gv$session s, gv$session_wait e
where s.username is not null
	and s.sid = e.sid
and s.inst_id = e.inst_id
	-- skip sqlnet idle session messages
	--and s.module like 'Gathering Stats :EMT%'
and s.event not in ('SQL*Net message from client','SQL*Net more data from client','REPL Capture/Apply: messages','rdbms ipc message')
order by s.username, upper(e.event)
/

 

   
   set lines 170  pages 1000
col event format a35
select inst_id,event,count(*) from gv$session_wait
group by inst_id,event 
having count(*) >2
order by 3 desc
/
 



set echo off heading on feedback on
set linesize 200 trimspool on
set pagesize 60
set tab off

col blocking_sql_id format a12 head 'BLOCKING|SQL_ID'
col session_id format 999999 head 'SID'
col event format a40 head 'EVENT'
col session_state format a12 head 'SESSION|STATE'
col time_waited format 999,999.99 head 'TIME|WAITED|SECONDS'

col sample_time format a25 head 'SAMPLE TIME'


with blocked as (
	select distinct h.inst_id, h.sample_time, h.sample_id, h.session_id, h.session_serial#, h.blocking_session, h.blocking_session_serial#, h.sql_id, h.event, h.session_state
	from gv$active_session_history h
	where h.blocking_session is not null
	--and h.event like '&v_event_filter'
and sample_time > sysdate - INTERVAL '15' MINUTE   
),
blockers as (
	select distinct
		 max(b.sample_id) over (partition by b.inst_id, b.session_id, b.session_serial#) sample_id
		, max(b.sample_time) over (partition by b.inst_id, b.session_id, b.session_serial#) sample_time
		, count(b.sample_time) over (partition by b.inst_id, b.session_id, b.session_serial#) event_count
		, b.sql_id
		, b.inst_id
		, b.session_id
		, b.blocking_session
		, b.event
		, b.session_serial#
		, b.session_state
		-- NULL for top level blockers
		--, b.time_waited
	from blocked b
	left outer join gv$active_session_history bl
		on bl.sample_id = b.sample_id
		and bl.inst_id = b.inst_id
		and bl.sql_id = b.sql_id
		and bl.blocking_session = b.session_id
		and bl.blocking_session_serial# = b.blocking_session_serial#
)
select
	--sample_id
	sample_time
	, sql_id
	, session_id
	, blocking_session
	, inst_id
	, event_count
	, session_state
	, event
from blockers
order by sample_time, session_id
/

=======




set lines 200 verify  off  head off  feedback on long 5000
 select distinct
 '========================================================='					||chr(10)||
 --'Sid , Serial#     : '||S.Sid   ||' , '||S.Serial# ||','||s.inst_id 			||Chr(10)||
 'Sid , Serial#     : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' 	||Chr(10)||
 'Server/Shadow     : '||P.Spid    												||Chr(10)||
 'Client/Foreground : '||S.Process   											||Chr(10)||
 'Terminal / Machine: '||S.terminal||' / '||S.Machine   						||Chr(10)||
 'Username........: '||S.Username   											||Chr(10)||
 'Osuser..........: '||S.Osuser   												||Chr(10)||
 'Program.........: '||S.Program    											||Chr(10)||
 'Module..........: '||S.Module    												||Chr(10)||
 'Status..........: '||S.Status  												||Chr(10)||
 'Action..........: '||S.Action   												||Chr(10)||
 'Wait_time.......: '||W.Wait_time   											||Chr(10)||
 'State ..........: '||W.State   												||Chr(10)||
 'Wait Event .....: '||W.Event   												||Chr(10)||
 'Seconds_in_wait.: '||W.Seconds_in_wait   										||Chr(10)||
 'Pga_alloc....: '|| To_char(P.Pga_alloc_mem/1024/1024,'9,999.99') ||' Mb'  	||Chr(10)||
 'Pga_used.....: '|| To_char(P.Pga_used_mem/1024/1024 ,'9,999.99') ||' Mb' 		||Chr(10)||
 'Pga_free.....: '|| To_char(P.Pga_freeable_mem/1024/1024,'9,999.99') ||' Mb'  	||Chr(10)||
 'Pga_max......: '|| To_char(P.Pga_max_mem/1024/1024,'9,999.99')      ||' Mb'  	||Chr(10)||
 'Lock / Latch.: '|| Nvl(S.Lockwait, 'None')||'/ '||Nvl(P.Latchwait, 'None')   	||Chr(10)||
 'Blocking Sessi=on: '||s.blocking_session										||Chr(10)||
 'Blocking Session Status: '|| s.blocking_session_status						||Chr(10)||
 'Latch Spin...: '|| Nvl(P.Latchspin, 'None')   								||Chr(10)||
 'Logon Time...: '|| To_char(S.Logon_time, 'Dy Dd-Mon-Yy Hh24:Mi:Ss')   		||Chr(10)||
 'Last Call....: '|| To_char(Sysdate-(S.Last_call_et/60/60/24), 'Dy Dd-Mon-Yy Hh24:Mi:Ss') || '  -> ' || To_char(S.Last_call_et/60,'99999.0') ||
 ' Mins' || To_char(S.Last_call_et/60/60, '99999.0') ||
 'Hours' ||To_char(S.Last_call_et/60/60/24, '99.0') || ' Days'  				||Chr(10)||
 'Sql Address. : '||S.Sql_address   											||Chr(10)||
 'Sql Hash.... : '||S.Sql_hash_value   											||Chr(10)||
 'Prev Sql Hash: '||S.Prev_hash_value   										||Chr(10)||
 'Trans Status : '|| Nvl(T.Status,'None') 										|| Chr(10)||
 'Trans Active : '|| Nvl(S.Taddr, 'None')										||Chr(10)||
 'Undo Generation: '||Nvl(T.Used_ublk,0) || ' Blocks'							||Chr(10)||
 'Changed Blocks : '||I.Block_changes||' Blocks'								||Chr(10)||
 '............    Current  Sql Statment   .................: '					||Chr(10)||
 '========================================================='					||Chr(10)||
Nvl(Q.Sql_text,'No Current Sql Statment')    									||Chr(10)||
 '========================================================='					||Chr(10)||
 '................  Prev Sql Statment  ..................: '					||Chr(10)||
 '========================================================='					||Chr(10)||
Nvl(Q2.Sql_text,'No Sql Statment')    ||Chr(10)||
 '========================================================='
 from gv$session s, gv$process p , gv$sqlarea q , gv$sqlarea q2,gv$session_wait w , gv$transaction t, gv$sess_io i
 where p.addr=s.paddr
 and s.sid=i.sid
 -- and s.sid=nvl('sid',s.sid)
 and s.sid=w.sid
 -- and p.spid=nvl('spid',p.spid)
 and q.HASH_VALUE(+)=s.sql_hash_value
 and q2.hash_value(+)=s.prev_hash_value
 and s.taddr=t.addr(+)
 --and s.sql_id in (select sql_id from gv$session where sql_id is not null and USERNAME='USER' )
and s.USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
 -- and s.sql_id in (select sql_id from gv$session where sql_id is not null  )
 -- and 
 --and nvl(s.process,-1) = nvl('ClientPid',nvl(s.process,-1))
 ;
 

*****************************

set linesize 600 pagesize 300 
col SQL_TEXT for a50
col kill for a15
col USERNAME for a15 
col PLSQL_ENTRY_OBJECT   for a15
  col PLSQL_ENTRY_SUBPROGRAM for a20    
col PLSQL_ENTRY_SUBPROGRAM  for a20    

SELECT ''''||se.sid ||','|| se.serial#||',@'||se.inst_id ||'''' kill 
,      username
,      ( SELECT max( substr( sql_text , 1, 40 )) SQL_TEXT  FROM gv$sql sq WHERE sq.sql_id = se.sql_id ) AS sql_text
,      ( SELECT object_name    FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
,      ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
,      ( SELECT object_name    FROM dba_procedures WHERE object_id = plsql_object_id       AND subprogram_id = 0)                         AS plsql_entry_object
,      ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id       AND subprogram_id = PLSQL_SUBPROGRAM_ID)       AS plsql_entry_subprogram
--,      se.*
,se.sql_id
FROM   gv$session se
WHERE  1=1
--AND    se.status = 'ACTIVE'
-- AND    sid = 369
AND    plsql_entry_object_id IS NOT NULL
and USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
and sql_id='2u2jkj3sgctvn'
;



==============================================================================
????





SET FEEDBACK OFF LINESIZE 500 PAGESIZE 1000
COL "BLOCKER|BLKD_SID" FOR A25
COL "USERNAME|SID,SERIAL#" FOR A50
COL "MACHINE|MODULE" FOR A40
COL EVENT FOR A28
COL "ST|WAITD|ACT_SINC|LOG_T" FOR A50
COL "I|BLKD_BY" FOR A15
COL "SQL_TEXT_BLOCKED" FOR A100
COL "SQL_TEXT_BLOCKER" FOR A100
COL WAIT_SECONDS FOR 10
COL INST_ID FOR 5
SELECT
    -- Blocker session info
    bs.USERNAME || ' | ' || bs.sid || ',' || bs.serial# AS "BLOCKER|BLKD_SID",
    bs.MACHINE || ' | ' || bs.MODULE AS "BLOCKER_MACHINE|MODULE",
    SUBSTR(bsq.sql_text,1,100) AS "SQL_TEXT_BLOCKER",
-- Blocked session info
    s.USERNAME || ' | ' || s.sid || ',' || s.serial# AS "USERNAME|SID,SERIAL#",
    s.MACHINE || ' | ' || s.MODULE AS "MACHINE|MODULE",
    SUBSTR(sq.sql_text,1,100) AS "SQL_TEXT_BLOCKED",
    SUBSTR(s.status || '|' || ROUND(sw.WAIT_TIME_MICRO/1000000) || '|' || s.LAST_CALL_ET || '|'
           || TO_CHAR(s.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS'),1,50) AS "ST|WAITD|ACT_SINC|LOG_T",
    SUBSTR(sw.event,1,28) AS "EVENT",
    ROUND(sw.WAIT_TIME_MICRO/1000000) AS WAIT_SECONDS,
    s.PREV_SQL_ID AS "PREV_SQL_ID",
    s.SQL_ID AS "CURR_SQL_ID",
    s.FINAL_BLOCKING_INSTANCE || '|' || s.FINAL_BLOCKING_SESSION AS "I|BLKD_BY",
    s.inst_id AS "INST_ID"
FROM 
    gv$session s
    JOIN gv$process p 
        ON s.paddr = p.addr
    LEFT JOIN gv$session_wait sw
        ON s.sid = sw.sid
        AND s.inst_id = sw.inst_id
    LEFT JOIN gv$session bs
        ON s.BLOCKING_SESSION = bs.sid
        AND s.BLOCKING_INSTANCE = bs.inst_id
    LEFT JOIN gv$sql sq
        ON s.sql_id = sq.sql_id
        AND s.inst_id = sq.inst_id
    LEFT JOIN gv$sql bsq
        ON bs.sql_id = bsq.sql_id
        AND bs.inst_id = bsq.inst_id
WHERE 
    s.BLOCKING_SESSION IS NOT NULL
    AND s.STATUS = 'ACTIVE'               -- currently active sessions
    AND sw.WAIT_TIME_MICRO/1000000 > 300  -- only waits > 5 minutes
ORDER BY
    WAIT_SECONDS DESC,                    -- longest waits first
    bs.inst_id,                           -- blocker RAC node
    bs.USERNAME || ' | ' || bs.sid || ',' || bs.serial#,
    s.inst_id,                            -- blocked RAC node
    s.USERNAME || ' | ' || s.sid || ',' || s.serial#;



SET FEEDBACK OFF
SET LINESIZE 350
SET PAGESIZE 1000

COL "BLOCK_LEVEL" FOR 3
COL "BLOCKER|BLKD_SID" FOR A25
COL "USERNAME|SID,SERIAL#" FOR A50
COL "MACHINE|MODULE" FOR A40
COL "EVENT|WAIT_SEC" FOR A30
COL "SQL_TEXT" FOR A100
COL INST_ID FOR 5
WITH block_chain (block_level, sid, serial#, username, inst_id, machine, module, event, wait_sec,
                  blocking_sid, blocking_inst, sql_text) AS (
    SELECT
        1 AS block_level,
        s.sid,
        s.serial#,
        s.username,
        s.inst_id,
        s.machine,
        s.module,
        sw.event,
        ROUND(sw.WAIT_TIME_MICRO/1000000) AS wait_sec,
        s.blocking_session,
        s.blocking_instance,
        SUBSTR(sq.sql_text,1,100) AS sql_text
    FROM gv$session s
    LEFT JOIN gv$session_wait sw
        ON s.sid = sw.sid
        AND s.inst_id = sw.inst_id
    LEFT JOIN gv$sql sq
        ON s.sql_id = sq.sql_id
        AND s.inst_id = sq.inst_id
    WHERE s.blocking_session IS NOT NULL
    UNION ALL
    SELECT
        bc.block_level + 1,
        s.sid,
        s.serial#,
        s.username,
        s.inst_id,
        s.machine,
        s.module,
        sw.event,
        ROUND(sw.WAIT_TIME_MICRO/1000000),
        s.blocking_session,
        s.blocking_instance,
        SUBSTR(sq.sql_text,1,100)
    FROM gv$session s
    LEFT JOIN gv$session_wait sw
        ON s.sid = sw.sid
        AND s.inst_id = sw.inst_id
    LEFT JOIN gv$sql sq
        ON s.sql_id = sq.sql_id
        AND s.inst_id = sq.inst_id
    JOIN block_chain bc
        ON s.sid = bc.blocking_sid
        AND s.inst_id = bc.blocking_inst
)
SELECT
    LPAD(' ',2*(block_level-1)) || username || ' | ' || sid || ',' || serial# AS "USERNAME|SID,SERIAL#",
    machine || ' | ' || module AS "MACHINE|MODULE",
    event || ' | ' || wait_sec AS "EVENT|WAIT_SEC",
    sql_text AS "SQL_TEXT",
    inst_id AS "INST_ID",
    block_level AS "BLOCK_LEVEL"
FROM block_chain
ORDER BY inst_id, block_level, username;




SET FEEDBACK LINESIZE 400 PAGESIZE 1000
COL "BLOCK_LEVEL" FOR 3
COL "BLOCKER|BLKD_SID" FOR A25
COL "USERNAME|SID,SERIAL#" FOR A50
COL "MACHINE|MODULE" FOR A40
COL "EVENT|WAIT_SEC" FOR A30
COL "SQL_TEXT" FOR A50
COL INST_ID FOR 5
WITH block_chain (block_level, sid, serial#, username, inst_id, machine, module, event, wait_sec,
                  blocking_sid, blocking_inst, sql_text) AS (
    SELECT
        1 AS block_level,
        s.sid,
        s.serial#,
        s.username,
        s.inst_id,
        s.machine,
        s.module,
        sw.event,
        ROUND(sw.WAIT_TIME_MICRO/1000000) AS wait_sec,
        s.blocking_session,
        s.blocking_instance,
        SUBSTR(sq.sql_text,1,50) AS sql_text
    FROM gv$session s
    LEFT JOIN gv$session_wait sw
        ON s.sid = sw.sid
        AND s.inst_id = sw.inst_id
    LEFT JOIN gv$sql sq
        ON s.sql_id = sq.sql_id
        AND s.inst_id = sq.inst_id
    WHERE s.blocking_session IS NOT NULL
    UNION ALL
    SELECT
        bc.block_level + 1,
        s.sid,
        s.serial#,
        s.username,
        s.inst_id,
        s.machine,
        s.module,
        sw.event,
        ROUND(sw.WAIT_TIME_MICRO/1000000),
        s.blocking_session,
        s.blocking_instance,
        SUBSTR(sq.sql_text,1,50)
    FROM gv$session s
    LEFT JOIN gv$session_wait sw
        ON s.sid = sw.sid
        AND s.inst_id = sw.inst_id
    LEFT JOIN gv$sql sq
        ON s.sql_id = sq.sql_id
        AND s.inst_id = sq.inst_id
    JOIN block_chain bc
        ON s.sid = bc.blocking_sid
        AND s.inst_id = bc.blocking_inst
)
SELECT
    LPAD(' ',2*(block_level-1)) || username || ' | ' || sid || ',' || serial# AS "USERNAME|SID,SERIAL#",
    machine || ' | ' || module AS "MACHINE|MODULE",
    event || ' | ' || wait_sec AS "EVENT|WAIT_SEC",
    sql_text AS "SQL_TEXT",
    inst_id AS "INST_ID",
    block_level AS "BLOCK_LEVEL"
FROM block_chain
ORDER BY inst_id, block_level, username;





SET FEEDBACK OFF LINESIZE 400  PAGESIZE 1000
COL "USERNAME|SID,SERIAL#" FOR A60
COL "MACHINE|MODULE" FOR A40
COL "EVENT|WAIT_SEC" FOR A30
COL "SQL_TEXT" FOR A100
COL "FLAG" FOR A20
COL INST_ID FOR 5
COL BLOCK_LEVEL FOR 3

WITH block_chain (block_level, sid, serial#, username, inst_id, machine, module, event, wait_sec,
                  blocking_sid, blocking_inst, sql_text) AS (
    -- Level 1: top-level blocked sessions
    SELECT
        1 AS block_level,
        s.sid,
        s.serial#,
        s.username,
        s.inst_id,
        s.machine,
        s.module,
        sw.event,
        ROUND(sw.WAIT_TIME_MICRO/1000000) AS wait_sec,
        s.blocking_session,
        s.blocking_instance,
        SUBSTR(sq.sql_text,1,100) AS sql_text
    FROM gv$session s
    LEFT JOIN gv$session_wait sw
        ON s.sid = sw.sid
        AND s.inst_id = sw.inst_id
    LEFT JOIN gv$sql sq
        ON s.sql_id = sq.sql_id
        AND s.inst_id = sq.inst_id
    WHERE s.blocking_session IS NOT NULL
    UNION ALL
    -- Recursive levels: sessions blocked by previous level
    SELECT
        bc.block_level + 1,
        s.sid,
        s.serial#,
        s.username,
        s.inst_id,
        s.machine,
        s.module,
        sw.event,
        ROUND(sw.WAIT_TIME_MICRO/1000000),
        s.blocking_session,
        s.blocking_instance,
        SUBSTR(sq.sql_text,1,100)
    FROM gv$session s
    LEFT JOIN gv$session_wait sw
        ON s.sid = sw.sid
        AND s.inst_id = sw.inst_id
    LEFT JOIN gv$sql sq
        ON s.sql_id = sq.sql_id
        AND s.inst_id = sq.inst_id
    JOIN block_chain bc
        ON s.sid = bc.blocking_sid
        AND s.inst_id = bc.blocking_inst
)
SELECT
    LPAD(' ',2*(block_level-1)) || username || ' | ' || sid || ',' || serial# AS "USERNAME|SID,SERIAL#",
    machine || ' | ' || module AS "MACHINE|MODULE",
    event || ' | ' || wait_sec AS "EVENT|WAIT_SEC",
    sql_text AS "SQL_TEXT",
    inst_id AS "INST_ID",
    block_level AS "BLOCK_LEVEL",
    CASE 
        WHEN block_level = 1 THEN '***BLOCKER***'
        WHEN wait_sec > 300 THEN '***LONG WAIT***'
        ELSE NULL
    END AS "FLAG"
FROM block_chain
ORDER BY inst_id, block_level, username;

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Tuesday, 5 May 2015

ORA-38784: Cannot create restore point

ORA-38784: Cannot create restore point

ORA-01153: an incompatible media recovery is active

Monday, 27 April 2015

pdksh-5.2.14 is missing on Centos for Oracle prerequisite

pdksh-5.2.14 is missing on Centos for Oracle prerequisite

Sunday, 8 March 2015

Oracle Session info

Session Info ...

Oracle DBA

anuj blog Archive