Service Manager / Service Center Practitioners Forum
Showing results for 
Search instead for 
Do you mean 

Purging the HPSM data via Database


Purging the HPSM data via Database



I need to do the purge of probsummary,incidents,ocmq,ocml and cm3r tables via Database directly.


We have Oracle DB.


Can someone let me know if you have queries to perform the purge via DB directly.



Gourav Sood

Honored Contributor

Re: Purging the HPSM data via Database



Try to purge data from application side and not from Database directly if you dont know which table store what kind of data.


From application go to System Definition--> Select Table --> You will find option "Purge data".


this will also purge related data.



Abhijit K

Honored Contributor

Re: Purging the HPSM data via Database

I would agree that you probably want to do this via the tool itself.


However, if for some reason you cannot purge data through the application itself, I think you'd want something as simple as


delete from <tablename>


For example

delete from incidentsm1


To see which tables you need to purge, use dbdict to check the mapping of the Service Manager objects to the underlying database.  For example, use dbdict to look at the incidents table, and check the SQL Tables tab.  All the tablenames listed there hold the records from the Interactions. (ie, incidentsm1, incidentsm2, incidentsa1)


Re: Purging the HPSM data via Database

I have tried from application (Purge/Archive) but it is very slow from application.


So that is why i wanted to do it from DB.


Also can we schedule it from application so that it runs and stops at some particular time.

Honored Contributor

Re: Purging the HPSM data via Database

The advantage of doing the purge from within the application is that the related records to transactional tickets will be deleted also (so not only will an incident be deleted, but its associated activities, attachments, clocks, etc. will all be deleted). 

If you purge directly from the database, none of the related records will be cleaned up. You would need to do that manually.

Kudos - what, where, how, and why
Want Good Answers? Ask Good Questions...
Honored Contributor

Re: Purging the HPSM data via Database

You can schedule the purge to start but there's no option to schedule the purge to stop. I don't think it's a good idea to kill the purge manually in the middle of its task.

One other option is to use System Administration, OnGoing Maintenance, System, Purge/Archive. Set up a query to select records based on a date range to purge so that you can do them in blocks . You can also test out your query by choosing the archive instead of purge and schedule. This will help you work out whether you query will pick the right records and how long the archive took. If you set the data map option, you can see the related records from other tables being selected too.

Always remember plan B, backup your database before your actual purge. If something unexpected goes wrong, you have a recovery method.

Honored Contributor

Re: Purging the HPSM data via Database

Hi Gourav,

Why you need to delete all data ? you should create new system and may take unload of operator profiles,form and other customized objects but have pro and cons.

As John said app will delete related records too so in case of manual you have to identify each associated table first.

And yes from application side it will be very slow and if you have tens of thousands of records in system for each table then application will take weeks or months to delete data.
Also as Jas suggested, you can filter the records to make it fit in delete time.
From DB end it will take seconds to delete data by truncate command.

Assign Kudo, if found post useful and mark it accepted if solves the issue.