7
Overview of the Database Tools

This chapter provides an introduction to the Oracle Enterprise Manager database administration (DBA) tools and utilities and describes their organization and basic elements. This chapter covers the following topics:

• Introduction
• Application Objects
• Application Menus
• Multi-Column Lists
• Dialog Boxes
• Property Sheets
• Starting an Application

Introduction

The database tool and utility applications are the primary administrative components of Oracle Enterprise Manager. You can use these applications to perform most of your DBA administration tasks.

The design of the applications allows you to:

• Use the graphical interface to execute the commands quickly and conveniently by pointing and clicking with the mouse rather than manually entering the SQL commands to perform these tasks.
• Concurrently perform multiple tasks in multiple Oracle Enterprise Manager windows.
• Administer multiple databases simultaneously in multiple windows.
• Centralize database administration tasks for both local and remote databases running on any Oracle platform in any location worldwide. In addition, these Oracle platforms can be connected by any network protocols supported by SQL*Net and the MultiProtocol Interchange.
• Perform DBA administrative tasks using the Server Manager line mode interface when a graphical user interface is unavailable or undesirable. The line mode option is available on all platforms.

The applications allow you to perform the following database administration tasks:

• Manage instances, initialization parameters, and sessions
• Manage schema objects
• Manager users, privileges, and profiles
• Manage software distribution
• Manage storage
• Enter and execute SQL statements, PL/SQL code, and Server Manager DBA commands
• Back up, recover, and restore a database
• Export/Import data to/from an Oracle database

Additional applications can be purchased to allow you to:

• Diagnose your database system
• Manage a replication environment

Application Objects

This section provides an overview of the objects administered by the database tools and utilities and briefly describes the objects that each application manages. For detailed descriptions of the objects and commands available in each application, see the specific chapter on the application.

Types of Objects

The database tools and utilities, like the Oracle Enterprise Manger Console, provide a tree list of groups and instances to organize the objects associated with the application. The tree list displays on the left side of an application window after the application has successfully connected to a database. See Figure 7-1, "An Application Window" for an example of a tree list in an application window.

The groups and instances are usually contained in a database folder. The database folder is the root of the tree list and displays the name of the database that the application is connected to.

Folders hold other objects and display a '+' or '-' to the left of the object's icon and name. A folder can be expanded or collapsed to show or hide its contents. Objects that do not contain other objects do not display a '+' or '-' to the left of the object's icon or name.

• A group is a folder that holds a group of specific object types and is identified with a folder icon. For example, a Users group contains only users.
• A composite or expandable instance is a folder that consists of various objects. For example, the user SCOTT is a composite instance that consists of the system privileges, object privileges, and roles that have been granted to SCOTT. Composite instances have property sheets that can be edited.
• A simple instance is a single object that does not contain other objects. For example, a system privilege, such as ALTER TABLESPACE, is an instance and does not contain any objects.

Figure 7-1 An Application Window

 

Manipulating Tree List Objects

With the tree list, you can:

• Click on the '+' to the left of an icon to expand and display the contents of a folder. You can also double-click on a collapsed folder icon or name.
• Click on the '-' to the left of an icon to collapse a folder. You can also double-click on an expanded folder icon or name.

Suggestion: For the fastest performance when expanding or collapsing folders in the tree list, click only on the '+' or '-' to the left of the folder rather than double-clicking an object name. If you select an object in the tree list, the application pauses to refresh the contents of the window on the right side before expanding or collapsing the folder.

• Select the Collapse and Expand menu options in the View menu to collapse and expand folders.
• Click on a group name, such as Users or Tablespaces, to display a multi-column scrolling list of summary information on the objects in the folder in the right side of the application window.

An example of a list is shown in Figure 7-1, "An Application Window" . For information about multi-column scrolling lists, see Multi-Column Lists on page 7-14 .

• Click on a composite instance, such as the SYSTEM tablespace or the user SCOTT, to display the associated property sheet for the object. Menu commands are also enabled for the group you selected.

For information about property sheets, see Property Sheets on page 7-17.

Attention:

If you alter an instance, such as a datafile named DATA1 or a role named CLERK, in any location of the tree list, all occurrences of the object in the tree are changed.

