Scheduling a job in oracle to drop and create table

Question

I am trying to DROP and CREATE a table using a scheduled job. How to do it please?

DROP TABLE REGISTRATION;

CREATE TABLE REGISTRATION AS
SELECT *
FROM REGISTRATION_MV@D3PROD_KMDW

Answer 1

perhaps you are looking for a materialized view instead of the table?

create materialized view registration as
select *
from registration_mv@d3prod_kmdw

and then refresh it in job

dbms_mview.refresh('REGISTRATION');

This is much better than dropping and creating the table because PL/SQL objects will become invalid after dropping the table. With mview it will be silent and without harm.

Answer 2

You should use DBMS_SCHEDULER to schedule jobs in Oracle.

Refer to the documentation for how to create a sample scheduler job

Answer 3

Don't drop and recreate the table. If you do this on a regular basis you will end up with a huge number of items in your recycle bin, which can lead to problems. Also as above you can invalidate pl/sql when you drop objects, and can break your app if they fail to recompile.

Instead you should truncate the table to delete all rows, and then repopulate it:

truncate table registration;

insert into registration (select * from registration_mv@D3PROD_KMDW);