Oracle8i Administrator's Guide
Release 8.1.5






Prev Next

The Oracle Database Administrator

This chapter describes the responsibilities of the person who administers the Oracle server, the database administrator.

The following topics are included:

Types of Oracle Users

At your site, the types of users and their responsibilities may vary. For example, at a large site the duties of a database administrator might be divided among several people.

This section includes the following topics:

Database Administrators

Because an Oracle database system can be quite large and have many users, someone or some group of people must manage this system. The database administrator (DBA) is this manager. Every database requires at least one person to perform administrative duties.

A database administrator's responsibilities can include the following tasks:

Security Officers

In some cases, a database might also have one or more security officers. A security officer is primarily concerned with enrolling users, controlling and monitoring user access to the database, and maintaining system security. You might not be responsible for these duties if your site has a separate security officer.

Application Developers

An application developer designs and implements database applications An application developer's responsibilities include the following tasks:

Application Administrators

An Oracle site might also have one or more application administrators. An application administrator is responsible for the administration needs of a particular application.

Database Users

Database users interact with the database via applications or utilities. A typical user's responsibilities include the following tasks:

Network Administrators

At some sites there may be one or more network administrators. Network administrators may be responsible for administering Oracle networking products, such as Net8.

See Also: "Network Administration" in Oracle8i Distributed Database Systems

Database Administrator Security and Privileges

To accomplish administrative tasks in Oracle, you need extra privileges both within the database and possibly in the operating system of the server on which the database runs. Access to a database administrator's account should be tightly controlled.

This section includes the following topics:

The Database Administrator's Operating System Account

To perform many of the administrative duties for a database, you must be able to execute operating system commands. Depending on the operating system that executes Oracle, you might need an operating system account or ID to gain access to the operating system. If so, your operating system account might require more operating system privileges or access rights than many database users require (for example, to perform Oracle software installation). Although you do not need the Oracle files to be stored in your account, you should have access to them.

In addition, Enterprise Manager requires that your operating system account or ID be distinguished in some way to allow you to use operating system privileged Enterprise Manager commands.

See Also: The method of distinguishing a database administrator's account is operating system specific. See your operating system-specific Oracle documentation for information.

Database Administrator Usernames

Two user accounts are automatically created with the database and granted the DBA role. These two user accounts are:

These two usernames are described in the following sections.


To prevent inappropriate access to the data dictionary tables, you must change the passwords for the SYS and SYSTEM usernames immediately after creating an Oracle database.  

You will probably want to create at least one additional administrator username to use when performing daily administrative tasks.


When any database is created, the user SYS, identified by the password CHANGE_ON_INSTALL, is automatically created and granted the DBA role.

All of the base tables and views for the database's data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle; they should never be modified by any user or database administrator, and no one should create any tables in the schema of the user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)

Most database users should never be able to connect using the SYS account. You can connect to the database using this account but should do so only when instructed by Oracle personnel or documentation.


When a database is created, the user SYSTEM, identified by the password MANAGER, is also automatically created and granted all system privileges for the database.

The SYSTEM username creates additional tables and views that display administrative information, and internal tables and views used by Oracle tools. Never create in the SYSTEM schema tables of interest to individual users.

The DBA Role

A predefined role, named "DBA", is automatically created with every Oracle database. This role contains all database system privileges. Therefore, it is very powerful and should be granted only to fully functional database administrators.

Database Administrator Authentication

Database administrators must often perform special operations such as shutting down or starting up a database. Because these operations should not be performed by normal database users, the database administrator usernames need a more secure authentication scheme.

This section includes the following topics:

Selecting an Authentication Method

The following methods for authenticating database administrators replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle:

Depending on whether you wish to administer your database locally on the same machine where the database resides or to administer many different databases from a single remote client, you can choose between operating system authentication or password files to authenticate database administrators. Figure 1-1 illustrates the choices you have for database administrator authentication schemes.

Figure 1-1 Database Administrator Authentication Methods

On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group (on UNIX systems, this is the DBA group) or giving that OS username a special process right.

The database uses password files to keep track of database usernames that have been granted administrator privileges.

See Also: "User Authentication" in Oracle8i Concepts.

