1

Topic: How to learn date-time of creation of a line in the table?

How in Oracle to learn date-time of creation of a line in the table?
In table ID it is generated from a sequel. The basis version 11g.

2

Re: How to learn date-time of creation of a line in the table?

It is possible to create a field in the table, to make the trigger on an insertion and to write to this field sysdate.
But in the table it is already full of records and very much it would be desirable to understand when they have been created.

3

Re: How to learn date-time of creation of a line in the table?

verter;
To create in the table a field, to make the trigger on an insertion and to write to this field sysdate.

4

Re: How to learn date-time of creation of a line in the table?

verter;

wrote:

NOROWDEPENDENCIES | ROWDEPENDENCIES
This clause lets you specify whether table will use row-level dependency tracking. With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row. You cannot change this setting after table is created.
ROWDEPENDENCIES
Specify ROWDEPENDENCIES if you want to enable row-level dependency tracking. This setting is useful primarily to allow for parallel propagation in replication environments. It increases the size of each row by 6 bytes.
NOROWDEPENDENCIES

wrote:

SCN_TO_TIMESTAMP
SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. The returned value is of TIMESTAMP datatype. This function is useful any time you want to know the timestamp associated with an SCN. For example, it can be used in conjunction with the ORA_ROWSCN pseudocolumn to associate a timestamp with the most recent change to a row.

.....
stax [url =] [/url]

5

Re: How to learn date-time of creation of a line in the table?

verter wrote:

As in Oracle to learn date-time of creation of a line in the table?

RTFM ORA_ROWSCN, SCN_TO_TIMESTAMP.

6

Re: How to learn date-time of creation of a line in the table?

It is not necessary silly hopes
Any ROWDEPENDENCIES does not rescue
Only a separate field, accordingly filled

7

Re: How to learn date-time of creation of a line in the table?

Dimitry Sibiryakov wrote:

RTFM

That's it, RTFM:

SQL> SELECT SCN_TO_TIMESTAMP (ORA_ROWSCN)
2 FROM EMP
3 WHERE ENAME = ' KING'
4 /
SELECT SCN_TO_TIMESTAMP (ORA_ROWSCN)
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL>
Notes: wrote:

The usual precision of the result value is 3 seconds.
The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.

Well and a maximum without flashback archives ~ 5 days.
SY.

8

Re: How to learn date-time of creation of a line in the table?

verter;
from Pseudocolumns