Using DBA Features in SQL Developer

SQL Developer enables users with DBA (database administrator) privileges to view (and in some cases edit) certain information relevant to DBAs and, in some cases, to perform DBA operations. To perform DBA operations, use the DBA navigator, which is similar to the Connections navigator in that it has nodes for all defined database connections. (If the DBA navigator is not visible, select View, then DBA.)

The following options are available from the DBA navigator hierarchy:

Connections

<connection-name> Database Configuration

View Database Feature Usage

Initialization Parameters

Current Database Properties

Automatic Undo Management

Resource Manager

Consumer Groups

Consumer Group Mappings

Plans Settings Statistics Security Audit Settings Profiles Roles Users Storage Archive Logs Control Files Datafiles

Redo Log Groups

Rollback Segments

Tablespaces

Temporary Tablespace Groups

To perform limited database management operations, you can right-click the connection name in the DBA navigator display and select Manage Database. For example, if a listener is running with a static listener configured for the database, you can start and stop the database, force database startup, and restrict access to the database.

You can right-click an item (node) at any level in the DBA navigator hierarchy to display a context menu with commands relevant to that item. Typical commands include the following:

■ Refresh queries the database for the current details about the selected object (for

example, a connection or just a table).

■ Apply Filter restricts the display of objects using a filter that you specify. To

remove the effects of applying a filter, right-click the node and select Clear Filter.

■ Open displays a pane with relevant information on the right side of the window.

■ Create New enables you to create a new object of that type.

■ Help displays a brief definition or description of objects of that type.

The pane with information about an item opened from the DBA navigator typically contains icons and other controls for the following:

■ Freeze View (the pin) keeps that object’s tab and information in the window when

you click another object in the Connections navigator; a separate tab and display are created for that other object. If you click the pin again, the object’s display is available for reuse.

■ Run updates the display by querying the database for the latest information. ■ Refresh lets you select an interval for automatically updating the display. ■ Actions displays a menu with actions appropriate for the object. The actions are

the same as when you right-click an object of that type in the Connections navigator.

With information displays that are in grid form:

■ You can often use the context (right-click) menu on a header or a data cell to

perform operations like those for the table data grid, as explained in Section 1.5, "Entering and Modifying Data". For example, right-clicking on a header lets you select options like Auto-fit, Columns, Sort, Delete Persisted Settings (such as any sort order specifications), and Filter Column; right-clicking any data cell lets you select options like Save Grid as Report, Single-Record View, Count Rows, Find/Highlight (find and optionally highlight values in the grid), and Unload (export data).

■ You can click the column heading to display a Filter box to restrict the display to

entries containing a string. For example, in the Database Configuration: Current Database Properties page, you can click PROPERTY_NAME and type NLS to see only properties containing "NLS" (that is, globalization properties).

For information that is read-only (not editable) using the DBA navigator, you may have other options within SQL Developer to specify relevant values. For example, to change the value of any globalization support (NLS) parameter, you can use the

Database: NLS preferences pane to change the value for use with all SQL Developer connections (current and future), or you can use the ALTER SESSION statement in the SQL Worksheet window to change the value for the current connection only.

Detailed explanations of various DBA options, including usage and reference

information, are available in appropriate manuals in the Oracle Database Documentation

Library on the Oracle Technology Network (OTN).

1.11.1 Database Configuration

Includes the following options related to database configuration management.

View Database Feature Usage

Displays database features and the number of detected usages for each.

Initialization Parameters

For each database initialization parameter, displays the name, value (current value), default value, description, and other information. You can modify the value of one or more parameters.

Current Database Properties

For each current database initialization parameter, displays the name, value (current value), and description.

Automatic Undo Management

Automatic undo management is a mode of the database in which undo data is stored in a dedicated undo tablespace. The only undo management that you must perform is the creation of the undo tablespace; all other undo management is performed

automatically. The Automatic Undo Management option displays information about automatic undo management and any recommendations relating to its use. You can change the Retention value.

1.11.2 Resource Manager

Includes the following options related to database resource management.

Consumer Groups

A resource consumer group is a group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource

consumer groups, not to individual sessions. The Consumer Groups option displays, for each consumer group, its description and whether it is mandatory.

Consumer Group Mappings

A consumer group mapping specifies mapping rules that enable the Resource Manager to automatically assign each session to a consumer group upon session startup, based upon session attributes. The Consumer Group Mappings option displays, for each attribute, its priority, value, and associated consumer group.

Plans

A resource plan is a container for directives that specify how resources are allocated to resource consumer group; you specify how the database allocates resources by

activating a specific resource plan. The Plans option displays, for each plan, its description and if its status is Active.

Settings

Lists any active resource plans.

Statistics

Lists various resource-related statistics (if applicable).

1.11.3 Security

Includes the following options related to database security management.

Audit Settings

The Audit Settings display includes the audit trail setting, whether SYS user operations are audited, and the directory or folder for the audit file.

Profiles

A profile is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.The Profiles option displays any limits on

Roles

A role is a set of privileges that can be granted to users or to other roles; you can use roles to administer database privileges. The Roles option displays the roles and their authentication settings.

Users

A database user is an account through which you can connect to the database. The Users option displays status and usage information about each database user.

1.11.4 Storage

Includes the following options related to database storage management.

Archive Logs

An archived redo log is a copy of one of the filled members of an online redo log group made when the database is in ARCHIVELOG mode. After the LGWR process fills each online redo log with redo records, the archiver process copies the log to one or more redo log archiving destinations; this copy is the archived redo log.

Control Files

A control file is a binary file that records the physical structure of a database and contains the names and locations of redo log files, the time stamp of the database creation, the current log sequence number, checkpoint information, and so on. The Control Files option displays, for each control file, its status, file name, and file directory.

Datafiles

A data file is a physical file on disk that was created by Oracle Database and contains the data for a database. The data files can be located either in an operating system file system or Oracle ASM disk group. The Datafiles option displays, for each data file, its file name, tablespace, status, and other information.

Redo Log Groups

A redo log group contains one or more members: each online redo log member (which corresponds to an online redo log file) belongs to a redo log group. The contents of all members of a redo log group are identical.The Redo Log Groups option displays, for each redo log group, its status, number of members, and other information.

Rollback Segments

A rollback segment records the before-images of changes to the database. The Rollback Segments option displays, for each rollback segment, its name, status, tablespace, and other information.

Tablespaces

A tablespace is a database storage unit that groups related logical structures together. The database data files are stored in tablespaces. The Tablespaces option displays, for each tablespace, its name; megabytes allocated, free, and used; and other information.

Temporary Tablespace Groups

A temporary tablespace group is a tablespace group that is assigned as the default temporary tablespace for the database. (A tablespace group enables a database user to consume temporary space from multiple tablespaces. Using a tablespace group, rather

than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions.) The Temporary Tablespace Groups option displays, for each

tablespace group, its name, the number of tablespaces in the group, the total size of the tablespaces, and whether the group is the default temporary tablespace.