FLASHBACK_TRANSACTION_QUERY和ora-01031

[oracle@linora11g ~]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.1.0.6.0 - Production ON Sat Aug 15 23:20:04 2009
 
Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
SQL> SELECT * FROM v$version;
 
BANNER
---------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS FOR Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
 
[oracle@linora11g ~]$ sqlplus "luoxuan/luoxuan"
 
SQL*Plus: Release 11.1.0.6.0 - Production ON Sat Aug 15 23:22:08 2009
 
Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
SQL> DESC FLASHBACK_TRANSACTION_QUERY;
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 XID                                                RAW(8)
 START_SCN                                          NUMBER
 START_TIMESTAMP                                    DATE
 COMMIT_SCN                                         NUMBER
 COMMIT_TIMESTAMP                                   DATE
 LOGON_USER                                         VARCHAR2(30)
 UNDO_CHANGE#                                       NUMBER
 OPERATION                                          VARCHAR2(32)
 TABLE_NAME                                         VARCHAR2(256)
 TABLE_OWNER                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(19)
 UNDO_SQL                                           VARCHAR2(4000)
 
SQL> SELECT COUNT(*) FROM FLASHBACK_TRANSACTION_QUERY;
SELECT COUNT(*) FROM FLASHBACK_TRANSACTION_QUERY
*
ERROR at line 1:
ORA-01031: insufficient privileges

这里觉得有点奇怪,所以想跟踪一下。跟踪发现,其实访问的是X$KTUQQRY视图。和10g相比,权限问题是不一样的(有兴趣可以自己测试一下)

FLASHBACK_TRANSACTION_QUERY的定义:

SQL> SELECT dbms_metadata.get_ddl('VIEW','FLASHBACK_TRANSACTION_QUERY') FROM dual;
 
DBMS_METADATA.GET_DDL('VIEW','FLASHBACK_TRANSACTION_QUERY')
--------------------------------------------------------------------------------
 
  CREATE OR REPLACE FORCE VIEW "SYS"."FLASHBACK_TRANSACTION_QUERY" ("XID", "STAR
T_SCN", "START_TIMESTAMP", "COMMIT_SCN", "COMMIT_TIMESTAMP", "LOGON_USER", "UNDO
_CHANGE#", "OPERATION", "TABLE_NAME", "TABLE_OWNER", "ROW_ID", "UNDO_SQL") AS
  SELECT xid, start_scn, start_timestamp,
          decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
          commit_scn, commit_timestamp,
          logon_user, undo_change#, operation, TABLE_NAME, table_owner,
          row_id, undo_sql
FROM sys.x$ktuqqry

把FLASHBACK_TRANSACTION_QUERY访问权限给luoxuan,看一下面情况(一般思路,其实不用特别授权):

session 1:

[oracle@linora11g ~]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.1.0.6.0 - Production ON Sun Aug 16 18:37:01 2009
 
Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
 
SQL> GRANT SELECT ON FLASHBACK_TRANSACTION_QUERY TO luoxuan;
 
GRANT succeeded.
 
SESSION 2:
 
[oracle@linora11g trace]$ sqlplus "luoxuan/luoxuan"
 
SQL*Plus: Release 11.1.0.6.0 - Production ON Sun Aug 16 18:37:14 2009
 
Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
SQL> SELECT COUNT(*) FROM FLASHBACK_TRANSACTION_QUERY;
SELECT COUNT(*) FROM FLASHBACK_TRANSACTION_QUERY
                     *
ERROR at line 1:
ORA-01031: insufficient privileges

还是不能访问!!!

继续往下:

SESSION 1:
SQL> REVOKE SELECT ON FLASHBACK_TRANSACTION_QUERY FROM luoxuan;
 
REVOKE succeeded.
 
SQL>  GRANT SELECT ANY TRANSACTION TO luoxuan;
 
GRANT succeeded.
 
SESSION 2:
SQL> SELECT COUNT(*) FROM FLASHBACK_TRANSACTION_QUERY;
 
  COUNT(*)
----------
     80208

session 2就可以访问。

总结:其实如果要回滚已经提交的事务,要有SELECT ANY TRANSACTION才行!


3 Responses to “FLASHBACK_TRANSACTION_QUERY和ora-01031”

  1. admin说道:

    metalink有篇相关文档Doc ID:266536.1

  2. 不轻易恋爱说道:

    小弟也新建了一个博客,希望大家以后多多交流!

  3. 不轻易恋爱说道:

    回访下 喜欢你的文章 多多交流

Post a Comment