Oracle smon_scn_time 表 说明
jimliu00 2013-03-29
一．SMON_SCN_TIME 表结构说明
SMON_SCN_TIME表存放的是SCN和Time之前的映射关系。 该表由SMON 进程负责维护。
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
2013-03-15 10:41:00 2092581
2013-03-15 10:45:46 2092682
在Oracle 11g中，该表的创建SQL在$ORACLE_HOME/rdbms/admin/dtxnspc.bsq 文件中。
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(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
/
我们可以直接delete掉SMON_SCN_TIME表中的记录：
SQL> delete from smon_scn_time;
2120 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from smon_scn_time;
COUNT(1)
----------
0
