| Approach to Purge Sensitive data periodically. [message #249245] |
Tue, 03 July 2007 14:10  |
divyanand
Messages: 3
Registered: July 2007
|
Junior Member |
|
|
I am working on a Design where I need to purge the user's related sensitive information after a certain time (Once in 3 months). These data are stored in Oracle9i and have date and time stamp on each row . If data is 18 month old then it has to be purged and should not be recovered by any mean .
I have not worked on database level for long time but I feel purging using Oracle provided feature will help in many ways but I am not sure where to look and if this approach is correct. Let me know which component I should use .
Appreciate if you can guide me or recommend me better approach for the same .
Please tell me your suggestion and recommendations and approach .
Regards
Divyanand
|
|
|
|
|
|
| Re: Approach to Purge Sensitive data periodically. [message #249254 is a reply to message #249248] |
Tue, 03 July 2007 15:47  |
divyanand
Messages: 3
Registered: July 2007
|
Junior Member |
|
|
Thanks for your response .
I want to do it programatically using Oracle provide packages and periodic duration I want to keep as property . There could be other business rules also so keeping all these as variables will made is generic .
Same can be done using Tread or Script but we are intrested wihin Oracle using oracle features .
An Oracle job which will run once in 3 months and find our which records are not used and moved in some other table table and timely we clean that .
Is there feature available in Oracle like triggger , procedure which can be created as a Job and it can also create some report also .
Regards
Divyanand
|
|
|
|
|
|
| Re: Approach to Purge Sensitive data periodically. [message #249307 is a reply to message #249254] |
Wed, 04 July 2007 01:06 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
set "job_queue_processes" parameters > 1.
and execute:
declare
job number;
begin
dbms_job.submit(job,
'delete mytable where dateCol<add_months(sysdate,-18);',
sysdate,
'add_months(trunc(sysdate,''Q''),3)');
end;
/
It will execute the job each first day of a quarter at midnight.
SQL> select sysdate, add_months(trunc(sysdate,'Q'),3) next_exec from dual;
SYSDATE NEXT_EXEC
------------------- -------------------
04/07/2007 08:04:14 01/10/2007 00:00:00
1 row selected.
Regards
Michel
|
|
|
|
| Re: Approach to Purge Sensitive data periodically. [message #249663 is a reply to message #249245] |
Thu, 05 July 2007 08:17 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| divyanand wrote on Tue, 03 July 2007 15:10 | These data are stored in Oracle9i and have date and time stamp on each row . If data is 18 month old then it has to be purged and should not be recovered by any mean .
|
In addition to the answer to help you "delete" that data, you will have to fire your DBAs or hire some that don't know how to restore a backup. Or just do away with backups altogether.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
