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.)

If no connections appear in the DBA navigator display, you must add at least one connection. To add a connection to the DBA navigator, click the plus (+) icon or right-click Connections and select Add Connection; then select a connection. (You can also specify a new connection by clicking the plus (+) icon in the Select Connection dialog box.)

You should add only connections for which the associated database user has DBA privileges (or at least privileges for the desired DBA navigator operations) on the specified database. If you attempt to perform a DBA navigator operation for which the database connection user does not have sufficient privileges, you will receive an error.

The following options are available from the DBA navigator hierarchy:

Connections
  <connection-name>
    Database Configuration
      Initialization Parameters
      Automatic Undo Management
      Current Database Properties
      Restore Points
      View Database Feature Usage
    Database Status
      Status
    Data Pump
      Export Jobs
      Import Jobs
    Multitenant Container Database
    Performance
      Snapshots
      Baselines
      Baseline Templates
      Automatic Database Diagnostic Monitor (ADDM)
      ASH (Active Session History) Report Viewer
      AWR (Automatic Workload Repository)
    RMAN Backup/Recovery
      Backup Jobs
      Backup Sets
      Image Copies
      RMAN Settings
      Scheduled RMAN Actions
    Resource Manager
      Consumer Group Mappings
      Consumer Groups
      Plans
      Settings
      Statistics
    SQL Translator FrameWork
      SQL Translation Profiles
      SQL Translators
    Scheduler
      Global Attributes
      Job Classes
      External Destinations
    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:

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

With information displays that are in grid form:

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).

Database Configuration

Includes the following options related to database configuration management.

Initialization Parameters

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

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.

Current Database Properties

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

Restore Points

Displays restore points that can be used for recovery; lets you create and delete restore points. A restore point is a name associated with a timestamp or an SCN of the database. A restore point can be used to flash back a table or the database to the time specified by the restore point without the need to determine the SCN or timestamp. Restore points are also useful in various RMAN operations, including backups and database duplication.

View Database Feature Usage

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

Related Topics

Using DBA Features in SQL Developer

Database Status

Includes options for displaying status information about the database.

Status

Includes tabs for displaying information about the Database Status, Oracle Host, Oracle Home, and TNS Listener.

Related Topics

Using DBA Features in SQL Developer

Data Pump

Includes options for using the Oracle Data Pump Export and Import utilities, which are described in detail in Oracle Database Utilities.

Export Jobs

Displays any Data Pump Export jobs. You can right-click and select Data Pump Export Wizard to create a Data Pump Export job.

Import Jobs

Displays any Data Pump Import jobs. You can right-click and select Data Pump Import Wizard to create a Data Pump Import job.

Related Topics

Using DBA Features in SQL Developer

Multitenant Container Database

(Available only for Release 12c connections.) Includes options for managing a Multitenant Container Database (CDB) and the pluggable databases (PDBs) within it.

Related Topics

Using DBA Features in SQL Developer

Performance

Includes options to create objects and view reports related to the use of database statistics. Oracle Database automatically persists the cumulative and delta values for most of the statistics at all levels (except the session level) in the Automatic Workload Repository (AWR). This process is repeated on a regular time period and the results are captured in an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period.

A statistical baseline is a collection of statistic rates usually taken over a time period when the system is performing well at an optimal level. Use statistical baselines to diagnose performance problems by comparing statistics captured in a baseline to those captured during a period of poor performance. This enables you to identify specific statistics that may have increased significantly and could be the cause of the problem. AWR supports the capture of baseline data by enabling you to specify and preserve a pair or range of AWR snapshots as a baseline.

For more information, see the chapter about gathering database statistics in Oracle Database Performance Tuning Guide.


Note:

Most Performance operations are performed internally by SQL Developer through calls to subprograms of the DBMS_WORKLOAD_REPOSITORY package (such as CREATE_SNAPSHOT, CREATE_BASELINE, CREATE_BASELINE_TEMPLATE, and several with names ending in _REPORT_HTML). These are documented in Oracle Database PL/SQL Packages and Types Reference.

Snapshots

Snapshots are sets of historical data for specific time periods that are used for performance comparisons by Automatic Database Diagnostic Monitor (ADDM). By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in AWR for 8 days. You can also manually create snapshots or change the snapshot retention period, but it is usually not necessary.

AWR compares the difference between snapshots to determine which SQL statements to capture based on the effect on the system load. This reduces the number of SQL statements that must be captured over time. After the snapshots are created, ADDM analyzes the data captured in the snapshots to perform its performance analysis.

Baselines

A baseline is a set of snapshots from a specific time period that is preserved for comparison with other snapshots when a performance problem occurs. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

Baseline Templates

Baseline templates enable you to create baselines for a contiguous time period in the future. There are two types of baseline templates: Single (for a single contiguous time period) and Repeating (based on a repeating time schedule; useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis).

Automatic Database Diagnostic Monitor (ADDM)

ADDM is an advisor that analyzes Automatic Workload Repository data on a regular basis, diagnoses the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system. ADDM is described it the chapter about automatic performance diagnostics in Oracle Database Performance Tuning Guide

ASH (Active Session History) Report Viewer

ASH enables you to examine and perform detailed analysis on sampled activity of active sessions. To facilitate the identification of transient performance problems, Oracle Database samples active sessions every second. By capturing only active sessions, a manageable set of data is represented with its size being directly related to the work being performed, rather than the number of sessions allowed on the system. For more information about ASH, see the chapter about analyzing sampled data in Oracle Database Performance Tuning Guide.

