Purging Fusion Applications ESS job files
We need to purge the ess job data in the regular intervals in the fusion applications all the ess jobs details will be store in the FUSION_TX_TOOLS table space.. if your are not purging the data in the regular time interval then there will be a huge the growth of the fusion tools table space.. In fusion we can purge the data by Specifying the application and function and programs
Step 1
Step 1
Login to the fusion applications Enterprise Manger which is your default Domain for example if you are Under the Financial Domain then Login to the Fin Em if you are in HCM then log in to the HCM Em
Step 2
Navigate to the Farm_<Default_Domain> -->Scheduling Services ->ESSAPP-->
and click the essserver under
Step 3
Select the scheduling services under the ESSAPP server from the top left side and select the Purge Policies from the drop down menu
Step 4
It will take to the new page and you can click the setup new button to setup new purge policy
Step 5
navigate to the Purge Policy Setup page
Provide the Name for the Purge Policy for example if u want to purge all your import journal related data then provide the naming convention with that
Step 6
Select the Particular module which need to be purged
Step 7
Select the applications which the particular program resides
for ex: we need to purge the import journal's data then we need to select the FinancialsESSApp
Step8
Select the necessary program and which it details to be purged
Step 9
Specify the execution type and summited by the user name if needed
we need to specify the no of retention period which the data will remain in the system
for example if we specify the retention period as 120 then the last 120 days records will be available in the system we need to specify the retention period level for all the parameters like success.. failure..
Click the scheduling process for scheduling it and you can use the existing schedule also or click once to run now with the time specified
Step 11
One the purge policy was create you can see it will be in the wait state until the Specific time
Step 12
We can check the details from the ess server also
After This process in the Em we need to manually purge the data from the database.all the ESS job related data are store under the schema FUSION_ORA_ESS check the tables to know the details of the ESS jobs..
---------------------------------------- ------------------------------ ------------------------------
SCHEMA_INFO FUSION_ORA_ESS FUSION_TS_TOOLS
ESS_CONFIG FUSION_ORA_ESS FUSION_TS_TOOLS
ESS_APP_CONFIGS FUSION_ORA_ESS FUSION_TS_TOOLS
JOB_INCOMPATIBILITY FUSION_ORA_ESS FUSION_TS_TOOLS
ESS_APP_REGISTRATION FUSION_ORA_ESS FUSION_TS_TOOLS
ESS_COORD FUSION_ORA_ESS FUSION_TS_TOOLS
OPERATIVE_BINDINGS FUSION_ORA_ESS FUSION_TS_TOOLS
REQUEST_HISTORY FUSION_ORA_ESS FUSION_TS_TOOLS
REQUEST_PROPERTY FUSION_ORA_ESS FUSION_TS_TOOLS
REQUEST_CONSTRAINT FUSION_ORA_ESS FUSION_TS_TOOLS
REQUEST_CP FUSION_ORA_ESS FUSION_TS_TOOLS
REQUEST_METADATA FUSION_ORA_ESS FUSION_TS_TOOLS
WAIT_QUEUE FUSION_ORA_ESS FUSION_TS_TOOLS
WORK_UNITS FUSION_ORA_ESS FUSION_TS_TOOLS
EVENT_WORK FUSION_ORA_ESS FUSION_TS_TOOLS
REQUEST_INCOMPATIBILITY FUSION_ORA_ESS FUSION_TS_TOOLS
INCOMPATIBILITY_LOCK FUSION_ORA_ESS FUSION_TS_TOOLS
COMMAND_WORK FUSION_ORA_ESS FUSION_TS_TOOLS
REQUEST_ATTRIBUTE FUSION_ORA_ESS FUSION_TS_TOOLS
REQUEST_CONTENT FUSION_ORA_ESS FUSION_TS_TOOLS
ESS_TEMP_REQID FUSION_ORA_ESS
PTEMP_REQUEST FUSION_ORA_ESS FUSION_TS_TOOLS
RECOVERY_DELETE FUSION_ORA_ESS FUSION_TS_TOOLS
FAILED_EXECUTION_HISTORY FUSION_ORA_ESS FUSION_TS_TOOLS
SCRATCH_QUERY_PRINCIPAL FUSION_ORA_ESS FUSION_TS_TOOLS
ESS_WS_ASYNC_INFO FUSION_ORA_ESS FUSION_TS_TOOLS
UNTHROTTLED_WORKASSIGNMENTS FUSION_ORA_ESS FUSION_TS_TOOLS
ASYNC_ALLOCATIONS FUSION_ORA_ESS FUSION_TS_TOOLS
ASYNC_LIMITS FUSION_ORA_ESS FUSION_TS_TOOLS
CONFIGURED_BINDINGS FUSION_ORA_ESS FUSION_TS_TOOLS
EVENT_FILTER FUSION_ORA_ESS FUSION_TS_TOOLS
31 rows selected.
These are the number of tables count from each schema form the fusion which is occupied in the fusion_ts_tools table space
SQL> select distinct(owner),count(table_name)"no_of_tables" from dba_tables where tablespace_name ='FUSION_TS_TOOLS'
2 group by owner
3 order by 2;
OWNER no_of_tables
2 group by owner
3 order by 2;
OWNER no_of_tables
---------------------------------------- ------------
FUSION_ORASDPLS 1
FUSION 1
FUSION_ORASDPXDMS 5
FUSION_ORASDPSDS 7
FUSION_PORTLET 12
FIN_FUSION_MDS_SOA 14
FUSION_MDS_ESS 14
CRM_FUSION_MDS_SOA 14
FUSION_MDS_SPACES 14
PRJ_FUSION_MDS_SOA 14
OIC_FUSION_MDS_SOA 14
FUSION_MDS 14
SETUP_FUSION_MDS_SOA 14
HCM_FUSION_MDS_SOA 14
SCM_FUSION_MDS_SOA 14
PRC_FUSION_MDS_SOA 14
FUSION_ORA_ESS 30
FUSION_ORASDPM 31
FUSION_ACTIVITIES 42
FUSION_OTBI 44
FUSION_IPM 49
FUSION_WEBCENTER 66
FUSION_DISCUSSIONS 69
FUSION_OCSERVER11G 119
FUSION_BIPLATFORM 152
FUSION_ODI 183
CRM_FUSION_SOAINFRA 265
FIN_FUSION_SOAINFRA 265
SETUP_FUSION_SOAINFRA 265
SCM_FUSION_SOAINFRA 265
HCM_FUSION_SOAINFRA 265
PRJ_FUSION_SOAINFRA 265
PRC_FUSION_SOAINFRA 265
OIC_FUSION_SOAINFRA 265
Once you have complete the Purging policy in the EM .. and successfully completed it ..the next step is to delete the metadata from the database directly
connect the database as the Fusion_ora_ess and then run the package
esspurge.purge_requests
The parameter are
systimestamp = which you can specify the date no of days to retention [in this example we have specified 120 and it will hold last 120 days records and purge remaining data ]
The no of request can be purged : you can specify the number according to your wish [in this example we have specified 1000000 as the max number of records to be purged]
SQL> connect FUSION_ORA_ESS
Enter password:
Connected.
SQL> set serveroutput on size unlimited
SQL> set timing on
SQL> execute esspurge.purge_requests(systimestamp - 120,1000000);
PL/SQL procedure successfully completed.
Elapsed: 00:01:03.88
SQL>
FUSION_ORASDPLS 1
FUSION 1
FUSION_ORASDPXDMS 5
FUSION_ORASDPSDS 7
FUSION_PORTLET 12
FIN_FUSION_MDS_SOA 14
FUSION_MDS_ESS 14
CRM_FUSION_MDS_SOA 14
FUSION_MDS_SPACES 14
PRJ_FUSION_MDS_SOA 14
OIC_FUSION_MDS_SOA 14
FUSION_MDS 14
SETUP_FUSION_MDS_SOA 14
HCM_FUSION_MDS_SOA 14
SCM_FUSION_MDS_SOA 14
PRC_FUSION_MDS_SOA 14
FUSION_ORA_ESS 30
FUSION_ORASDPM 31
FUSION_ACTIVITIES 42
FUSION_OTBI 44
FUSION_IPM 49
FUSION_WEBCENTER 66
FUSION_DISCUSSIONS 69
FUSION_OCSERVER11G 119
FUSION_BIPLATFORM 152
FUSION_ODI 183
CRM_FUSION_SOAINFRA 265
FIN_FUSION_SOAINFRA 265
SETUP_FUSION_SOAINFRA 265
SCM_FUSION_SOAINFRA 265
HCM_FUSION_SOAINFRA 265
PRJ_FUSION_SOAINFRA 265
PRC_FUSION_SOAINFRA 265
OIC_FUSION_SOAINFRA 265
Once you have complete the Purging policy in the EM .. and successfully completed it ..the next step is to delete the metadata from the database directly
connect the database as the Fusion_ora_ess and then run the package
esspurge.purge_requests
The parameter are
systimestamp = which you can specify the date no of days to retention [in this example we have specified 120 and it will hold last 120 days records and purge remaining data ]
The no of request can be purged : you can specify the number according to your wish [in this example we have specified 1000000 as the max number of records to be purged]
SQL> connect FUSION_ORA_ESS
Enter password:
Connected.
SQL> set serveroutput on size unlimited
SQL> set timing on
SQL> execute esspurge.purge_requests(systimestamp - 120,1000000);
PL/SQL procedure successfully completed.
Elapsed: 00:01:03.88
SQL>
No comments:
Post a Comment