Copyright: Copyright (c) 1994, Oracle Corporation.
Citation: Copyright (c) 1994, Oracle Corporation.


Entry Topic
Oracle Database Tools User's Guide

Oracle Database Tools™ User's Guide

Preface

CHAPTER 1. Connecting to Oracle7

CHAPTER 2. Using the Database Manager

CHAPTER 3. Using the User Manager

CHAPTER 4. Using the Object Manager

CHAPTER 5. Using the Session Manager

CHAPTER 6. Using the Database Expander

CHAPTER 7. Using Export, Import, and SQL*Loader

CHAPTER 8. Using the Backup and Recovery Managers

CHAPTER 9. Using the Database Password Manager

GLOSSARY


This help file created via:

Oracle Book to Microsoft Help Version 1.0w

© 1994 Oracle Corporation Inc.

Authored by KM .


Oracle Database Toolsä User's Guide
Oracle Database Tools™ User's Guide

RELEASE 1.0

Part No. A23223-1

 Oracle Database Tools User's Guide , Release  1.0

Part No. A23223-1

Copyright © Oracle Corporation  November 1994

Primary Authors:  Kurt Thompson, Karen Aziz, Diana Lorentz

Contributors:  Ram Subbaroyan, Marilyn Schneider, Pari Tajiki, Geraldine Kuo, Kevin Whitley, Pei Yu, Mark Richter, Keith Majkut, Toby Close, Heather Stewart, Steve Gowin

This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law.  Reverse engineering of the software is prohibited.

If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

Restricted Rights Legend Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988).

Oracle Corporation, 500 Oracle Parkway, Redwood City, CA  94065.

If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

The information in this document is subject to change without notice.   If you find any problems in the documentation, please report them to us in writing.   Oracle Corporation does not warrant that this document is error-free.

ORACLE, Oracle, and SQL*Loader are registered trademarks, and Oracle Database Tools is a trademark of Oracle Corporation. Microsoft, MS, and MS-DOS are registered trademarks, and Windows is a trademark of Microsoft Corporation.

All other product or company names are used for identification purposes only, and may be trademarks or registered trademarks of their respective owners.


Preface

This  Oracle Database Tools User's Guide  contains the introductory, installation, and configuration information you need to get up and running with Oracle Database Tools.  This Preface provides detailed information on the following topics:

•	what you need to know to use this Guide

•	how this Guide is organized

•	conventions used in this Guide

Who Should Read This Guide

This Guide is intended for those responsible for installing and maintaining Oracle Database Tools.  Read this entire Guide and refer as necessary to the other Oracle publications available.

Knowledge Assumed of the Reader

This Guide assumes you are familiar with Windows.  Refer to your Microsoft and Windows documentation if you need assistance.

This Guide also assumes that you are familiar with relational database management software issues.

If you are not familiar with Oracle products and terminology, refer to the Introduction of this Guide and to the Introduction of the Oracle7 Server Concepts Manual.

Prerequisites

This Guide assumes that you have installed, configured, and tested Windows and your PC.

How This Guide Is Organized

Chapter 1	Connecting to Oracle7 Describes how to connect to an Oracle7 database, and defines the difference between user names and passwords and database names and passwords.

Chapter 2	Using the Database Manager Describes how to use the Database Manager to start and shut down the database, and how to manage initialization parameters and database aliases.

Chapter 3	Using the User Manager Explains how to create and manage user accounts and access privileges.

Chapter 4	Using the Object Manager Describes how to create and modify database objects, and how to grant object privileges to users.

Chapter 5	Using the Session Manager Provides instructions on how to disconnect user sessions.

Chapter 6	Using the Database Expander Describes how to expand the database.

Chapter 7	Using Export, Import, and SQL*Loader Explains how to use Export and Import to move data from one Oracle database to another, and to load data from foreign data sources into an Oracle Database Tools database.

Chapter 8	Using the Backup and Recovery Managers Provides backup and recovery instructions.

Chapter 9	Using the Database Password Manager Explains how to use the Database Password Manager to create, change, or delete the database password.

Conventions Used In This Guide

Note these typographical conventions when reading this Guide:

Monospace text	Type text exactly as shown. Text typed for a command statement is not case sensitive unless noted otherwise.

lowercase mono	Lowercase characters within command lines represent variables. You should substitute an appropriate value for the variable. In examples, lowercase characters represent sample values for the variables.

lowercase italics	Lowercase italics in the text represent variables. You should substitute an appropriate value for the variable.

[  ]	Brackets enclose optional items or indicate a function key. Do not enter the brackets.

|	A vertical bar represents an "or" option among several options. You must enter only one of the options. Do not enter the vertical bar.

Punctuation	Punctuation other than brackets and vertical bars must be entered in commands exactly as shown.

UPPERCASE	Uppercase characters within the text represent command names, SQL reserved words and keywords, and filenames.

\DIRECTORY	A backslash before a directory name indicates that it is a subdirectory.

C:\>	C:\> represents the Windows (or DOS) command prompt of the current hard disk drive. Your prompt may differ.

Several special icons are provided to alert you to particular information within the body of this Guide:

Suggestion: The lightbulb highlights suggestions and practical tips that could save time, make procedures easier, and so on.

Additional Information:  The book icon highlights text referring you to specific parts of the other Oracle documentation, including the online documentation.

Warning: The warning icon highlights text that warns you of actions that could be particularly damaging or fatal to your operations.

Your Comments Are Welcome

We value and appreciate your comments as an Oracle user and reader of our manuals. As we write, revise, and evaluate our documentation, your opinions are the most important input we receive. At the back of this Guide is a Reader's Comment Form. We encourage you to use it to tell us what you like and dislike about this (or other) Oracle manuals.

If the form is missing, or if you would like to contact us, write us at the following address:

Oracle Database Tools Product Line Manager

Desktop Products Division

Oracle Corporation

500 Oracle Parkway

Redwood Shores  CA  94065

FAX:  415.506.7200


CHAPTER 1. Connecting to Oracle7

This chapter defines the following terms:

•	username (user ID or Login ID)

•	user password

•	database name (host name or connect string)

•	database password

and explains

•	how to connect to Oracle7.

•	available database tools and utilities

 ________________________________________


Login Terminology

All of the login dialogs share common terminology (defined below):

Username	A unique name by which a user is known to the Oracle database and to other Oracle users.  A username must be associated with a user password.  The maximum length is 30 characters.

User Password	A secret word or phrase that is associated with a username.  The maximum length is 30 characters.  Unlike a database password, the user password pertains only to the user, regardless of the function being performed.

Database Name	Can be either a SQL*Net connect descriptor or an alias that defines the name of the database to which you want to connect. The maximum length is 128 characters.

Note: The database name is sometimes called the "host name" or "connect string".  The database name is made up of two components; the SQL*Net protocol code and the name of the physical server where the Oracle database resides.  For example, in the database name "t:escher", "t:" is the SQL*Net protocol code (TCP/IP in this example) and "escher" is the name of the PC where the Oracle database is installed.

A user with administrative responsibility must also know the database password.

Database Password	A password that is associated with a database.  Unlike a user password, the database password is not associated with a particular user, but applies to all users of the database whenever they perform certain functions.  The database password is defined during installation, and is optional.

Some users may have SYSOPER or SYSDBA privileges.  Such users must provide their username, password, database, and the database password.

User Password Versus Database Password

While a user password is attached to a particular username, the database password is attached to a database, and restricts certain functions to only those who know the database password.

A database password is optional.  However, Oracle recommends that you use a password to secure your database.  Refer to the chapter entitled,"Using the Database Password Manager" for further information on creating or changing the database password.

 ________________________________________


Connecting to Oracle7

The dialog shown in Figure 1 - 1 prompts you for your username, your user password, and the name of the database to which you want to connect (or its alias).

Figure 1 - 1.Typical login dialog

The value for Database can be either:

•	a SQL*Net connect descriptor and database name

or

•	an alias, such as "Accounting", that is assigned through the Database Manager

Entering the Database Password

The Database Password Required dialog, shown in Figure 1 - 2, appears only when the function you are requesting is restricted by a database password.  This dialog prompts you for the database password.  In this example a password has been assigned to the Accounting database.

Figure 1 - 2.Database Password Required dialog

When you enter the correct database password and choose OK, you gain access to the restricted administrative functions of the database.

Database Tools and Oracle Utilities

Many database administrative functions can be performed with the following database tools and Oracle utilities. Instructions for using these tools are provided in Chapters 2 through 9.

 Database Tools

Database Manager	The Database Manager starts and shuts down the database, enables you to customize initialization parameters, and lets you create database aliases.

User Manager	The User Manager enables you to create user accounts and roles, grant privileges to users, and manage user passwords.

Object Manager	The Object Manager lets you manage database objects such as tables, indexes, synonyms, and views.  With the Object Manager, you can also grant object privileges to users.

Session Manager	The Session Manager enables you to view and disconnect user sessions on the database.

Database Expander	The Database Expander allows you to expand the database to make room for additional data.

Backup and Recovery Managers	The Backup and Recovery Managers enable you to back up and recover your data.

Password Manager	The Database Password Manager tool enables system administrators to create, change, or delete the database system password (not to be confused with user passwords) from the database.

 Oracle Utilities

The Oracle Utilities include:

Export	This utility writes data from an Oracle7 database to an operating system file for the purpose of archiving, moving, or restoring data.

Import	This utility reads data from files exported with Export into the Oracle7 RDBMS.

SQL*Loader	This utility enables you to load data from foreign data sources--usually ASCII data files--into the Oracle7 RDBMS.


CHAPTER 2. Using the Database Manager

This chapter provides detailed instructions on how to use the Database Manager.  The Database Manager enables you to perform the most common management tasks of the Oracle7 database.  Topics covered in this chapter include:

•	overview of the Database Manager

•	checking the status of the database

•	starting the database

•	shutting down the database

•	command-line option

•	customizing database configurations

•	creating and deleting aliases for databases

 ________________________________________


Overview

The Database Manager enables you to perform the most common database management tasks from one dialog.  From the Database Manager, you can start and shut down any Oracle7 database, create database aliases, and maintain multiple "configurations", sets of initialization parameters.  Figure 2 - 1 shows the Database Manager dialog.

Figure 2 - 1.Database Manager dialog

The Database Manager dialog consists of the following elements:

Dialog element	Explanation

Database	Enter a database name or use the dropdown list to select an alias.  The database name specified during installation will appear by default.

Configuration	This is the configuration that will be used to start the database.  Configurations can be chosen or edited by choosing the Configure button (see below).

Status light	The status light and text below it indicate the status or availability of the database. Green means the database is running; yellow means that a startup, shutdown, or status check is in process; red means the database is shut down or unavailable.

Status	Choose Status to obtain the status of the database.

Startup	Choose Startup to start the database.

Shutdown	Choose Shutdown to shut down the database.

Configure	Choose Configure to open the Configure Initialization Parameters dialog (described below).  Use the Configure Initialization Parameters dialog to choose configurations and to optionally edit the initialization parameters.

Aliases	Choose Aliases to open the Database Aliases dialog and create aliases for databases.

 ________________________________________


Checking Database Status

Periodically you will need to check the status of the database, for example, to determine whether any users are connected to the database before shutting it down for maintenance. All users may obtain database status.

To check the status of the database:

1.	Open the Database Manager tool.

The Database Manager dialog appears (see Figure 2 - 1). The status light is off, indicating that the status of the database is unknown.

2.	Enter a database name in the Database text box or choose an alias from the dropdown list box.

3.	Choose Status to find out the current status of the database.

A status light displays the current status of the database.

Green	Running

Yellow	Startup, shutdown, or status check in process

Red	Not running or unavailable

 ________________________________________


Starting the Database

Users cannot access data an Oracle7 database unless it is started. Only users who know the database password may start the database.

To start the database:

1.	Open the Database Manager tool.

The Database Manager dialog appears. The status light is off, indicating that the status of the database is unknown.

2.	Enter a database name in the Database text box or choose an alias from the dropdown list box.