Using Operating System Authentication

If you choose, you can have your operating system authenticate users performing database administration operations.

  1. Set up the user to be authenticated by the operating system.

  2. Make sure that the initialization parameter, REMOTE_LOGIN_PASSWORDFILE, is set to NONE, which is the default value for this parameter.

  3. Authenticated users should now be able to connect to a local database, or to connect to a remote database over a secure connection, by typing one of the following commands:


If you successfully connect as INTERNAL using an earlier release of Oracle, you should be able to continue to connect successfully using the new syntax shown in Step 3.


To connect as SYSOPER or SYSDBA using OS authentication you do not need the SYSOPER or SYSDBA system privileges. Instead, the server verifies that you have been granted the appropriate OSDBA or OSOPER roles at the operating system level.  


Two special operating system roles control database administrator logins when using operating system authentication: OSOPER and OSDBA.




Contains all system privileges with ADMIN OPTION, and the OSOPER role; permits CREATE DATABASE and time-based recovery.  

OSOPER and OSDBA can have different names and functionality, depending on your operating system.

The OSOPER and OSDBA roles can only be granted to a user through the operating system. They cannot be granted through a GRANT statement, nor can they be revoked or dropped. When a user logs on with administrator privileges and REMOTE_LOGIN_PASSWORDFILE is set to NONE, Oracle communicates with the operating system and attempts to enable first OSDBA and then, if unsuccessful, OSOPER. If both attempts fail, the connection fails. How you grant these privileges through the operating system is operating system specific.

If you are performing remote database administration, you should consult your Net8 documentation to determine if you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure, regardless of which version of Net8 you are using.

See Also: For information about OS authentication of database administrators, see your operating system-specific Oracle documentation.

Using an Authentication Password File

If you have determined that you need to use a password file to authenticate users performing database administration, you must complete the steps outlined below. Each of these steps is explained in more detail in the following sections of this chapter.

  1. Create the password file using the ORAPWD utility.

        ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
  2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.

  3. Add users to the password file by using SQL to grant the appropriate privileges to each user who needs to perform database administration, as shown in the following examples.

        GRANT SYSDBA TO scott;
        GRANT SYSOPER TO scott;

    The privilege SYSDBA permits the user to perform the same operations as OSDBA. Likewise, the privilege SYSOPER permits the user to perform the same operations as OSOPER.

  4. Privileged users should now be able to connect to the database by using a command similar to the one shown below.

        CONNECT scott/ AS SYSDBA

Password File Administration

You can create a password file using the password file creation utility, ORAPWD or, for selected operating systems, you can create this file as part of your standard installation.

This section includes the following topics:

See Also: See your operating system-specific Oracle documentation for information on using the installer utility to install the password file.


When you invoke the password file creation utility without supplying any parameters, you receive a message indicating the proper use of the command as shown in the following sample output:

Usage: orapwd file=<fname> password=<password> entries=<users>
        file - name of password file (mand),
        password - password for SYS and INTERNAL (mand),
        entries - maximum number of distinct DBAs and OPERs (opt),
      There are no spaces around the equal-to (=) character.

For example, the following command creates a password file named ACCT.PWD that allows up to 30 privileged users with different passwords. The file is initially created with the password SECRET for users connecting as SYSOPER or SYSDBA:

    ORAPWD FILE=acct.pwd PASSWORD=secret ENTRIES=30

Following are descriptions of the parameters in the ORAPWD utility.


This parameter sets the name of the password file being created. You must specify the full pathname for the file. The contents of this file are encrypted, and the file is not user-readable. This parameter is mandatory.

The types of file names allowed for the password file are operating system specific. Some platforms require the password file to be a specific format and located in a specific directory. Other platforms allow the use of environment variables to specify the name and location of the password file. See your operating system-specific Oracle documentation for the names and locations allowed on your platform.

If you are running multiple instances of Oracle using the Oracle Parallel Server, the environment variable for each instance should point to the same password file.


It is critically important to the security of your system that you protect your password file and environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.  


This parameter sets the password for SYSOPER and SYSDBA. If you issue the ALTER USER command to change the password after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. The INTERNAL user is supported for backwards compatibility only. This parameter is mandatory.


