If you're using the default settings, there's nothing guaranteeing you can do this!
The psuedocolumn ora_rowscn tells you SCN when you inserted a row. You can use scn_to_timestamp to figure out what time this is:
create table t (
x int
);
insert into t values (1);
commit;
select x, ora_rowscn, scn_to_timestamp(ora_rowscn) from t;
X ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
1 26,658,533 04-AUG-2016 08.28.25
But scn_to_timestamp only has a precision of 3 seconds. So you could be a second or two out.
And there's another problem with this. By default every row in the block as the same SCN. So if you insert another row, the SCN for the original increases too:
exec dbms_lock.sleep(2);
insert into t values (2);
commit;
select x, ora_rowscn, scn_to_timestamp(ora_rowscn) from t;
X ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
1 26,658,535 04-AUG-2016 08.28.25
2 26,658,535 04-AUG-2016 08.28.25
You can reduce this by re-creating the table with rowdependencies.
drop table t purge;
create table t (
x int
) rowdependencies;
insert into t values (1);
commit;
select x, ora_rowscn, scn_to_timestamp(ora_rowscn) from t;
X ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
1 26,658,604 04-AUG-2016 08.29.22
exec dbms_lock.sleep(2);
insert into t values (2);
commit;
select x, ora_rowscn, scn_to_timestamp(ora_rowscn) from t;
X ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
1 26,658,604 04-AUG-2016 08.29.22
2 26,658,606 04-AUG-2016 08.29.25
But if you update a row, you're still incrementing the SCN:
exec dbms_lock.sleep(2);
update t
set x = 3
where x = 1;
commit;
X ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
3 26,658,609 04-AUG-2016 08.29.28
2 26,658,606 04-AUG-2016 08.29.25
Another method you could use is flashback query. With this you can find what was in the table at 12am this morning:
select * from t as of timestamp trunc(sysdate);
But this uses undo to figure this out. This brings two issues.
1. Oracle only ensures you can query as far back as the value of the undo_retention parameter. By default this is 15 minutes. So it's unlikely you'll be able to do this!
2. Restarting a database wipes undo, so you can't query across this. And some DDL operations also break your ability to do this. So even if your undo_retention is large enough, you may not be able to go back to the point you want.
You could enable Flashback Data Archive to overcome both of these issues. But this is something you need to enable:
http://www.oracle.com/technetwork/issue-archive/2008/08-jul/flashback-data-archive-whitepaper-129145.pdf https://oracle-base.com/articles/12c/flashback-data-archive-fda-enhancements-12cr1 Bottom line:
If you need to know when someone inserted a row, add a insert_datetime column and record it explicitly!