2
Database Tools Overview

Oracle8i Client includes various tools to perform database functions. This chapter describes the preferred tools to perform common database administration tasks.

Specific topics discussed are:

• Choosing a Database Tool

• Starting Database Tools

• Using SQL*Loader

• Using Windows NT Tools

• Optional Windows NT Diagnostic and Tuning Utilities

Choosing a Database Tool

Database tools is a collective term for tools, utilities, and assistants that you can use to perform database administration tasks. Some database tools perform similar tasks, though no one database tool performs all database administration tasks. The following sections indicate which database tools can be used on particular operating systems and the preferred tools to use for common database administration tasks.

Note: This chapter describes tasks that use SQL*Plus command line syntax. In this guide, all Server Manager text and examples have been replaced with SQL*Plus equivalents. Although Server Manager continues to ship with 8.1.x releases, Oracle Corporation strongly recommends that you migrate to SQL*Plus as soon as possible. See your SQL*Plus documentation for information on using SQL*Plus to perform database administration tasks. Note that for all previous Oracle8 8.0.x releases, the Server Manager executable was SVRMGR30. For 8.1.6, the Server Manager executable is SVRMGRL. The "L" indicates line mode.

Tools and Operating System Compatibility

This table lists tools and the operating system(s) on which each can be used:

Tools	Windows NT and Windows 20001	Windows 95 and Windows 98
Application Development
SQL*Plus (SQLPLUS)2	Yes	Yes
Pro*C/C++	Yes	Yes
Pro*COBOL	Yes	Yes
Object Type Translator (OTT)	Yes	Yes
Oracle Web Publishing Assistant	Yes	No
Oracle Services for Microsoft Transaction Server	Yes	Yes
Oracle AppWizard for Microsoft Visual C++	Yes	Yes
Oracle Objects for OLE	Yes	Yes
Oracle Provider for OLE DB	Yes3	Yes4
Database Administration
Oracle Enterprise Login Assistant (a feature of Oracle Advanced Security)5	Yes	Yes
Oracle Enterprise Manager, Release 2.1
Oracle DBA Management Pack (database tools and wizards)	Yes	Yes
Diagnostics Pack	Yes	Yes
Enterprise Manager: Console	Yes	Yes
Diagnostics Pack	Yes	Yes
Extended Applications Application Manager Replication Manager	Yes	Yes
Extended Database Administration: Distributed Access Manager, Beta Enterprise Security Manager Oracle interMedia Text Manager Oracle Spatial Index Advisor, Beta	Yes	Yes
Migration Utilities
Oracle Migration Workbench	Yes	Yes
Oracle Utilities from the MS-DOS Command Line
Export Utility (EXP)	Yes	Yes
Import Utility (IMP)	Yes	Yes
Recovery Manager (RMAN)	Yes	Yes
SQL*Loader (SQLLDR)	Yes	Yes
TKPROF (TKPROF)	Yes	Yes
Network Administration
Net8 Assistant	Yes	Yes
Net8 Configuration Assistant	Yes	Yes
Oracle Wallet Manager (a feature of Oracle Advanced Security)6	Yes	Yes
Windows NT Tools
Task Manager	Yes	Yes
Control Panel	Yes	Yes
Event Viewer	Yes	No
Registry	Yes	Yes
User Manager	Yes	No
Microsoft Management Console7	Yes	No

1 Windows 2000 production versions were not available during the development and testing of Oracle8i release 8.1.6 products. Oracle Corporation has used the Release Candidate versions of Windows 2000 for development and testing. Refer to the READMEDOC.HTM file at the top level of the CD-ROM for the latest information on certification and support of release 8.1.6 products on Windows 2000. 2 The ORADEBUG utility can be used through SQL*Plus to send debug commands to Oracle processes. 3 Available only with Windows NT. 4 Available only with Windows 98. 5 Available only with Oracle8i Enterprise Edition, and not Oracle8i. 6 Available only with Oracle8i Enterprise Edition, and not Oracle8i. 7 Automatically included with Windows 2000. To use with Windows NT 4.0, you must obtain the Microsoft Management Console from Microsoft Corporation.

Preferred Database Tools

This table lists common database administration tasks and the various database tools you can use to perform them. Oracle Corporation recommends you use the tools listed in the "Preferred Database Tool" column of the table. After choosing a tool to perform a task, go to "Starting Database Tools" for instructions on how to start the tool.