This parameter sets the maximum number of entries allowed in the password file. This corresponds to the maximum number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. Entries can be reused as users are added to and removed from the password file. This parameter is required if you ever want this password file to be EXCLUSIVE.


If you ever need to exceed this limit, you must create a new password file. It is safest to select a number larger than you think you will ever need.  

See Also: Consult your operating system-specific Oracle documentation for the exact name of the password file or for the name of the environment variable used to specify this name for your operating system.


In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are described below.


To start up an instance or database, you must use Enterprise Manager. You must specify a database name and a parameter file to initialize the instance settings. You may specify a fully-qualified remote database name using Net8. However, the initialization parameter file and any associated files, such as a configuration file, must exist on the client machine. That is, the parameter file must be on the machine where you are running Enterprise Manager.  


Setting this parameter to NONE causes Oracle to behave as if the password file does not exist. That is, no privileged connections are allowed over non-secure connections. NONE is the default value for this parameter.


An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can contain the names of users other than SYSOPER and SYSDBA. Using an EXCLUSIVE password file allows you to grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.


A SHARED password file can be used by multiple databases. However, the only users recognized by a SHARED password file are SYSDBA and SYSOPER; you cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.


To achieve the greatest level of security, you should set the REMOTE_LOGIN_PASSWORDFILE file initialization parameter to EXCLUSIVE immediately after creating the password file.  

Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information is added to the password file. If the server does not have an EXCLUSIVE password file, that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, you receive an error message if you attempt to grant these privileges.

A user's name only remains in the password file while that user has at least one of these two privileges. When you revoke the last of these privileges from a user, that user is removed from the password file.

To Create a Password File and Add New Users to It

  1. Follow the instructions for creating a password file.

  2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.

  3. Connect with SYSDBA privileges as shown in the following example:

        CONNECT SYS/change_on_install AS SYSDBA
  4. Start up the instance and create the database if necessary, or mount and open an existing database.

  5. Create users as necessary. Grant SYSOPER or SYSDBA privileges to yourself and other users as appropriate.

  6. These users are now added to the password file and can connect to the database as SYSOPER or SYSDBA with a username and password (instead of using SYS). The use of a password file does not prevent OS authenticated users from connecting if they meet the criteria for OS authentication.

Granting and Revoking SYSOPER and SYSDBA Privileges

If your server is using an EXCLUSIVE password file, use the GRANT command to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:


Use the REVOKE command to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:


Because SYSDBA and SYSOPER are the most powerful database privileges, the ADMIN OPTION is not used. Only users currently connected as SYSDBA (or INTERNAL) can grant SYSDBA or SYSOPER system privileges to another user. This is also true of REVOKE. These privileges cannot be granted to roles, since roles are only available after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles, which are a completely independent feature.

See Also: For more information about system privileges, see Chapter 24, "Managing User Privileges and Roles".

Listing Password File Members

Use the V$PWFILE_USERS view to determine which users have been granted SYSDBA and SYSOPER system privileges for a database. The columns displayed by this view are as follows:


The name of the user that is recognized by the password file.


If the value of this column is TRUE, the user can log on with SYSDBA system privileges.


If the value of this column is TRUE, the user can log on with SYSOPER system privileges.

Connecting with Administrator Privileges

When you connect with SYSOPER or SYSDBA privileges using a username and password, you are connecting with a default schema of SYS, not the schema that is generally associated with your username.

Connecting with Administrator Privileges: Example

For example, assume user SCOTT has issued the following commands:

CONNECT scott/tiger
CREATE TABLE scott_test(name VARCHAR2(20));

Later, when SCOTT issues these commands:

SELECT * FROM scott_test;

He receives an error that SCOTT_TEST does not exist. That is because SCOTT now references the SYS schema by default, whereas the table was created in the SCOTT schema.

Non-Secure Remote Connections

To connect to Oracle as a privileged user over a non-secure connection, you must meet the following conditions:

Local and Secure Remote Connections

To connect to Oracle as a privileged user over a local or a secure remote connection, you must meet either of the following sets of conditions:

