In addition to browsing objects and writing your own ad hoc queries against the database, Oracle SQL Developer has a separate Reports navigator with a set of predefined SQL queries known as reports. The main focus is the set of predefined data dictionary reports that range from providing basic details about your database to database administration, security, and quality assurance. Having these predefined reports means that you can quickly run a variety of SQL queries to analyze and assess the health and status of your database, saving you from needing to write the queries yourself.
The Reports navigator provides shipped reports to review the results of any non-Oracle database migrations that have been run, the details of any Database Data Dictionary and Data Modeler designs that have been exported, and any available Oracle Application Express applications. The output of any of these reports is controlled by the database connection used while executing a report.
You can extend the set of shipped, predefined reports by adding your own local or shared reports. While creating your own reports, not only can you add to the existing data dictionary reports, but you can also build up a set of reports to review and analyze the instance data in any application under development.
In this chapter, we will review the Reports navigator entirely. You will learn more about the shipped reports and how to run them. We'll look briefly at the Migration, Oracle APEX, and Data Modeler reports. These are also covered in the chapters on each of these topics. We'll review the various report styles available and show how you can make use of these different styles while creating your own reports.
Whether you are a DBA or an application developer, running SQL queries against the data dictionary is a useful source of information. It provides you with details about the objects various schemas own, the health of a system, and the integrity of the data in the applications. If you work with the Oracle database regularly, you almost certainly already have a set of queries that you frequently run. For example, you might have a query to determine the tables that have no Primary Keys or, if you are a DBA, you might have a query to find out the currently connected sessions. Over the years, you may gather these queries in a file and have them at hand no matter what project you are working on, and they can play a significant role in your daily tasks.
SQL Developer has a categorized group of these SQL queries (reports) and it also provides the tools to create and save your essential queries as reports.
In this first section, we'll discuss the Reports navigator in general, how to run and use the reports, and any errors you might encounter.
To run any report in SQL Developer, you need a connection to the database.
Anyone can run or create reports in SQL Developer, from the least to the most privileged user. What you see depends on your security access, which is true throughout SQL Developer. It is not the product that enforces security, but the database itself. So, if you are presented with an error while running a report, consider the privileges you have and whether you'd be able to run this query in any other environment or circumstance, such as in SQL*Plus or the SQL Worksheet. Invariably, the error message displayed does explain the problem at hand and more often than not, it is a security level access issue.
You can use the reports on a daily basis throughout a project to track the details about your system. Reports provide high-level details about your database and can be used to drill down to the finest detail.
The following screenshot shows the broad categories of the shipped reports that are available:
Use the shipped reports to determine the details about your system, which include:
- Finding the version and parameters' details of the database that you are connected to
- Learning more about the data dictionary
- Reviewing the tables and related details for a particular schema
- Finding the objects in a schema
- Reviewing the valid and invalid PL/SQL in a system
- Searching for PL/SQL text
To run a report, expand any of the nodes in the Reports navigator and select the report. The first time you select a report, the Select Connection dialog will be invoked to offer a choice of connections, as shown in the following screenshot:
Some reports are parameter-driven as shown in the following screenshot:
Most users can successfully run most of the shipped reports, needing only the database connection to start. Even users with the most limited connection privileges can run many of the reports, such as those in the About Your Database category. However, they will have limited or no access to the reports in the Database Administration category, as SYSTEM or DBA privileges are required to run these.
If you do not have access to the underlying tables being queried, you will get the standard Oracle error message, "table or view does not exist". This is a common security access error message and you will need to switch to another more privileged user to run the report.
In the same way, you also need access to the underlying tables in a project to run user-defined report, where you might create a report for the instance data.
The first major category of the reports shipped with SQL Developer is the Data Dictionary Reports node. These are, by definition, about the data dictionary, and therefore, do not include any instance data reports, which you can define in the User Defined Reports section.
Once you have learned to write a select statement, getting to know the data dictionary is an important and useful progression to make. This is the data dictionary that holds the metadata about your database. Knowing how to query the data dictionary means that you can determine which dictionary view holds the information about the tables, columns, constraints, and privileges you can access. Therefore, you can find details about the project or the application that you are working on. As mentioned earlier, many people who are familiar with the data dictionary have SQL scripts and queries, which they frequently run. This is exactly what the shipped reports provide.
We have said that SQL Developer provides a long list of reports that query the data dictionary, but these do not cover every possibility. Starting with the two reports under the Data Dictionary node will help you become familiar with this environment.
If the reports SQL Developer provides are sufficient for all your query purposes, then you may never use them. However, if you want to know something about your database that is not available through any report provided, then you can start with these two dictionary views and build a query based on your findings.
For example, SQL Developer does not provide any report on dimensions. If you want to learn about any dimension you have, you can write an SQL query, but you first need to know which dictionary views to query. This is where the Data Dictionary | Dictionary Views report can help. Select the report and run it. Provide the bind variable input parameter "dimension" when prompted. You do not need to add any wild cards (%), as the report does this for you.
The report returns the set of records that relate to dimensions, listing the view names and the descriptions about the views. Once you have determined which view to use, use the Data Dictionary | Dictionary View Columns report (as shown in the following screenshot) to determine the columns in the view. You can now use the detail to write the query in the SQL Worksheet.
Both the reports are shown in the previous screenshots, illustrating the detail you need to be able to write your own query.
If you work with a number of different databases, then there are a few reports that are very useful to determine the version of the database you're connected to and the database parameters that are set. These reports fall under two categories:
- About Your Database
- Database Parameters—found in the Database Administration section
In the example that we have just seen, the All Parameters report has been run with a bind variable of "cursors" to list all of the parameters related to cursors. By using the connections drop-down list on the right-hand side, you can switch between database connections and compare these cursor parameters between databases.
SQL Developer provides a number of reports that are related to security issues, including the Auditing and Encryption reports. If you are new to the database and are getting to know your environment, you are more likely to frequently run a group of security-related reports in the Grants and Privileges folder. If you are unable to access certain objects or find that you have no ability to create, edit, or update objects, then reviewing the Role Privileges and System Privileges reports is a good place to start.
In the example shown in the previous screenshot, we have selected the Object Grants report for HR. Here, we see that the HR schema has an EXECUTE privilege on the DBMS_STATS procedure owned by the SYS user.
The selection of Quality Assurance reports in the Table node is useful while working on a project or application development. This is not a comprehensive list (as shown in the following screenshot), but a good indication of the type of reports you might create to keep a check on the status of an application:
It's also worth noting that these are just reports and not suggestions of the best practice. While it is advisable to have indexes on your tables, there may be a good reason why some tables are not indexed. For example, if you were adding bulk sets of data, you would want to add the indexes after the fact. Having the report indicates where there are gaps. You'll need to make the decisions based on the results.
There are a few reports in the PLSQL node. Possibly, the most useful one is the Search Source Code report. SQL Developer provides a number of search facilities, not just in the reports area, which help you find:
- Parameters in the PL/SQL code
- Objects in a schema
- Words and text in the current editor
If you use a command-line tool, such as SQL*Plus to access the database, then you will need to write an SQL query to search for specific strings in the SQL or PL/SQL code. The PLSQL | Search Source Code report allows you to look for either the PL/SQL object by name, or a string or piece of text in the code, as shown in the following screenshot:
Once you have found the source code, you can move to the code using the context menu on the object name, or by double-clicking on the object name.
There are a growing number of Oracle Active Session History (ASH) and Automatic Workload Repository (AWR) reports provided by SQL Developer. AWR captures workload-related performance data at user and system levels. ASH provides the history of the activities in the recent active sessions. The following report is a chart of Daily ASH Statistics:
These reports are database-specific and require the Oracle Diagnostics Pack to be licensed for the databases you run the reports against. You are warned before you run these reports that the underlying reports require the license.
SQL Developer provides a selection of reports that are useful if you are involved in migrations from non-Oracle databases. As for the other reports, if you do not have access to the underlying structures, the reports will deny you access. You'd typically run these reports as the migrations repository owner or as a migrations user with similar privileges.
SQL Developer provides a selection of Application Express reports. These are listed in the Application Express node under Data Dictionary Reports. These reports provide details about the applications you have access to. The Applications and Pages reports provide the same details that are available in the Connection navigator. For more information on this and all the Application Express support in SQL Developer, see the chapter on Chapter 11, Working with Application Express.
A new category of reports was added in SQL Developer 2.1. This is the Data Modeler Reports node. The tables that these reports run against are first created and populated when you export your design from Oracle SQL Developer Data Modeler. Subsequent records are added each time you export a design in the Data Modeler. The reporting repository and the Data Modeler are discussed in the chapter on Chapter 9, Introducing SQL Developer Data Modeler.
A new category of reports or additional reports in the existing categories is always added in the latest releases of SQL Developer. Since SQL Developer version 2.1, there has been many additional categories of reports added. For example, the OLAP Reports and TimesTen Reports nodes. We can easily explore all the reports by just clicking on the reports followed by the selection of the databases you want to run the report on.
A report available from a top-level menu saves having to navigate to the report in the correct category. These reports are Monitor Sessions and Monitor SQL, which are both available on the main Tools menu, and Manage Database, which is available in the Connections main context menu. We'll now review each of these reports.
The Monitor Sessions report displays the connected SQL Developer sessions and is available:
- In the Sessions category in the Database Administration section
The context menu for the report includes two additional items, Trace Session and Kill Session (as shown in the following screenshot). If you run this report as SYSTEM or a DBA, you can terminate these SQL Developer sessions using the menu. This is particularly useful if a session has not been terminated in a clean manner and needs to specifically be terminated.
- On the main Tools menu
You can run the Manage Database report using the context menu for any connection in the Connections navigator. This report displays the tablespaces and the associated details. If you run the report as SYS, you can also shut down the database from this report, as the report provides an additional Shutdown button.
Oracle Database 11g introduced real-time SQL monitoring, a feature now also exposed in the SQL Developer releases. Tuning is often considered the domain of DBAs, and this feature is typically used for monitoring performance and finding problem areas (for example, what point a query has reached in the explained plan and where the time is being spent). Having a report you can run easily brings tuning closer to the developer. Real-time SQL monitoring is useful for long running queries and comes into play by default when:
- The
STATISTICS_LEVELinitialization parameter is set toALLorTYPICAL(the default value) - SQL statements consume more than 5 seconds
- SQL queries run in parallel
In SQL Developer, the report is Monitor SQL under the Tools menu. When you run it, it will display a grid of all the monitored statements.
In the following example, we used the shipped reports via All Objects | Object Count by Type. You can write your own long-running query or do the same. Once the report has started running, switch to or invoke the Monitor SQL report. A data grid of records will be displayed, as shown in the following screenshot:
We have run a number of reports, so we have a record for each of the queries that fit the earlier mentioned criteria. To see the details of the report, invoke the context menu and select Show Sql Details.
This query is still in progress, so the statements with the green arrows are still being executed.