Newsgroups: comp.databases.oracle
Subject: Re: starting a job at specified intervals
References: <31C8BFAE.6FA3@luc.ac.be>
Jan Timmermans writes:
>Hi,
>we have a simple database in ORACLE and each record contains a date of
>insertion. Now we want to automize the process of removing records after
>a certain time following there insertion. Is there a way to tell oracle
>to monitor the database and remove records that are too old.
>If you have any suggestions please mail me.
>Thanks
Jan,
The way to do this depends on the operating system and the version of
Oracle. I can tell you how we do this at our site, which runs Oracle
7.0.16 on a Unix platform.
We schedule a cron job (a Unix concept that runs a script at given times)
that deletes records from journal tables every day. To avoid using a
password in the script, we use sqldba and "connect internal". For this to
work, the cron job must be run by oracle (or a user in the "dba" unix
group). The commands to run are redirected from standard input. An
example follows:
sqldba << EOF
connect internal;
delete from POGO_STICKS_BOUGHT_JRN
where INSERT_DT < sysdate - 5;
EOF
Good luck!
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: www.arikaplan.com <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page