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;

Saturday, 24 January 2015

Oracle export as sysdba

How to use "/ as sysdba" to exp and imp

How to use / as sysdba to exp

exp userid=\'/ as sysdba\'

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

-bash-4.1$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jan 24 17:54:11 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> def

DEFINE _DATE = "24-JAN-15" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "vihaan" (CHAR)

DEFINE _USER = "SYS" (CHAR)

DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1201000100" (CHAR)

DEFINE _EDITOR = "ed" (CHAR)

DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)

DEFINE _O_RELEASE = "1201000100" (CHAR)

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

parafile

-bash-4.1$ cat exp.par

file=mydump.dmp

userid="/ as sysdba" <<<<<<<<<<----- with sys user

compress=n

log=mydump.log

direct=y

full=n

owner=TEST

consistent=y

feedback=10000

-bash-4.1$ exp parfile=exp.par

Export: Release 12.1.0.1.0 - Production on Sat Jan 24 17:51:53 2015

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

EXP-00105: parameter CONSISTENT is not supported for this user

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user TEST

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user TEST

About to export TEST's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export TEST's tables via Direct Path ...

. . exporting table TEST

2803 rows exported

EXP-00091: Exporting questionable statistics.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

done .....

-bash-4.1$ ls -ltr *.dmp

-rw-r--r-- 1 oracle oinstall 876544 Jan 24 17:52 mydump.dmp

Monday, 19 January 2015

INS-40922 Invalid Scan Name - Unresolvable to IP address

INS-40922 Invalid Scan Name - Unresolvable to IP address in Grid installation

Include scan IPs in host file and try again

nslookup mrac-scan
Server: 127.0.0.1
Address: 127.0.0.1#53

Name: rac-scan
Address: 192.168.0.153
Name: rac-scan
Address: 192.168.0.154
Name: rac-scan
Address: 192.168.0.155

on all nodes

cat /etc/hosts

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

################################################################

####### --------------- eth0 - PUBLIC ------------ ###########

################################################################

192.168.0.49 mrac1.localdomain mrac1

192.168.0.50 mrac2.localdomain mrac2

################################################################

####### ---------------- eth1 priv------------------ ###########

################################################################

192.168.1.49 mrac1-priv.localdomain mrac1-priv

192.168.1.50 mrac2-priv.localdomain mrac2-priv

################################################################

####### -----------------VIP------------------------ ###########

################################################################

192.168.0.151 mrac1-vip.localdomain mrac1-vip

192.168.0.152 mrac2-vip.localdomain mrac2-vip

#################################################################

# SCAN IP #########################################

################################################################

192.168.0.153 mrac-scan.localdomain mrac-scan

192.168.0.154 mrac-scan.localdomain mrac-scan

192.168.0.155 mrac-scan.localdomain mrac-scan
#################################################################

Monday, 17 November 2014

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

error

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

FROM ."GGS_SETUP" ERROR at line 2: ORA-00942: table or view does not exist

FROM "GGATE" ."GGS_SETUP" ERROR at line 2:ORA-00942: table or view does not exist

error in GoldenGate while setting up ....

[oracle@db ~]$ cd /u01/app/oracle/product/gg/

[oracle@db gg]$ sqlplus / as sysdba

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

SQL> @ddl_enable.sql

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

FROM ."GGS_SETUP" ERROR at line 2: ORA-00942: table or view does not exist

FROM "GGATE" ."GGS_SETUP" ERROR at line 2:ORA-00942: table or view does not exist

DDL TRIGGER RUNNING STATUS

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

ENABLED

FROM "GGATE" ."GGS_SETUP"

ERROR at line 2:

ORA-00942: table or view does not exist

FROM "GGATE" ."GGS_SETUP"

ERROR at line 2:

ORA-00942: table or view does not exist

FROM "GGATE" ."GGS_SETUP"

ERROR at line 2:

ORA-00942: table or view does not exist

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

solution

drop this trigger

as sys

SQL> drop TRIGGER SYS.GGS_DDL_TRIGGER_BEFORE ;

Trigger dropped.

grant following

SQL> grant create any table to ggate;

grant create any view to ggate;

grant create any procedure to ggate;

grant create any sequence to ggate;

grant create any index to ggate;

grant create any trigger to ggate;

grant create any view to ggate;

Check this shold be true

SQL> show parameter ENABLE_GOLDENGATE_REPLICATION

