重庆思庄Oracle、Redhat认证学习论坛
标题:
关于pupbld.sql
[打印本页]
作者:
王硕
时间:
2015-12-6 15:54
标题:
关于pupbld.sql
问题: (静默安装下,要执行几个sql,由于没创建范例,发现了一个新问题)
SQL> conn hr/hr
ERROR:
ORA-00942: table or view does not exist
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
作者:
王硕
时间:
2015-12-6 15:55
解决:
SQL> conn system/oracle
Connected.
SQL> @?/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
DATE_VALUE FROM PRODUCT_USER_PROFILE
*
ERROR at line 3:
ORA-00980: synonym translation is no longer valid
DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
Grant succeeded.
Synonym dropped.
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
Synonym dropped.
Synonym created.
SQL> conn hr/hr
Connected.
作者:
王硕
时间:
2015-12-6 15:55
总结:这次问题其实很简单,只要登录system跑一下脚本就可以了,但是却因为习惯和不当回事的态度,在登录system用户时顺手加上了as sysdba,导致后面问题的出现,由此可见,在Oracle中,细节是非常非常重要的,来不得半点马虎,另外一点,出现问题,要有条理的从头来分析,不要过于发散思维,要有逻辑性,这样会为解决问题节省很多时间的。
补充(以下内容部分转载自网络):
关于PUPBLD.SQL
我们可以分析一下PUPBLD.SQL中代码,知道它实际上是创建了一个表SQLPLUS_PRODUCT_PROFILE,基于此表创建视图PRODUCT_PRIVS(包含表中所用字段),把视图PRODUCT_PRIVS的SELECT权限设置为PUBLIC,创建了视图PRODUCT_PRIVS的同义词PRODUCT_PROFILE,创建了表SQLPLUS_PRODUCT_PROFILE的同义词PRODUCT_USER_PROFILE,后用创建了视图PRODUCT_PRIVS的PUBLIC同义词PRODUCT_USER_PROFILE 。
SQLPLUS_PRODUCT_PROFILE(基表)->PRODUCT_USER_PROFILE(同义词)
PRODUCT_PRIVS(视图,授权SELECT给PUBLIC)->PRODUCT_PROFILE(同义词)
PRODUCT_USER_PROFILE(同义词,PUBLIC)
因为自己对这表、视图和同义词和PUBLIC,不是很了解。所以,先通过以下实验来检验一下:
首先用普通用户登录
SQL> conn scott/tiger
Connected.
普通用户无法访问SQLPLUS_PRODUCT_PROFILE
SQL> select * from system.SQLPLUS_PRODUCT_PROFILE;
select * from system.SQLPLUS_PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from SQLPLUS_PRODUCT_PROFILE;
select * from SQLPLUS_PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
普通用户可以在加模式前缀的前提下访问PRODUCT_PRIVS
SQL> select * from system.PRODUCT_PRIVS;
no rows selected
SQL> select * from PRODUCT_PRIVS;
select * from PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist
普通用户可以直接访问PRODUCT_PROFILE
SQL> select * from system.PRODUCT_PROFILE;
select * from system.PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from PRODUCT_PROFILE;
no rows selected
普通用户可以直接访问PRODUCT_USER_PROFILE
SQL> select * from PRODUCT_USER_PROFILE;
no rows selected
SQL> select * from system.PRODUCT_USER_PROFILE;
select * from system.PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
基本可以得出以下结论:
1、访问同义词可以不用使用模式前缀(理解为同义词可以方便我们访问)。
2、只有被授权为PUBLIC的,才可以由其他用户访问。
欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)
Powered by Discuz! X3.2