scn_to_timestamp问题

显示全部楼层 · 2009-9-29 15:44:15

大家帮我看一下,这个函数怎么不好用呢?

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select scn_to_timestamp(dbms_flashback.get_system_change_number) from dual;
select scn_to_timestamp(dbms_flashback.get_system_change_number) from dual
*
ERROR at line 1:
ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------

775629
SQL>
SQL> show user;
USER is "SYS"

复制代码

千问 · 2009-9-29 15:44:15

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE
10.2.0.4.0
Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select scn_to_timestamp(dbms_flashback.get_system_change_number) from dual;
SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
---------------------------------------------------------------------------
21-APR-10 04.04.02.000000000 PM

千问 · 2009-9-29 15:44:15

select scn_to_timestamp(775629) from dual;
单独执行试试

千问 · 2009-9-29 15:44:15

我之前还好用,做了一次不完全恢复,就不好用了

千问 · 2009-9-29 15:44:15

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------

775629
SQL> show user;
USER is "SYS"
SQL> select scn_to_timestamp(775629) from dual;
select scn_to_timestamp(775629) from dual
*
ERROR at line 1:
ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier

SQL>
这个函数丢了

千问 · 2009-9-29 15:44:15

select * from dba_source where name='SCN_TO_TIMESTAMP'
有结果没

千问 · 2009-9-29 15:44:15

有啊
1select * from dba_source where name='SCN_TO_TIMESTAMP'
2*
SQL> /
OWNER
NAME
TYPE
LINE
------------------------------ ------------------------------ ------------ ----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
SYS
SCN_TO_TIMESTAMP
FUNCTION
1
function scn_to_timestamp(query_scn IN NUMBER)
SYS
SCN_TO_TIMESTAMP
FUNCTION
2
return TIMESTAMP
SYS
SCN_TO_TIMESTAMP
FUNCTION
3
IS EXTERNAL
SYS
SCN_TO_TIMESTAMP
FUNCTION
4
NAME "ktfexscntot"
SYS
SCN_TO_TIMESTAMP
FUNCTION
5
WITH CONTEXT
SYS
SCN_TO_TIMESTAMP
FUNCTION
6
PARAMETERS(context,
SYS
SCN_TO_TIMESTAMP
FUNCTION
7
query_scn OCINUMBER,
SYS
SCN_TO_TIMESTAMP
FUNCTION
8
RETURN)
SYS
SCN_TO_TIMESTAMP
FUNCTION
9
LIBRARY DBMS_TRAN_LIB;

9 rows selected.

千问 · 2009-9-29 15:44:15

select * from dba_OBJECTs where object_name = 'SCN_TO_TIMESTAMP'; 看看对象在不在。
[ 本帖最后由 inthirties2 于 2010-4-21 16:19 编辑 ]

千问 · 2009-9-29 15:44:15

状态也是正常的阿
SQL> select * from dba_OBJECTs where object_name = 'SCN_TO_TIMESTAMP';
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUST G S
------------------------------ ---------- -------------- ------------------- --------- --------- ------------------- ------- - - -
SYS
SCN_TO_TIMESTAMP

4440
FUNCTION
17-FEB-08 17-FEB-08 2008-02-17:01:53:19 VALID N N N
PUBLIC
SCN_TO_TIMESTAMP

4442
SYNONYM
17-FEB-08 17-FEB-08 2008-02-17:01:53:19 VALID N N N

千问 · 2009-9-29 15:44:15

create or replace
function scn_to_time(query_scn IN NUMBER)
return TIMESTAMP
IS EXTERNAL
NAME "ktfexscntot"
WITH CONTEXT
PARAMETERS(context,
query_scn OCINUMBER,
RETURN)
LIBRARY DBMS_TRAN_LIB;

然后，select scn_to_time(775629) from dual 试试看。