Home » SQL & PL/SQL » SQL & PL/SQL » Approach to Purge Sensitive data periodically.
Approach to Purge Sensitive data periodically. [message #249245] Tue, 03 July 2007 14:10 Go to next message
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 #249248 is a reply to message #249245] Tue, 03 July 2007 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "delete where dateCol<add_months(sysdate,-18)"
Or reorganize the table to a partition one with one partition by quarter, then add a new partition and drop the first one each quarter.

Regards
Michel
Re: Approach to Purge Sensitive data periodically. [message #249254 is a reply to message #249248] Tue, 03 July 2007 15:47 Go to previous messageGo to next message
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 #249256 is a reply to message #249254] Tue, 03 July 2007 15:54 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
DBMS_JOB <<<<FOR 9I

http://www.unix.org.ua/orelly/oracle/bipack/ch13_04.htm

Note: This package is deprecated and has been supplanted by DBMS_SCHEDULER.
DBMS_SCHEDULER FOR 10G

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/jobtosched.htm

[Updated on: Tue, 03 July 2007 15:59]

Report message to a moderator

Re: Approach to Purge Sensitive data periodically. [message #249307 is a reply to message #249254] Wed, 04 July 2007 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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.
Re: Approach to Purge Sensitive data periodically. [message #249683 is a reply to message #249245] Thu, 05 July 2007 09:51 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Laughing
Re: Approach to Purge Sensitive data periodically. [message #249688 is a reply to message #249683] Thu, 05 July 2007 09:57 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Easy solution :

Have a party every three month where you ritually burn the old backup tapes over a few bottles of beer. Wink

./fa/1602/0/
icon14.gif  Re: Approach to Purge Sensitive data periodically. [message #249758 is a reply to message #249245] Thu, 05 July 2007 15:09 Go to previous message
divyanand
Messages: 3
Registered: July 2007
Junior Member

I am greatful to you guys .
Thanks for all help you given to me .
I have read more about scheduler package and I feel we should able to achive what we wanted to.
I will update if my design get approved by team.

Regards
Divyanand
Previous Topic: Performance issue with an update statement
Next Topic: How to know the locks on a object and the user placed on it
Goto Forum:
  


Current Time: Tue Dec 06 12:16:40 CST 2016

Total time taken to generate the page: 0.05194 seconds