3.	If you want to start the database using a different configuration, choose the Configure button and select a different configuration.

4.	Choose Startup.

5.	If prompted, enter the database password in the Database Password Required dialog.

There may be a brief delay while the database starts, during which the status light is yellow. After the database starts, the status light turns green. If the server is already running, the light will turn green more quickly.

Users can now access the database.

 ________________________________________


Shutting Down the Database

Periodically you will need to shut down the database; for example to perform routine maintenance on the server. Only users who know the database password may shut down the database.

To shut down the database:

1.	Open the Database Manager tool.

The Database Manager dialog appears.

2.	Enter a database name in the Database text box or choose an alias from the dropdown list box.

3.	Choose Shutdown.

4.	If prompted, enter the database password in the Database Password Required dialog, and choose OK.

If other users are connected to the database, the dialog shown in Figure 2 - 2 appears.

Figure 2 - 2.Shutdown Database dialog

•	If you choose Show Connected Sessions, you will be able to disconnect user sessions.  See the chapter entitled, "Using the Session Manager".

•	If you choose OK, the Database Manager will shut down the database. However, if any sessions are active or marked for disconnection, a warning dialog appears prompting you for second confirmation.

If you choose "Yes" in the second dialog, all sessions are aborted and the database shuts down.

Warning: If you choose to abort user sessions, do not attempt to back up the database until you first restart the database and then shut it down without aborting any user sessions. Otherwise the backup will not be successful.

While the database is shutting down, the status light is yellow. When shutdown is complete, the light changes to red. After you shut down the database, users cannot access the database until it is started again. However, if you have DBA privileges, you may perform some administrative tasks (such as backing up) while the database is shut down.

 ________________________________________


Command-Line Option

In certain situations, you may want to start up or shut down your Oracle7 database using a command-line option.  For example, you may want to start up your Oracle7 database every time you start Windows.  In this case, you can copy the Database Manager icon to your Windows Startup program group and edit its properties as explained below.

Using the command-line option can save time and effort.

To use the startup and shutdown command-line option, simply add the options (described below) to the command line within the Program Item Properties dialog for the Database Manager icon.

To use the command-line option:

1.	Select the Database Manager icon.

2.	Choose File from the Windows menu bar, then choose Properties.

The Program Item Properties dialog appears.  Notice that the Command Line ends with "VSMGR.EXE".

3.	Edit the Command Line so that it ends with the following:

VSMGR.EXE[DATABASE=dbname][PASSWORD=passwd][ACTION=act][CONFIGURATION=config]

where dbname equals the database name or alias, passwd equals the database password (not a user password), act equals either STARTUP or SHUTDOWN, and config is the name of the configuration to use the start the database.  If the configuration name includes any embedded spaces, it should be enclosed in quotes.

 Example

The following example starts up the database:

VSMGR.EXE DATABASE=Accounting PASSWORD=manager ACTION=startup CONFIGURATION="NT Small"

Note: When using the command-line option, no spaces are allowed in the option=action syntax.

The command-line syntax is not case-sensitive.

In most cases, the Database Manager will not appear.  However, if any option--database, action, or password (if there is a database password)--is missing or incorrect, or if an error occurs, the Database Manager dialog will appear.

If action is set to SHUTDOWN and users are still connected, a dialog will appear prompting you to disconnect user sessions.

 ________________________________________


Customizing Database Configurations

The Database Manager enables you to create custom "configurations", or parameter sets, each of which can be used to start any Oracle7 database.  Initialization parameters are read whenever the database is started.  They determine how the database will work.  A configuration is a set of initialization parameters.  For example, you can specify the amount of memory the database will use, and whether or not comprehensive logs of database transactions will be saved.

Warning: Before you customize the initialization parameters, read the appropriate section of your Installation and User's Guide that describes initialization parameters. Refer also to the Oracle7 Server Administrator's Guide and the Oracle7 Server Concepts Manual.

Once you have edited initialization parameters, the new settings will take effect only when you restart the database using the Database Manager.

Editing Basic Database Parameters

You can edit basic database parameters by choosing the Configure button on the Database Manager dialog (see Figure 2 - 1), which opens the Configure Initialization Parameters dialog, shown in Figure 2 - 3. The parameters shown correspond to the most commonly changed parameters.

Figure 2 - 3.Configure Initialization Parameters dialog

The Configure Initialization Parameters dialog consists of the following elements:

Dialog element	Explanation

Configuration	The currently selected configuration name.  (A "configuration" is a named set of parameter values.)  You can create your own configuration by selecting a predefined configuration and then using the Save As button to save it under a new name. Optionally, you can select the "<new>" configuration, edit the parameter values, and save it under a new name.  Any configuration you create is editable.

Server Version	The version of the current database server for which this configuration is intended.

Comment	Add a descriptive comment for the current configuration.

Log Buffer size 	Displays the current value (in Kb) of the LOG_BUFFER parameter. Enter a new value if you want to change the number of bytes allocated for the redo log files. The default value is usually adequate for all but extremely busy systems.

Sort Buffer	Displays the current value (in Kb) of the SORT_AREA_SIZE parameter. Enter a new value if you wish to change the amount of disk space allocated for sorting database files. The default value is adequate for most purposes; creation of very large indexes might require an increase of this value. (See "Creating an Index" section in Using the Object Manager chapter for further information.)

Shared Pool	Displays the current value of the SHARED_POOL_SIZE parameter (in Kb). Enter a new value if you wish to change the size of the area where stored procedures are located. Larger values improve performance on multi-user systems; smaller values use less memory. The default value is adequate for most LAN-based server environments.

Block Buffers	Displays the current value of the DB_BLOCK_BUFFERS parameter. Enter a new value if you wish to change the number of database blocks cached in memory. Higher values speed database performance, but use more memory. The amount of memory used by database buffers equals 2048 times the number of buffers. For example, 1,000 buffers will consume 2,048,000 bytes of storage on the server.

Open Cursors	Displays the current value of the OPEN_CURSORS parameter. Enter a new value if you wish to change the number of context areas a user can access during one session.

Support Recovery	When selected, the database automatically maintains the records (called "archived redo logs") needed to recover the database after a catastrophic failure.

Warning: A backup must have been performed and the Support Recovery option been on continuously between the time of the previous database startup and the failure.

Save As	Enables you to save the current configuration under a new name.

Delete	Deletes the current configuration.

Default	Choose Default to reset all parameters (in both the Configure Initialization Parameters and the Advanced Parameter Editing dialogs) to the database default values.

Advanced	Choose Advanced to open the Advanced Parameter Editing dialog (described below) for advanced parameters.

To customize a database configuration:

1.	Open the Database Manager tool.

The Database Manager dialog appears.

2.	Choose Configure.

The Configure Initialization Parameters dialog appears.

3.	Either select the configuration you want to change from the Configuration dropdown list box, or create a new configuration by selecting any configuration from the list box and choosing Save As.  Then, enter a new name in the Save As dialog and choose Ok.

4.	Change parameters as desired.

Note: If the configuration you select is marked with an asterisk, it is read-only and you cannot edit its parameters.  However, you can choose one of these configurations and then save it under a name of your own.  The parameter values from the read only configuration will be copied to the new configuration you are creating.  Any configuration you create in this manner is editable.

5.	If you want to change parameters other than those listed, choose Advanced and proceed to the next section.

6.	Choose OK to save your changes to the current configuration, Save As to save your changes to a new configuration, or Cancel to undo your changes.

If you chose Save As, enter a new name for the configuration in the Save As dialog and choose OK.

Editing Advanced Database Parameters

You perform advanced parameter editing by choosing the Advanced button on the Configure Initialization Parameters dialog, which opens the Advanced Parameter Editing dialog, shown in Figure 2 - 4. Be sure to read the cautions at the beginning of Customizing Database Configurations before using the Advanced Parameter Editing dialog.

Figure 2 - 4.Advanced Parameter Editing dialog

The Advanced Parameter Editing dialog contains the following elements:

Dialog element	Explanation

Parameter 	Select the parameter you want to modify.

Value	Enter the new value of the parameter you are modifying.

Set Values	Displays the parameters you have modified and their current values. You can select multiple parameters, for example, to unset them. Double-clicking on an item in this list places the parameter and its value in their respective Parameter and Value text boxes.

Show All Platforms	If selected, allows you to see configurations for all platforms. If not selected, you can see configurations only for the server platform for which the client installation was targeted.

Save Configuration as File	If selected, when you save the changes to the configuration, a copy of the configuration is created and saved to the specified file and the original is saved where it is normally saved.

File	Enter the name of the file to which this configuration should be written in an "init.ora"-style format. This file can then be used as an init.ora file.

Browse	Use this to select a file to which this configuration should be written.

Lookup	Choose Lookup to see the most recently set value for the selected parameter.

Set	Choose Set to add the selected parameter and its new value to the Set Values box.

Unset	Choose Unset to remove the selected parameter from the Set Values box.

Note: Your changes to parameter values are not saved until you choose OK in both this dialog and in the Configure Initialization Parameters dialog.

To perform advanced parameter editing:

1.	In the Advanced Parameter Editing dialog, select the parameter you want to change from the Parameter dropdown list box.

Note: The appropriate section of your Installation and User's Guide contains a listing of initialization parameters, their default values, and valid ranges.

2.	To check the current value for the parameter, choose Lookup.

3.	Enter a new value for the parameter in the Value field.

4.	Choose Set.

Repeat steps 1 through 4 as needed.

5.	If you want to see all configurations, select Show All Platforms.

6.	When finished, choose OK.

The Configure Initialization Parameters dialog appears.

7.	Choose OK to save all of your parameter changes, or Cancel to undo them.

A dialog appears, informing you that the new parameter settings will not take effect until the next time you start the database.

 ________________________________________


Creating a Database Alias

A database alias is an alternative name for a database. Aliases are easier to remember and use. All users may create database aliases. You create a database alias by choosing the Aliases button on the Database Manager (see Figure 2 - 1), which opens the Database Aliases dialog, shown in Figure 2 - 5.

Figure 2 - 5.Database Aliases dialog

The Database Aliases dialog contains the following elements:

Dialog element	Explanation

Alias	Enter the alias you want to assign to the database, such as "Sales". The maximum length is 128 characters.

Full Name	Enter the full name of the database, including the network protocol designation and the database name (for example, "p:westsale").  The maximum length is 128 characters.

Aliases	Displays the list of aliases that have been assigned. You can select multiple aliases from this list, for example, to delete them. Double-clicking on an item in this list places the alias and full name into their respective text boxes.

Add	Choose Add to add the new alias to the Aliases list.

Delete	After selecting an alias from the Aliases box, choose Delete to delete the selected alias.

To create a database alias:

1.	Open the Database Manager tool.

The Database Manager dialog appears (see Figure 2 - 1).

2.	Choose Aliases.

The Database Aliases dialog appears.

3.	Enter a new alias name in the Alias box.

4.	Enter the database name (network protocol code, a colon, and name of the database) for which you are creating the alias in the Full Name box.

5.	Choose Add.

Your new alias appears in in the Aliases list box the syntax provided above.

6.	Choose OK to save.

The Database Manager dialog appears.

Note: You may create multiple aliases for the same database name.

 ________________________________________


Deleting a Database Alias

From time to time you may need to delete a database alias. For example, an alias that referred to a department by name may no longer be useful if the department now has a new name. You may want to delete the alias and replace it with a more appropriate one. All users may delete database aliases.

1.	Open the Database Manager tool.

The Database Manager dialog appears (see Figure 2 - 1).

2.	Choose Aliases.

The Database Aliases dialog appears.

3.	Highlight the alias or aliases you want to delete and choose Delete.

The alias or aliases are removed from the list.

4.	Choose OK.


CHAPTER 3. Using the User Manager

This chapter provides detailed instructions on how to use the User Manager.  The User Manager enables you to manage users, roles, and their associated database privileges for any Oracle7 database.  The topics discussed in this chapter include the following:

