Monday, December 29, 2008

HOW TO CLEAR WLI_PROCESS_EVENT

Can I clear WLI_PROCESS_EVENT, the system is using 29 GB of LOB space and I have an idea that WLI_PROCESS_EVENT is responsible for it. The reason of clearing is DB is getting full and we don't want historic data, and when server crash it took longer to come back.

Some Counts from the DB (Those with CLOB's/BLOB's):
SELECT COUNT(*) FROM WLI_CALENDAR
-- 1
SELECT COUNT(*) FROM WLI_PROCESS_DOCUMENT
-- 12
SELECT COUNT(*) FROM WLI_PROCESS_EVENT
-- 638564
SELECT COUNT(*) FROM WLI_PROCESS_TRACKING
-- 81
SELECT COUNT(*) FROM WLI_WORKLIST_DATA
-- 0
SELECT COUNT(*) FROM WLI_MT_CONTENT 
-- 0
SELECT COUNT(*) FROM WLI_PROCESS_INSTANCE_INFO;
-- 112

Solution: There are large amount of orphaned events which has a LOB column, so basically get rid of them:
Run this SQL Query and commit

$ Update  <DB_SCHEMA_NAME>.WLI_PROCESS_INSTANCE_INFO SET PROCESS_STATUS = 5 WHERE PROCESS_STATUS = 1;

Go to wliconsole and do a manual purge. Run this SQL Query and commit:

Run this SQL Query and commit:
DELETE FROM WLI_PROCESS_EVENT WHERE PROCESS_INSTANCE IN (SELECT WLI_PROCESS_EVENT.PROCESS_INSTANCE FROM WLI_PROCESS_EVENT LEFT OUTER JOIN WLI_PROCESS_INSTANCE_INFO ON WLI_PROCESS_EVENT.PROCESS_INSTANCE = WLI_PROCESS_INSTANCE_INFO.PROCESS_INSTANCE WHERE WLI_PROCESS_INSTANCE_INFO.PROCESS_INSTANCE is NULL)

NOTE: Because I'm using Oracle, the LOB segment did not shrink, it only freed up blocks. To free up space I need to drop the column (which will drop the lobsgment) and recreate the column OR truncate the table.

No comments:

Post a Comment