Consult your operating system-specific Oracle documentation for details on operating system authentication.

See Also: "Password File Administration" .

Maintaining a Password File

This section describes how to expand, relocate, and remove the password file, as well as how to avoid changing the state of the password file.

Expanding the Number of Password File Users

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and re-grant the privileges to the users.

To Replace a Password File

  1. Note which users have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.

  2. Shut down the database.

  3. Delete the existing password file.

  4. Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORAPWD". Be sure to set the ENTRIES parameter to a sufficiently large number.

  5. Follow the instructions in "Adding Users to a Password File" .

Relocating the Password File

After you have created the password file, you can relocate it as you choose. After relocating the password file, you must reset the appropriate environment variables to the new pathname. If your operating system uses a predefined pathname, you cannot change the password file location.

Removing a Password File

If you determine that you no longer need to use a password file to authenticate users, you can delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After removing this file, only users who can be authenticated by the operating system can perform database administration operations.


Do not remove or modify the password file if you have a database or instance mounted using REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE (or SHARED). If you do, you will be unable to reconnect remotely using the password file. Even if you replace it, you cannot use the new password file, because the timestamp and checksums will be wrong.  

Changing the Password File State

The password file state is stored in the password file. When you first create a password file, its default state is SHARED. You can change the state of the password file by setting the parameter REMOTE_LOGIN_PASSWORDFILE. When you start up an instance, Oracle retrieves the value of this parameter from the initialization parameter file stored on your client machine. When you mount the database, Oracle compares the value of this parameter to the value stored in the password file. If these values do not match, the value stored in the file is overwritten.


You should use caution to ensure that an EXCLUSIVE password file is not accidentally changed to SHARED. If you plan to allow instance start up from multiple clients, each of those clients must have an initialization parameter file, and the value of the parameter REMOTE_LOGIN_PASSWORDFILE must be the same in each of these files. Otherwise, the state of the password file could change depending upon where the instance was started.  

Database Administrator Utilities

Several utilities are available to help you maintain and control the Oracle server.

The following topics are included in this section:


SQL*Loader is used by both database administrators and users of Oracle. It loads data from standard operating system files (files in text or C data format) into Oracle database tables.

See Also: Oracle8i Utilities

Export and Import

The Export and Import utilities allow you to move existing data in Oracle format to and from Oracle databases. For example, export files can archive database data, or move data among different Oracle databases that run on the same or different operating systems.

See Also: Oracle8i Utilities

Priorities of a Database Administrator

In general, you must perform a series of steps to get the database system up and running, and then maintain it.

The following steps are required to configure an Oracle server and database on any type of computer system. The following sections include details about each step.

To Configure an Oracle Server

Step 1: Install the Oracle Software

As the database administrator, you must install the Oracle server software and any front-end tools and database applications that access the database. In some distributed processing installations, the database is controlled by a central computer and the database tools and applications are executed on remote machines; in this case, you must also install the Oracle Net8 drivers necessary to connect the remote machines to the computer that executes Oracle.

See Also: For more information, see "Identifying Oracle Software Releases" .

For specific requirements and instructions for installation, see your operating system-specific Oracle documentation and your installation guides for your front-end tools and Net8 drivers.

Step 2: Evaluate the Database Server Hardware

After installation, evaluate how Oracle and its applications can best use the available computer resources. This evaluation should reveal the following information:

Step 3: Plan the Database

As the database administrator, you must plan:

It is important to plan how the logical storage structure of the database will affect system performance and various database management operations. For example, before creating any tablespaces for your database, you should know how many data files will make up the tablespace, where the data files will be physically stored (on which disk drives), and what type of information will be stored in each tablespace. When planning the database's overall logical storage structure, take into account the effects that this structure will have when the database is actually created and running. Such considerations include how the database's logical storage structure will affect the following items:

Plan the relational design of the database's objects and the storage characteristics for each of these objects. By planning relationships between objects and the physical storage of each object before creating it, you can directly impact the performance of the database as a unit. Be sure to plan for the growth of the database.

In distributed database environments, this planning stage is extremely important. The physical location of frequently accessed data can dramatically affect application performance.

