Home > SQL Developer Concepts and ... > Using DBA Features in SQL D...
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:
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. The pane may be read-only or editable.
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 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 Export (unload 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).
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
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
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
(Available only for Release 12c connections.) Includes options for managing a Multitenant Container Database (CDB) and the pluggable databases (PDBs) within it.
Related Topics
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 theDBMS_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:
AWR Report Viewer: Generates a Workload Repository Report for a baseline or a snapshot range.
Difference Report Viewer: Generates a Workload Repository Compare Period Report for two baselines or snapshot ranges.
SQL Report Viewer: Generates a Workload Repository SQL Report for a baseline or a snapshot range for a specific SQL statement.
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
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.
Create Backup Wizard (on context menu) Displays a wizard where you specify Backup Properties (type of backup and whether to use RMAN encryption), and Script Properties (Save to File and where to save the script file, or Run Scheduler Job and specify a credential or create a new one).
For Create New Credential, specify the credential name, and the user name and user password on the server system.
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.
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
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
(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
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.
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
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