Handling Locked Statistics in Gather Schema Statistics

Issue

During the execution of the Gather Schema Statistics concurrent program, the log file reported that certain tables had locked statistics, such as:

• WF_NOTIFICATION_OUT

• WF_NOTIFICATION_IN

This prevented the program from collecting fresh statistics.

---

Cause

The Gather Schema Statistics process failed because:

1. Some tables had their statistics locked, and

2. The FND_STATS_HIST table contained large volumes of data, impacting performance.

---

How to Identify Locked Statistics

Use the query below to find tables with locked statistics:

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;

This will list all objects where statistics are currently locked.

---

Solution

1. Unlock All Tables in a Schema

If many tables are locked, you can unlock the entire schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner');

---

2. Unlock Statistics for a Specific Table

To unlock statistics for a single table, run:

exec dbms_stats.unlock_table_stats('table_owner', 'table_name');

Example:

exec dbms_stats.unlock_table_stats('APPLSYS', 'WF_NOTIFICATION_OUT');

RMAN Queries

In this post, I’ve shared a set of RMAN-related SQL queries that help you quickly check backup details, identify issues, and verify restore sessions— directly from the database views.

1. Find RMAN Complete Percentage

set linesize 110

set pagesize 300

SELECT sid, serial#, context, sofar, totalwork , opname,

round(sofar/totalwork*100,2) "Complete"

FROM v$session_longops

WHERE opname LIKE 'RMAN%' and totalwork<>0 and round(sofar/totalwork*100,2)<>100;

--More Details

col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"

col input_mbytes       for 99,999,990.00 justify right head "READ_MB"

col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"

col output_device_type for a10           justify left head "DEVICE"

col complete           for 990.00        justify right head "COMPLETE %" 

col compression        for 990.00        justify right head "COMPRESS|% ORIG"

col est_complete       for a20           head "ESTIMATED COMPLETION"

col recid              for 9999999       head "ID"

select recid

     , output_device_type

     , dbsize_mbytes

     , input_bytes/1024/1024 input_mbytes

     , output_bytes/1024/1024 output_mbytes

     , (output_bytes/input_bytes*100) compression

     , (mbytes_processed/dbsize_mbytes*100) complete

     , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete

  from v$rman_status rs

     , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 

 where status='RUNNING'

   and output_device_type is not null

/

2. Find RMAN session in Database

select b.sid, b.serial#, a.spid, b.client_info

from v$process a, v$session b

where a.addr=b.paddr and client_info like 'rman%';

3. Kill RMAN Sessions:

alter system kill session 'SID,SERIAL#' immediate;

alter system kill session 'SID,SERIAL#,@<Instance_id>' immediate;  -- In RAC

4. RMAN Restore Monitor:

SET LINE 132

SELECT opname, round(sofar/totalwork*100) "% Complete"

  FROM gv$session_longops

 WHERE opname LIKE 'RMAN%'

   AND totalwork != 0

   AND sofar <> totalwork

 ORDER BY 1;

5. RMAN Backup Details with Size:

set pages 2000 lines 200

COL STATUS FORMAT a9

COL hrs FORMAT 999.99

select INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,

TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,

ELAPSED_SECONDS/3600 hrs,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;

6. History of RMAN Backup Details:

select

  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,

  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,

  (j.output_bytes/1024/1024/1024) output_Gbytes, j.status, j.input_type,

  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',

                                     3, 'Tuesday', 4, 'Wednesday',

                                     5, 'Thursday', 6, 'Friday',

                                     7, 'Saturday') dow,

  j.elapsed_seconds, j.time_taken_display,

  x.cf, x.df, x.i0, x.i1, x.l,

  ro.inst_id output_instance

from v$RMAN_BACKUP_JOB_DETAILS j

  left outer join (select

                     d.session_recid, d.session_stamp,

                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,

                     sum(case when d.controlfile_included = 'NO'

                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,

                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,

                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,

                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L

                   from

                     v$BACKUP_SET_DETAILS d

                     join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count

                   where s.input_file_scan_only = 'NO'

                   group by d.session_recid, d.session_stamp) x

    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp

  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id

                   from Gv$RMAN_OUTPUT o

                   group by o.session_recid, o.session_stamp)

    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp

where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS

order by j.start_time desc

/