how to execute procedure with timer in oracle

I need to clear all records in a table if the data exist for one hour. to know the start time, I have a column "StartTime" with "date" data type. I think I need a timer to开发者_JS百科 do this,

how can I do this in oracle ?

Depending on what your exact requirements are, I would probably look at using a view to only show the valid rows when queried. This would make it seem like only the last one hour of records were available. This would also mean that you don't need to remove rows exactly an hour after they are created.

Then to remove the rows, I would look at using DBMS_JOB or DBMS_SCHEDULER to remove the rows as has been suggested in some of the other answers.

Remember that just because your requirement is to clear the rows from the table after an hour, you probably really only need to remove the ability to query on them, which you could do with a view.

For what version of Oracle?

For version v7.3.4 to 9i, use DBMS_JOB to schedule a task. 10g+, you want to use DBMS_SCHEDULER. It's not clear to me how often you want/need this to run...

You can create scheduled Jobs in Oracle 10G and above using the DBMS_SCHEDULER

If you are really fastidious, you can schedule this job - which calls your procedure - to run every 1 minute so that the data is cleared out as soon as the 60th minute expires.

Refer this link for an example of how to setup / schedule a job via scripts in Oracle 10G

The requirements are not Quite clear.

Is this a job/program that you have to run once and will delete records that existed for more than an hour? If that is the case, you can use..

delete from <table_name>
where StartTime < (sysdate-1/24);
commit;

If you need to purge records constantly, you'll need to schedule this as a job . The frequency will depend on how often you want the records to be deleted.

What is the business case you are trying to solve?