12-11-2013 01:08 AM
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.
12-11-2013 03:47 AM
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.
12-11-2013 05:41 AM
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>
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)
12-11-2013 07:53 AM
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.
12-11-2013 10:03 AM
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.
12-11-2013 01:57 PM
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.
12-12-2013 01:26 AM
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.