•	creating user accounts and roles

•	granting user and role privileges

•	deleting user accounts and roles

•	changing a user password

 ________________________________________


Overview

Use the User Manager to create users and roles, grant privileges to users and roles, change user passwords, and delete users and roles.

A user is simply a person who has been granted access to the database.  Each user has certain access rights, or privileges, to the database.

A role is a named set of privileges to which users can be assigned.  By changing the privileges of a role, you can globally change the privileges granted to those users assigned to that role.  Otherwise, you must change the privileges of each individual user.

Figure 3 - 1 shows the User Manager dialog.

Figure 3 - 1.User Manager dialog

The User Manager dialog contains the following elements:

Dialog element	Explanation

Main list box	Displays either user or role information. When listing users, the box displays usernames, userIDs, and user creation dates. (The userIDs and user creation dates are system-generated.) When listing roles, the box displays role names and whether each role requires a password.

Show Roles (or Show Users)	Choose Show Roles (or Show Users) to toggle between user and role listings.

Create	Choose Create to open the Create User/Role dialog.

Delete	After selecting a user or role, choose Delete to delete the user or role. (You will be prompted to confirm the deletion.)

Password	After selecting a name or role from the list box, choose Password (or double-click on the user or role name) to modify the password of the selected user or role.

Privileges	Choose Privileges to open the Privileges dialog (see below), where you can grant or revoke user privileges and role privileges.

Database	Choose Database to perform User Manager tasks on another database.

 ________________________________________


Creating User Accounts and Roles

Each user of the database must have an account, consisting of a username and password. When you create a user account, the user is automatically granted CONNECT privileges, which allow that user to connect to the database.  Only users with DBA privileges may create a user account.

A role is a set of privileges that you can define and then assign to one or more users. Then, if you need to change the privileges assigned to several users, you can do so globally by changing the privileges assigned to the role. Only users with DBA privileges may create roles.

New User Account

To create a new user account:

1.	Open the User Manager tool.

2.	Enter your username, password, and database when prompted.

The User Manager dialog appears and displays a list of existing users.

3.	Choose Create.

A dialog appears, asking you to choose User or Role.

4.	Verify that User is selected and choose OK.

The Create New User dialog appears.

Figure 3 - 2. Create New User dialog

5.	Enter a new username for the user account you are creating. Enter a password and confirm it.

6.	Choose OK.

New Role

To create a new role:

1.	Follow steps 1 and 2 for creating a new user (above).

2.	Choose Show Roles.

3.	Choose Create.

A dialog appears, asking you to choose User or Role.

4.	Verify that Role is selected and choose OK.

5.	Enter a Role name for the role you are creating.

6.	If you want this role to be password protected, enter a password. Otherwise, continue to the next step.

Note: Passwords for roles are optional. However, only those users to whom you grant role privileges may use the role.

7.	Choose OK.

 ________________________________________


Granting (and Revoking) User and Role Privileges

You assign privileges to all users in order to control their access to the database and their ability to perform various database tasks. When you create a new user account, that user is automatically assigned CONNECT privileges. However, you must assign additional privileges in order for a user to have additional access to data and database objects. Only users with DBA privileges may grant or revoke user privileges. However, all users may view the privileges that have been granted to them.

You can also assign privileges to roles, so that you can globally change the privileges assigned to multiple users by changing the privileges assigned to the role that applies to those users. Only users with DBA privileges may grant or revoke role privileges.

You grant user and role privileges by choosing the Privileges button on the User Manager dialog. If you have DBA privileges, this action is accessed in the Privileges dialog.

Figure 3 - 3. Privileges dialog (for users with DBA privileges)

The Privileges dialog contains the following elements:

Dialog element	Explanation

DatabaseDisplays the current database name.

Privileges For 	Displays the current user (or role). Use the arrow to display other users (or roles) to select.  Each time you select a new user or role, any changes you made to the previous user or role are accepted.

Granted Privileges	Displays the privileges already granted to the selected user (or role).

Remaining Privileges	Displays the remaining privileges that can be granted to the selected user (or role).

Reset	Choose Reset to cancel any changes you have made to privilege assignments for the current user or role. This button has no effect on privileges of other users or roles.

Grant	After selecting an item (or items) from the Remaining Privileges list, choose Grant to transfer selected privileges to the Granted Privileges list.

Revoke	After selecting an item (or items) from the Granted Privileges list, choose Revoke to transfer selected privileges to the Remaining Privileges list.

Cancel/OK	When this dialog first appears, it displays the Cancel button, which allows you to exit the dialog. When you make any changes, this button changes to OK.  This allows you to accept the changes and exit the dialog.

If you do not have DBA privileges, the dialog shown in Figure 3 - 4 appears, which only allows you to view your own privileges.

Figure 3 - 4.Privileges dialog (non-DBA)

To grant privileges to a user or to a role:

1.	Open the User Manager tool.

2.	Enter your username, password, and database when prompted.

The User Manager dialog appears and displays a list of existing users.

3.	Do one of the following:

•	If you are granting (or revoking) privileges to a user, select a username and choose Privileges.

•	If you are granting (or revoking) privileges to a role, choose Show Roles, and then select a role name and choose Privileges.

The Privileges dialog appears and displays the privileges of the selected user or role.

4.	Select those privileges from the Remaining Privileges list box that you want to grant or those from the Granted Privileges list box that you want to revoke.

5.	When finished, choose Grant or Revoke, as appropriate.

Caution: You can choose Reset to cancel your changes at any time. However, the Reset function only acts on the current user or role. Selecting a different user or role from the dropdown list box acts as a command to save changes, not to abandon your changes to the current user or role.

6.	Choose OK.

 ________________________________________


Deleting User Accounts and Roles

You delete a user account if you no longer want a specific username and password combination to connect to the database. You delete a role if you want to remove the privileges assigned to users through that role. (Deleting a role does not affect privileges granted to users directly.) Only users with DBA privileges may delete user accounts and roles.

To delete a role from the system:

1.	Open the User Manager tool.

2.	Enter your username, password, and database when prompted.

The User Manager dialog appears and displays a list of existing users.

3.	Do one of the following:

•	If you are deleting a user account, select the username you want to delete.

•	If you are deleting a role, choose Show Roles. Then select the role you want to delete.

4.	Choose Delete.

•	If you are deleting a user name, a dialog appears, warning you that all the objects the user currently owns will also be deleted. Choose OK to delete the user account, or Cancel to cancel.

•	If you are deleting a role, confirm that you want to delete this role by choosing Yes.

 ________________________________________


Changing a Password

From time to time you should change user passwords to ensure database security. All users may change their own passwords. Only users with DBA privileges may change another user's password.

To change a password:

1.	Open the User Manager tool.

2.	Enter your username, password, and database when prompted.

The User Manager dialog appears and displays a list of existing users.

3.	Highlight a user and choose Password.

The Change Password dialog appears.

Figure 3 - 5. Change Password dialog

4.	Enter the new password, confirm by entering it again, and choose OK.


CHAPTER 4. Using the Object Manager

This chapter provides detailed instructions on how to use the Object Manager.  The Object Manager enables you to create and modify database objects on any Oracle7 database.  The topics discussed in this chapter include the following:

•	Object Manager overview

•	creating and modifying tables

•	creating indexes

•	creating synonyms

•	creating and modifying database links

•	creating views

•	creating and modifying snapshots

•	granting object privileges to users or roles

•	deleting database objects

 ________________________________________


Overview

Use the Object Manager to create, modify, grant object privileges to, and delete tables, indexes, database links, views, and snapshots.

Figure 4 - 1 shows the Oracle Object Manager dialog.

Figure 4 - 1.Object Manager dialog

Note: Sample database files exist for the Scott/Tiger account. If you want to amend or delete them, you can find instructions for doing so in the SQL*Plus User's Guide and Reference, available online. The contents of the tables are contained in the DEMOBLD.SQL script; the sample table contents can be deleted with the DEMODRP.SQL script.

The Object Manager dialog contains the following elements:

Dialog element	Explanation

Type	Displays the current type of object. Use the arrow to select a different type of object.

Owner	Displays the username of the object's owner. Use the arrow to select the user whose objects you wish to view.

Objects	Displays the objects that belong to the selected user (the "owner").

Show	After selecting an object from the list box, choose Show to open the Table Management, Index Management, Synonym Management, Snapshot Management, or View Management dialog (described below), depending upon the type of object currently selected.

Create	Choose Create to open the Create New Object dialog, which lets you select what type of object to create.

Delete	Choose Delete to delete the selected object (you are prompted to confirm the deletion).

Privileges	Choose Privileges to open the Privileges dialog and grant or revoke object privileges to users or roles.

Database	Choose Database to select another database on which to perform object management tasks.

 ________________________________________

Table


Creating and Modifying a Table

Once you create a table, you become the owner of that table. Other users may not access that database object unless you, the owner, grant object privileges to them.  All users may create tables.

You can create and modify tables with the Object Manager.

To create a new table or modify an existing one:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears.

3.	Do one of the following:

•	If you are creating a new table, choose Create.

The Create New Object dialog appears.  Select Table and choose OK.

•	If you are modifying an existing table, highlight the table you want to modify and choose Show.

The Table Management dialog displays a table definition grid (see Figure 4 - 2) where you can create or modify a table.

Figure 4 - 2.Table Management dialog

Each row of the grid represents a column in the table you are creating. (Table columns are sometimes known as "fields".) Each cell in the row defines one element of the column.

Note: You can increase the width of any column by dragging the internal vertical lines of the grid.

Dialog element	Explanation

Table	This dropdown list box shows the name of the current table or "<New Table>".

Save	Choose Save if you wish to save your changes to the existing table.

Save As	Choose Save As if you wish to save the new or modified table under a new name.

The columns that make up the grid are defined below:

Column	Function

