大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说ORACLE重编译同义词(synonym)遇到ORA-01031总结,希望您对编程的造诣更进一步.
我们在编译INVALID的同义词(synonym)的时候，可能在某些版本会遇到一些特殊权限问题，具体来说是遇到ORA-01031错误。下面构造这样一个例子：
如下所示，scott用户下面创建了一个公共同义词emp（下面测试环境为Oracle 10.2.0.5）
SQL> show user;
USER is "SCOTT"
SQL> create public synonym emp for scott.emp;
Synonym created.
SQL>
假设我们需要重新编译同义词，在system用户下去编译同义词时，就会遇到ORA-01031
SQL> show user;
USER is "SYSTEM"
SQL> alter public synonym emp compile;
alter public synonym emp compile
*
ERROR at line 1:
ORA-01031: insufficient privileges
另外一种情况也会遇到这个错误，UserA 去编译UserB下的同义词的话，也会遇到这个错误。按照我的理解，如果用户拥有CREATE ANY SYNONYM和DROP ANY SYNONYM权限的话，应该是可以编译其它SCHEMA下的同义词。而且SYSTEM用户被授予了DBA角色，DBA角色拥有CREATE ANY SYNONYM 或DROP ANY SYNONYM的权限。
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE IN("CREATE ANY SYNONYM","DROP ANY SYNONYM") AND GRANTEE="DBA";
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP ANY SYNONYM YES
DBA CREATE ANY SYNONYM YES
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE="SYSTEM";
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SYSTEM AQ_ADMINISTRATOR_ROLE YES YES
SYSTEM DBA YES YES
SYSTEM MGMT_USER NO YES
但是metalink上的文档Doc ID 1299001.1提示：同义词的编译时，要么是sys用户或者同义词的OWNER才能编译，也就是说，你要编译同义词emp，必须是scott用户或sys用户。
SQL> show user
USER is "SYS"
SQL> alter public synonym emp compile;
Synonym altered
所以在Oracle 10g下要编译其它schema下的同义词，必须是sys用户。Ora-01031 When Compiling A Synonym (Doc ID 1299001.1)的介绍如下：
CAUSE
This is the expected behaviour. The command “alter synonym <name> compile” is not documented and as such it is not supported. Although it works in some circumstances it is not meant to be used by the users directly.
SOLUTION
This is the expected behaviour. If all the synonyms have to compiled then run the recompilation script as SYS.
文档Ora-01031 When Executing Alter Synonym Compile For Other Schema (Doc ID 1435898.1)中也有相关简单阐述
在Oracle 11.2.0.4.0中测试也是如此。但是后续官方文档中将这个现象视为一个Bug，官方文档4189542 – Only SYS can “alter synonym… compile” for another users”s synonym (Doc ID 4189542.8)中有简单介绍，并且在后面的一些版本中修复了这个Bug，Doc ID 4189542.8的具体信息如下如下：
Only SYS can “alter synonym… compile” for another users”s synonym.
Other users get ORA-1031 attempting to compile another users synonym.
With this fix a user with both CREATE ANY SYNONYM and DROP ANY SYNONYM
privilege is allowed to issue ALTER SYNONYM .. COMPILE for another users synonym.
相同的现象在不同文档中有不同阐述，确实有点让人迷惑，如果获取的信息不全或比较片面的话，可能对这个问题和现象有不同的理解。
参考资料：
Ora-01031 When Compiling A Synonym (Doc ID 1299001.1)
Ora-01031 When Executing Alter Synonym Compile For Other Schema (Doc ID 1435898.1)
Bug 4189542 – Only SYS can “alter synonym… compile” for another users”s synonym (Doc ID 4189542.8)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/6056.html
