AWS RDS ORACLE DBMS_SCHEDULER to run a purgue every minute

Question

I want to use the ORACLE DBMS_SCHEDULER on my AWS RDS ORACLE

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Scheduler.html

to do the following command every minute:

delete from MYTABLE.RECEIVED_TOKEN where EXPIRY_DATE < systimestamp and rownum <= 1;
commit;
exit

can I do that with this scheduler? I want to avoid the possibility to use a Lambda if it is possible.

I don´t understand too much about how it works or if I can schedule something like that.

Answer 1

I don't know AWS.

---

As this is an Oracle database, use its scheduling capabilities. How? "Convert" that delete statement into a stored procedure which will then be scheduled by (older and somewhat simpler) DBMS_JOB or (modern, improved and more complex) DBMS_SCHEDULER package.

Here's example.

Procedure:

SQL> CREATE OR REPLACE PROCEDURE p_del_rt
  2  IS
  3  BEGIN
  4     DELETE FROM received_token
  5           WHERE     expiry_date < SYSTIMESTAMP
  6                 AND ROWNUM <= 1;
  7
  8     COMMIT;
  9  END;
 10  /

Procedure created.

Daily job which runs at 02:00 (2 past midnight):

SQL> BEGIN
  2     DBMS_SCHEDULER.CREATE_JOB (
  3        job_name    => 'delete_received_token',
  4        job_type    => 'PLSQL_BLOCK',
  5        job_action  => 'BEGIN p_del_rt; end;',
  6        start_date  =>
  7           TO_TIMESTAMP_TZ ('10.01.2023 02:00 Europe/Zagreb',
  8                            'dd.mm.yyyy hh24:mi TZR'),
  9        repeat_interval  =>
 10           'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=2; BYMINUTE=0',
 11        enabled     => TRUE,
 12        comments    => 'Delete rows whose expiry date is less than "right now"');
 13  END;
 14  /

PL/SQL procedure successfully completed.

What is it set to?

SQL> SELECT job_action,
  2         TO_CHAR (next_run_date, 'dd.mm.yyyy hh24:mi:ss') next_run_date
  3    FROM USER_SCHEDULER_JOBS
  4   WHERE job_name = 'DELETE_RECEIVED_TOKEN';

JOB_ACTION           NEXT_RUN_DATE
-------------------- -------------------
BEGIN p_del_rt; end; 11.02.2023 02:00:00

SQL>

---

So that we wouldn't wait until tomorrow, I'll run the job manually. This is table contents before (dates are in DD.MM.YYYY format) (today is 10.02.2023, which means that ID = 1 and ID = 2 have expiry_date less than today):

SQL> SELECT * FROM received_token;

        ID EXPIRY_DATE
---------- ------------
         1 23.12.2022
         2 28.01.2023
         3 13.08.2023

SQL> BEGIN
  2     DBMS_SCHEDULER.run_job ('delete_received_token');
  3  END;
  4  /

PL/SQL procedure successfully completed.

Table contents after:

SQL> SELECT * FROM received_token;

        ID EXPIRY_DATE
---------- ------------
         2 28.01.2023
         3 13.08.2023

SQL>

---

Apparently, it works. Though, I'm not sure what you meant to say by using the following condition:

and rownum <= 1

Why do you want to restrict number of rows deleted to (at most) one? (it'll be zero if no row's expiry_date is less than systimestamp). Without that condition, both ID = 1 and ID = 2 rows would have been deleted.

No problem with me, just saying.