简单问题的出现:
使用SCOTT用户登陆时出现问题:
SQL> conn scott/tiger
访问 PRODUCT_USER_PROFILE 时出错
警告: 未加载产品用户概要文件信息!
您需要将 PUPBLD.SQL 作为 SYSTEM 运行
已连接。
想法很简单,因为是刚手工创建的库,使用system跑一遍PUPBLD.SQL脚本就好。
SQL> conn system/oracle as sysdba
已连接。
SQL> @D:\oraclexe\app\oracle\product\10.2.0\server\sqlplus\admin\pupbld.sql
跑脚本成功后,再次使用SCOTT用户登陆:
SQL> conn scott/tiger
访问 PRODUCT_USER_PROFILE 时出错
警告: 未加载产品用户概要文件信息!
您需要将 PUPBLD.SQL 作为 SYSTEM 运行
已连接。
问题依旧,开始疑惑,直接查看pupbld.sql脚本:
*省略注释*
DROP SYNONYM PRODUCT_USER_PROFILE;
CREATE TABLE SQLPLUS_PRODUCT_PROFILE AS
SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE,
DATE_VALUE FROM PRODUCT_USER_PROFILE;
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG);
-- Create SQLPLUS_PRODUCT_PROFILE from scratch
CREATE TABLE SQLPLUS_PRODUCT_PROFILE
(
PRODUCT VARCHAR2 (30) NOT NULL,
USERID VARCHAR2 (30),
ATTRIBUTE VARCHAR2 (240),
SCOPE VARCHAR2 (240),
NUMERIC_VALUE DECIMAL (15,2),
CHAR_VALUE VARCHAR2 (240),
DATE_VALUE DATE,
LONG_VALUE LONG
);
-- Remove SQL*Plus V3 name for sqlplus_product_profile
DROP TABLE PRODUCT_PROFILE;
-- Create the view PRODUCT_PRIVS and grant access to that
DROP VIEW PRODUCT_PRIVS;
CREATE VIEW PRODUCT_PRIVS AS
SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,
NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE
FROM SQLPLUS_PRODUCT_PROFILE
WHERE USERID = 'PUBLIC' OR USER LIKE USERID;
GRANT SELECT ON PRODUCT_PRIVS TO PUBLIC;
DROP PUBLIC SYNONYM PRODUCT_PROFILE;
CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS;
DROP SYNONYM PRODUCT_USER_PROFILE;
CREATE SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.SQLPLUS_PRODUCT_PROFILE;
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE;
CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS;
-- End of pupbld.sql
手动执行sql语句,创建表和视图及同义词:
SQL> DROP SYNONYM PRODUCT_USER_PROFILE;
SQL> DROP TABLE PRODUCT_USER_PROFILE;
SQL> CREATE TABLE SQLPLUS_PRODUCT_PROFILE(PRODUCT VARCHAR2 (30) NOT NULL,USERID VARCHAR2 (30),ATTRIBUTE VARCHAR2 (240),SCOPE VARCHAR2 (240),NUMERIC_VALUE DECIMAL (15,2),CHAR_VALUE VARCHAR2 (240),DATE_VALUE DATE,LONG_VALUE LONG);
表已创建。
SQL> DROP VIEW PRODUCT_PRIVS;
视图已删除。
SQL> CREATE VIEW PRODUCT_PRIVS AS
2 SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,
3 NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE
4 FROM SQLPLUS_PRODUCT_PROFILE WHERE USERID = 'PUBLIC' OR USER LIKE USERID;
视图已创建。
SQL> GRANT SELECT ON PRODUCT_PRIVS TO PUBLIC;
授权成功。
SQL> DROP PUBLIC SYNONYM PRODUCT_PROFILE;
同义词已删除。
SQL> CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS;
同义词已创建。
成功。
使用SCOTT用户登陆,问题依旧,检查创建的表:
SQL> select * from product_user_profile;
select * from product_user_profile
*
第 1 行出现错误:
ORA-00980: 同义词转换不再有效
SQL> select * from system.product_privs;
select * from system.product_privs
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
仔细检查执行过的SQL语句,发现疑点:conn system/oracle as sysdba system.product_privs;
SQL> show user
USER 为 "SYS"
问题找到,再次确定一下:
SQL> select * from sys.product_privs;
未选定行
确定问题,是细节失误,在Oracle里,system如果正常登录,它其实就是一个普通的dba用户,但是如果以as sysdba登录,其结果实际上它是作为sys用户登录的,这一点类似Linux里面的sudo的感觉,从登录信息里面可以看出来。因此在as sysdba连接数据库后,创建的对象实际上都是生成在sys中的。其他用户也是一样,如果 as sysdba登录,也是作为sys用户登录的。
登录到system用户:
SQL> conn system/oracle
已连接。
检查用户:
SQL> show user
USER 为 "SYSTEM"
这次没有问题了,执行pupbld.sql脚本成功后,再次使用SCOTT用户登陆:
SQL> conn scott/tiger
已连接。
OK,问题解决!
总结:这次问题其实很简单,只要登录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 的,才可以由其他用户访问。来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14184018/viewspace-691502/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14184018/viewspace-691502/