RelationalDBDesignRelationalDBDesign


Create Database   «Prev  Next»
Lesson 9 Running the PUPBLD Script
ObjectiveCreate the Product User Profile in the COIN database

Running PUPBLD Script

Even if you are not going to use the product user profile, I think it's a good idea to create it, and I always do. There are two reasons for this. One is simply my sense of completeness. It should be there, so I make sure that it is there. The second reason, and perhaps the more pragmatic one, is that it removes a potential source of confusion and worry for my users. I realize that the average user does not use SQL*Plus, but it is not unusual for some users--perhaps they are technical people, to use it. If one of your users runs SQL*Plus, logs in to the database, and sees that warning about the product user profile being missing, he or she is likely to get worried, pick up the phone, and call you. Then you will have to explain why is is not there. Multiply that by even a handful of users, and you're likely to get tired of explaining pretty quickly. It also makes you look bad, like you forgot something important. So just create it to begin with, and you will be safe.
An often overlooked script is the one that creates the product user profile. The product user profile is a table, a view, and a set of synonyms that are used to implement an application security scheme in SQL*Plus. Because the product user profile is not critical, the task of creating it is often overlooked. You can easily tell if this has happened by using SQL*Plus to log in to the database as an unprivileged user. If the product user profile does not exist, you will get the following SQL*Plus error:
Error accessing PRODUCT_USER_PROFILE

Warning: Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

To create the product user profile, you need to run a script named PUPBLD.SQL. You will find this script in the $ORACLE_HOME/sqlplus/admin directory. (On some older releases of Oracle for Windows NT, you will need to look in plus/admin, not sqlplus/admin.) Unlike the other scripts that you have run so far, PUPBLD needs to be run by the user named SYSTEM. Instead of doing a CONNECT INTERNAL, connect as SYSTEM as shown in this example:
SVRMGR> CONNECT system/dogtoad

Connected.

SVRMGR> @c:\orawin95\rdbms\admin\pupbld

Error Messages

You will see a number of error messages when you run this script. That's OK. They are nothing to worry about. The script has been carefully written by Oracle to cover both the case where you are creating a new database and the case where you are updating one from an earlier release. SQL*Plus doesn't implement any type of branching logic, so both sets of commands always execute. No matter which case you are dealing with, you will probably get some errors.

Create Product User Profile-Exercises

Create Product User Profile-Exercise
Now, those of you who are running Oracle should go on to this exercise, to create the product user profile in your database.