hello, i am trying to drop a table every 2 hours using dbms_scheduler. This is how my create job code looks like.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DROP_DATA_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN drop_data; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY; INTERVAL=2',
enabled => TRUE
);
END;
However, I got the error
Error at line 2/18: ORA-06550: line 2, column 18: PLS-00225: subprogram or cursor 'DBMS_SCHEDULER' reference is out of scope ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230200", line 801 ORA-06550: line 2, column 3: PL/SQL: Statement ignored
Hence, I tried granting the dbms_scheduler to my account grant execute on dbms_scheduler to ‘xyz@gmail.com’; but it told me that the user or role doesn't exist. So I granted the access to another user i created and it was successful. But when i tried running the command to create job, it still didn't work.
Alternatively, does anyone know of any other methods i could go about dropping my table every 2 hours? I tried to look into automations but I am quite lost as well.
Any input would be much appreciated!