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')

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%');