Chances are you have a workflow that is looping and continuously creating step transitions at a rapid rate. Look at the most recent entries in these tables to see what workflows and steps the entries are associated with.
I agree with Jim, it sounds like you may have some short step timeouts or something that is triggering transitions at an extreme rate. Maybe look at KWFL_STEP_TRANSACTIONS grouped by instance_source_id to see if the number of transitions seems abnormally high for individual requests...something like
select instance_source_id,count(*) from KWFL_STEP_TRANSACTIONS aa join KWFL_STEP_TRANSACTION_HISTORY bb on bb.step_transaction_id = aa.step_transaction_id group by instance_source_id order by 2 desc
If so, maybe you can delete the offending requests. This will cascade to the KWFL_STEP_* tables.
About message, "You successfully deleted 0 request".
This occurs when the deletion from the application is not possible, due to the request being tied to different entity say Project. Check the references section and try again.
Also, if you want to reduce the size of current DB, identify number of older request/package that you can delete. What all modules are you currently using? You can check each module to see if you have old information that can be deleted now.
If you want to maintain the older information, clone the DB and then perform the deletion activity.
we are having the very same problem for one of our customer. The implementer who did the first implementation configured an automatic step with a timeout of 2 minutes. Because of this step, the step_transaction_history table passed 80.000.000 records (80 million).
You have 3 solutions:
1. Live with it. Of course, you should change the configuration of those problematic steps. Also, if you have to create any report / portlet from those tables, you have to really be careful on the indexing for those tables.
2. Partition the tables. This is a solution that HP support agrees to support, since they are not (still!) providing a feature for archiving the requests.
3. Delete the problematic records. Let's say that for 1 request you have 9000 transactions for a specific step. in this case, you should delete all records except for the last one (this is a must), in order to keep the consistency. This is the solution that will dramatically reduce the space required by the database. NB: in order to actually clear the space in the DB server storage, the DBA should defrag / shrink the required tablespaces!
--remember to kudos people who helped solve your problem