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

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. maclean says:

    SQL> SELECT CID, dbms_registry_sys.patch_script(CID) AS source_file
    2 FROM sys.registry$
    3 where cid not in (‘CATALOG’, ‘CATPROC’, ‘OWB’);

    CID SOURCE_FILE
    —————————— ———————————————————————-
    AMD ?/olap/admin/amdpatch.sql
    APEX ?/rdbms/admin/nothing.sql
    APS ?/olap/admin/apspatch.sql
    CATJAVA ?/rdbms/admin/catjava.sql
    CONTEXT ?/ctx/admin/ctxpatch.sql
    EM ?/sysman/admin/emdrep/sql/empatch.sql
    EXF ?/rdbms/admin/exfpatch.sql
    JAVAVM ?/javavm/install/jvmpatch.sql
    ORDIM ?/ord/im/admin/impatch.sql
    OWM ?/rdbms/admin/owmpatch.sql
    RAC ?/rdbms/admin/catclust.sql
    RUL ?/rdbms/admin/rulpatch.sql
    SDO ?/md/admin/sdopatch.sql
    XDB ?/rdbms/admin/xdbpatch.sql
    XML ?/xdk/admin/xmlpatch.sql
    XOQ ?/olap/admin/xoqpatch.sql

    After completing the previous steps, if the component is still not VALID, then run the specific component
    patch script as described below to try to validate the component. These scripts are present even in the
    base version even if no patchset has been applied. For exxample 10.2.0.1.0.

    It is advisable to start up the database in UPGRADE mode and connect ‘/as sysdba’ to
    execute any of the following scripts.

    JServer:

    SQL> @?/javavm/install/jvmpatch.sql

    XDK for Java:

    SQL> @?/xdk/admin/xmlpatch.sql

    Java Supplied Packages

    SQL> @?/rdbms/admin/catjava.sql

    Oracle Text

    SQL> @?/ctx/admin/ctxpatch.sql

    Oracle XML Database

    SQL> @?/rdbms/admin/xdbpatch.sql

    Real Application Clusters

    SQL> @?/rdbms/admin/catclust.sql

    Oracle Workspace Manager

    SQL> @?/rdbms/admin/owmpatch.sql

    Oracle Data Mining

    SQL> @?/rdbms/admin/odmpatch.sql

    Messaging Gateway

    SELECT dbms_registry_sys.patch_script(‘MGW’) AS patch_name FROM DUAL;
    @

    OLAP Analytic Workspace

    SQL> @?/olap/admin/apspatch.sql

    OLAP Catalog

    SQL> @?/olap/admin/amdpatch.sql

    OLAP API

    SQL> @?/olap/admin/xoqpatch.sql

    Intermedia

    SQL> @?/ord/im/admin/impatch.sql

    Spatial

    SQL> @?/md/admin/sdopatch.sql

    Ultrasearch

    SQL> @?/ultrasearch/admin/wkpatch.sql

    Oracle Label Security

    SELECT dbms_registry_sys.patch_script(‘OLS’) AS patch_name FROM DUAL;
    @

    Expression Filter

    SQL> @?/rdbms/admin/exfpatch.sql

    Enterprise Manager Repository

    SQL> @?/sysman/admin/emdrep/sql/empatch.sql

    Rule Manager

    SQL> @?/rdbms/admin/rulpatch.sql

    After running any of the above scripts run utlrp.sql once again and see if the component is validated or not.
    If not then see if there were any errors in the spooled output of script execution and according to the error proceed further.

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569