• Drag and drop an object onto a folder to assign the object to the folder. For example, you can drag and drop a role named CLERK onto the user named SCOTT to grant the CLERK role to SCOTT.

Backup Manager

Backup Manager allows you to administer your backup and recovery environment. You can now select from three backup subsystems:

• Oracle8 Recovery Manager: Provides an intuitive graphical user interface for Recovery Manager, an extremely powerful and flexible command line backup and recover utility designed expressly for the Oracle8 database. Wizards simplify backup, recovery, and report operations.
• Operating System Backup: Provides automated tablespace backup and recovery using the Oracle Enterprise Manager job system (Oracle7 databases). Operating System Backup also provides limited backup and recover capability for Oracle8 databases.
• Enterprise Backup Utility: Installation and Configuration: Provides backup and recovery functionality for Oracle7 databases only. See your Enterprise Backup Utility documentation for further information. Note: Enterprise Backup Utility is not compatible with Oracle running on Windows NT systems.

Data Manager

Using an intuitive wizard to guide you, Oracle Data Manager supports the Export, Import, and Load utilities.

The Export utility allows you to export data from an Oracle database. The exported data is written to an operating system file in Oracle-binary format and can only be read by the Import utility. Export files can be used to transfer data between databases or used as backups. The Export utility provides dialog boxes that allow you to define how the data is exported.

The Import utility allows you to import data to an Oracle database. Import can only read data that has been created with the Export utility. The Import utility provides dialog boxes that allow you to define how the data is imported.

The Load utility allows you to transfer data to an Oracle database from files, such as text files, that have not been exported from an Oracle database. By using a control file you specify how the data is stored externally and how it is to be loaded into the database.

Instance Manager

With the Instance application you can start up or shut down a database, place the database in ARCHIVELOG mode or NOARCHIVELOG mode, examine and edit database initialization parameters, manage in-doubt transactions, and manage users' sessions. The Instance application manages the following groups:

• Initialization Parameters
• In-doubt Transactions
• Stored Configurations
• Sessions

Note:

If you have SYSOPER privileges, you can only startup and shutdown the database from the Console using the context-sensitive menu.

Schema Manager

With the Schema application you can create, edit, and examine schema objects. The Schema application manages the following groups:

• Array Types
• Clusters
• Database Links
• Functions and Procedures
• Indexes
• Object Types
• Packages and Package Bodies
• Refresh Groups
• Queue Tables
• Sequences
• Snapshots and Snapshot Logs
• Synonyms
• Tables
• Table Types
• Triggers
• Views

Security Manager

With the Security application you can create, alter, and drop users, roles, and profiles. The Security application manages the following groups:

• Users
• Roles
• Profiles

Software Manager

Oracle Software Manager provides key services that allow all software to be managed from a central location. This product addresses the problems faced by application software administrators by offering:

• An integrated toolset for managing software in a distributed fashion
• A next generation architecture that offers network scalability through an advanced client-agent-server paradigm
• An easy-to-use graphical user interface that allows administrators to easily run software configuration jobs and track assets across the entire network

Storage Manager

With the Storage Manager you can perform administrative tasks associated with managing database storage. These tasks include managing tablespaces and rollback segments, and adding and renaming datafiles. The Storage Manager manages the following groups:

• Tablespaces
• Datafiles
• Rollback Segments

SQL Worksheet

A SQL Worksheet allows you to dynamically enter SQL statements, PL/SQL code, and Server Manager DBA commands. You can also run scripts from a SQL Worksheet.

A SQL Worksheet maintains a history of the commands you have entered. Thus, you can use the SQL Worksheet to edit and re-execute an earlier command without having to retype it.

You can have multiple SQL Worksheets open at a time, each of which is separate from the others. So, you can commit or roll back work in each worksheet independently.

Application Menus

Database tools and utilities applications include four main pull-down menus in the menu bar:

• File
• View
• Log
• Help

The applications include other menus that are specific to the groups that the application manages. For example, the Security Manager provides the User, Profile, and Role menus in addition to the standard menus.

When a menu item is dimmed, it is not currently available. Menu items that end with an ellipsis (...) indicate that you will be asked to enter more information to complete the command.

