简单问题的出现:
使用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
(
PRODUCTVARCHAR2 (30) NOT NULL,
USERIDVARCHAR2 (30),
ATTRIBUTEVARCHAR2 (240),
SCOPEVARCHAR2 (240),
NUMERIC_VALUEDECIMAL (15,2),
CHAR_VALUEVARCHAR2 (240),
DATE_VALUEDATE,
LONG_VALUELONG
);
-- 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
2SELECT PRODUCT, USERID, ATTRIBUTE, SCOPE,
3NUMERIC_VALUE, CHAR_VALUE, DATE_VALUE, LONG_VALUE
4FROM 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