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

Container Database

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

Related Topics

Using DBA Features in SQL Developer

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

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 and edit 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