File Menu

The File menu items allow you to open or close application windows and create new connections. The File menu includes:

Change Database Connection

Displays the Connect dialog box to connect to an instance and open a window for that database. See Connecting to an Instance on page 1-23. To connect to multiple databases concurrently, you need to launch the application multiple times from the console.

Enable Roles

Allows you to select a role from the multi-column list of available roles.

Exit

Exits the application.

View Menu

The View menu allows you to show or hide objects, such as the toolbar or status line, and expand, collapse, or refresh the tree list.

Refresh

Refreshes the tree list. This command is useful when other database administrators are making changes to the objects you are viewing, or when you are making changes in a different window, such as a SQL Worksheet.

Filter Folder

Filters the tree list based on criteria you choose. This allows you to display only the objects that match the criteria.

Expand One Level

Opens and displays the next level of items contained in the selected objects in the tree.

Expanding a folder can be delayed because the objects displayed in the listing are often queries on remote databases.

Collapse Branch

Closes the selected folder in the tree list.

Collapse All

Closes all open folders in the tree list.

Toolbar

Shows or hides the toolbar.

Status bar

Shows or hides the status bar.

Basic/Advanced Mode

Allows you to choose between basic property sheet functionality (Basic Mode) or complete property sheet functionality (Advanced Mode). By default, the last chosen mode is selected.

Log Menu

The Log menu options allow you to record and save the SQL statements that you have executed. When you save the statements to a file, you can also choose to schedule a job using the file on multiple databases. This feature provides the ability to perform identical actions on multiple databases without having to enter each SQL statement over again. If the databases are not identical, you can simply edit the SQL statements in the file before running the file on a database.

Record

Start recording the SQL statements that are executed through the application.

Stop

Stop recording SQL statements. A dialog displays for you to save the recorded statements in a file. Enter a filename. Schedule a job that would run the file of SQL statements.

Pause

Pause the recording of statements while you execute actions that you do not want recorded.

Help Menu

From the Help menu, you can access the Oracle Enterprise Manager online Help system. For more information about Oracle Enterprise Manager online Help system, see Online Help on page 1-8.

Contents

Displays the overview page for Oracle Enterprise Manager help.

Search for help on

Displays a search dialog box. You can either search by index or key word.

Using Help

Displays a dialog box containing information about the Help system. See Online Help on page 1-8 for information about the Help dialog box.

About Application

Displays a dialog box containing version information about the application.

Additional Menus

Most database tool and utility applications include pull-down menus that contain the commands available for specific groups. The menu titles usually match the names of the groups. For example, the Security application provides the User, Profile, and Role menus.

Some of the menu commands, such as Drop, require that you select an individual object first. These commands are unavailable until you select an appropriate object. Unavailable commands are represented by dimmed menu items. Other menu items, such as Create, do not require an object selection and are never dimmed.

A menu item can be dimmed even when an object is selected because the command does not apply to that particular object. For example, you cannot drop an object type group, such as the Users group.

Some menu items are followed by an ellipsis (...). This indicates that to complete the command, the application prompts you for more information with a dialog box or property sheet.

Context-Sensitive Menus

Context-sensitive menus may also be provided when you select a tree list object or item in the multi-column list with the right mouse button. This feature provides quick access to a subset of the menu options provided in the menu bar. Normally, objects in an application tree list (or the console Navigator) and objects in a multi-column list have similar context-sensitive menu options.

For detailed information about the application menus, see the chapters on the specific applications.

Multi-Column Lists

A multi-column list displays when a group folder, such as a Users group, is selected. Each row in the list provides a quick view of summary information about an object in the selected folder. Additionally, you can select and alter items through their respective property sheets,

Note:

If an object has a property sheet, display the property sheet if you want more detailed information. See Property Sheets on page 7-17 for information.

The location of a group folder determines the columns that are displayed in the list. For example, the Roles group that is a main branch of the database folder displays different columns than a Roles group under a user.

By default, the objects are sorted on the first column. You can sort a list on any column by clicking on the column heading. The column heading appears in bold to indicate that the list is sorted on that column.

