My personal cycling results on veloviewer.com

Activities for Zukus

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'

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

• Redo Processing
• V$RECOVERY_PROGRESS

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;