Oracle 10g – How to Auto Update a Column Every 24 Hours

oracleoracle-10g

I want to reset the values of a column every 24 hours. Is it possible? The column contains numeric values.

It's a ticket reservation system. I need to reset the number of tickets.

Best Answer

Sure, just create a job. For example, update column every midnight:

begin
  dbms_scheduler.create_job
  (
   job_name             => 'UPDATE_TABLE',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'begin update table set column = value; commit; end;',
   start_date           => trunc(sysdate+1),
   repeat_interval      => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0',
   enabled              => TRUE,
   auto_drop            => FALSE
  );
end;
/

Related Solutions

Question on Oracle rowid column in every table

I found the answer of my own question. The so called object number from DBMS_ROWID.Rowid_object corresponds to dba_objects.data_object_id, not object_id.

sys@ORCL>select owner, object_name, object_id, data_object_id from dba_objects where data_object_id = 73321;

OWNER    OBJECT_NAME OBJECT_ID  DATA_OBJECT_ID
-------- ----------- ---------- --------------
HR       EMPLOYEES   73953      73321

sys@ORCL>

Support ticket system – when/how to move old tickets

What is the problem with the table becoming large? Generally, any sort of OLTP query will access the table using an appropriate index in which case the size of the table is more or less irrelevant. The cost of using an index will grow at an O(log(n)) rate-- practically, a b*-tree index will only add one or two levels for any realistically sized table. And you can potentially limit that further by using function-based indexes to limit the size of the index by doing things like only indexing the active rows.

The only queries that should care about the size of the table are queries where you want to do a full scan on the table in order to do things like produce metrics about how many tickets have been opened since the beginning of time (or, at least, over a significant fraction of history). If you are concerned about those sorts of reporting queries, you can do things like use materialized views to pre-aggregate the data.

Normally, I would suggest keeping a single table and ensuring that appropriate indexes and/or materialized views exist to support the queries about whose performance you are concerned.

Best Answer

Related Solutions

Question on Oracle rowid column in every table

Support ticket system – when/how to move old tickets

Related Question