NAME TYPE VALUE

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

enable_goldengate_replication boolean TRUE

[oracle@db ~]$ cd /u01/app/oracle/product/gg/

[oracle@db gg]$ sqlplus / as sysdba

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

SQL> @ddl_enable.sql

our user ggate ...

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos Error

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

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

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

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

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

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

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

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDL IGNORE TABLE

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

DDL IGNORE LOG TABLE

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

DDLAUX PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDL HISTORY TABLE

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

DDL HISTORY TABLE(1)

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

DDL DUMP TABLES

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

DDL DUMP COLUMNS

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

DDL DUMP LOG GROUPS

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

DDL DUMP PARTITIONS

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

DDL DUMP PRIMARY KEYS

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

DDL SEQUENCE

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

GGS_TEMP_COLS

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

GGS_TEMP_UK

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

DDL TRIGGER CODE STATUS:

Line/pos Error

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

No errors No errors

DDL TRIGGER INSTALL STATUS

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

DDL TRIGGER RUNNING STATUS

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

ENABLED

STAYMETADATA IN TRIGGER

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

OFF

DDL TRIGGER SQL TRACING

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

DDL TRIGGER TRACE LEVEL

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

LOCATION OF DDL TRACE FILE

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

/u01/app/oracle/diag/rdbms/vihaan/vihaan/trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

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

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos Error

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

No errors No errors

CREATE_TRACE STATUS:

Line/pos Error

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

No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error

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

No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error

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

No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDL IGNORE TABLE

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

DDL IGNORE LOG TABLE

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

DDLAUX PACKAGE STATUS:

Line/pos Error

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

No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error

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

No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error

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

No errors No errors

DDL HISTORY TABLE

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

DDL HISTORY TABLE(1)

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

DDL DUMP TABLES

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

DDL DUMP COLUMNS

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

DDL DUMP LOG GROUPS

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

DDL DUMP PARTITIONS

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

DDL DUMP PRIMARY KEYS

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

DDL SEQUENCE

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

GGS_TEMP_COLS

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

GGS_TEMP_UK

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

DDL TRIGGER CODE STATUS:

Line/pos Error

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

No errors No errors

DDL TRIGGER INSTALL STATUS

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

DDL TRIGGER RUNNING STATUS

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

ENABLED

STAYMETADATA IN TRIGGER

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

OFF

DDL TRIGGER SQL TRACING

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

DDL TRIGGER TRACE LEVEL

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

LOCATION OF DDL TRACE FILE

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

/u01/app/oracle/diag/rdbms/vihaan/vihaan/trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

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

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggate

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

SQL> @ddl_enable.sql

Trigger altered.

Saturday, 15 November 2014

Search string in database

Oracle find string or number in the database

search string
find text in database

set serveroutput on size 1000000

accept string char prompt "Enter Char String to search for:"

accept owner_name prompt "Enter Owner Name:"

set term off

set verify off

set lines 80

set feedback off

spool find_string.tmp

prompt spool find_string.out

prompt set feedback off

prompt set pages 1000

prompt set lines 132

prompt set wrap on

prompt break on found_in skip 1

prompt col found_in format a55 heading "Found In .."

prompt col string format a70 heading "String Found"

-- prompt col row_id format 99999999999999999999

prompt set term on

prompt

declare

cursor varchar_tables_cur IS

select owner

, table_name

, column_name

from dba_tab_columns

where owner != 'SYS'

and owner != 'SYSTEM'

and owner = upper('&OWNER_NAME')

and data_type in ( 'VARCHAR2','VARCHAR','CHAR');

begin

for v_t_rec in varchar_tables_cur loop

dbms_output.put_line('--');

-- dbms_output.put_line('prompt '||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||'');

dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');

dbms_output.new_line;

dbms_output.put(''||v_t_rec.column_name||' String ');

dbms_output.new_line;

-- dbms_output.put_line('rowid Row_id ');

dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');

dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');

dbms_output.put_line('/');

end loop;

end;

prompt spool off

prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

===

-- set term off

set verify off

set lines 80

set feedback off

set serveroutput on

spool find_string.tmp

prompt spool find_string.out

prompt set feedback off

prompt set pages 1000

prompt set lines 132

prompt set wrap on

prompt break on found_in skip 1

prompt col found_in format a55 heading "Found In .."

prompt col string format a70 heading "String Found"

