понедельник, 31 июля 2017 г.

Determine How Long a Session has been Idle using v$session

Following SQL using information from LAST_CALL_ET:
select s.*
--select 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' kill_serial_sid
  from (
        SELECT  s.sid,s.serial#,s.username
                ,s.status
                ,substr(s.machine,1,10)
                ,s.osuser,s.module
                ,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
                -- idle time
                ,to_dsinterval(
                  -- days separately
                  substr('0'||trunc(last_call_et/86400),-2,2) || ' ' ||
                  -- hours
                  substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
                  -- minutes
                  substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
                  --seconds
                  substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2)
                ) idle_time
         FROM   v$session s, v$process p
        WHERE   s.username IS NOT NULL
          -- use outer join to show sniped sessions in
          -- v$session that don't have an OS process
          AND p.addr(+) = s.paddr
       ) s
 where 1 = 1
   and s.status = 'INACTIVE'
   and s.idle_time >= to_dsinterval('1 00:00:00')
 ORDER BY idle_time desc;
Note: LAST_CALL_ET - If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.

Search Idle Sessions using filter in Enterprise Manager

You can specify search criteria using WHERE clause

as following:
1 = 1
and username IS NOT NULL
and status = 'INACTIVE'
and to_dsinterval(
      -- days separately
      substr('0'||trunc(last_call_et/86400),-2,2) || ' ' ||
      -- hours
      substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
      -- minutes
      substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
      --seconds
      substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2)
    ) >= to_dsinterval('1 00:00:00')

среда, 7 июня 2017 г.

Search values in all columns of all tables

Улучшил процедуру whereIsValue из статьи  SQL: Поиск значения по всем колонкам всех таблиц:

  • Добавил параметр schemaName для поиска в конкретной схеме
  • Заменил user_ views на all_ чтобы была возможность поиска по любой схеме
  • Обернул в кавычки "' || columnName || '" так как столкнулся с ситуацией когда колонка называлась зарезервированным словом "DATE" 
create or replace procedure whereIsValue(p_schemaName varchar2, p_searchValue varchar2)
AS
  TYPE VALCUR IS REF CURSOR;
  cursor tabl(c_schemaName varchar2) is select table_name from all_tables where owner = c_schemaName order by table_name;
  cursor col (c_schemaName varchar2, c_tablename varchar2) is select column_name from all_tab_columns where owner = c_schemaName and table_name like c_tableName;
  valueCursor VALCUR;
  tableName varchar2(50);
  columnName varchar2(50);
  columnValue varchar2(500);
begin
  open tabl(p_schemaName);
  LOOP
    fetch tabl into tableName;
    EXIT WHEN tabl%NOTFOUND;
    --dbms_output.put_line('Search in table - ' || tableName);
    OPEN col(p_schemaName, tableName);
    LOOP     
      fetch col into columnName;
      EXIT WHEN col%NOTFOUND;
      --dbms_output.put_line('Search in column - ' || columnName);
        OPEN valueCursor for 'select "' || columnName || '" from ' || p_schemaName || '."' || tableName || '"';
        LOOP
          BEGIN
            fetch valueCursor into columnValue;        
            EXIT WHEN valueCursor%NOTFOUND;
            if (columnValue like p_searchValue) then
              dbms_output.put_line('Found in table - ' || tableName || ' and column - ' || columnName);
              exit;
            end if;
          EXCEPTION
            WHEN OTHERS then
              NULL;
          END;
        END LOOP;
        CLOSE valueCursor;
    END LOOP;
    CLOSE col;
  END LOOP;
end;
/
Примеры вызова:
sqlplus /nolog
conn system

SET SERVEROUTPUT ON

exec whereIsValue('SCOTT', '1496765408852');
exec whereIsValue('SCOTT', 'CLERK');
exec whereIsValue('SCOTT', '%JONES%');

вторник, 19 июля 2016 г.

My personal cycling results on veloviewer.com

Activities for Zukus

среда, 22 июня 2016 г.

TEMP tablespace usage history

V$TEMPSEG_USAGE

SELECT username,
       session_addr,
       session_num,
       sqladdr,
       sqlhash,
       sql_id,
       contents,
       segtype,
       extents,
       blocks
FROM   v$tempseg_usage
ORDER BY username;

Script to display the recent activity

