View the generation time of Oracle table records using one SQL statement

The following describes how to use SQL statements to view the generation time of Oracle table records. If you have encountered a similar problem before, take a look.

Sometimes you need to check when the record is created in the table, some will use logminer to view it, and some will use flashback to query, which is actually very simple.

Don't bother. oracle creates an implicit field ora_rowscn when creating each table. When you see the name of this field, will someone think of scn? By the way, oracle will record the scn of the current database when inserting records into the table, so you will soon think that since each record has an scn, so why can't I check the creation time of each Oracle table record through scn? All right!

With just one sentence, you can easily view the generation time of Oracle table records. I believe this will be helpful!

For example:

Select to_char (scn_to_timestamp (ORA_ROWSCN), 'yyyy-mm-dd hh24: mi: ss') insert_time from scott. emp;

The creation time of each record in the emp table is displayed.

By default, the ORA_ROWSCN record in each row is Block-based, which is inaccurate unless the create table... Rowdependencies), which records the scn at the row level.

Therefore, you must add the rowdependencies option when creating an Oracle table to view the exact creation time!

Learn about Oracle recovery Structure

Explain the physical structure of Oracle

Learn more about Oracle tablespace

Three phases of Oracle Startup Mode

Oracle Database Restart Method