To view the report, specify the desired options (From Time, To Time, Filters) and click the Generate Report icon.

AWR (Automatic Workload Repository)

An AWR report shows data captured between two snapshots (or two points in time). AWR reports are divided into multiple sections. The content of the report contains the workload profile of the system for the selected range of snapshots. The HTML report includes links that can be used to navigate quickly between sections. The following types of AWR reports are available:

For more information, see the section about generating Automatic Workload Repository reports in Oracle Database Performance Tuning Guide.

To view a report, specify the desired options and click the Generate Report icon.

Related Topics

Using DBA Features in SQL Developer

RMAN Backup/Recovery

Includes options related to database backup and recovery. The options use the Oracle Database Recovery Manager (RMAN) feature, which is described in detail in Oracle Database Backup and Recovery User's Guide. You should be familiar with RMAN concepts and techniques before using these options.

In any dialog box or wizard for RMAN operations, you can click the SQL or Summary tab to see the statements that will be used to implement the specified options.

Backup Jobs

Displays the backup jobs that have been previously run; lets you create and run new backup. (Note that backup jobs are distinct from action jobs.)

Backup Sets

Displays the backup sets that have been created by previous backup jobs and that can be used for recovery.

Image Copies

Displays the image copies that have been created by previous backup jobs and that can be used for recovery.

RMAN Settings

Displays settings for backup and recovery. (These settings are stored in the server and are used and managed by RMAN.)

Scheduled RMAN Actions

For Oracle Database 11.1 and later connections: Displays DBMS_SCHEDULER jobs that have been used to execute RMAN scripts; lets you view log files. For more information, see Using Action Jobs.

Using Action Jobs

Action jobs are applicable to connections to an Oracle Database Release 11.1 or later database.

Most backup and recovery actions involve RMAN scripts. The action jobs dialog boxes let you save the generated RMAN to a disk; you can then copy the script to the server system and run the script there.

Before you can execute the script for an action job, you must create a DBMS_SCHEDULER credential by running a procedure in the following format:

BEGIN
    DBMS_SCHEDULER.CREATE_CREDENTIAL(
        username => 'user-name',
        password => 'password',
        database_role => NULL,
        windows_domain => NULL,
        comments => NULL,
        credential_name => 'credential-name'
    );
END;
/

Then, run the Action Jobs, Configure action. This action places some scripts in the server-side database home (in <home>/sqldeveloper/dbascripts) and creates some DBMS_SCHEDULER program objects in the database. (If you want to unconfigure an action job, you can use the Action Jobs, Unconfigure action, which removes the server-side directory containing the script and log files and drops the DBMS_SCHEDULER program objects.)

After you perform the configuration, the Run Scheduler Job action of the Script Processing control becomes available in the RMAN dialog boxes, and you can click Apply to cause the RMAN script to be executed in the server using a DBMS_SCHEDULER job.

After an RMAN job has been run, you can view the log file containing the output from RMAN by using the Action Jobs, View Latest Log action. This lets you check for any errors that may have occurred during the running of the RMAN script.

Some RMAN jobs involve performing a database restart. Examples are setting the archive log mode and some whole database backup and restore operations. In such cases, after you click Apply you are asked to confirm that you want to proceed; and if you do proceed, the job is queued (with no waiting for the completion). Because of the restart, the SQL Developer connection must be disconnected and then connected again after the database is restarted. After the reconnection, examine the log file to see whether the job completed successfully.

Related Topics

Using DBA Features in SQL Developer

Resource Manager

Includes the following options related to database resource management.

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.

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.

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).

Related Topics

Using DBA Features in SQL Developer

SQL Translator FrameWork

(Available only for Release 12c connections.) Includes options for creating and managing SQL translation profiles and SQL translators.

SQL Translation Profiles

A SQL translation profile is a database schema object that directs how non-Oracle SQL statements are translated into Oracle SQL dialects.

SQL Translators

A SQL translator is a stored procedure that translates non-Oracle SQL statements into Oracle SQL dialects automatically. After a SQL translation profile is created, users can register a SQL translator with the translation profile to translate the non-Oracle SQL statements.

For reference and usage information about creating SQL translators and SQL translation profiles, see the Install SQL Translator dialog box material.

Related Topics

Using DBA Features in SQL Developer

Scheduler

Includes the following options related to Scheduling Jobs Using SQL Developer. (The objects under Scheduler in the DBA navigator are for objects that owned by the SYS user and that can be created and modified only by users with DBA privileges. Other objects are listed under Scheduler for users in the Connections navigator.)

Global Attributes

The Global Attributes display lets you view and edit attributes such as the default time zone, the email sender and server, event expiry time, log history retention, and maximum job slave processes.

Job Classes

The Job Classes display lets you view and edit information about Job Classes. The information for each job class includes the job class name, logging level, log history, resource consumer group, service, and comments.

External Destinations

The External Destinations display lets you view information about external destinations for jobs.

Security

Includes the following options related to database security management. For profiles, roles, and users, you can perform relevant operations, such as creating new objects of that type and editing and dropping existing objects.

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 activities and resource usage for each profile.

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.

Related Topics

Using DBA Features in SQL Developer

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.

Related Topics

Using DBA Features in SQL Developer