select distinct
c.username "user",
c.osuser ,
c.sid,
c.serial#,
'alter system kill session ''' || c.sid || ',' || c.serial# || ''' immediate;' kill_session_cmd,
b.spid "unix_pid",
c.machine,
c.program "program",
a.blocks * e.block_size/1024/1024 mb_temp_used  ,
a.tablespace,
d.sql_text
from
v$tempseg_usage a, /* v$sort_usage */
v$process b,
v$session c,
v$sqlarea d,
dba_tablespaces e
where c.saddr=a.session_addr
and b.addr=c.paddr
and a.sqladdr=d.address(+)
and a.tablespace = e.tablespace_name
order by mb_temp_used desc;

TEMP tablespace usage history

A views V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY include temp_space_allocated field.
Script below showing last 7 days top 5 SQLs for each day of temp tablespace consumers:
select t.sample_time, t.sql_id, t.temp_mb, t.temp_diff
       ,s.sql_text
  from (
        select --session_id,session_serial#,
               --'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,
               trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff
               , row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn
          from (
                select sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb, 
                       temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff
                 --from dba_hist_active_sess_history 
                 from v$active_session_history
                where 1 = 1 
                -- session_id=&1 
                -- and session_serial#=&2
               )
         group by --session_id,session_serial#,
                  trunc(sample_time),
                  sql_id
       ) t
  left join v$sqlarea s
    on s.sql_id = t.sql_id
 where 1 = 1
   and rn <=5
   and sample_time >= trunc(sysdate) - 7                 
 order by sample_time desc, temp_mb desc
To release TEMP space usage before shrinking temporary tablespace use:
select distinct
       session_id,session_serial#,
       'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd
  from v$active_session_history
 where 1 = 1
   and sql_id='&3'

четверг, 25 февраля 2016 г.

Monitoring the recovery progress of Standard/Standby database using v$recovery_process


Here it is script to track recovery progress of your standard/standby database:
set linesize 255
set pagesize 60
col type format a30
col sofar_formatted format a20
col total_formatted format a15
col comments format a30

select to_char(start_time, 'dd-mon-rr hh24:mi:ss') start_time, type, item, units,
  sofar,
  case units
      when 'KB/sec' then round(sofar/1024,2) || ' MB/Sec'
      when 'Megabytes' then round(sofar/1024,2) || ' G'
      when 'Seconds' then to_char(cast(numtodsinterval(sofar, 'SECOND') as interval day(2) to second(2)), 'DD HH24:MI:SS')
      else to_char(sofar)
  end sofar_formatted,
  total,
    case units
      when 'KB/sec' then round(total/1024,2) || ' MB/Sec'
      when 'Megabytes' then round(total/1024,2) || ' G'
      when 'Seconds' then to_char(cast(numtodsinterval(total, 'SECOND') as interval day(2) to second(2)), 'DD HH24:MI:SS')
      else to_char(total)
  end total_formatted,
  comments
from v$recovery_progress;

Output:

set linesize 255
START_TIME                  TYPE                           ITEM                             UNITS                                 SOFAR SOFAR_FORMATTED           TOTAL TOTAL_FORMATTED COMMENTS
--------------------------- ------------------------------ -------------------------------- -------------------------------- ---------- -------------------- ---------- --------------- ------------------------------
11-feb-16 22:50:00          Media Recovery                 Log Files                        Files                                     8 8                             8 8
11-feb-16 22:50:00          Media Recovery                 Active Apply Rate                KB/sec                                 3316 3.24 MB/Sec                3316 3.24 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Average Apply Rate               KB/sec                                  762 .74 MB/Sec                  762 .74 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Maximum Apply Rate               KB/sec                                 9617 9.39 MB/Sec                9617 9.39 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Redo Applied                     Megabytes                              1144 1.12 G                     1144 1.12 G
11-feb-16 22:50:00          Media Recovery                 Last Applied Redo                SCN+Time                                  0 0                             0 0               SCN: 6296684740127
11-feb-16 22:50:00          Media Recovery                 Active Time                      Seconds                                 214 +00 00:03:34.00             214 +00 00:03:34.00
11-feb-16 22:50:00          Media Recovery                 Apply Time per Log               Seconds                                  22 +00 00:00:22.00              22 +00 00:00:22.00
11-feb-16 22:50:00          Media Recovery                 Checkpoint Time per Log          Seconds                                   5 +00 00:00:05.00               5 +00 00:00:05.00
11-feb-16 22:50:00          Media Recovery                 Elapsed Time                     Seconds                                1537 +00 00:25:37.00            1537 +00 00:25:37.00

References