Create the User Scott Manually

When you build an database via "create database" statement, there always are not create the user scott and its objects. But we can create the user manually. There is a script $ORACLE_HOME/rdbms/admin/utlsampl.sql. You can just run this script to create the user scott and its objects.

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

Always there will be an warning show like:

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

The root cause for this warning is there is no the table system.product_user_profile. Now, let's resole this problem.

connect as system.

SQL> conn system/passwd

Then, run a script $ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> @?/sqlplus/admin/pupbld.sql
SQL> desc product_user_profile
 Name                       Null?    Type
 ------------------------- -------- ----------------------------
 PRODUCT                   NOT NULL VARCHAR2(30)
 USERID                             VARCHAR2(30)
 ATTRIBUTE                          VARCHAR2(240)
 SCOPE                              VARCHAR2(240)
 NUMERIC_VALUE                      NUMBER(15,2)
 CHAR_VALUE                         VARCHAR2(240)
 DATE_VALUE                         DATE
 LONG_VALUE                         LONG

Now, all things are done.

Actually, the table product_user_profile is a mean to control users' privilege, with this table, sysdba can limit some action from general user. Here is an example.

SQL> insert into product_user_profile (
　2  product, userid, attribute, char_value)
　3  values (
　4  'SQL*Plus','SCOT%','DROP','DISABLED');
　　1 row inserted
SQL> commit;
SQL> create table t_test_profile(
　2 id number);
　  Table created.
SQL> drop table t_test_profle;
　　SP2-0544: invalid command: drop

From this example, we can learn the effect of product_user_profile. Now oracle system reject all the "drop" action from users like 'SCOT%'.