Oracle Fusion Middleware & Application Server

Wednesday, March 21, 2018

Oracle SOA Suite 12c - Purge

Many people struggle with the configuration and execution of the SOA Purge functionality under Oracle SOA Suite 12c.

Oracle provided with SOA Suite 12c a nice web interface for enabling, and scheduling the AutoPurge functionality within the Enterprise Manager Fusion Middleware Control 12c.

But in case you just enable, schedule and define your retention time for the AutoPurge within this web interface nothing will be happen :-( You have to modify in addition some MBeans in order to enable and execute your AutoPurge Schedules correctly.

Simply login to your Enterprise Manager Fusion Middleware Control 12c (usually http://servername:/em), open the Target Navigation, navigate to "SOA/soa-infra" (in case you got a clustered environment, simply pick one of the available soa-infra, changes will be valid for all cluster members):

Under your soa-infra open the menu "SOA Infrastructure / SOA Administration / Auto Purge"

In the Auto Purge option you will have to define multiple sections.

  • Pick your "Auto Purge Job":
    • SOA Flow Purge Job 1
    • SOA Flow Purge Job 2
    • SOA In-Memory Flow Purge Job
    • Integration Workload Statistics Purge Job
    • Health Check Purge Job
  • Activate the "Enable" button
  • Define your "Job Schedule"
  • Set your "Retain Data"
  • Hit the Apply Button
  • Finally click the Link "More Auto Purge Configuration Properties...": this will bring you to the System MBean Browser to set the necessary options, so that your Auto Purge Job will really do something :-)

In the System MBean Browser you will be pointed directly to the Application Defined MBeans: AutoPurgeJobConfig:purge.
There you will find the Attribute "PurgeJobDetails", simply click it

Under the Attribute PurgeJobDetails you will see under the Key following Structure:

  • PurgeJobDetails

Below the DELETE_INSTANCES_AUTO_JOB1 you will need to align the Key / Element pairs:

  • maxCreationPeriodDays, default value is -1, which means it's DISABLED !!!
  • minCreationPeriodDays, default value is -1, which means it's DISABLED !!!
  • purgePartitionedComponent, default is false, which means you don't have Partitions in your Metadata Repository tables

In my case, I have defined a retentionPeriod of 7 (under the Auto Purge Option, it's called Retain Data), so I have to set my maxCreationPeriodDays to retentionPeriod + 1 = 8 and my minCreationPeriodDays I define with 250. In case your SOA Metadata Repository was created with the profile LARGE in the Repository Creation Utility (rcu), you will have Partitions in several Metadata Repository tables, so you will need to align the value for purgePartitionedComponent from false to true.

Finally go up to the top of the current page and hit the button "Apply" to save your changes.

Now your Auto Purge Job will really purge data in your SOA Metadata Repository. Either wait to the next scheduled execution or execute the Auto Purge Job manually.

In order to check the executions of your Auto Purge Jobs, you can easily query the table SOA_PURGE_HISTORY under your SOAINFRA schema. Under the columns START_TIME and END_TIME you will see the start and end time of the Purge Job, under the column T you can see if your Job execution was single or parallel loop, under the column THREAD you will find the number of parallel threads and under the column S you will find the status of your Job execution, C = Completed, R = Running

 select * from SOA_PURGE_HISTORY order by 1;
    JOB_NO START_TIME                     END_TIME                       T     THREAD S
---------- ------------------------------ ------------------------------ - ---------- -
       442 14-MAR-18 AM   14-MAR-18 AM   S          0 C
       462 15-MAR-18 AM   15-MAR-18 AM   S          0 C
       482 16-MAR-18 AM   16-MAR-18 AM   S          0 C
       483 17-MAR-18 AM   17-MAR-18 AM   S          0 C
       503 18-MAR-18 AM   18-MAR-18 AM   S          0 C
       523 19-MAR-18 AM   19-MAR-18 AM   S          0 C
       524 20-MAR-18 AM   20-MAR-18 AM   S          0 C
       543 21-MAR-18 AM   21-MAR-18 AM   S          0 C
       544 21-MAR-18 PM   21-MAR-18 PM   S          0 C

Enjoy your SOA Purging :-)

Wednesday, March 14, 2018

Oracle 18c - SQLPlus cute Features

As I got my fingers on the latest Oracle 18c Release, I had the chance to test some cute new features within SQLPlus, which are really helpful.


The new option WINDOW for SET LINESIZE automatically adjust your linesize to your current window size.

As you can see in the below screenshot, the first select was executed without the linesize option, so you will have the usual line break in your result set. Afterwards I execute "SET LINESIZE WINDOW", re-executed the select statement and you can see that the linesize is automatically adjusted to my current window size.


This new option SQL_ID for SET FEEDBACK ON is my personal favorite. This option will provide you on the end of your executed SQL Statement the SQL_ID.

sqlplus test_new/Oracle18c@pdb01
SQL> set feedback on sql_id
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
T1                             TABLE
V1                             VIEW

2 rows selected.

SQL_ID: adcm6b60qf64q

Enjoy the new 18c Features :-)

Friday, March 9, 2018


2 years ago, I have published an article series about the undocumented feature "ALTER USER RENAME":

As I was getting access to an Oracle 18c Database, I was trying directly, if the ALTER USER RENAME still works => IT'S STILL WORKING :-)

Let's create a test user with one table and one view.
sqlplus sys/<password>@pdb01 as sysdba

SQL> select banner from v$version;

Oracle Database 18c Enterprise Edition Release - Production

SQL> create user test identified by "Oracle18c" default tablespace users quota unlimited on users;

User created.

SQL> grant connect, resource, create table, create view to test;

Grant succeeded.

SQL> connect test/Oracle18c@pdb01

SQL> create table t1 (id number, col1 varchar2(20));

Table created.

SQL> insert into t1 values (1,'Test 1');

1 row created.
SQL> insert into t1 values (2, 'Test 2');

1 row created.

SQL> commit;

Commit complete.

SQL&gt create view v1 as select * from t1;

Now let's rename the above created user TEST to TEST_NEW
sqlplus sys/<password>@pdb01 as sysdba

SQL> alter session set "_enable_rename_user"=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter user test rename to test_new identified by "Oracle18c";

User altered.

SQL> alter system disable restricted session;

System altered.

Now let's try to connect with the renamed user TEST_NEW
sqlplus test_new/Oracle18c@pdb01 as sysdba

SQL> select * from t1;

---------- --------------------
  1 Test 1
  2 Test 2

SQL> select * from v1;

---------- --------------------
  1 Test 1
  2 Test 2
So even with Oracle18c the undocumented feature ALTER USER RENAME is still working :-)