认真就输

rem

rem create the scn<->time tracking table that smon will maintain

rem as a circular queue – notice that we populate the entire

rem table with at least 144000 entries (enough for 5 days).

rem

rem -"thread" is for backward compatibility and is always 0

rem -"orig_thread" is for upgrade/downgrade

rem – scn_wrp, scn_bas, and time_dp are for backward compatibility

rem   and not queried by the ktf layer.

rem

create cluster smon_scn_to_time (

  thread number                         /* thread, compatibility */

)

/

create index smon_scn_to_time_idx on cluster smon_scn_to_time

/

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_wrp number,                        /* scn.wrp, compatibility */

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

  num_mappings number,

  tim_scn_map raw(1200),

  scn number default 0,                  /* scn */

  orig_thread number default 0           /* for downgrade */

) cluster smon_scn_to_time (thread)

/

create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp)

/

create unique index smon_scn_time_scn_idx on smon_scn_time(scn)

/

create cluster smon_scn_to_time_aux (

  thread number                         /* thread, compatibility */

) tablespace SYSAUX

/

create index smon_scn_to_time_aux_idx on cluster smon_scn_to_time_aux

/

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_wrp number,                        /* scn.wrp, compatibility */

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

  num_mappings number,

  tim_scn_map raw(1200),

  scn number default 0,                  /* scn */

  orig_thread number default 0           /* for downgrade */

) cluster smon_scn_to_time_aux (thread)

/

create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp)

  tablespace SYSAUX

/

create unique index smon_scn_time_scn_idx on smon_scn_time(scn)

  tablespace SYSAUX

/

Version

Interval

11g

3 seconds

10g

6 seconds

9.2

5 minutes

SMON wakes up every 5 minutes and checks how many on-disk mappings we have–the max is 144000.

The new mappings are then added for the last period (since SMON last updated), and if this is over 144000, SMON will then issue the delete statement:

delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0)

There will be an execution of this each time SMON wakes to update smon_scn_time, and if one deletion does not free enough mappings, then there will be multiple executions.

alter system set event="12500 trace name context forever, level 10" scope=spfile;