原创
O7_DICTIONARY_ACCESSIBILITY是针对select any table权限控制是否可以访问相关数据字典的
今天在数据库ORACLE 10G测试了下 AUTOTRACE 功能 发现原来10G中不用在手工创建PLAN_TABLE了可以直接进行SET AUTOTRACE ON。
我首先建立测试用户TEST 密码为TEST
create user test identified by test
然后赋予权限
grant connect to test
grant select any table to test
grant CREATE any table to test
然后创建PLUSTRACE角色，并且赋予给TEST
grant plustrace to test
然后切换用户到 TEST
connect test/test@win10g
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
PLUSTRACE
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE ANY TABLE
SELECT ANY TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
但是在进行SET AUTOTRACE ON 报错
SQL> set autotrace on
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用STATISTICS报告时出错
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
PLUSTRACE
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: 生成AUTOTRACE EXPLAIN报告时出错
分析下 ORA-01039: insufficient privileges on underlying objects of the view 可以发现可能是因为对底层的数据字典表的访问权限不够所以可能涉及到参数O7_DICTIONARY_ACCESSIBILITY
SQL> show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
然后修改
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
系统已更改。
SQL> SHUTDOWN IMMEDIATE
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 369098752 bytes
Fixed Size 788952 bytes
Variable Size 342619688 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
数据库装载完毕。
数据库已经打开。
SQL> SHOW PARAMETER O7
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
然后更改用户
SQL> set autotrace on;
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
PLUSTRACE
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=25 Card=1 Bytes=28
)
1 0 NESTED LOOPS (Cost=25 Card=1 Bytes=28)
2 1 FIXED TABLE (FULL) OF 'X$KZSRO' (TABLE (FIXED)) (Cost=24
Card=1 Bytes=13)
3 1 TABLE ACCESS (CLUSTER) OF 'USER$' (CLUSTER) (Cost=1 Card
=1 Bytes=15)
4 3 INDEX (RANGE SCAN) OF 'I_USER#' (INDEX) (Cost=0 Card=1
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
现在已经正常了