If the list is longer than the its window display area, you can use the vertical scroll bar at the side of list to scroll to the first or last row. You can also resize the application window to make the window display area taller.

If the list is wider than its window display area, you can use the horizontal scroll bar at the bottom of the list to scroll to the first or last column. You can also resize the application window or drag the splitter bar in the window to widen the right side of the window that contains the multi-column list.

An example of a multi-column list is shown in Figure 7-2, "Multi-column List" .

Figure 7-2 Multi-column List

 

Dialog Boxes

Dialog boxes allow you to make selections, enter information, or confirm actions.

You exit a dialog box by clicking a button. In many cases, you first enter field values, or choose options from pop-up menus or buttons, before you exit. An example of a dialog box is shown in Figure 7-3, "Dialog Box".

Figure 7-3 Dialog Box

 

The boxes generally contain one or more of the following command buttons:

OK

Executes the action button that was specified and closes the dialog box. The name of this button may vary.

Cancel

Cancels any changes made and closes the dialog box.

Apply

Implements any changes made to the dialog box.

SQL

Opens or closes the SQL window to show or hide the SQL statements that are used to execute commands.

Help

Displays online Help about the dialog box.

Property Sheets

A property sheet is a dialog box used to specify options when you are creating or altering a composite instance, such as a user or tablespace. A property sheet consists of one or more pages that are labeled with page tabs. By default, one page is open when the sheet is entered, but all pages show page tabs. To access another page, click its page tab.

Property sheets can be displayed on one of two modes: Basic and Advanced. When in Basic mode, a property sheet displays only those pages that are required for basic functionality. Advanced mode displays additional pages that go beyond basic functionality for a particular function. When you close an application, the current mode is saved.

When a composite instance is selected in the tree list on the left side of an application window, the Alter property sheet that is associated with the object is usually displayed on the right side of the window. The properties available for each sheet are specific to the group type. An example of an Alter property sheet for a user is shown in Figure 7-4, "Property Sheet".

Figure 7-4 Property Sheet

 

Spreadsheets

Some property sheets, particularly those requiring large amounts of data, use spreadsheets for simplified data entry. Spreadsheets consist of a table in which data is typed directly into rows and categorized by columns. Some entry fields in the spreadsheet have predefined content and are toggled by clicking on the table cell with the left mouse button (e.g. setting the Admin Option on or off by clicking directly on the cell).

For most spreadsheets, context-sensitive menus are available. You first select a single row or spreadsheet cell with the left mouse button and then click the right mouse button to call up a context-sensitive menu that is appropriate for a particular application. In some cases, a spreadsheet context-sensitive menu, unlike those available from the tree list or multi-column list, may not be a subset of the menu options available in the application's menu bar. In such cases, the context-sensitive menu may consist of operations that are specific to a particular spreadsheet or spreadsheet cell.

Newly added rows are indicated by a plus '+' sign in the far left column of the spreadsheet until you click Apply.

Property Sheet Buttons

The property sheets generally contain the following command buttons:

Apply/Create

Implements an action specified in the property sheet. Apply would save any changes made to the Quick Edit property sheet and Create would create a new the object specified by the Create property sheet.

Revert

Cancels any changes made to the property sheet and resets the fields to previously saved values.

SQL

Opens or closes the SQL window to show or hide the SQL statements that are used to execute commands.

Help

Displays online Help about the property sheet.

Starting an Application

To start an application, click its icon in the Launch Palette or select its name from the Launch menu in the Oracle Enterprise Manager Console.

• If you have already connected to a database in the Oracle Enterprise Manager Console, the application window displays.
• If a valid database connection has not already been made, the Connect dialog box displays. See Connecting to an Instance on page 1-23.

Note:

You can change the database connection with the Change Database Connection option in the application File menu.

After a successful connection to a database, the tree list displays a tree list of objects managed by the application. The display on the right side of the window is determined by the object selected in the tree list. A multi-column list, property sheet, or other information displays.

For example, after the Security application completes a connection to a database, the Users, Profiles, and Roles groups display under the database folder in a tree list on the left side of the Security window. The Users, Profiles, and Roles groups can be expanded to show the objects they contain. An example of an application window with an expanded tree list is shown in Figure 7-1, "An Application Window".

---