1
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/<system_password> @pupbld
--
--   **Connect as SYSTEM before running this script**


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

What are the specific reasons to run it as SYSTEM?

3
  • Do you mean perhaps why it can't be run as SYS instead of SYSTEM? Oct 22, 2012 at 7:41
  • @Colin'tHart - yes on similar grounds , why only this script is run as SYSTEM and not as SYS
    – oratab
    Oct 22, 2012 at 8:13
  • Like Phil says, that's how it was designed and built, it's just the way it is. Oct 22, 2012 at 8:14

2 Answers 2

1

The script creates certain objects, and it will put them in the current schema. Oracle wishes that these objects be created on SYSTEM schema, hence the requirement.

There are two Oracle schemas, SYS and SYSTEM, because they have two purposes. In short, SYS truly is the "special" user in respect to database internals, while SYSTEM works normally.

The SYS is the owner of the database, the only user with access to X$ tables, the only user which acts as SYSDBA (internally), that bypasses logon triggers, and that, for some obscure reason, cannot obtain read-consistent view of data. As a schema it holds crucial Oracle stuff, mainly the data dictionary.

The SYSTEM is fairly normal schema with DBA privilege (database administrator, not the same as SYSDBA), only that it is built-in and contains some additional (but also quite important) Oracle stuff. You shouldn't put your own stuff in this schema, if not instructed so by Oracle.

0

The script assumes that the script is run as the SYSTEM user. For example:

CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS;

Why even question what a core Oracle provided script instructs the end user to do?

1
  • Well for purpose of knowing why (Even if oracle mentions , i am curious to know why) so and why not as SYS. Thanks .
    – oratab
    Oct 22, 2012 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.