SYMPTOMS
The following error appears repeatedly in the alert log:
ORA-21780: Maximum number of object durations exceeded.
Alert.log:
Mon Apr 12 17:51:45 2010
Errors in file /oracle_binaries/app/diag/rdbms/prod/PROD1/trace/PROD1_smon_13729.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file /oracle_binaries/app/diag/rdbms/prod/PROD1/trace/PROD1_smon_13729.trc:
ORA-21780: Maximum number of object durations exceeded.
Mon Apr 12 17:51:56 2010
Errors in file /oracle_binaries/app/diag/rdbms/prod/PROD1/trace/PROD1_smon_13729.trc:
ORA-21780: Maximum number of object durations exceeded.
Errors in file /oracle_binaries/app/diag/rdbms/prod/PROD1/trace/PROD1_smon_13729.trc:
ORA-21780: Maximum number of object durations exceeded.
SMON trace contains:
*** 2010-04-12 17:52:36.012
Drop transient type: SYSTPg9CboAG5QgLgQKjA3AEn5Q==
*** 2010-04-12 17:52:36.012
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.
*** 2010-04-12 17:52:41.014
Drop transient type: SYSTPg9CboAG5QgLgQKjA3AEn5Q==
*** 2010-04-12 17:52:41.014
SMON: following errors trapped and ignored:
ORA-21780: Maximum number of object durations exceeded.
*** 2010-04-12 17:52:46.010
Drop transient type: SYSTPg9CboAG5QgLgQKjA3AEn5Q==
CAUSE
A number of bugs have been filed against the same situation but the root cause as to why SMON has problems cleaning up these objects has not been determined. Of the bugs filed, all have been closed by development as non reproducible issues or the bug was abandoned.
The solution provided is from Bug 7485772, closed as "Could Not Reproduce".
This issue only seems to occur in RAC environments.
SOLUTION
There are two possible solutions.
Solution #1
------------
Step 1
Make sure you have a good full backup.
Step 2
spool obj.lis
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set pagesize 1000
select o.* from obj$ o, type$ t
where o.oid$ = t.tvoid and
bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007;
spool off
Step 3
Find the object owner:
Step 4
Drop the objects.
DROP TYPE "SYSTPf/r2wN4keX7gQKjA3AFMSw==" FORCE;
Solution #2
------------
You can prevent SMON to cleanup transient types by setting event 22834 like below.
However, this can cause transient types to increase.
SQL> alter system set events '22834 trace name context forever, level 1';
System altered.
To turn off:
SQL> alter system set events '22834 trace name context off' ;
System altered.