Why is this:
SQL> select scn_to_timestamp(ora_rowscn) timestamp from EMP;
select scn_to_timestamp(ora_rowscn) timestamp from EMP
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
That's because you did not read the 1st comment of Tom Kyte from the article I linked earlier.
>I tried to recommend trigger but it was disapproved by the management since the perfornance is very critical now. They said trigger will mirror the table and hence will have big impact on performance?
Are you asking ? Anyway, they have to choose, you have to think what you could do to make "them" change their mind (benchmark).
How do I know if my table contents in 1 day is within the valid SCN?
Supposing my tables is only doing pure INSERT (NO UPDATE, NO DELETE) will the ora_rowscn be reliable?
I created a sample table like below and inserted couple of rows:
create table table1(id number,amount number);
insert into table1 values (1,20);
insert into table1 values (2,25);
insert into table1 values (3,-5);
To store date and sum of amount of table1, I created table2 like below:
create table table2(run_date date,sum_of_amount number);
Now, I am creating a procedure who will sum amount and store it into table2.
create or replace procedure prc_sum_amount_table1
execsql:='insert into table2 values ((select sysdate from dual),(select sum(amount) from table1))';
execute immediate execsql;
Now, I am creating and submitting a job which will run every day every one hour.
job_name => 'sum_amount_table1',
job_type => 'STORED_PROCEDURE',
job_action => 'prc_sum_amount_table1',
repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
enabled => TRUE,
comments => 'Every 1 hour sum amount of table1 and output to table2'
Since above is just a simple demo of jobs with scheduler, so I am dropping that job:
Is that you want ?
Of course, using a trigger will have a performance impact, but you have nothing at no cost.
Ok, probably my last post here because you still did not even provided any table desc, nor effort to test anything.
Here I go with a small and dirty test. Saying 1036800 is the number of rows you approximately have to insert per day:
SQL> create or replace trigger trg_ai_mytable
2 after insert on mytable for each row
4 insert into mytable_date(myid,mydate) values (:new.id,sysdate);
SQL> insert into mytable select rownum as rn, 'My string '||rownum as str from dual connect by level <=1036800;
1036800 rows created.
129 seconds to insert 1036800 rows with a trigger
You said, you have about 12 rows/sec, I inserted here 1036800/129=8037.2 rows/sec, means 669.7 times more than your requirement. Of course it depends of many factors.
I do not say it's an ideal solution, but now your on your own to do your own conclusion/test/benchmark and so forth.
But that is not what I wanted. The sample of yours will count everything over and over again.
This is it:
Supposing I have 24,000 rows inserted on EMP table for the whole day of 24 hrs.
1st hr > 1000
2nd hr > 1000
3rd hr > 1000
...and so on
But in your program I will have duplicate counts like:
1st hr > 10000
2nd hr > 2000
3rd hr > 3000
Ok I will do benchmark and recommend to my boss if the result is positive. Thanks
Honestly, I am sad, I missed sb and sybrand and their comments. I used to it and it seems been part of my life