Oracle smon_scn_time table description

1. Structure of the SMON_SCN_TIME table

The SMON_SCN_TIME table stores the mappings between SCN and Time. This table is maintained by the SMON process.

SQL> desc smon_scn_time

Name Null? Type

-----------------------------------------------------------------------------

THREAD NUMBER

TIME_MP NUMBER

TIME_DP DATE

SCN_WRP NUMBER

SCN_BAS NUMBER

NUM_MAPPINGS NUMBER

TIM_SCN_MAP RAW (1200)

SCN NUMBER

ORIG_THREAD NUMBER

 

SQL> alter session set nls_date_format = 'yyyy-mm-ddhh24: mi: ss ';

Session altered.

 

SQL> select time_dp, scn from smon_scn_time where rownum <5;

TIME_DP SCN

-----------------------------

2013-03-15 10:31:04 2092348

2013-03-15 10:35:49 2092452

10:41:00 2092581

10:45:46 2092682

 

 

In Oracle 11g, the SQL statement for this table is in the $ ORACLE_HOME/rdbms/admin/dtxnspc. bsq file.

 

Create table smon_scn_time (

Thread number,/* thread, compatibility */

Time_mp number,/* time this recent scn represents */

Time_dp date,/* time as date, compatibility */

Scn_wrpnumber,/* scn. wrp, compatibility */

Scn_bas number,/* scn. bas, compatibility */

Num_mappings number,

Tim_scn_map raw (1, 1200 ),

Scnnumber default 0,/* scn */

Orig_thread number default 0/* for downgrade */

) Cluster smon_scn_to_time_aux (thread)

/

Create unique index smon_scn_time_tim_idxon smon_scn_time (time_mp)

Tablespace SYSAUX

/

Create unique index smon_scn_time_scn_idxon smon_scn_time (scn)

Tablespace SYSAUX

/

 

We can delete the records in the SMON_SCN_TIME table directly:

SQL> delete from smon_scn_time;

2120 rows deleted.

 

SQL> commit;

Commit complete.

 

SQL> select count (1) from smon_scn_time;

COUNT (1)

----------

0

1 2 3 4 Next Page