scn_to_timestamp的scn和time的对应关系从何而来?
是来自于系统表smon_scn_time吗?
用10046去trace"select scn_to_timestamp(xxxxxxx) from dual"
并没有发现访问smon_scn_time.
但是对scn_to_timestamp来说,最小的有效的scn是"select min(scn_bas) from smon_scn_time"
SYS@bbb>select min(scn_bas) SCN_BASE from smon_scn_time;
SCN_BASE
----------
3771579140
SYS@bbb>select scn_to_timestamp(3771579140-1) from dual;
select scn_to_timestamp(3771579140-1) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
比"select min(scn_bas) SCN_BASE from smon_scn_time"小的scn会导致
ORA-08181: specified number is not a valid system change number
对scn_to_timestamp来说,最大的有效的scn并不是"select max(scn_bas) from smon_scn_time"
可以比"select max(scn_bas) SCN_BASE from smon_scn_time"大一些
SYS@bbb>select max(scn_bas) SCN_BASE from smon_scn_time;
SCN_BASE
----------
3771603459
SYS@bbb>select scn_to_timestamp(3771603459+1) from dual;
SCN_TO_TIMESTAMP(3771603459+1)
---------------------------------------------------------------------------
19-FEB-17 03.35.55.000000000 AM
删除smon_scn_time中最小的scn=3771579140
SYS@bbb>delete smon_scn_time where scn_bas=3771579140;
1 row deleted.
SYS@bbb>commit;
Commit complete.
当前session仍然可以查scn=3771579140
SYS@bbb>select scn_to_timestamp(3771579140) from dual;
SCN_TO_TIMESTAMP(3771579140)
---------------------------------------------------------------------------
18-FEB-17 09.30.08.000000000 PM
应该是pga里有smon_scn_time的缓存
在新的session里scn=3771579140已不可用
SYS@bbb>select scn_to_timestamp(3771579140) from dual;
select scn_to_timestamp(3771579140) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
是来自于系统表smon_scn_time吗?
用10046去trace"select scn_to_timestamp(xxxxxxx) from dual"
并没有发现访问smon_scn_time.
但是对scn_to_timestamp来说,最小的有效的scn是"select min(scn_bas) from smon_scn_time"
SYS@bbb>select min(scn_bas) SCN_BASE from smon_scn_time;
SCN_BASE
----------
3771579140
SYS@bbb>select scn_to_timestamp(3771579140-1) from dual;
select scn_to_timestamp(3771579140-1) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
比"select min(scn_bas) SCN_BASE from smon_scn_time"小的scn会导致
ORA-08181: specified number is not a valid system change number
对scn_to_timestamp来说,最大的有效的scn并不是"select max(scn_bas) from smon_scn_time"
可以比"select max(scn_bas) SCN_BASE from smon_scn_time"大一些
SYS@bbb>select max(scn_bas) SCN_BASE from smon_scn_time;
SCN_BASE
----------
3771603459
SYS@bbb>select scn_to_timestamp(3771603459+1) from dual;
SCN_TO_TIMESTAMP(3771603459+1)
---------------------------------------------------------------------------
19-FEB-17 03.35.55.000000000 AM
删除smon_scn_time中最小的scn=3771579140
SYS@bbb>delete smon_scn_time where scn_bas=3771579140;
1 row deleted.
SYS@bbb>commit;
Commit complete.
当前session仍然可以查scn=3771579140
SYS@bbb>select scn_to_timestamp(3771579140) from dual;
SCN_TO_TIMESTAMP(3771579140)
---------------------------------------------------------------------------
18-FEB-17 09.30.08.000000000 PM
应该是pga里有smon_scn_time的缓存
在新的session里scn=3771579140已不可用
SYS@bbb>select scn_to_timestamp(3771579140) from dual;
select scn_to_timestamp(3771579140) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/37279/viewspace-2134303/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/37279/viewspace-2134303/