среда, 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);
  qq number(3);
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
              qq := 4;
          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#,
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$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#,
               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

четверг, 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

среда, 20 января 2016 г.

Index Rebuild vs. Coalesce vs. Shrink Space

Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones) contains demo Differences between a Coalesce, Shrink Space and Rebuild
ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space, pct_used FROM index_stats;

SELECT n.name, s.value FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name = 'redo size';

ALTER INDEX bowie_stuff_i COALESCE;
-- or
ALTER INDEX bowie_stuff_i SHRINK SPACE COMPACT;
-- or
ALTER INDEX bowie_stuff_i SHRINK SPACE;
-- or
ALTER INDEX bowie_stuff_i REBUILD;
-- or
ALTER INDEX bowie_stuff_i REBUILD ONLINE NOLOGGING;

SELECT n.name, s.value FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name = 'redo size';

ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space, pct_used FROM index_stats;