Logical objects are viewable from SQL only after the database has been started. In contrast, physical objects can be viewed via OS utilities even if the instance is not started.

Figure 10-3 does not show all the relationships of all logical and physical aspects of an Oracle database. Rather, it focuses on components that you are most likely to encounter on a daily basis. This base relational diagram forms a foundation for leveraging Oracle’s data dictionary infrastructure.

Keep an image of Figure 10-3 open in your mind; now, add it to Figure 10-4.

Figure 10–4. Relationships of commonly used data dictionary views

Voilà, these data dictionary views map very closely to almost all the logical and physical elements of an Oracle database. Figure 10-4 does not show every data dictionary view. Indeed, the figure barely scratches the surface. However, this diagram does provide you with a secure foundation on which to build your understanding of how to leverage the data dictionary views to get the data you need to do your job.

The diagram shows the relationships between views, but it does not specify which columns to use when joining views together. You will have to describe the tables and make an educated guess as to how the views should be joined.

Use the diagram as a guide for where to start looking for information and how to write SQL queries that will provide answers to problems and expand your knowledge of Oracle’s internal architecture and inner workings. This anchors your problem-solving skills on a solid foundation. Once you firmly understand the relationships of Oracle’s logical and physical components and how this relates to the data dictionary, you can confidently address any type of database issue.

Note there are several thousand CDB/DBA/ALL/USER static views and more than 900 V$ dynamic performance views.

A Few Creative Uses of the Data Dictionary

In every chapter of this book, you will find several SQL examples of how to leverage the data dictionary to better understand concepts and resolve problems. Having said that, it is worth showing a few offbeat examples of how DBAs leverage the data dictionary. The next few sections do just that. Keep in mind that this is just the tip of the iceberg: there are endless number of queries and techniques that DBAs employ to extract and use data dictionary information.

DBAs commonly face the following types of database issues:

•     Database refusing connections because the maximum number of sessions is exceeded.

•     An application is hung, apparently because of some sort of locking issue.

•     An insert into a table fails because a tablespace cannot extend.

•     A PL/SQL statement is failing, with a memory error.

•     A user is trying to update a record, but a unique key constraint violation is thrown.

•     A SQL statement has been running for hours longer than normal.

•     Application users have reported that performance seems sluggish and that something must be wrong with the database.

The prior list is a small sample of the typical issues a DBA encounters on a daily basis. A certain amount of knowledge is required to be able to efficiently diagnose and handle these types of problems. A fundamental piece of that knowledge is an understanding of Oracle’s physical structures and corresponding logical components.

For example, if a table cannot extend because a tablespace is full, what knowledge do you rely on to solve this problem? You need to understand that when a database is created, it contains multiple logical space containers called tablespaces. Each tablespace consists of one or more physical data files. Each data file consists of many OS blocks. Each table consists of a segment, and every segment contains one or more extents. As a segment needs space, it allocates additional extents within a physical data file.

Once you understand the logical and physical concepts involved, you intuitively look in data dictionary views such as DBA_TABLES, DBA_SEGMENTS, DBA_TABLESPACES, and DBA_DATA_FILES to pinpoint the issue and add space as required.

In a wide variety of troubleshooting scenarios, your understanding of the relationships of various logical and physical constructs will allow you to focus on querying views that will help you quickly resolve the problem at hand.

To that end, inspect Figure 10-3. This diagram describes the relationships between logical and physical structures in an Oracle database.

The rounded rectangle shapes represent logical constructs, and the sharp-cornered rectangles are physical files.

Figure10-3.Oracle database logical and physical structure relationships