Question:
Why do I get an error message similar to the following when staritng SQL*Plus as anything other than SYSTEM (and in later versions of Oracle SYS) ?
Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM
Answer:
This is a warning only, not a severe error, and the error message may vary with your version of Oracle. If you are running with a default database built by the installer, it will probably not appear; but if you have written your own script to create a new database you may have omitted a minor step at the end of the process.
The error message relates to a feature introduced in Oracle 6 - Product User Profiles. This is basically a reference table that can be used by a client program to check if there are any restrictions on what the user may do with that program. As far as I am aware, SQL*Plus is the only program that recognises it, and when it starts up it tries to access the view (formerly table) SYSTEM.PRODUCT_USER_PROFILE. If the table does not exist, then this error message appears.
If you want to stop this error message, you need only connect through SQL*Plus as SYSTEM and run the 'pupbld.sql' script which is located under the ORACLE_HOME in subdirectory sqlplus/admin.
As an example of usage, the following row inserted into the table will stop any user with an ID starting with 'JPL' from being able to create table from SQL*Plus, even if they have the 'create table' privilege.
insert into product_user_profile ( product, userid, attribute, char_value ) values ( 'SQL*Plus','JPL%','CREATE','DISABLED' );
Any attempt by the user to create a table, they will get the error message:
SP2-0544: invalid command: create