scn time 转换问题 --有人遇到过吗?

[傻儿哥]

这样查出来的scn无法转化成时间.

SQL>  select dbms_flashback.get_system_change_number scn from dual; 

       SCN
----------
9.4586E+12

SQL> ! oerr ora 08181
08181, 00000, "specified number is not a valid system change number"
// *Cause: supplied scn was beyond the bounds of a valid scn.
// *Action: use a valid scn.

SQL> select scn_to_timestamp(9.4586E+12)  from dual; 
select scn_to_timestamp(9.4586E+12)  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

这样查询是可以的.

SQL> select scn_to_timestamp(dbms_flashback.get_system_change_number) from dual;

SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
---------------------------------------------------------------------------
21-OCT-09 10.13.25.000000000 AM

查询原因:
SQL> ! oerr ora 08181
08181, 00000, "specified number is not a valid system change number"
// *Cause: supplied scn was beyond the bounds of a valid scn.
// *Action: use a valid scn.

是不是这个scn
9.4586E+12 是简写，有什么办法变成全写的?

[oracledbalgtu]

select to_char(dbms_flashback.get_system_change_number ) scn from dual; 

[Quote=引用楼主 csucxcc 的回复:]
这样查出来的scn无法转化成时间.
SQL code
SQL>select dbms_flashback.get_system_change_number scnfrom dual;

SCN----------9.4586E+12

SQL> ! oerr ora0818108181,00000, "specifiednumberisnot a valid system changenumber"//*Cause: supplied scn was beyond the boundsof a valid scn.//*Action:use a valid scn.

SQL>select scn_to_timestamp(9.4586E+12)from dual;select scn_to_timestamp(9.4586E+12)from dual*
ERROR at line1:
ORA-08181: specifiednumberisnot a valid system changenumber
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line1

这样查询是可以的.
SQL code
SQL>select scn_to_timestamp(dbms_flashback.get_system_change_number)from dual;

SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)---------------------------------------------------------------------------21-OCT-0910.13.25.000000000 AM


查询原因:
SQL> ! oerr ora 08181
08181, 00000, "specified number is not a valid system change number"
// *Cause: supplied scn was beyond the bounds of a valid scn.
// *Action: use a valid scn.

是不是这个scn
9.4586E+12 是简写，有什么办法变成全写的?


[/Quote]

[傻儿哥]

哈哈,清楚了
SQL> select to_char(dbms_flashback.get_system_change_number ) scn from dual;

SCN
----------------------------------------
9458563299674

SQL>

谢谢 oracledbalgtu

[傻儿哥]

SQL> select scn_to_timestamp(9458563299674) from dual;

SCN_TO_TIMESTAMP(9458563299674)
---------------------------------------------------------------------------
21-OCT-09 10.58.07.000000000 AM

SQL>
结帖给分