忽视细节导致简单问题的复杂化关于PUPBLD.SQL

最新推荐文章于 2021-11-19 10:42:00 发布

cuanwuqian6695

最新推荐文章于 2021-11-19 10:42:00 发布

阅读量156

点赞数

文章标签：数据库

简单问题的出现：

使用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 。