ITPub博客

首页 > 数据库 > Oracle > 拥有dba权限的情况下创建视图报"ORA-01031: insufficient privileg"错误

拥有dba权限的情况下创建视图报"ORA-01031: insufficient privileg"错误

原创 Oracle 作者:wxjzqym 时间:2014-04-01 15:21:09 0 删除 编辑

今天在oracle中以dba权限创建一个视图时报ORA-01031的错误,最后确定s这是oracle本身的限制,如果在非sys用户下创建基于数据字典或动态性能视图为基表的视图会报ORA-01031的错误,具体操作过程如下:
版本:10.2.0.5.0(64bit)
1.查看会话角色
SQL> select * from session_roles;on

ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
PLUSTRACE
OLAP_DBA

2.查看会话权限
SQL> select * from session_privs where privilege like '%VIEW%';

PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
UNDER ANY VIEW
MERGE ANY VIEW

3.查询数据字典
SQL> select count(*) cnt from dba_data_files;

  COUNT(*)
----------
        24

4.创建基于数据字典的视图
SQL> create view v as select count(*) cnt from dba_data_files;
create view v as select count(*) cnt from dba_data_files
                                      *
ERROR at line 1:
ORA-01031: insufficient privileges

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20801486/viewspace-1133999/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-15

  • 博文量
    100
  • 访问量
    474525