subject         part

stay Oracle in ,SYS.SMON_SCN_TIME What is the function of the base watch ?

          Answer section          

SYS.SMON_SCN_TIME The base table is used to record SCN With specific timestamps (timestamp) Mapping between , Because it's the mapping of sampling records , therefore SMON_SCN_TIME You can locate something roughly SCN Time information of . Actually SMON_SCN_TIME It's a cluster table Cluster table .SMON_SCN_TIME The data of the base table is from SMON Background process to maintain .

stay Oracle 11g in , The creation of this table SQL stay $ORACLE_HOME/rdbms/admin/dtxnspc.bsq In file , Can be viewed directly :

 1create cluster smon_scn_to_time_aux (
 2  thread number                         /* thread, compatibility */
 3) tablespace SYSAUX
 5create index smon_scn_to_time_aux_idx on cluster smon_scn_to_time_aux
 7create table smon_scn_time (
 8  thread number,                         /* thread, compatibility */
 9  time_mp number,                        /* time this recent scn represents */
10  time_dp date,                          /* time as date, compatibility */
11  scn_wrp number,                        /* scn.wrp, compatibility */
12  scn_bas number,                        /* scn.bas, compatibility */
13  num_mappings number,
14  tim_scn_map raw(1200),
15  scn number default 0,                  /* scn */
16  orig_thread number default 0           /* for downgrade */
17) cluster smon_scn_to_time_aux (thread)
19create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp) 
20  tablespace SYSAUX
22create unique index smon_scn_time_scn_idx on smon_scn_time(scn)
23  tablespace SYSAUX

from Oracle 10g Start ,SMON It will be cleaned regularly SMON_SCN_TIME Records in .SMON The background process will every 5 Minute is awakened once , Check SMON_SCN_TIME The total number of mapped records on disk , If the total exceeds 144000 strip , The oldest record will be deleted using the following statement (TIME_MP Column minimum ):

1delete from smon_scn_time
2 where thread = 0
3   and time_mp = (select min(time_mp) from smon_scn_time where thread = 0);

If only deleting one record is not enough to get enough space , be SMON The above will be executed repeatedly DELETE sentence . You can set 12500 The event stopped SMON The process is right SMON_SCN_TIME.

1alter system set events '12500 trace name context forever, level 10';