Watermark table - should old records be deleted

[Colleen]

The sys_watermark table on our production instance contains over 13 million records, some records are over 15 years old.  I want to create a data cleanup rule to delete watermark records more than 5 years old .

I reckon that deleting old watermark records would make lookups on the table faster.  Is there any good reason not to delete old watermark records?

Update:

I've created both table cleanup (sys_auto_flush) and data management (sys_dm_policy) to delete records from sys_watermark. Neither worked. It appears that only users with the maint role can delete sys_watermark records. 

I could try creating an access rule that enables users with the admin role to delete sys_watermark records, but am not sure whether that is advisable.

Therefore my revised question is:

Has anyone managed to mass delete old sys_watermark record?

[Colleen]

My original question may be irrelevant since it does not seem to be possible to delete records from sys_watermark.

I tried creating a data management policy (sys_dm_policy.) with an associated sys_auto_flush record for sys_watermark. I verified that there was an hourly DMScheduler job in sys_trigger. I checked the sys_auto_flush_run table an hour later and there was no record of the job running.

I deactivated the data policy record, and created a Data Management Delete Job (sys_dm_delete) record to delete sys_watermark records more than 15 years old. When I clicked "Execute Now", this message was displayed.

The user does not have the necessary permissions to delete records from table sys_watermark

I checked the access control rules on sys_watermark and only users with the maint role can delete sys_watermark records. I am logged in as an admin user, but gs.hasRole('maint') returns false.

I could try creating an access control rule to enable admin users to delete sys_watermark records, but I'm not sure whether that would be advisable.

Therefore, the question is:

Has anyone managed to delete old records from sys_watermark?

[Chaitanya ILCR]

Hi @Colleen ,

we have not taken any action

we similar issue sys_watermark records are being piled up last 10+ years

But

I don't see any use of having them once the related email is deleted. 

we can set the cascade delete and once destroy rule of the email archival runs these might get deleted

or else you would have to create an ACL to get the delete access and create a scheduled job and loop through few thousand records everyday orderby a to z on sys_created_on field and check if the email record still exists in the sys_email table (this step because even after the record is deleted the email field is still storing the sys_id of the email since there is cascade rule defined)

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya

[Mark Roethof]

Hi there,

On the out-of-the-box Delete ACL, you could add another role, like admin. That would give you access to delete any records, or creating a Table Cleaner, etc..