ORA-02030: can only select from fixed tables/views
2018-12-19 14:07 潇湘隐者 阅读(4133) 评论(0) 编辑 收藏 举报有时候给一些普通用户授予查询系统对象(例如dynamic performance views)权限时会遇到“ORA-02030: can only select from fixed tables/views”,如下所示:
SQL> grant select on v$session to test;
grant select on v$session to test
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
关于ORA-02030错误介绍如下,也是就是对于fixed tables 或fixed views只能进行SELECT查询,不能做SELECT之外的任何操作
[oracle@DB-Server ~]$ oerr ora 2030
02030, 00000, "can only select from fixed tables/views"
// *Cause: An attempt is being made to perform an operation other than
// a retrieval from a fixed table/view.
// *Action: You may only select rows from fixed tables/views.
关于V$ Views的介绍如下:
V$ Views
The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. Database administrators and other users should access only the V$ objects, not the V_$ objects.
The dynamic performance views are used by Oracle Enterprise Manager, which is the primary interface for accessing information about system performance. After an instance is started, the V$ views that read from memory are accessible. Views that read data from disk require that the database be mounted, and some require that the database be open.
我们查询发现V$SESSION,V$DBLINK都是fixed views,而且v$这类我们经常查的视图都是v_$开头视图的同义词。
SQL> SELECT * FROM V$FIXED_TABLE WHERE NAME IN( 'V$SESSION','V$DBLINK');
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
V$SESSION 4294950919 VIEW 65537
V$DBLINK 4294951157 VIEW 65537
SQL>
SQL> COL OWNER FOR A12;
SQL> COL OBJECT_NAME FOR A32;
SQL> COL OBJECT_TYPE FOR A32;
SQL> SELECT OWNER, OBJECT_NAME ,OBJECT_TYPE
2 FROM DBA_OBJECTS
3 WHERE OBJECT_NAME='V$SESSION';
OWNER OBJECT_NAME OBJECT_TYPE
------------ -------------------------------- --------------------------------
PUBLIC V$SESSION SYNONYM
SQL>
SQL> COL TABLE_OWNER FOR A12;
SQL> COL SYNONYM_NAME FOR A20;
SQL> COL TABLE_NAME FOR A16;
SQL> COL DB_LINK FOR A8;
SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='V$SESSION';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------ -------------------- ------------ ---------------- --------
PUBLIC V$SESSION SYS V_$SESSION
所以要授权就应该执行下面SQL语句
SQL>
SQL> GRANT SELECT ON V_$SESSION TO TEST;
Grant succeeded.
如果遇到这样的错误,直接找到对应同义词对应的视图或基表,然后进行授权,如下所示:
SQL> show user;
USER is "SYS"
SQL> grant select on v$dblink to test;
grant select on v$dblink to test
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL> COL OWNER FOR A12;
SQL> COL OBJECT_NAME FOR A32;
SQL> COL OBJECT_TYPE FOR A32;
SQL> SELECT OWNER, OBJECT_NAME ,OBJECT_TYPE
2 FROM DBA_OBJECTS
3 WHERE OBJECT_NAME=UPPER('v$dblink');
OWNER OBJECT_NAME OBJECT_TYPE
------------ -------------------------------- --------------------------------
PUBLIC V$DBLINK SYNONYM
SQL> COL TABLE_OWNER FOR A12;
SQL> COL SYNONYM_NAME FOR A20;
SQL> COL TABLE_NAME FOR A16;
SQL> COL DB_LINK FOR A8;
SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='V$DBLINK';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------ -------------------- ------------ ---------------- --------
PUBLIC V$DBLINK SYS V_$DBLINK
SQL> grant select on v_$dblink to test;
Grant succeeded.
SQL>

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2015-12-19 SQL SERVER 监控数据文件增长情况
2012-12-19 监听服务管理
2012-12-19 ORACLE同义词总结