Statistics not Being Auto Purged – Splitting Purge

Hi all!
The post Purge SYSAUX Tablespace,  made on Fabruary 8this, is yet being high accessed. So, if  you’re interested, here it goes another post about:

Last week I supported a database was not purging statistics through MMON job, because is timeouting. Worst than simply that, the database is not purging statistics since 2012 and SYSAUX was huge!
To understand: By default, the MMON performs the automatic purge that removes all history older than:
1) current time – statistics history retention (by default 31 days)
2) time of recent analyze in the system – 1
MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged.

The problem was very clear in alert.log, through the entry:

Unexpected error from flashback database MMON timeout action
Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc:
ORA-12751: cpu time or run time policy violation

But it’s happening since 2012! How to address that?
Continue reading

Manipulating JMS queues using WLST Script

Hi.

Today, let’s talk about Java Message Systems (JMS), the reason led me talk about this, is that my environment, a complex architecture of messages where we have more of two hundred queues in the same domain.
The administration of queues in the weblogic console is very simple, but, if you need to remove a million messages, in a hundred queues, you have a problem!
To turn more agile the visualization of messages, state and other queue properties, nothing better than to use WLST.

This post shows a script, which can grow up where you imagine, for while the script have just three options (the most useful to me) and nothing prevents to have more.

Continue reading

Flashback- Part 3 (Flashback Versions Query)

Hi Everyone,

Here we are to continue our Flashback Saga! If you lost our first 2 posts about that and are in the mood for a good reading, please go through the links below:

Flashback Drop

Flashback Query

Today we are going to discuss Flashback Versions Query, which has a strong link with the previous post, the Flashback Query (AS OF). With this feature, we are able to verify all changes made  between 2 points in time in the past, using SCN or a Timestamp. Of course, the Flashback Versions Query will retrieve only the committed data. Just like Flashback Query, the Flashback Versions Query is undo-based, so make sure your undo Tablespace and undo retention period is good enough for you.

Continue reading

APEX: Let’s Talk About Charts Attributes (Inverted Scale)

Hello! If you had play with Apex before, you know how easy is to build a simple report to present your data. But sometimes, your boss will ask you to build something more “graphical” or with a better design. But I never thought in color themes or pictures when I developed my simple reports in Sqlplus. Those colorful themes and design things are, most of the times, not familiar for DBA’s.

Continue reading

Package Body APEX_030200.WWV_FLOW_HELP Invalid after Oracle Text Installing

Hi all!
The package body APEX_030200.WWV_FLOW_HELP become invalid after Oracle Text installation with the follow errors:

Compilation errors for PACKAGE BODY APEX_030200.WWV_FLOW_HELP
#13#10Error: PL/SQL: ORA-00942: table or view does not exist
Line: 189
#13#10Error: PL/SQL: SQL Statement ignored
Line: 188
#13#10Error: PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
Line: 191
#13#10Error: PL/SQL: Statement ignored
Line: 191
#13#10Error: PL/SQL: ORA-00942: table or view does not exist
Line: 197
#13#10Error: PL/SQL: SQL Statement ignored
Line: 196
#13#10Error: PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
Line: 199
#13#10Error: PL/SQL: Statement ignored
Line: 199
#13#10Error: PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared
Line: 261
#13#10Error: PL/SQL: Statement ignored
Line: 261
#13#10Error: PLS-00201: identifier 'CTX_DDL.SET_ATTRIBUTE' must be declared
Line: 262
#13#10Error: PL/SQL: Statement ignored
Line: 262
#13#10Error: PLS-00201: identifier 'CTX_DDL.SET_ATTRIBUTE' must be declared
Line: 265
#13#10Error: PL/SQL: Statement ignored
Line: 265
#13#10Error: PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared
Line: 280
#13#10Error: PL/SQL: Statement ignored
Line: 280
#13#10Error: PLS-00201: identifier 'CTX_DOC.FILTER' must be declared
Line: 292
#13#10Error: PL/SQL: Statement ignored
Line: 292
#13#10Error: PLS-00201: identifier 'CTX_DOC.FILTER' must be declared
Line: 312
#13#10Error: PL/SQL: Statement ignored
Line: 312

It happens bassically because APEX schema has not been granted with execute privileges for CTX_DDL and CTX_DOC. The note below it’s exactly about it:
The WWV_FLOW_HELP PACKAGE Status is Invalid After Installing Oracle Text (Doc ID 1335521.1)

The solution is simple:

mydb> grant execute on ctx_ddl to APEX_030200;
Grant succeeded.
mydb> grant execute on ctx_doc to APEX_030200;
Grant succeeded.
mydb> alter package APEX_030200.WWV_FLOW_HELP compile;
Package altered.
mydb> alter package APEX_030200.WWV_FLOW_HELP compile body;
Package body altered.

Have a nice day!
Matheus.