Restart QMon process

Event "Streams AQ: waiting for time management or cleanup tasks" in running state

SELECT event, state, COUNT(*) AS cnt, sum(seconds_in_wait) AS wait_in_seconds
FROM v$session_wait
WHERE event LIKE 'Streams AQ%'
GROUP BY event, state;
 
select * from GV$QMON_TASKS
 
select * from GV$QMON_SERVER_STATS
 
select isdefault from v$parameter where name='aq_tm_processes';

Restart QMon process

show parameter aq_tm_processes
 
alter system set aq_tm_processes = 0;
 
alter system set aq_tm_processes = 1;
 
show parameter aq_tm_processes
 
alter system reset aq_tm_processes scope=spfile;

Shrinking undo tablespace

Useful links

Reclaiming Unused Space in Datafiles

Undo tablespace

The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace:

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;
 
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;
 
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Undo segments in DBA_ROLLBACK_SEGS view

select owner, segment_name, tablespace_name, status
--select *
from dba_rollback_segs
 where 1 = 1
   --and tablespace_name='UNDOTBS1'
   --and status='ONLINE'
   --and segment_name ='_SYSSMU51_2438556163$'
;

Who holds UNDO tablespace

select  
   substr(a.os_user_name,1,15)   "OS User",
   substr(a.oracle_username,1,8) "DB User",
   substr(b.owner,1,8)           "Schema",
   substr(b.object_name,1,20)    "Object Name",
   substr(b.object_type,1,10)    "Type",
   substr(c.segment_name,1,15)   "RBS",
   substr(d.used_urec,1,12)      "# of Records",
   e.sid,
   e.serial#
from v$locked_object    a,
     dba_objects        b,
     dba_rollback_segs  c,
     v$transaction      d,
     v$session e
where  
   a.object_id =  b.object_id
 and
   a.xidusn    =  c.segment_id
and
   a.xidusn    =  d.xidusn
and
   a.xidslot   =  d.xidslot
and
   d.addr      =  e.taddr;

Issue: Drop UNDO including datafiles does not delete files at OS level

Find oracle process which hold file :

[root@hostname][/]:lsof | grep undo | grep deleted
oracle     3818     oracle  258u      REG               8,65 32421978112      17089 /u01/app/oracle/oradata/DB11G/undotbs01.dbf (deleted)
 
sqlplus / as sysdba
SQL> SELECT sid, serial#, process, terminal, program, event FROM v$session WHERE process in ('3818')

Look at this SID and it event, and find solution how to release hold of file by this session, for example I have issue with Restart QMon process

Solving event I/O and Cluster wait and freeing wasted space in LOBs

Issue: I/O and Cluster wait for LOBs

Sometimes in ADDM Report you can finding in "Top SQL Statements" following action:

Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID 
      "2adh0x2quv6uh". Additionally, investigate this statement for possible 
      performance improvements. You can supplement the information given here 
      with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 2adh0x2quv6uh.
         INSERT INTO USERINFO (SESSIONID, USERID, DICTIONARY, EXPIRES) 
         VALUES(:1 , :2 , :3 , :4)
   Rationale
      I/O and Cluster wait for LOB "SCOTT.SYS_LOB0000155472C00008$$" with 
      object ID 154473 consumed 60% of the database time spent on this SQL 
      statement.

Segment Advisor recommend shrink table SESSIONINFORMATION for reclaim space, but shrinking does not work for LOBs::

ALTER TABLE "SCOTT"."USERINFO" ENABLE ROW MOVEMENT
ALTER TABLE "SCOTT"."USERINFO" SHRINK SPACE

Solution

Reclaiming Wasted Space:

1. Use CASCADE option for shrink a table and all of its dependent segments (including LOB segments).
   
   When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows
   but postpones the resetting of the high water mark and the deallocation of the space
   until you reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete
   

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE COMPACT;
   ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;

2. Check free unused bytes, returns information about the position of the high water mark and the amount of unused space in a segment.

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   VARIABLE total_blocks NUMBER
   VARIABLE total_bytes NUMBER
   VARIABLE unused_blocks NUMBER
   VARIABLE unused_bytes NUMBER
   VARIABLE lastextf NUMBER
   VARIABLE last_extb NUMBER
   VARIABLE lastusedblock NUMBER
   exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'USERINFO', 'TABLE', :total_blocks, -
       :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
       :last_extb, :lastusedblock)
   print

3. Reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete.
   

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE;
   ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;

4. Check unused bytes again, script from step 2.
5. Issue deallocate unused space. Prior to deallocation, you can run the UNUSED_SPACE procedure, script from step 2.
   

   ALTER TABLE SCOTT.USERINFO  DEALLOCATE UNUSED;

6. Check unused bytes again, script from step 2

The LOBSEGMENT SYS_LOB0000155472C00008$$ has been reclaimed wasted space.

Resources

Oracle 10g New Features: Oracle Reclaiming Unused Space
Space, Object, and Transaction Management in Oracle Database 10g: Online Segment Shrink