For one of my projects we ran into an issue with constantly running out of tablespace for the ODI metadata tables. Instead of constantly increasing the operator logs I figured it is a better option to purge the logs periodically. This blogs walks through the steps on how to achieve that. 

 To purge the ODI operator logs we can create a new ODI package to purge the operator logs. 

Step 1:  Define the variable to hold the dates to be purged from 

From Designer tab in ODI Studio, navigate to Projects > BI Apps Project > Variables. Then right click to create new variable called END_DATE_FOR_PURGE as below.

Choose Date as data type for this variable.

Click on Refreshing tab, then select schema DW_BIAPPS11g and enter the query as below.

SELECT SYSDATE-30 FROM DUAL

Click on Save icon on the left corner of ODI Studio.

Step 2:  Define the mapping to use this variable to purge

Now navigate to Projects > Mappings > Custom_SILOS. Right click to create New Sub-Folder called ODIpurgelog and then click Save icon on left corner.Open ODIpurgelog folder and right click on Packages to create new ODI package called ODIpurgelog. Click on Diagram tab of the package. Drag & Drop END_DATE_FOR_PURGE variable to package diagram.

In diagram, insert OdiPurgeLog object and design package as shown below.

Click on Odi PurgeLog 1 and change configuration of this object as bolow.

Purge Type: All
End Date: #END_DATE_FOR_PURGE
Purge Reports: Yes
Archive Purged Objects: No



Click save and right click on the new created package and select execute to purge ODI Operator log.

Deending on the size of the current operator log it might take a few hours to purge the log initially. 

Step 3:  Scheduling the ODI operator log purging

Right click on ODIpurgelog package and select Generate Scenario to generate scenario for this package. 

Navigate to Load Plans and Scenarios > Generated Load Plans. Right click to create new load plan called ODI Purge Log.

Drag & Drop new scenario created above to the new load plan as shown below. Change Restart behavior to Restart from failed step and change Scenario version to -1.

Click Save icon on the left corner of ODI Studio.

Right click on ODI Purge Log load plan and select New Scheduling.

Configure the scheduling for this load plan as shown below. Then click Save icon on the left corner of ODI Studio.

Go to Topology tab. Right click on OracleDIAgent and select Update Schedule.

The schedule should be updated successfully.

If you want to discuss how to use this functionality or in general how to leverage on your BICS investment please feel free to contact us.