Database Administration Task	Preferred Database Tool	Other Database Tools
Export data	Export Wizard	Export Utility (EXP)
Import data	Import Wizard	Import Utility (IMP)
Load data	Load Wizard	SQL*Loader (SQLLDR)
Publish data to the Web	Oracle WebDB	Oracle Web Publishing Assistant
Authenticate database administrators and users	Security Manager	Oracle Enterprise Login Assistant SQL*Plus Windows NT operating system Oracle Administration Assistant for Windows NT (OS authenticated users)
Grant database roles	Security Manager	User Manager Oracle Administration Assistant for Windows NT (OS authenticated users)

Starting Database Tools

This section describes how to start each of the tools in the following categories:

• Starting Tools in Multiple Oracle Homes

• Starting Tools

• Starting Oracle Utilities from the Command Line

• Starting Oracle Enterprise Manager

• Starting Windows NT Tools

You will be referred back to this section for tool startup procedures as you use this guide.

Starting Tools in Multiple Oracle Homes

If you have multiple Oracle homes on your computer from previous releases, see "Multiple Oracle Home Functionality in Different Releases" and "Multiple Oracle Home Environments" for a description of the differences between pre-8.1.6 Oracle homes and release 8.1.6 and later Oracle homes.

Starting Tools from Release 8.0.4 and later 8.0.x Multiple Oracle Homes

If you are using multiple Oracle homes functionality, the command to start a tool includes a HOME_NAME, where HOME_NAME indicates the name of a different Oracle home. Note that the first Oracle home created on your computer does not have HOME_NAME appended to the group. For example:

To start SQL*PLUS from the first Oracle home, choose:

Start > Programs > Oracle > Application Development > SQL*PLUS

To start Oracle Database Assistant from an additional Oracle home, choose:

Start > Programs > Oracle - HOME_NAME > Application Development > SQL*PLUS

Starting Tools from Release 8.1.6 Multiple Oracle Homes

In release 8.1.6, all Oracle homes, including the first Oracle home you create on your computer, have a unique HOME_NAME. For example, the command to start Database Configuration Assistant is as follows:

Start > Programs > Oracle - HOME_NAME > Application Development > SQL*PLUS, where HOME_NAME is the name of the Oracle home. For example, either HOME1 or HOME2 in the following figure:

Starting Tools

This table describes how to start most tools, and where to go for further information on using these products¹:

Tool	Choose Start > Programs > Oracle - HOME_NAME >	For More Information, See...
Oracle Migration Workbench	Migration Utilities > Migration Workbench	Oracle Migration Workbench Release Notes Oracle Migration Workbench for MS SQL Server and Sybase Adaptive Server Reference Guide Oracle Migration Workbench for MS Access Reference Guide
Net8 Assistant	Network Administration > Net8 Assistant	Net8 Administrator's Guide
Net8 Configuration Assistant	Network Administration > Net8 Configuration Assistant	Net8 Administrator's Guide
Oracle Wallet Manager	Network Administration > Wallet Manager	Oracle Advanced Security Administrator's Guide
Oracle Web Publishing Assistant	Application Development > Oracle Web Publishing Assistant	Oracle Web Publishing Assistant Getting Started
Oracle Enterprise Login Assistant	Network Administration > Enterprise Login Assistant	Oracle Advanced Security Administrator's Guide
OO4O	Application Development > OO4O	Online help available from the Start Menu.
Oracle Provider for OLE DB	Application Development > Oracle Provider for OLE DB	Oracle Provider for OLE DB User's Guide

¹When you use an assistant, you must have read/write access to the directory where database files will be moved/created. Additionally, users must have administrative privileges to create an Oracle8i database. If the Oracle Database Configuration Assistant is run from an account that is not part of the Administrator's group, the tool exits without completing the operation.

Starting Oracle Utilities from the Command Line

This table describes how to start Oracle utilities from the MS-DOS command prompt, and where to go for further information on using these products:

Oracle Utilities	To Start...	For More Information, See...
Export Utility (EXP)	Enter the following at the MS-DOS command prompt followed by your user name and password: C:\> EXP EXP starts and prompts you for parameters. To obtain a list of these parameters, enter the following at the MS-DOS command prompt: C:\> EXP HELP=Y Note: When running the Export Utility, the default values for the following parameters under Windows NT are: BUFFER 4 KB RECORDLENGTH 2 KB Note: To export an entire database, you must use the user name SYSTEM. Do not use INTERNAL or SYS.	Oracle8i Utilities, which describes how to use the Export Utility Oracle8i Error Messages for information on error messages
Import Utility (IMP)	Enter the following at the MS-DOS command prompt followed by your user name and password: C:\> IMP IMP starts and prompts you for parameters. To obtain a list of these parameters, enter the following at the MS-DOS command prompt: C:\> IMP HELP=Y Note: When running the Import Utility, the default values for the following parameters under Windows NT are: BUFFER 4 KB RECORDLENGTH 2 KB	Oracle8i Utilities, which describes how to use the Import Utility Oracle8i Error Messages for information on error messages
Recovery Manager (RMAN)	Enter the following at the MS-DOS command prompt: C:\> RMAN PARAMETERS	Oracle8i Backup and Recovery Guide for instructions on using this tool
SQL*Plus (SQLPLUS)	Enter the following at the MS-DOS command prompt: C:\> SQLPLUS	SQL*Plus User's Guide and Reference
SQL*Loader (SQLLDR)	Invoke SQL*Loader at the MS-DOS command prompt followed by certain keywords. Enter the following and SQL*Loader displays a Help screen with the available keywords and default values: C:\> SQLLDR	Oracle8i Utilities, which describes how to use SQL*Loader Oracle8i Error Messages for information on error messages "Starting Windows NT Tools"
TKPROF (TKPROF)	Enter the following at the MS-DOS command prompt: C:\> TKPROF	Oracle8i Tuning

Starting Oracle Enterprise Manager

Applications in the DBA Management Pack can be launched through the Oracle Enterprise Manager console or launched separately as standalone applications. All database applications can also be launched from the console within a web browser.

When an application is launched through the console, it is connected to the Oracle Management Server and is used in the Oracle Enterprise Manager repository. When an application is launched separately, the user has the option to connect to either a specific database or to a Management Server. When connected to an Oracle Management Server, the DBA Management Pack application has access to all the databases in that Oracle Enterprise Manager repository.

To start an Oracle Enterprise Manager tool as a standalone application:

1. Choose Start > Programs > Oracle - HOME_NAME > DBA Management Pack > tool.

   For example, choose Start > Program > Oracle - HOME1 > DBA Management Pack > Schema Manager.

   After launching a DBA application, the Oracle Enterprise Manager Login dialog box appears, giving you the option to connect to either the Oracle Management Server or directly to a single database.

   
   
2. Choose the login method for the database administration application and enter the appropriate connect information.
   • Login to the Oracle Management Server.

     When a database administration application is connected to the Oracle Management Server, the DBA application can access all the databases on discovered nodes in that repository, and all of these databases appear in the client's tree list of managed objects. The Oracle Management Server must be running for a DBA application to connect to it.

   • Connect directly to a single database.

     When connected to a single database, the Oracle Management Server does not need to be running, and that database is the only database that shows in the client's tree list. Your TNSNAMES.ORA file must have an entry for the database. You can also enter the host:port:sid connect string for your service.

     See Also: Oracle Enterprise Manager Configuration Guide for information on configuration tasks you must perform before using Oracle Enterprise Manager and information on how to connect to an Oracle database.

To start an Oracle Enterprise Manager tool from the Console:

1. Choose Start > Programs > Oracle - HOME_NAME > Enterprise Manager > Console.

   The Login Information dialog box appears.

2. Log on when prompted.

3. You can now either:
   • Select the database you want to administer in the Navigator tree or in the Map window, then choose the tool from the Console Tools menu or in the Launch Palette.

   • Select the database you want to administer in the Navigator tree, then choose the tool from the Related Tools menu of the context-sensitive menu.

   • Choose the application from the Console Tools menu or from the Launch Palette, then enter the connect information in the Login Information dialog box.

     Note: When you select a database before starting a tool, you are connected to the database according to the preferred credentials that have been set up for the database or the credentials you used to log on to the Console. If connection to the database fails for any reason, the Login Information dialog box reappears.

To start an Oracle Enterprise Manager tool from a Web browser:

1. Launch your Web browser and enter the following URL regardless of which Web server you have installed.

   http://<webserver hostname>:<port number>/
   
   oem_webstage/EMWebSite.html
   
   

   For example:

   http://jfox-sun:3339/oem_webstage/EMWebSite.html
   

An index page appears, allowing you to launch various products, documentation, and Web sites.

• Enter the machine name for the Management Server to which you want to connect and click the application icon or name of the application you want to launch.

  
  

• If you are logging in to Oracle Enterprise Manager for the first time, type in the default credentials (administrator name and password).

  Administrator = sysman

  Password = oem_temp

  These credentials are for the default super administrator account. The first time you start Enterprise Manager, you must log in as the super administrator. After other administrator accounts have been created using the super administrator account, you can log in as a different administrator.

  Starting Windows NT Tools

  This table describes how to start each Windows NT tool, and where to go for more information on using these products:

  Windows NT Tools	To Start...	For More Information, See...
  Control Panel	Choose Start > Settings > Control Panel	"Control Panel" Your Microsoft Windows NT documentation
  Event Viewer	Choose Start > Programs > Administrative Tools > Event Viewer	"Event Viewer" Your Microsoft Windows NT documentation
  Oracle Performance Monitor for Windows NT	Choose Database Administration > Oracle for Windows NT Performance Monitor	"Registry" Your Microsoft Windows NT documentation
  Registry	Enter the following at the MS-DOS command prompt on Windows NT: C:\> REGEDT32 The registry editor window appears. Enter the following at the MS-DOS command prompt on Windows 95 or Windows 98: C:\> REGEDIT The registry editor window appears.	"Registry" Appendix B, "Oracle8i Configuration Parameters and the Registry" Your Microsoft Windows NT documentation
  User Manager	Choose Start > Programs > Administrative Tools > User Manager	"User Manager" Your Microsoft operating system documentation
  Microsoft Management Console (MMC)	Start > Programs > Oracle - HOME_NAME > Database Administration > Oracle Administration Assistant for Windows NT Note: MMC is launched when the Oracle Administration Assistant for Windows NT is started.	Your Microsoft operating system documentation
  Task Manager	Right-click on the Task Bar.	Your Microsoft operating system documentation

  Using SQL*Loader

  This section describes Windows NT-specific information for using SQL*Loader (SQLLDR).

  Windows NT Processing Options

  These are the possible values for the Operating System Dependent (OSD) file processing specifications string option, referred to in the "SQL*Loader Control File Reference" chapter of Oracle8i Utilities.

  Processing Option	Description
  ""1	Stream record format in which each record is terminated by a newline character. The maximum record size is 48 KB.
  "FIX n"	Fixed record format in which each record is exactly n bytes long. If the record is terminated by a newline character, the newline character must be the nth byte. Note that the Oracle8i Utilities guide refers to this control file option as "RECSIZE".
  "VAR xxxx"	Load variable length records. Specify the OSD "VAR recsizehint" in the control file for this option to take effect. The xxxx gives an estimate of the average record size to SQL*Loader so that it can approximate buffer sizes accurately and not waste memory. The default length is eighty characters. The xxxx does not specify how many leading bytes of length are included in each record. It only acts as a hint to SQL*Loader. Each record must always be preceded by five ASCII bytes containing the length of the remainder of the record. For example, a record must look like the following: 00024This is a 24 byte string Any whitespace, carriage returns, or linefeeds at the end of the record are ignored unless specifically included in the byte count in the length field.

  1 Two double quote characters with no space in between.

  Direct Path Option

  SQL*Loader includes a direct path option that bypasses redo log and data verification features, thereby decreasing loading time. Use the direct path option with data files known to be error free.

  Control File Conventions

  When preparing a SQL*Loader control file (.CTL), you must follow certain syntax and notational conventions. When specifying datatypes in the SQL*Loader control file, note that the default sizes of native datatypes are specific to Windows NT. You cannot override these defaults in the control file.

  Native Datatypes	Default Field Length
  DOUBLE	8
  FLOAT	4
  INTEGER	4
  SMALLINT	2

  See Also: Oracle8i Utilities for a complete list of options and instructions on using SQL*Loader.

  Using Windows NT Tools

  The following Windows NT tools can be used to administer an Oracle database:

  • Control Panel

  • Event Viewer

  • Registry

  • User Manager

  Control Panel

  The Control Panel enables you to modify system options such as computer services. A service is an executable process registered in the registry and administered by Windows NT. The registry automatically tracks and records security information for each service you create.

  Which Oracle Services Appear in the Control Panel?

  When you install the Oracle database and other products, Oracle services are created and displayed in the Services dialog box:

  
  

  Use the Services dialog box to start, stop, pause, or continue each of the Oracle services available on the computer.

  Oracle uses services to provide support for its operations, similarly to Windows NT services. In order to create, connect to and use an Oracle instance, an Oracle service is created during the database creation process and associated with the Oracle database.

  Once a service is created with your Oracle database, the service can run even while no user is logged on. This is because your Oracle database starts each instance as a service.

  Additional Information: You can have multiple, active Oracle home directories on a single computer. This affects the naming conventions for Oracle services. See "Multiple Oracle Home Environments" for additional information.

  Event Viewer

  Event Viewer is included with the Windows NT operating system, along with the other built-in Windows NT diagnostic and tuning utilities. These include:

  Event Viewer enables you to monitor events in your system. An event is an important occurrence in the system or application (such as your Oracle database) that requires user notification. While messages for major events can display on-screen as you work at your computer, events not requiring your immediate attention are recorded by Windows NT in the Event Viewer log file. You can then view this information at your convenience.

  What Oracle Database Events Are Monitored?

  Event Viewer can be used to monitor Oracle database events, such as:

  • initialization of the System Global Area (SGA) for the active instance

  • initialization of the Program Global Area (PGA) for the background processes of the active instance

  • connection to the Oracle database with the CONNECT INTERNAL command

  In addition, the operating system audit trail is logged to Event Viewer. The following figure shows Event Viewer displaying Oracle database events. Double-click an entry to find out specific information about an event.

  
  

  Registry

  The Oracle database stores its configuration information in a structure known as the registry. You can view and modify this configuration information through the registry editor. The registry contains configuration information for your computer, and must not be accessible for editing by inexperienced users. Only experienced administrators should view and change this information.

  The registry editor displays configuration information in a tree-like format consisting of four keys (or folders). These keys are shown in the tree view in the left-hand window. In the right-hand window, the parameters and values assigned to that key are displayed.

  What Database Parameters Are Configured?

  When you install products from your CD-ROM, configuration parameters are automatically entered in the registry. These parameters are read each time your Windows NT computer is restarted and whenever an Oracle product is launched. These parameters include settings for:

  • Oracle home directory

  • Language

  • Company name

  • Oracle home subdirectories for individual products

  • Individual products such as SQL*Plus

  • Services

  The following figure shows some of the Oracle database configuration parameters in the registry:

  
  

  See Also: Appendix B, "Oracle8i Configuration Parameters and the Registry" for definitions of Oracle database configuration parameters and specific instructions on using the registry to modify Oracle database configuration parameters.

  User Manager

  User Manager enables you to manage Windows NT computer security and create user accounts.

  What Oracle8i Database Tasks Can User Manager Perform?

  With User Manager, you can:

  • Grant Oracle database roles.

  • Use operating system authentication for user accounts. For example, grant DBA access to an NT user.

  • Create an NT user account that enables you to make secure client connections to the Oracle8i database without a password.

  Optional Windows NT Diagnostic and Tuning Utilities

  The following tools are supplied with the Windows NT Resource Kit:

  • QuickSlice

    Provides a quick, GUI overview of what is occurring on the system. It has the following benefits:

    • Distinguishes between time spent in user mode and kernel mode

    • Low overhead on the system (unlike Performance Monitor)

    • Shows a continuous display, rather than just a snapshot

    • You can double-click on a process to open a window with more details

  • Process Viewer

    Summarizes resource usage by a process.

  • Process Explode

    Provides a detailed display of resource usage by a process.

  • Task List

    Resource usage and other details of a process may be displayed by giving its PID or process name as an argument to Task List. This tool also displays a list of executables and DLLs associated with a process.

    See Also: Appendix B, "Oracle8i Configuration Parameters and the Registry". Oracle8i Client Installation Guide for Windows, Chapter 1, "Introducing Oracle8i Client for Windows" for the components available for installation.