Error accessing PRODUCT_USER_PROFILE?

有客户发邮件来表示新建user后，尝试使用该user登录时sqlplus会出现如下警告:

Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

PRODUCT_USER_PROFILE是Oracle 10g中一个用来显示记录那些不希望用户可以在sqlplus执行命令的权限表，该表一般会在数据库创建时在system模式下被建立；显然用户的这个数据库可能是以手工创建数据库的方式create出来的，而在最后执行脚本的阶段没有执行建立该表的pupbld.sql脚本，该脚本一般位于$ORACLE_HOME/sqlplus/admin目录下:

[maclean@rh2 admin]$ pwd
/s01/10gdb/sqlplus/admin

[maclean@rh2 admin]$ cat pupbld.sql
--
-- Copyright (c) Oracle Corporation 1988, 2003.  All Rights Reserved.
--
-- NAME
--   pupbld.sql
--
-- DESCRIPTION
--   Script to install the SQL*Plus PRODUCT_USER_PROFILE tables.  These
--   tables allow SQL*Plus to disable commands per user.  The tables
--   are used only by SQL*Plus and do not affect other client tools
--   that access the database.  Refer to the SQL*Plus manual for table
--   usage information.
--
--   This script should be run on every database that SQL*Plus connects
--   to, even if the tables are not used to restrict commands.

-- USAGE
--   sqlplus system/ @pupbld
--
--   Connect as SYSTEM before running this script

-- If PRODUCT_USER_PROFILE exists, use its values and drop it

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;

DROP TABLE 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

/*接下来我们在一个执行过pupbld.sql脚本的库中将PRODUCT_USER_PROFILE相关对象都drop掉 */

SQL> DROP TABLE SQLPLUS_PRODUCT_PROFILE;
DROP VIEW  PRODUCT_PRIVS;
DROP PUBLIC SYNONYM PRODUCT_PROFILE;
DROP SYNONYM PRODUCT_USER_PROFILE;
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE;
Table dropped.

SQL>
View dropped.

SQL>
Synonym dropped.

SQL>
Synonym dropped.

SQL>
Synonym dropped.

SQL> create user test identified by test;

User created.

SQL> grant connect to test;

Grant succeeded.

SQL> conn test/test
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.

SQL> conn system/d2nb51tz
Connected.
SQL> @pupbld
...........................
SQL> conn test/test
Connected.

/*重新执行pupbld.sql脚本后登录恢复正常 */

在一个新库中若出现Error accessing PRODUCT_USER_PROFILE等信息，那么极有可能是该库在手动创建过程中没有执行必要的pupbld.sql脚本。在这里我们有必要列举出在手动创建数据库后有必要执行的一系列脚本:

1. @?/rdbms/admin/catalog.sql         --creates the data dictionary and public synonyms for many of its views, and grants PUBLIC access to the synonyms
2. @?/rdbms/admin/catproc.sql         --runs all scripts required for or used with PL/SQL.
3. @?/rdbms/admin/catblock.sql      --creates views that can dynamically display lock dependency graphs
4. @?/rdbms/admin/dbmspool.sql   -- create dbms_shared_pool package
5. @?/rdbms/admin/owminst.plb     --install oracle workspace manager,视乎需求可不运行
6. @?/rdbms/admin/catoctk.sql         --creates the Oracle Cryptographic Toolkit package,视乎需求可不运行

以上脚本均需以sysdba身份运行，而以下脚本以system用户运行:

1. @?/sqlplus/admin/pupbld.sql                                        --install the SQL*Plus PRODUCT_USER_PROFILE tables
2. @?/sqlplus/admin/help/hlpbld.sql helpus.sql                --create SQL*Plus HELP system