-- prompt col row_id format 99999999999999999999

prompt set term on

prompt

declare

cursor varchar_tables_cur IS

select table_name,

column_name

from user_tab_columns a, user_objects b

where data_type in ( 'VARCHAR2','VARCHAR','CHAR')

and object_name=table_name

and object_type='TABLE' ;

-- and a.OWNER not in ('SYS','SYSTEM') ;

begin

for v_t_rec in varchar_tables_cur loop

dbms_output.put_line('--');

-- dbms_output.put_line('prompt '||v_t_rec.table_name||'.'||v_t_rec.column_name||'');

dbms_output.put('select distinct'''||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');

dbms_output.new_line;

dbms_output.put(''||v_t_rec.column_name||' String ');

dbms_output.new_line;

-- dbms_output.put_line('rowid Row_id ');

dbms_output.put_line('from '||v_t_rec.table_name||'');

dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');

dbms_output.put_line('/');

end loop;

end;

prompt spool off

prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

===

Oracle search string from a database

-- set term off

set verify off

set lines 80

set feedback off

set serveroutput on

spool find_string.tmp

prompt spool find_string.out

prompt set feedback off

prompt set pages 1000

prompt set lines 132

prompt set wrap on

prompt break on found_in skip 1

prompt col found_in format a55 heading "Found In .."

prompt col string format a70 heading "String Found"

-- prompt col row_id format 99999999999999999999

prompt set term on

prompt

declare

cursor varchar_tables_cur IS

select table_name,a.owner owner, column_name

-- from user_tab_columns a, user_objects b

from dba_tab_columns a, dba_objects b

where data_type in ( 'VARCHAR2','VARCHAR','CHAR')

and object_name=table_name

and object_type='TABLE'

and a.OWNER not in ('SYS','SYSTEM') ;

begin

for v_t_rec in varchar_tables_cur loop

dbms_output.put_line('--');

-- dbms_output.put_line('prompt '||v_t_rec.table_name||'.'||v_t_rec.column_name||'');

dbms_output.put('select distinct'''||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');

dbms_output.new_line;

dbms_output.put(''||v_t_rec.column_name||' String ');

dbms_output.new_line;

-- dbms_output.put_line('rowid Row_id ');

dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');

dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');

dbms_output.put_line('/');

end loop;

end;

prompt spool off

prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

====

set serveroutput on size 1000000

accept string char prompt "Enter Char String to search for:"

accept owner_name prompt "Enter Owner Name:"

set term off

set verify off

set lines 80

set feedback off

spool find_string.tmp

prompt spool c:\temp\find_string.out

prompt set feedback off

prompt set pages 1000

prompt set lines 132

prompt set wrap on

prompt break on found_in skip 1

prompt col found_in format a55 heading "Found In

.."

p rompt col string format a70 heading "String Found"

-- prompt col row_id format 99999999999999999999

prompt set term on

prompt

declare

cursor varchar_tables_cur IS

select owner

, table_name

, column_name

from dba_tab_columns

where owner != 'SYS'

and owner != 'SYSTEM'

and owner = '&OWNER_NAME'

and data_type in ( 'VARCHAR2','VARCHAR','CHAR');

begin

for v_t_rec in varchar_tables_cur loop

dbms_output.put_line('--');

-- dbms_output.put_line('prompt '||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_n ame||'');

dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');

dbms_output.new_line;

dbms_output.put(''||v_t_rec.column_name||' String ');

dbms_output.new_line;

-- dbms_output.put_line('rowid Row_id ');

dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');

dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'%&string%'||chr(39)||'');

dbms_output.put_line('/');

end loop;

end;

prompt spool off

prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

sqlplus set vi editor

vi editor in sqlplus ..

vi editor in sqlplus

EDITOR=vi; export EDITOR in .profile file

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Sunday, 8 March 2015

Oracle Session info

Session Info ...

Saturday, 24 January 2015

Oracle export as sysdba

Oracle export as sysdba

Monday, 19 January 2015

INS-40922 Invalid Scan Name - Unresolvable to IP address

INS-40922 Invalid Scan Name - Unresolvable to IP address in Grid installation

Monday, 17 November 2014

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

Saturday, 15 November 2014

Search string in database

Oracle find string or number in the database

sqlplus set vi editor

vi editor in sqlplus ..

Oracle DBA

anuj blog Archive