During the above planning phases, also plan a backup strategy for the database. After developing this strategy, you might find that you want to alter the database's planned logical storage structure or database design to improve backup efficiency.

It is beyond the scope of this book to discuss relational and distributed database design; if you are not familiar with such design issues, refer to accepted industry-standard books that explain these studies.

See Also: See Chapters 9 through 21 for specific information on creating logical storage structures, objects, and integrity constraints for your database.

Step 4: Create and Open the Database

Once you have finalized the database design, you can create the database and open it for normal use. Depending on your operating system, a database may already have been created during the installation procedure for Oracle. If so, all you need to do is start an instance and mount and open the initial database.

To determine if your operating system creates an initial database during the installation of Oracle, check your installation or user's guide. If no database is created during installation or you want to create an additional database, see Chapter 2 of this book for this procedure.

See Also: See Chapter 3 for database and instance startup and shutdown procedures.

Step 5: Implement the Database Design

Once you have created and started the database, you can create the database's planned logical structure by creating all necessary rollback segments and tablespaces. Once this is built, you can create the objects for your database.

See Also: See Chapters 9 through 21 for instructions on creating logical storage structures and objects for your database.

Step 6: Back Up the Database

After you have created the database structure, carry out the planned backup strategy for your database by creating any additional redo log files, taking the first full database backup (online or offline), and scheduling future database backups at regular intervals.

See Also: See the Oracle8i Backup and Recovery Guide for instructions on customizing your backup operations and performing recovery procedures.

Step 7: Enroll System Users

Once you have backed up the database structure, you can begin to enroll the users of the database in accordance with your Oracle license agreement, create roles for these users, and grant appropriate roles to them.

See Also: See Chapters 22 through 24 for the procedures to create user accounts and roles, and information on complying with your license agreement.

Step 8: Tune Database Performance

Optimizing the database system's performance is one of your ongoing responsibilities.

See Also: Oracle8i Tuning for information about tuning your database and applications.

Identifying Oracle Software Releases

Because Oracle products are always undergoing development and change, several releases of the products can be in use at any one time. To identify a software product fully, as many as five numbers may be required.

This section includes the following topics:

Release Number Format

An Oracle Server distribution tape might be labeled "Release" The following sections translate this number.

Figure 1-2 Example of an Oracle Release Number

Version Number

The version number, such as 8, is the most general identifier. A version is a major new edition of the software, which usually contains significant new functionality.

Maintenance Release Number

The maintenance release number signifies different releases of the general version, starting with 0, as in version 8.0. The maintenance release number increases when bug fixes or new features to existing programs become available; in the example here, 8.1 indicates the first maintenance release of the version 8.

Patch Release Number

The patch release number identifies a specific level of the object code, such as 8.1.5. A patch release contains fixes for serious bugs that cannot wait until the next maintenance release. The first distribution of a maintenance release always has a patch number of 0.

Port-Specific Patch Release Number

A fourth number (and sometimes a fifth number) can be used to identify a particular emergency patch release of a software product on that operating system, such as or An emergency patch is not usually intended for wide distribution; it usually fixes or works around a particular, critical problem.

Examples of Release Numbers

The following examples show possible release numbers for Oracle8i:


the first distribution of Oracle8i  


the first maintenance release of Oracle8i  


the second maintenance release (the third release in all) of Oracle8i  


the second patch release after the second maintenance release  

Versions of Other Oracle Software

As Oracle Corporation introduces new products and enhances existing ones, the version numbers of the individual products increment independently. Thus, you might have an Oracle server Release system working with Oracle Forms Version 4.0.3, SQL*Plus Version 3.1.9, and Pro*FORTRAN Version 1.5.2. (These numbers are used only for illustration.)

Checking Your Current Release Number

To see which release of Oracle and its components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION, as shown below (This information is useful if you need to call Oracle Support.):

SELECT * FROM product_component_version;

PRODUCT                    VERSION               STATUS
-------------------        -----------------     ----------
CORE                           Production
NLSRTL                         Production
Oracle8i Server                Beta Release
PL/SQL                         Beta
TNS for SunOS:                 Production
5 rows selected


Copyright © 1999 Oracle Corporation.

All Rights Reserved.