Column Name	Enter the name of the column. (When editing an existing table's definition, you cannot change existing column names.) Each column name must be unique within the table; the maximum length is 30 characters.

Type	Clicking in this column creates a dropdown list.  Use the arrow to select a valid Oracle data type for this column.

Table 4 - 1.

 Oracle Datatypes

------------------------------------------------------------------------

Data Type	Description

CHAR	Alphanumeric data at least 1 character long and no more

than 255 characters long.

DATE	Date. Oracle includes as part of the date the century, year,

month, day, hour, minute, and second.

LONG	Variable length alphanumeric strings up to 2 gigabytes in

size.

LONG RAW	Binary data up to 2 gigabytes in size.

NUMBER	Stores numeric zero, positive, or negative fixed or floating

point data.

RAW	Binary data up to 255 bytes in size.

ROWID	Hexadecimal string representing the unique address of a row

in a table.

VARCHAR2	Alphanumeric data at least 1 character long and no more

than 2,000 characters long.

Size	Enter the number of characters or bytes necessary to hold your data. For NUMBER data types, data size includes the total number of digits on both the left and right side of the decimal point.

Scale	For NUMBER datatypes, enter the number of digits to the right of the decimal point.

Default	Default value for the column if none is specified.  This feature is useful if a column cannot be null.

Null OK?	Click on this column to toggle between Yes and No. "Yes" means the column can be empty.

Unique Key?	Click on this column to toggle between Yes and No. "Yes" designates the column as a unique key. In a unique key column, no two table rows can have the same value.

Primary Key?	The number designates the position of this column in the primary key, that uniquely identifies a row in the table.  A primary key must satisfy two conditions: (1) no two table rows can have the same value; and (2) the current column cannot be empty. Click on this column to append it to the primary key.  The number that appears will be one greater that the highest primary key value in use for the current table.  Double click in the column to clear all primary key designations.

Foreign Key	Enter a foreign key if you want the values of this column to refer to the primary or unique key values in a different table. The general syntax for specifying a foreign key is:

OWNER.TABLE.(COLUMN NAME)

See the Oracle7 Server SQL Language Reference Manual (available online) for other detailed information.

Check Condition	Enter a combination of one or more expressions and logical operators that evaluate to TRUE, FALSE, or unknown. You can make entries here when creating a new table. However, once a table has been saved, you cannot edit the check condition of existing columns. An example of a check condition statement is as follows:

ORDERID IS NOT NULL

CUSTID IS NOT NULL

TOTAL>=0

See the Oracle7 Server SQL Language Reference Manual (available online) for other detailed information.

When you are finished defining or modifying your table, proceed as follows:

4.	Do one of the following:

•	If you want to save changes to an existing table, overwriting the table as it now exists, choose Save.

You will be prompted whether to overwrite the existing table.

Choose Confirm to overwrite the table with the changes, or Cancel to abort change.

•	If you want to save a new table or to save modifications to an existing table under a different name (so that you do not overwrite the existing table), choose Save As.

The New Table Name dialog appears.

Figure 4 - 3. New Table Name  dialog

The remaining steps apply only if you choose Save As.

5.	Enter a name for the new table and choose OK.  Table names must not exceed 30 characters.

The Object Privileges dialog (described below) appears for the table you just created, because users cannot use a table until you, the owner, grant object privileges to them.

6.	Select a user or role from the Privileges dropdown list box.

7.	Select those privileges from the Remaining Privileges text box that you want to grant or those from the Granted Privileges text box that you want to revoke.

Repeat Steps 6 and 7 as needed.

8.	When finished, choose Grant or Revoke.

Caution: At any time, you may choose Reset to cancel all the changes you made for the current table, and for the current user or role. However, the Reset function only acts on the current table, and for the current user or role. Selecting a different table, user, or role acts as a command to save changes, not to abandon them and switch to a different table, user, or role.

9.	Choose Close.

Additional Information: For more information about defining a table, see the Oracle7 Server SQL Language Reference Manual, available online.

 ________________________________________

Index


Creating an Index

Indexes are structures that you can create to facilitate your viewing and retrieval of data. All users may create indexes.

You create an index by choosing Create in the Object Manager and selecting Index in the Create New Object dialog, which opens the Index Management dialog. (To look at or edit an existing index, select an index name and choose Show.)

Figure 4 - 4. Index Management dialog

The Index Management dialog contains the following elements:

Dialog element	Explanation

Index	Displays the name of the current index or "<New Index>".

Table	Displays the list of available tables.

Table definition grid	Specify the columns in the order in which you want them indexed. Use the scroll bar to see all the column names. Clicking on a cell under Order makes the corresponding column the next one on which the index is built. Double-clicking in the Order column clears all index ordering and starts the ordering with the current column.

Save As	Choose Save As to display a dialog where you can name a new index.

To create a new index:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears.

3.	Choose Create.

The Create New Object dialog appears.

4.	Select Index and choose OK.

The Index Management dialog appears and <New Index> is selected in the Index field.

5.	Use the Table dropdown list box to select the table on which you want to create the index.

6.	Click on the columns in the order in which you want the index built. A double-click on any column clears all index ordering.

7.	When finished, choose Save As.

The New Index Name dialog appears.

Figure 4 - 5. New Index dialog

8.	When prompted, enter the name for the new index (or accept the default name) and choose OK. Object names must not exceed 30 characters. See the Oracle7 Server SQL Language Reference Manual (available online) for further information.

 ________________________________________


Creating a Synonym

Synonyms are simple, descriptive names for tables (or for other synonyms). For example, users have an easier time remembering table names like "Accounting" and "Emp" than "Reg1_acct" and "FY94_EMP". All users may create synonyms.

You create synonyms by choosing Create in the Object Manager and selecting Synonym in the Create New Object dialog, which opens the Synonym Management dialog.

Figure 4 - 6. Synonym Management dialog

The Synonym Management dialog contains the following elements:

Dialog element	Explanation

Synonym 	Displays the current synonym name or "<New Synonym>". Use the arrow to select a synonym.

From	Select the database that contains the objects you will use to create your synonym. This list contains the database to which you are currently connected, and all available database links.

On	Displays the full name of the table or synonym to which the selected synonym applies or--if <New Synonym> is selected--the object for which you want to assign a synonym.

Public	If you have DBA privileges, selecting Public makes the synonym available to all users.

Save As	After choosing a database object from the On dropdown list, choose Save As. You will be prompted for the new synonym.

To create a new synonym:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears.

3.	Choose Create.

The Create New Object dialog appears.

4.	Select Synonym and choose OK.

The Synonym Management dialog appears.

5.	Using the From dropdown list box, select a server from which you will look for objects.

6.	Using the On dropdown list box, select an object for which you want to create a synonym.

7.	If you have DBA privileges, you have the option to select the Public checkbox, which makes the synonym you just created available to all users.

8.	Choose Save As.

The New Synonym Name dialog appears.

Figure 4 - 7. New Synonym dialog

9.	When prompted, provide a name for the table synonym. Object names must not exceed 30 characters. See the Oracle7 Server SQL Language Reference Manual (available online) for further information.

10.	Choose Ok.

Note: If you choose to make the synonym public, it does not appear under your name as owner in the main Object Manager dialog. Instead, it appears under the Public user.

 ________________________________________


Creating and Modifying Database Links

A database link is an object in the local database that allows you to access objects on a remote database or to mount a secondary database in read-only mode.  All users may create and modify database links.

To create a new database link or modify an existing one:

1.	Open the Object Manager tool.

2.	Enter a username, password, and database.

The Object Manager dialog appears.

3.	Do one of the following:

•	If you are creating a new database link, choose Create.

The Create New Object dialog appears.  Select Database Link and choose OK.

•	If you are modifying an existing database link, select Database Link in the Type dropdown list box in Object Manager.  Highlight the database link you want to modify and choose Show.

The Database Link Manager dialog appears.

Figure 4 - 8. Database Link Name dialog

Dialog element	Explanation

Link	The name of the current database link, or "<New Link>".

Connect To	The database name or its connect string (or "connect descriptor" for SQL*Net V2).

Username	The name under which the connection is made. If this is left blank, the user's current login is used.

Password	The password for the user name, if applicable.

Public	If selected, makes the database link public to all users.  If not selected, the link is private and may be used only by the owner.

4.	Specify a database name in the Connect To field.

5.	Enter a username and password, if desired.

6.	If you want the database link you are now creating available to all users, select Public.

7.	When finished, either select Save to save changes to an existing database link, or select Save As to save a new or existing database link under a new name.

The New Database Link Name dialog appears.

Figure 4 - 9. New Database Link Name dialog

Dialog element	Explanation

Database Name	Specify the name of the current database.

Domain	Specify the name of the domain (i.e., the global object name) where the database is located.  See the Oracle7 Server Administrator's Guide, Chapter 15, for further information.

Link Name	Specify a name that uniquely identifies the link you are creating.

8.	Specify the database name and its domain.

9.	Enter a link name of your choice and choose OK.

Note: If you choose to make the database link public, it does not appear under your name as owner in the main Object Manager dialog. Instead, it appears under the Public user.

 ________________________________________


Creating a View

Views are custom-tailored presentations of the data from one or more database tables. They enable you to examine data in arrangements different from the actual arrangement of the data in the table(s). The Object Manager enables you to create and modify views. All users may create views.

To create a new view:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears.

3.	Choose Create.

The Create New Object dialog appears.

4.	Select View and choose OK.

The Create View dialog appears.

Figure 4 - 10. Create View dialog

Dialog element	Explanation

Create View From	Select Table to choose columns from a table, or View to choose columns from an existing view to create your new view.  Or, select SQL Statement to manually write the SQL code that will construct your view.

Source Location	Select the database which contains the objects you will use to create your view.  This list contains the database to which you are currently connected, and all available database links.

Tables (Views)	Select specific tables (and/or views) to use as sources for your view.

Choose Columns	Select columns from the sources you specified in the Create View From, Source Location, and Tables (Views) dropdown list boxes to add to your view.

Add	Adds the selected column in the Choose Columns box to the Selected Columns box.

Selected Columns	Lists all columns that you have selected to become part of your view.

Remove	Removes columns from the Selected Columns box.

Up	Moves the currently selected column up in the column order of your view.

Down	Moves the currently selected column down in the column order of your view.

Rename	Enables you to rename the currently selected column of your view.

Where...	Enables you to specify a condition (SQL WHERE clause) that will apply to the data that makes up your view.

5.	Select the down arrow on the Create View From dropdown list box, and select either SQL Statement, Table, or View.

6.	Do one of the following:

•	If you selected SQL Statement in the previous step, enter a valid SQL statement, choose Save As, and skip to step 13.

•	If you selected Table or View in the previous step, continue with the next step.

7.	From the Source Location dropdown list box, select the database that contains the tables or views from which you want to create your view.

8.	From the Tables or Views dropdown list box, select a table or view from which you want to use a column for your view.

9.	From the Choose Columns box, select columns that you want to add to your view by clicking on the row describing the column and choosing Add, or by double-clicking on the row.

Each column you add appears in the Selected Columns box.

You can use the Where button to specify conditions that apply to the data that will become part of the view.

If you choose Where, the Specify Condition dialog displays.

Figure 4 - 11. Specify Condition dialog

Dialog element	Explanation

WHERE	Write a SQL WHERE clause that will apply a condition to your view.

Columns	Lists columns currently listed in the Selected Columns box of the Create View dialog.

Append	Adds the name of the selected column to the end of the current WHERE clause.

This dialog allows you to specify conditions that will define which rows are included in your view.  For example, if you wanted to see only the rows where the value of the AGE column in the STUDENT table, owned by TEACHER, is more than 10, you would enter TEACHER.STUDENT.AGE>10 as your SQL WHERE clause.

10.	When you have finished creating your WHERE clause, select OK.

The Create View dialog reappears.

11.	You can also add columns to your view from multiple databases, tables, and views by selecting them from the dropdown list boxes at the top of the Create View dialog.  Repeat steps 5 through 9 as necessary to build your view.

12.	Once you have added all the columns that you want to make up your view, use the Remove, Up, Down, and Rename buttons to specify how you want your view to be constructed.

13.	When finished, choose Save As.

14.	Enter a name for your new view when prompted and choose OK.

Your view is created.

 ________________________________________


Showing a View

To show an existing view:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears.

3.	Choose View from the Type dropdown list box.

4.	Select the View you want to show.

5.	Choose Show.

The Show Views dialog appears.

Figure 4 - 12. Show Views dialog

The Show Views dialog contains the following elements:

Dialog element	Explanation

View	Displays the name of the current view and the name of the owner. Use the arrow to select another view.

Table definition grid	Displays the columns (and their characteristics) that define the view. You can use the arrows to scroll through the column descriptions and you can resize the individual cell widths, but you cannot edit the cell contents in this grid.

Query	Displays the SQL query used to create this view. You can use the arrows to scroll through the query.

 ________________________________________


Creating a Snapshot

A snapshot is a table that contains the results of a query or one or more tables or views.  Only users with DBA privileges may create snapshots.

To create a new snapshot:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears.

3.	Choose Create.

The Create New Object dialog appears.

4.	Select Snapshot and choose OK.

The Create Snapshot dialog appears.

Figure 4 - 13. Create Snapshot dialog

Dialog element	Explanation

Create Snapshot From	Select Table to choose columns from a table, or View to choose columns from an existing view to create your new snapshot.  Or, select SQL Statement to write the SQL code that will construct your snapshot.

Source Location	Select the database which contains the objects you will use to create your view.  This list contains the database to which you are currently connected, and all available database links.

Tables (Views)	Select specific tables (and/or views) to use as sources for your snapshot.  You can only create a snapshot from an object to which you have been specifically granted privilege, rather than having privilege through a role.

Choose Columns	Select columns from the sources you specified in the Create Snapshot From, Source Location, and Tables (Views) dropdown list boxes to add to your snapshot.

Add	Adds the selected column in the Choose Columns box to the Selected Columns box.

Selected Columns	Lists all columns that you have selected to become part of your snapshot.

Remove	Removes columns from the Selected Columns box.

Up	Moves the currently selected column up in the column order of your snapshot.

Down	Moves the currently selected column down in the column order of your snapshot.

Next Refresh	The number of hours until the next snapshot refresh takes place.  (The first snapshot refresh will occur immediately once you save your new snapshot.)  Valid values can be expressed as fractions of hours.  For example, if you wanted the snapshot refreshed every 90 minutes, enter 1.5.  If you want it refreshed every 10 minutes, enter 1/6.

Create Snapshot Log on Selected Table	Creates a snapshot log, a table associated with the master table of the snapshot.  Oracle stores changes to the master table in the snapshot log and then uses the snapshot log to refresh the snapshot.  You can only create a snapshot log if the snapshot is made up of columns from exactly one table.

Where	Enables you to specify a condition (SQL WHERE clause) that will apply to the data that makes up your snapshot.

5.	Select the down arrow on the Create Snapshot From dropdown list box, and select either SQL Statement, Table, or View.

6.	Do one of the following:

•	If you selected SQL Statement in the previous step, enter a valid SQL statement and and choose Save As and skip to step 14.

•	If you selected Table or View in the previous step, continue with the next step.

7.	From the Source Location dropdown list box, select the database that contains the tables or views from which you want to create your snapshot.

8.	From the Tables or Views dropdown list box, select a table or view from which you want to use a column for your snapshot.

9.	From the Choose Columns box, select columns that you want to add to your snapshot by clicking on the row describing the column and choosing Add, or by double clicking on the row.

Each column you add appears in the Selected Columns box.

You can use the Where button to specify conditions that apply to the data that will become part of the snapshot.

When you choose Where, the Specify Condition dialog displays.

Figure 4 - 14. Specify Condition dialog

Dialog element	Explanation

WHERE	Write a SQL WHERE clause that will apply a condition to your view.

Columns	Lists columns currently listed in the Selected Columns box of the Create View dialog.

Append	Adds the name of the selected column to the end of the current WHERE clause.

This dialog allows you to specify conditions that will define which rows are included in your snapshot.  For example, if you wanted to see only the rows where the value of the AGE column in the STUDENT table, owned by TEACHER, is more than 10, you would enter TEACHER.STUDENT.AGE>10 as your SQL WHERE clause.

10.	When you have finished creating your WHERE clause, select OK.

The Create Snapshot dialog reappears.

11.	You can also add columns to your snapshot from multiple databases, tables, and views by selecting them from the dropdown list boxes at the top of the Create Snapshot dialog.  Repeat steps 5 through 9 as necessary to build your snapshot.

12.	Once you have added all the columns that you want to make up your snapshot, use the Up and Down buttons to specify the order in which columns appear in your snapshot.

You can also use the Remove button to remove columns from your snapshot.

13.	Specify the Next Refresh interval in hours (or fractions of hours).

14.	When finished, choose Save As.

15.	Enter a name for your new snapshot, when prompted, and choose OK.

Your snapshot is created.

 ________________________________________


Showing or Modifying a Snapshot

You can only modify the refresh interval for an existing snapshot. To show or modify an existing snapshot:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears.

3.	Choose Snapshot from the Type dropdown list box.

A list of existing snapshots appears.

4.	Select the snapshot you want to show or modify.

5.	Choose Show.

The Snapshot Management dialog appears.

Figure 4 - 15. Snapshot Management dialog

Dialog element	Explanation

Snapshot	Displays the name of the current snapshot.

Last Refresh	Indicates the date and the time when this snapshot was last refreshed.

Next Refresh	If desired, edit the value for the next scheduled refresh.

Snapshot definition grid	Displays the definition of the current snapshot.  Use the scroll bar to see all the column names.

Query	Displays the SQL query that created the snapshot.  Use the scroll bar to see the whole query.

6.	Make changes to the Next Refresh item, as desired.

7.	Select Save to save the changes.

 ________________________________________

Object Privileges


Granting Object Privileges

Oracle gives you the opportunity to assign "object privileges" to users for any database object that you own. For example, you can "share" with other users a table that you have created by assigning object privileges on that table to those users. Object privileges can also be granted to roles, which is like granting object privileges to a group of users globally. Only owners of a database object may grant object privileges to users. Only users with DBA privileges may grant object privileges to roles.  You can use the object manager to assign privileges to tables only.

You grant object privileges in one of two ways :

•	after creating a new table or

•	by choosing Privileges from the Object Manager.

In both cases, the Object Privileges dialog appears.

Figure 4 - 16. Object Privileges dialog

Table 4 - 2 lists the object privileges that you may grant to a user.

Table 4 - 2.

Object Privileges

------------------------------------------------------------------------

These privileges	allow the user to

ALTER	Alter the table

DELETE	Delete rows of the table

INDEX	Create an index on the table

INSERT	Add new rows to the table

REFERENCES	Place constraints on a table column

SELECT	Query the table

UPDATE	Change data in the table

To grant privileges on an object:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears.

3.	Select Table from the Type dropdown list.

A list of tables appears in the Objects box.

4.	Select a table.

5.	Choose Privileges.

The Object Privileges dialog appears for the selected table.

6.	Select a user or role from the Privileges for: dropdown list box.

7.	Select a table from the Table dropdown list box.

8.	Select those privileges from the Remaining Privileges text box that you want to grant or those from the Granted Privileges text box that you want to revoke.

9.	When finished, choose Grant or Revoke.

Caution: At any time you may choose Reset to cancel all the changes you made for the current table, and for the current user or role. However, the Reset function only acts on the current table, and for the current user or role. Selecting a different table, user, or role acts as a command to save changes.

10.	Choose OK.

 ________________________________________

Database Objects


Deleting Database Objects

When database objects--tables, indexes, and synonyms--become obsolete, redundant, or for some other reasons are no longer needed, you should delete them. Owners may delete the database objects they own. Users with DBA privileges may delete all database objects.

To delete database objects:

1.	Open the Object Manager tool.

2.	Enter your username, password, and database.

The Object Manager dialog appears and displays a list of tables.

3.	Use the Type dropdown list box to select the type of object you want to delete.

4.	Highlight the object(s) you want to delete and choose Delete.

5.	Confirm the deletion by choosing OK.


CHAPTER 5. Using the Session Manager

This chapter provides detailed instructions on how to use the Session Manager to manage user sessions on any Oracle7 database. The topics discussed in this chapter include the following:

•	overview

•	disconnecting sessions

 ________________________________________


Overview

The Session Manager tool enables you to view and disconnect sessions in the database.

Figure 5 - 1. Session Manager dialog

The Session Manager dialog lists all current sessions and features:

Dialog element	Explanation

Main list box	Lists all sessions.

Refresh	Choose Refresh to update the list of sessions.

Disconnect 	Choose Disconnect to disconnect the selected session. (Double-clicking on the session has the same effect.)

Database	Choose Database to select another database in which to manage sessions.

 ________________________________________


Disconnecting Sessions

From time to time, you may need to shut down the database to perform routine maintenance or to handle a server problem. You should disconnect all sessions before shutting down the database. Only users who know the database password may disconnect sessions.

You disconnect sessions from the Oracle Session Manager dialog.  To disconnect a session:

1.	Open the Session Manager tool.

2.	When prompted, enter the database name and choose OK.

3.	When prompted, enter the database password and choose OK.

The Session Manager dialog displays a list of sessions.

Note: Choose Refresh to update the list of sessions, since connections might change over time.

4.	Select the session you want to disconnect and choose Disconnect.

5.	Choose Yes to confirm that you want to disconnect the session.


CHAPTER 6. Using the Database Expander

This chapter provides detailed instructions on how to use the Database Expander to increase, that is "expand", the amount of disk designated to hold user data.  The topics discussed in this chapter include the following:

•	Database Expander overview

•	expanding the database

 ________________________________________


Overview

When the initial database is first installed, a default amount of disk space is designated to hold user data. When this space is filled up, you must expand the database to obtain more space for user data.

The Database Expander displays the amount of space used and the amount of space that is free, and lets you expand the database if needed.

Oracle recommends that you expand the database as soon as it is 75% full to prevent problems arising from lack of space. Select a conservative amount by which to expand your database. You may always expand again, but it is impossible to contract the database. Only users who know the database password may expand the database.

Figure 6 - 1. Database Expander dialog

The Database Expander dialog contains the following elements:

Tablespace 	Displays the current tablespace. Use the arrow to select another tablespace. The tablespaces that most often need expanding are SYSTEM, USER_DATA, and any tablespace created by a third-party application.

Expand by	Enter the amount (in Kb) that you want to add to the current tablespace.

Database Space Allocation	The pie chart and accompanying text in this area show free, used, and total Kb of the current tablespace, if one is selected.

Expand	Choose Expand to perform the expansion. After confirmation, Oracle adds the specified amount to the current tablespace.

Database	Choose Database to select another database to expand.

 ________________________________________


Expanding the Database

To expand the database:

1.	Open the Database Expander tool.

2.	When prompted, enter the database name and choose OK.

3.	When prompted, enter the database password and choose OK.

The Database Expander dialog appears.

4.	Select a tablespace from the dropdown list box.

The dialog displays a pie chart showing the current allocation of free, used, and total space (in kilobytes).

5.	If you need to expand the database, select the number of kilobytes you want to add to the database from the Expand by dropdown list box or type in the amount.

6.	Choose Expand.

The Confirm Database Expansion dialog appears.

Caution: Expanding the database is not a procedure that can be "undone". Do not confirm the expansion unless you are sure you want to expand the database.

7.	Choose OK or Cancel.

Suggestion: After expanding one or more tablespaces, Oracle recommends that you shut down and back up the database.


CHAPTER 7. Using Export, Import, and SQL*Loader

This chapter describes how to use

•	Export

•	Import

•	SQL*Loader

to move data from one Oracle database into another, and load foreign data into an Oracle7 database.

 ________________________________________


Export

Use the Export utility to transfer

•	specified tables

•	database objects belonging to specified owners

•	the full database

to an operating system file.

Attention: Please note that only one copy of the Exporter can be running at one time.

Note: The Export utility is available for many different operating systems and hardware platforms. This section describes only those characteristics of the Export utility that apply to the Windows operating system.  For general information on the Export utility, especially details on the Export parameters discussed in this User Guide, please refer to the Oracle7 Server Utilities User's Guide.

Figure 7 - 1. Database Exporter dialog

The Database Exporter dialog contains the following elements:

Dialog element	Explanation

Export File	Enter the operating system filename that will contain your exported data.  (Specifies the value for the FILES parameter.)

Browse	Use this to select the operating system filename that will contain your exported data.

Tables	Choose if you want to export tables.  You must choose Specify to select the tables to export.  (Specifies the value for the TABLES parameter.)

Owners	Choose if you want to export database objects belonging to specified owners.  You must choose Specify to select users whose objects are to be exported.  (Specifies the value for the OWNER parameter.)

Full Database	Choose if you want to export the entire database.  (Specifies the value for the FULL parameter.)

Specify...	If you selected Tables or Users, choose Specify... to select a list of tables or a list of users to export.

Grants	Select if you want grants on database objects to be exported.  (Specifies the value for the GRANTS parameter.)

Rows	Select to export rows of tables.  You can elect not to export rows if your objective is to export only table definitions.  (Specifies the value for the ROWS parameter.)

Constraints	Select if you want table constraints exported.  (Specifies the value for the CONSTRAINTS parameter.)

Indexes	Select if you want indexes exported.  (Specifies the value for the INDEXES parameter.)

Advanced	Enables you to edit advanced parameters.

Database	Lets you change the database to which you are attached.

Defaults	Sets all options to their default value.

When the Specify button is chosen the Export Objects dialog appears.

Figure 7 - 2. Export Objects dialog

Dialog element	Explanation

Export	Moves selected objects from the Remaining Tables / Users list to the Selected Tables / Users list.

Export All	Moves all objects from the Remaining Tables / Users list to the Selected Tables / Users list.

Don't Export	Moves selected objects from the Selected Tables / Users list to the Remaining Tables / Users list.

Export None	Moves all objects from the Selected Tables / Users list to the Remaining Tables / Users list.

Exporting Table(s) to a File

To export a table or a list of tables that you own or have privileged access to:

1.	Open the Export utility.

2.	Enter your username and password when prompted.

The Database Exporter dialog appears.

3.	Make sure that Tables is selected.

4.	Choose Specify.

The Export Objects dialog appears, and displays a list of tables that you may export.

5.	Select the tables you want to export from the list on the right and choose Export.  Or, just choose Export All to export the entire list.

6.	When finished, choose OK.

The Database Exporter dialog appears.

7.	Verify that all values in the Export These Objects box are as you want them.

8.	If necessary, edit advanced export options by choosing Advanced. For further information, refer to the Editing Advanced Export Options section of this chapter.

9.	When ready, choose Export.

Exporting Owner(s) to a File

To export all objects belonging to an owner or list of owners:

1.	Open the Export utility.

2.	Enter your username, password, and database when prompted.

The Database Exporter dialog appears.

3.	Make sure that Owners is selected.

4.	Choose Specify.

The Export Objects dialog appears, and displays a list of owners whose objects you may export.

5.	Select the owners whose objects you want to export from the list on the right and choose Export.  Or, just choose Export All to export the entire list.

6.	When finished, choose OK.

The Database Exporter dialog appears.

7.	Verify that all values in the Export These Objects box are as you want them.

8.	If necessary, edit advanced export options by choosing Advanced.  For further information, refer to the Editing Advanced Export Options section of this chapter.

9.	When ready, choose Export.

Exporting the Full Database

To export the full database:

1.	Open the Export utility.

2.	Enter your username, password, and database when prompted.

The Database Exporter dialog appears.

3.	Make sure that Full Database is selected.

4.	Verify that all values in the Export These Objects box are as you want them.

5.	If necessary, edit advanced export options by choosing Advanced.  For further information, refer to the Editing Advanced Export Options section of this chapter.

6.	When ready, choose Export.

Editing Advanced Export Options

To edit advanced export options:

1.	If you want to modify advanced options, choose Advanced.

The Advanced Export Options dialog appears.

Figure 7 - 3. Advanced Export Options dialog

Dialog element	Explanation

Record Length	Enter a value for the length in bytes of the file record.  (Specifies the value for the RECORDLENGTH parameter.).

Buffer Size	Enter a value for the size in bytes of the buffer used to fetch data rows. ( Specifies the value for the BUFFER parameter.)

Record Export in System Data	Select if you want the export process recorded in the database.  (Specifies the value for the RECORD parameter).

Compress when imported	Select if you want to compress table data into one extent during an import (Specifies the value for the COMPRESS parameter).

Keep Components Consistent	Select if you want to export a read-consistent view of the database (Specifies the value for the CONSISTENT parameter).

Increment Type	Select type of incremental export.  (Specifies the value for the INCTYPE parameter).  Valid values for Windows are NONE, INCREMENTAL, COMPLETE, and CUMULATIVE.

Statistics Type	Select type of database optimizer statistics to generate when exported data is imported back into Oracle.  (Specifies the value for the STATISTICS parameter).  Valid values for Windows are NONE, ESTIMATE, and COMPUTE.

Generate Log File	Select if you want a log file generated for the export (Specifies the value for the EXP parameter).  You must specify the name of the log file.

Log File	Name of log file.  Used only when Generate Log File is selected.  (Specifies the value for the LOG parameter.) Use the associated Browse button to specify the path and filename for the log file.

Defaults	Sets all options to their default value.

2.	Edit the parameters as desired.

3.	When finished, choose OK.

The Database Exporter dialog appears.

Command-line Option

In certain situations, you may want to run Export using the command-line option.  For example, if you periodically want to run Export using the same options every time, you could add command-line syntax to the properties for the Export icon (as described below).

To use the command-line option, simply add the options to the command line within the Program Item Properties dialog for the Export icon.

To use the command-line option:

1.	Select the Export icon.

2.	Choose File from the Windows menu bar, then choose Properties.

The Program Item Properties dialog appears.

3.	Edit the Command Line as you see fit.

 Additional Information:    For more information about command-line options for Export, see the Oracle7 Server Utilities User's Guide.

4.	When finished, choose OK.

 ________________________________________


Import

Use the Import utility to transfer

•	specified tables

•	database objects belonging to specified owners

•	the full database

into an Oracle database.

Note: The Import utility is available for many different operating systems and hardware platforms; and operates in essentially the same way for all platforms. This section describes only those characteristics of the Import utility that apply to the Windows operating system. For general information on the Import utility, especially details on the Import parameters referred to in this User Guide, please see the Oracle7 Server Utilities User's Guide.

Attention: Please note that only one copy of the Importer can be running at one time.

Figure 7 - 4. Database Importer dialog

Dialog element	Explanation

Import File	Specify operating system file to import into an Oracle database.  (Specifies the value for the FILE parameter.)

Browse	Use this to select the operating system filename that will be imported into an Oracle database.

Ignore Creation Errors	If selected, Import will not report "object already exists" errors when attempting to create database objects.  (Specifies the value for the IGNORE parameter.)

Tables	Specify with this mode that you want to import only tables.  You must specify the table(s) you want to import by choosing Specify.

Users	Specify with this mode that you want to import database objects belonging to specified users.  You must specify the user(s) whose objects you want to import by choosing Specify.

Full Database	Specify with this mode that you want to import the full database.  (Specifies the value for the FULL parameter.)

Tables to Import	If you selected the Tables option, you must specify which tables to import by choosing Specify.  (Specifies a value for the TABLES parameter.)

Users to Import	If you selected the Users option, you must specify the users whose objects you want to import by choosing Specify.  (Specifies a value for the USER parameter.)

Specify	If you selected Tables or Users, choose Specify to specify a list of table(s) or a list of user(s) to import.

Grants	Specifies whether to import grants on objects.  (Specifies a value for the GRANTS parameter.)

Rows	Specifies whether to import rows of table data, as opposed to importing only the table definition.  (Specifies the value for the ROWS parameter.)

Indexes	Specifies whether indexes are to be imported.  (Specifies the value for the INDEXES parameter.)

Advanced	Enables you to edit advanced parameters.

Database	Lets you change the database to which you are attached.

Defaults	Sets all options to their default value.

Importing Table(s) From a File

To import a list of table(s) from an operating system file:

1.	Open the Import utility.

2.	Enter your username, password, and database when prompted.

The Database Importer dialog appears.

3.	Make sure that Tables is selected.

4.	Choose Specify.

The Specify Import Tables dialog appears, and displays a list of Remaining Tables that you may import.

5.	Select the table(s) that you want to import from the Remaining Tables list and choose Import.  Or, just choose Import All to import the entire list.

6.	When finished, choose OK.

The Database Importer dialog appears.

7.	Verify that all values in the Objects to Import box are as you want them.

8.	If necessary, edit advanced import options by choosing Advanced.

9.	When ready, choose Import.

Importing User(s) Objects From a File

To import a list of objects owned by specified users from an operating system file:

1.	Open the Import utility.

2.	Enter your username and password when prompted.

The Database Importer dialog appears.

3.	Make sure that Users is selected.

4.	Choose Specify.

The Specify Import Users dialog appears.

5.	Choose a name from Import this User.

6.	Assuming you have DBA privileges, if you want to import the user's objects into the schema of a user other than the one logged in, choose a user from To This User.

7.	Choose Add.

8.	Repeat steps 5-6 to import any additional user(s).

9.	Choose Done when finished.  The Database Importer dialog appears.

10.	Verify that all values in the Object to Import box are as you want them.

11.	If necessary, edit advanced import options by choosing Advanced.

12.	When ready, choose Import.

Importing a Full Database From a File

To import a full database from an operating system file:

1.	Open the Import utility.

2.	Enter your username, password, and database when prompted.

The Database Importer dialog appears.

3.	Choose Full Database.

4.	Verify that all values in the Objects to Import box are as you want them.

5.	If necessary, edit advanced import options by choosing Advanced.

6.	When ready, choose Import.

Editing Advanced Import Options

1.	From the Database Importer, choose Advanced.

The Advanced Import Options dialog appears.

Figure 7 - 5. Advanced Import Options dialog

Dialog element	Explanation

Record Length	Enter the length in bytes of the file record.  (Specifies the value for the RECORDLENGTH parameter).

Buffer Size	Enter the size in bytes of the buffer used to transfer data rows.  (Specifies the value for the BUFFER parameter.)

Commit After Each Array	If selected, Import will commit after each array insert.  (Specifies the value for the COMMIT parameter.)

Overwrite Existing Database Files	When doing a full database import, select this if you want to overwrite the existing data files making up the database. (Specifies the value for the DESTROY parameter.)

Generate Log File	If selected, Import will generate an import log file.

Log File	The name of the log file that contains the screen output.  (Specifies the value for the LOG parameter.)  Use the associated Browse button to specify the path and filename for the log file.

Write Index- Creation Commands to File	If selected, Import will create a file to hold the index-creation SQL statement.

Index File	The name of the file that will contain the index- creation SQL statement.  Use the associated Browse button to specify the path and filename for the index file.  (Specifies the value for the INDEXFILE parameter.)

Increment Type	Select which type of incremental import, SYSTEM, RESTORE, or None.  (Specifies the value for the INCTYPE parameter.)

2.	Verify that all values in the Advanced Import Options dialog are as you want them.

3.	When finished, choose OK.

Command-line Option

In certain situations, you may want to run Import using the command-line option.  For example, if you periodically want to run Import using the same options every time, you could add command-line syntax to the properties for the Import icon (as described below).

To use the command-line option, simply add the options to the command line within the Program Item Properties dialog for the Import icon.

To use the command-line option:

1.	Select the Import icon.

2.	Choose File from the Windows menu bar, then choose Properties.

The Program Item Properties dialog appears.

3.	Edit the Command Line as you see fit.

 Additional Information:    For more information about command-line options for Import, see the Oracle7 Server Utilities User's Guide.

4.	When finished, choose OK.

 ________________________________________


SQL*Loader

SQL*Loader enables you to load data from ASCII files containing delimited or fixed-length rows (i.e., "records").  See the Oracle7 Server Utilities User's Guide for a definition of variable and fixed-length records.  Only users who know the database password may use SQL*Loader.

To load data from an ASCII file using SQL*Loader for Windows, you must build a control file, which is an ASCII file (with the extension of .CTL) that tells SQL*Loader how to interpret the data contained in the data file.

For a complete list of options and instructions on using SQL*Loader, refer to the Oracle7 Server Utilities User's Guide.

Building a Control File

When preparing a SQL*Loader control file, you must follow certain syntax and notational conventions. For general information on the use and syntax of control files, refer to the Oracle7 Server Utilities User's Guide.

 Native Datatypes

When specifying datatypes in the SQL*Loader control file, note that the default size of native datatypes are specific to Windows. You cannot override these defaults in the control file. Table 7 - 1 lists defaults for native datatypes under Windows.

Table 7 - 1. Native Datatypes

Native Datatype	Default Field Length

INTEGER	4

SMALLINT	2

FLOAT	4

DOUBLE	8

Loading Your Data Into Oracle

To load your data into an Oracle database:

1.	Open SQL*Loader.

The SQL*Loader dialog appears.

Figure 7 - 6. SQL*Loader dialog

Dialog element	Explanation

Username	Enter your username

Password	Enter your password

Database	Enter the name of the database into which you want to load data

Control File	Enter the path and filename for the control file, or choose the Control File Browse button to select the control file from a list.

Note: You can not load data until you supply a username, password, and control file.  The Load button will remain dimmed and inoperable until you do so.

Data	Either enter the path and filename for the data file, or choose the Optional Files Browse button to select the data file from a list, or choose the Default button to select the default data file.

Log	Enter the path and filename for the log file, or choose the Default button to select the default log file.

Bad	Enter the path and filename for the bad file, or choose the Default button to select the default bad file.

Discard	Enter the path and filename for the discard file, or choose the Default button to select the default discard file.

Advanced	Choose the Advanced button to open the Advanced SQL*Loader Options dialog.

2.	Enter your username, password, and the name of the database into which you want to load the data.

3.	Enter the path and filename for the control file, or choose the Browse button to select the filename from a list.

If you do not have a control file set up, see "Building a Control File" section appearing earlier in this chapter.

4.	Choose the Optional Files Browse button to select the data file, or choose the Default button to default to the control file

5.	If you want, you may change the path and filename of the SQL*Loader log file.

6.	Specify the bad file.  During the execution of SQL*Loader, a bad file is generated to contain those data records, if any, that are rejected by SQL*Loader or by Oracle.

7.	Specify the discard file.  During the execution of SQL*Loader, a discard file is generated to contain those data records, if any, that are neither rejected nor inserted by SQL*Loader or by Oracle.  Such records are filtered out because they did not match any of the record-selection criteria specified in the control file.

8.	If necessary, edit advanced options by choosing Advanced.  For further information, please refer to Editing Advanced SQL*Loader Options later in this chapter.

9.	When finished, choose Load.

After you choose Load, a status dialog appears and reports on the status of the load, including feedback on number of records processed, errors, and so on.

Editing Advanced SQL*Loader Options

To edit advanced SQL*Loader options:

1.	Open SQL*Loader.

2.	Enter your username, password, and the name of the database into which you want to load the data.

3.	Specify path and filename of the control file.

If you do not have a control file set up, see "Building a Control File" section appearing earlier in this chapter.

4.	Choose Advanced.

The Advanced SQL*Loader Options dialog appears.  Each field is filled either with default values, or with inherited values from the last time these options were edited and saved.  After each load is run, these values revert back to default values.

Figure 7 - 7. Advanced SQL*Loader Options dialog

Dialog element	Explanation

Records to Skip	Specifies the number of logical records from the beginning of the file that should not be loaded.  Use this parameter to continue loads that have been interrupted.  (Specifies the value for the SKIP parameter.)

Records to Load	Specifies the number of logical records to load (LOAD parameter) after skipping the number of records specified in Records to Skip.

Rows per Commit	This parameter (ROWS) indicates the number of rows in the bind array.

Maximum Errors	Specifies the number of insert errors (ERRORS parameter.) to allow before SQL*Loader terminates with an error.

Maximum Discards	Specifies the number of discards (DISCARDS parameter) to allow before SQL*Loader terminates with an error.

Maximum Bind Array	Specifies the maximum size of the bind array (BINDSIZE parameter) in Bytes.

Data Path	Indicates that a conventional data path will be used to load your data into an Oracle7 database.  This sets the DIRECT parameter to FALSE.  (Only a conventional data path is available with this release.)

Defaults	Sets all options in this dialog box to their default value.

5.	When you have finished editing your advanced SQL*Loader options, choose OK.

The SQL*Loader dialog appears.

Command-line Option

In certain situations, you may want to run SQL*Loader using the command-line option.  For example, if you periodically want to run SQL*Loader using the same options every time, you could add command-line syntax to the properties for the SQL*Loader icon.

To use the command-line option, simply add the options to the command line within the Program Item Properties dialog for the SQL*Loader icon.

To use the command-line option:

1.	Select the SQL*Loader icon.

2.	Choose File from the Windows menu bar, then choose Properties.

The Program Item Properties dialog appears.

3.	Edit the Command Line as you see fit.

 Additional Information:    For more information about command-line options for SQL*Loader, see the Oracle7 Server Utilities User's Guide.

4.	When finished, choose OK.


CHAPTER 8. Using the Backup and Recovery Managers

This chapter describes how to use the following utilities:

•	Backup Manager

•	Recovery Manager

 Additional Information:    For more information see the Oracle7 Server Administrator's Guide for a discussion about backing up an Oracle7 database.

 ________________________________________


Backup Manager

Use the Backup Manager to back up your Oracle7 database.  The Backup Manager runs in two modes, depending upon the status of the database and the Support Recovery option, located on the Configure Initialization Parameters dialog.

See the chapter entitled "Using the Database Manager", for more information on the Support Recovery option.

If the database is running and the Support Recovery option is on, the database is in ARCHIVELOG mode.  If the database is running and the Support Recovery option is off, the database is in NOARCHIVELOG mode.  The Backup Manager, when opened, appears in one of two ways, depending upon the status of the database:

•	running in ARCHIVELOG mode

•	running in NOARCHIVELOG mode or not running

During the Backup process, you may be prompted to enter your database password.  If so, enter your database password and select OK.

If the database is running and operating in ARCHIVELOG mode, the Backup Manager dialog appears.

Figure 8 - 1.Backup Manager dialog: ARCHIVELOG mode

Dialog element	Explanation

Database Status	Indicates the status of the database.

Offline - Full Database	If selected, shuts down the database and perform a full database backup.  If the database was running before the backup, the Backup Manager restarts the database once the backup is complete.  Full database backup includes all the data files, all online redo log files, and one copy of the control file.

Online - Selected Tablespace	If selected, backs up the selected tablespace.

Online - Control File only	If selected, backs up only one copy of the control files.

Tape	If selected, the backup is stored to tape.

Device	Indicates the tape device that stores the backup.

Disk, Directory, and Browse	If selected, the backup file is stored to the directory specified in the Directory field. Oracle recommends that you specify the complete path. You can use the associated Browse button to select where you want the backup file to be stored.

Backup	Initiates the Backup procedure.

If the database is running in NOARCHIVELOG mode, or is not running, the Backup Manager appears.

Figure 8 - 2. Backup Manager dialog: NOARCHIVELOG mode or not running

Dialog element	Explanation

Database Status	Indicates the status of the database, either running in NOARCHIVELOG mode, or not running.

Backup Type	Indicates that the only type of backup possible is offline full backup.

Tape	If selected, the backup will be stored to tape.

Device	Indicates the tape device that will store the backup.

Disk, Directory, Browse	If selected, the backup file is stored to the directory specified in the Directory field. Oracle recommends that you specify the complete path. You can use the associated Browse button to select where you want the backup file to be stored.

Backup	Initiates the Backup the procedure.

Files	Enables you to review or modify the database files according to the current database structure.  These include data files, online redo log files, and control files.  (The Files button is only enabled when the database is not running.)

When the database is not running, the Files button is enabled.  Since the database is not running, detailed information about the database structure is probably not available.  However, accurate information is available if you have previously used the Backup Manager or Recovery Manager while the database was running.  When the database is running, Backup Manager reads and stores the structure of the database.

If the database is not running and you have not previously used Backup Manager with the database running, Backup Manager reads the database default values to determine the structure of the database.

Oracle recommends that you use the Files button to review the database files before performing a backup.

Choose Files to review or modify the database files.  The Database Files dialog appears.

Figure 8 - 3. Database Files dialog

Dialog element	Explanation

Data Files	If selected, the File List displays the list of data files on your database.

Log Files	If selected, the File List displays the list of log files and their group number associated with your database.

Control Files	If selected, the File List displays the list of control files on your database.

File List	Displays the valid list of files, depending upon whether Data, Log, or Control Files are selected.

Filename box	Enter a path and filename for a file you want to add to the list of files on your database.

Add	Adds the specified file in the Filename box to the File List.

Remove	Removes the selected file from the File List.

Group Number	Enabled only when the Log Files option is selected.  This indicates the log file group sequence to which the currently selected log file belongs.

Backing Up In ARCHIVELOG Mode

To back up the database in ARCHIVELOG mode:

1.	Open the Backup Manager.

The Backup Manager dialog appears.

2.	When prompted, enter the database password and choose OK.

3.	Select either Offline Full Database, Online Selected Tablespace, or Online Control File only, depending on what you want to back up.

4.	Select either Tape or Disk as a destination for the backup file(s).

Note: Backup to tape is not supported on all operating systems.

5.	If you selected Disk in the previous step, specify a destination path either by using the Browse button or by typing the path in the Directory field.

6.	Choose Backup to begin the backup procedure.

If you selected Offline Full Database, the database is automatically shut down by the backup process.  A Backup Status dialog appears showing you how the backup is proceeding.  Once the backup is complete, the database automatically re-starts.

Backing Up In NOARCHIVELOG Mode

To back up the database in NO ARCHIVELOG mode:

1.	Open the Backup Manager.

The Backup Manager dialog appears.

2.	When prompted, enter the database password and choose OK.

3.	Select either Tape or Disk as a destination for the backup file(s).

Note: Backup to tape is not supported on all operating systems.

4.	If you selected Disk in the previous step, specify a destination path either by using the Browse button or by typing the path in the Directory field.

5.	Choose Backup to begin the backup procedure.

The database is automatically shut down by the backup process.  A Backup Status dialog appears, showing you how the backup is proceeding.  Once the backup is complete, the database automatically re-starts.

Backing Up With the Database Not Running

To back up the database when not running:

1.	Open the Backup Manager.

The Backup Manager dialog appears.

2.	Select either Tape or Disk as a destination for the backup file(s).

Note: Backup to tape is not supported on all operating systems.

3.	If you selected Disk in the previous step, specify a destination path either by using the Browse button or by typing the path in the Directory field.

4.	Choose Files to review and optionally modify the filenames of the database structure.

The Database Files dialog appears.  The Data Files radio button is selected and these files are listed in the File List box.

5.	Use the Filename box, the Add button, and the Remove button to define the list of data files in the database.

6.	Select the Log Files radio button.

7.	Use the Filename box, the Group Number box, the Add button, and the Remove button to define the list of log files in the database.

8.	Select the Control Files radio button.

9.	Use the Filename box, the Add button, and the Remove button to define the list of control files in the database.

10.	When finished, choose OK.

The Backup Manager dialog appears.

11.	Choose Backup to begin the backup procedure.

A Backup Status dialog appears, showing you how the backup is proceeding.

 ________________________________________


Recovery Manager

Use the Recovery Manager to recover your Oracle7 database after a failure.

During the Recovery process, you may be prompted to enter your database password.  If so, enter your database password and select OK.

Additional Information: For more information about recovery, see Chapter 19, "Recovering A Database", in the Oracle7 Server Administrator's Guide.

Figure 8 - 4. Recovery Manager dialog

Dialog element	Explanation

Automatic Recovery	Performs an automatic recovery.

Restore From Full Database Backup	Restores the database from a full database backup file.

Restore Data File,then Do Recovery	Restores the selected data files and performs an automatic recovery.

Data file list box	Indicates the selected data file to restore.

Restore Control File, then Do Recovery	Restores the control file and performs an automatic recovery.

Recover	Initiates the recovery procedure.

Files...	Only enabled if the database is not running at the time you open the Recovery Manager.  Use this button to review or edit the file list according to the current database structure.

Parameters	Only enabled if the database is not running.  Use this button to review or edit the initialization parameters to be used during the recovery process.

Automatic Recovery

To perform an automatic recovery:

1.	Open the Recovery Manager.

The Recovery Manager dialog appears.

2.	Select Automatic Recovery.

3.	If the database is currently not running and you want to review and modify the filenames of the database structure, choose Files.  The Database Files dialog appears.  See "Reviewing and Modifying Database Files for Recovery" section later in this chapter for information on how to proceed. Otherwise, skip to step 5.

4.	If the database is currently not running and you want to change initialization parameters that will take effect once the database starts during the recovery process, choose Parameters.  Otherwise, skip to the next step.

The Edit Database Parameters dialog appears.  Refer to the chapter entitled, "Using the Database Manager" for further information on editing advanced database parameters.

5.	Choose Recover.

Restoring From Full Database Backup

1.	Open the Recovery Manager.

The Recovery Manager dialog appears.

2.	Select Restore From Full Database Backup

3.	If the database is currently not running and you want to review and modify the filenames of the database structure, choose Files.  Refer to the "Reviewing and Modifying Database Files for Recovery" section below.

4.	If the database is currently not running and you want to change initialization parameters that will take effect once the database starts during the recovery process, choose Parameters.

The Edit Database Parameters dialog appears.  Refer to the chapter entitled, "Using the Database Manager" for further information on editing advanced database parameters.

5.	Choose Recover.

The Full Database Restore dialog appears.

Figure 8 - 5. Full Database Restore dialog

Dialog element	Explanation

Restore From Tape	If selected, the database is restored from files contained on the tape listed in the Device dropdown list box.

Device	The tape device.

Restore From Disk	If selected, the database is restored from files contained in the directory listed in the Directory dropdown list box.

Directory	Indicates the directory where the backup files are stored.

Browse	Opens the Restoration Source dialog.

Backup	Lists all backup files located in the currently selected directory or tape.  Each backup is listed as "<latest backup>" or by date and time.

6.	Select either Restore From Tape or Restore From Disk.

7.	If you are restoring from tape, select the Device from which to restore from the dropdown list box.  If you are restoring from disk, select the Directory from which to restore either by using the Browse button or typing the directory path in the Directory field.

8.	Select the backup from which you want to restore files from the Backup dropdown list box.  Selecting "<latest backup>" causes the Recovery Manager to use the latest backup in the directory you specified.

If the database is not running, you are prompted for your database password.

9.	Enter your password and select OK.

10.	When finished, choose OK.

Restoring a Data File

1.	Open the Recovery Manager.

The Recovery Manager dialog appears.

2.	Select Restore Data File.

3.	Select a data file to restore from the dropdown list box.

4.	If the database is currently not running and you want to review and modify the filenames of the database structure, choose Files.  Refer to the "Reviewing and Modifying Database Files for Recovery" section below.

5.	If the database is currently not running and you want to change initialization parameters that will take effect once the database starts during the recovery process, choose Parameters.  Otherwise, skip to the next step.

The Edit Database Parameters dialog appears.  Refer to the chapter entitled, "Using the Database Manager" for further information on editing advanced database parameters.

6.	Choose Recover.

The Data File Recovery dialog appears.

Figure 8 - 6. Data File Recovery dialog

Dialog element	Explanation

Restore From Tape	If selected, the database is restored from files contained on the tape listed in the Device dropdown list box.

Device	The tape device.

Restore From Disk	If selected, the database is restored from files contained in the directory listed in the Directory dropdown list box.

Directory	Indicates the directory where the backup files are stored.

Browse	Opens the Restoration Source dialog.

Create A New Data File	If selected, Recovery Manager creates a new empty data file during recovery process.  Use this option if no data file backup exists.

Backup	Lists all backup files located in the currently selected directory or tape.  Each backup is listed as "<latest backup>" or by date and time.

7.	Select either Restore From Tape, Restore From Disk, or Create a New Data File.

You would select Create a New Data File only if no data file backup exists. Go to step 9.

If you select Restore From Tape, select the tape Device from the dropdown list box.  If you select Restore From Disk, select the directory from which to restore either by using the Browse button or by typing the directory path in the Directory field.

8.	Select the backup from which you want to restore files from the Backup dropdown list box.  Selecting "<latest backup>" causes the Recovery Manager to use the last known backup in the directory you specified.

9.	When finished, choose OK.

Restoring a Control File

1.	Open the Recovery Manager.

The Recovery Manager dialog appears.

2.	Select Restore Control File.

3.	If the database is currently not running and you want to review and modify the filenames of the database structure, choose Files.  Refer to the "Reviewing and Modifying Database Files for Recovery" section below.

4.	If you want to change initialization parameters that will take effect once the database starts during the recovery process, choose Parameters.  Otherwise, skip to the next step.

The Edit Database Parameters dialog appears.  Refer to the chapter entitled, "Using the Database Manager" for further information on editing advanced database parameters.

5.	Choose Recover.

The Control File Recovery dialog appears.

Figure 8 - 7. Control File Recovery dialog

Dialog element	Explanation

Restore From Tape	If selected, the database is restored from files contained on the tape listed in the Device dropdown list box.

Device	The tape device.

Restore From Disk	If selected, the database is restored from files contained in the directory listed in the Directory dropdown list box.

Directory	Indicates the directory where the backup files are stored.

Browse	Opens the Restoration Source dialog.

Create A New Control File	If selected, Recovery Manager creates a new control file as part of the recovery process.  Use this option if a backup control file is not available.

Backup	Lists all backup files located in the currently selected directory or tape.  Each backup is listed as "<latest backup>" or by date and time.

6.	Select either Restore From Tape, Restore From Disk, or Create a New Control File.

You would select Create a New Data File only if no data file backup exists. Go to step 8.

If you select Restore From Tape, select the tape Device from the dropdown list box.  If you select Restore From Disk, select the directory from which to restore either by using the Browse button or by typing the directory path in the Directory field.

7.	Select the backup from which you want to restore files from the Backup dropdown list box.  Selecting "<latest backup>" causes the Recovery Manager to use the last known backup in the directory you specified.

8.	When finished, choose OK.

Reviewing and Modifying Database Files for Recovery

When the database is not running, the Files button is enabled.  Since the database is not running, detailed information about the database structure is probably not available.  However, accurate information will be available if you have previously used the Backup Manager or Recovery Manager while the database was running.  When the database is running, Backup Manager or Recovery Manager reads and stores the structure of the database.

If the database is not running and you have not previously used Backup Manager or Recovery Manager with the database running, Recovery Manager reads the default values to determine the structure of the database.

To review and modify the filenames of the database structure, perform the following steps.  Note that the Files button is disabled if the database is running.

1.	Open the Recovery Manager.

The Recovery Manager dialog appears.

2.	Choose Files.

The Database Files dialog appears.  The Data Files radio button is selected and these files are listed in the File List box.

3.	Use the Filename box, the Add button, and the Remove button to define the list of data files in the database.

4.	Select the Log Files radio button.

5.	Use the Filename box, the Group Number box, the Add button, and the Remove button to define the list of log files in the database.

6.	Select the Control Files radio button.

7.	Use the Filename box, the Add button, and the Remove button to define the list of control files in the database.

8.	When finished, choose OK.

The Recovery Manager dialog appears.


CHAPTER 9. Using the Database Password Manager

This chapter describes how to use the Database Password Manager.  The topics discussed in this chapter include:

•	overview

•	changing a database password

 ________________________________________


Overview

Oracle requires that you use a password to secure your database. Doing so helps to protect your data from unauthorized access or accidental corruption or loss. In addition, from time to time you may want to change your database password to further ensure security. The Database Password Manager tool enables you to change the database password.  Only users who know the database password can change the database password.

Do not confuse the database password with a user password.  While a user password is attached to a particular username, the database password is attached to a database, and restricts certain functions to only those who know the database password.

For a discussion of the differences between the database password and the user password, and for information on how to connect to an Oracle7 database, refer to the chapter entitled, "Connecting to Oracle7."

Figure 9 - 1. Database Password Manager dialog

 ________________________________________


Changing a Database Password

Change the existing database password periodically to ensure data security. To change your existing database password:

1.	Choose the Database Password Manager icon.

The Database Password Manager dialog appears.

2.	Enter your current database password and choose Verify. If the password you entered is correct, the Change button is enabled.

3.	Choose Change.

4.	When prompted, enter and confirm a new database password.

5.	Choose OK.


GLOSSARY

 alias     An alternative name for a database (such as "Accounting").

 client     A system that runs an Oracle-supported application and connects to a shared database on an Oracle Server. Users running the Database Tools are clients.

 CONNECT privileges     allow you to access, use, and create database objects.

 connect string     The set of parameters, including a protocol, that SQL*Net uses to connect to a specific Oracle instance on the network.

 database name     A SQL*Net string that identifies the database and server you wish to use. This string consists of a letter representing the network protocol, a colon, and a server name (e.g., p:region). You can substitute an alias for this string.

 database expansion     Adding a specific amount of disk space to the current amount allocated for data.

 database object     A table, index, synonym, or view that is part of a database.

 database password     A password that secures a database.

 DB_BLOCK_BUFFERS     The number of database blocks that may be cached in the SGA. This is the most significant determinant of the SGA size and database performance. More block buffers improve the performance of the cache at the expense of system memory. The default value depends on the configuration selected in the Database manager.

 DBA privileges     allow high-level unrestricted access to the database and all its objects.

 EXP_FULL_DATABASE privileges     allow you to write data from the database to an operating system file.

 foreign key     A column in one table whose values refer to the primary or unique key values in another table. Foreign keys are used to guarantee that the values in one column also exist in a column of another table (also known as referential integrity).

 host     A computer that provides a shared resource on a network.

 hostname     The name or alias of the host system to which you want to connect.

 IMP_FULL_DATABASE     allow you to import files that were exported by Export.

 index     A database object. An index is an optional structure associated with a table to facilitate data retrieval.

 instance     The combination of background processes that begins each time a database is started.

 LOG_BUFFER     The memory allocated for the redo log buffer in the SGA. Larger values reduce log file I/O, particularly for long transactions. The default is four database block buffers.

 null     A null value represents missing, unknown, or inapplicable data.

 object ownership     Whoever creates a database object owns that object. A user with DBA privileges technically owns all database objects. Individual users, however, own the objects they create.

 object privilege     A privilege that you give to another user to access a database object that you own.

 OPEN_CURSORS     The maximum number of cursors (context areas) a single-user process can have at once. This is on a per-process basis, rather than a system wide limit.

 Oracle database     The Oracle Relational Database Management System (RDBMS).

 Oracle RDBMS     The software used to create and maintain the database system, as well as the actual data stored in the database.

 Oracle tool     Any Oracle end-user tool, such as SQL*Plus, or a third-party software program that interacts with an Oracle Server.

 primary key     A primary key is one or more column values that uniquely identify a row. There may only be one primary key in a table. No two rows in a table can have the same value for the primary key.

 redo log     A set of two or more files that record all changes made to the database.

 RESOURCE privileges     duplicate CONNECT privileges, but allow unlimited tablespace use and offer backward compatibility with older versions of Oracle.

 role     A named set of user privileges.

 server     A host system that runs a multiuser Oracle RDBMS and maintains a database that can be shared by remote clients.

 session     One connection between a user and the database.

 SHARED_POOL_SIZE     The size of the shared memory pool, containing shared cursors and PL/SQL. Larger values improve the performance of multi-user systems at the expense of system memory.

 shutdown     Shutting down the database prevents users from accessing the database and allows certain database management tasks to be performed that cannot be performed when users are connected.

 SORT_AREA_SIZE     The maximum amount of PGA memory used by an external sort. The default value is usually large enough for most database operations, though increasing it will improve the performance of large index creation. This is most common when running import of a large table.

 SQL     Structured Query Language. SQL (pronounced "sequel") is the internationally accepted standard for defining and manipulating relational databases.

 SQL*Net     The Oracle client/server communication software that enables Oracle tools or databases to operate transparently over any type of network protocol and operating system.

 startup     When an Oracle database is started, users can access the data it contains.

 SYSDBA privileges     contains all system privileges with ADMIN OPTION, and the SYSOPER system privilege; permits CREATE DATABASE and time-based recovery.

 SYSOPER     permits you to perform STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER, and include the RESTRICTED SESSION privilege.

 synonym     An alternate name given to a table for easier identification. You can also assign a synonym to another synonym.

 table     A database object consisting of columns and rows that contain data.

 tablespace     A logical portion of an Oracle database, used to allocate storage for table and index data.

 thread     Each instance of the database has its own set of redo logs. Collectively, these redo logs make up a thread of online logs enabling recovery if problems arise.

 unique key     Disallows duplicate values in a column.

 user password     A password that identifies the user to the database.

 user privileges     Permission to access, use, and create database objects, such as tables, indexes, synonyms, and views. User privileges are granted by someone with DBA privileges. Oracle7 for Windows has seven types of user privileges: CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE, SYSOPER, and SYSDBA.

 view     A presentation of the data in one or more tables. A view is the result of a SQL query.