By default, up to 8,000 bytes of a LOB column are stored inline with the table row. Prior to 23c it was up to 4,000 bytes. If the LOB is more than 8,000 bytes, then Oracle automatically stores it outside the row data. The main advantage of storing a LOB in a row is that small LOBs require less I/O, because Oracle does not have to search out of the row for the LOB data.

However, storing LOB data in a row is not always desirable. The disadvantage of storing LOBs in a row is that the table row sizes are potentially longer.

This can affect the performance of full-table scans, range scans, and updates to columns other than the LOB column. In these situations, you may want to disable storage in the row.

For example,you explicitly instruct Oracle to store the LOB outside the row with the DISABLE STORAGE IN ROW clause:

SQL> create table patchmain( patch_id number, patch_desc clob , lob_file blob)

lob(patch_desc, lob_file) store as (tablespace lob_data disable storage in row);

ENABLE STORAGE IN ROW is enabled by default and will store up to 8,000 bytes of a LOB in the table row. The LOB locator is aways stored inline with the row even if you specify DISABLE STORAGE IN ROW.

You cannot modify the LOB storage in a row after the table has been created. The only ways to alter storage in a row are to move the LOB column or drop and re-create the table. This example alters the storage in a row by moving the LOB column:

SQL> alter table patchmain move lob(patch_desc)

store as (enable storage in row);

You can verify the in-row storage via the IN_ROW column of USER_LOBS:

SQL> select table_name, column_name, tablespace_name, in_row from user_lobs;

TABLE_NAME   COLUMN_NAME   TABLESPACE_NAME IN_ROW

PATCHMAIN PATCHMAIN LOG_FILE PATCH_DESC
LOB_DATA         YES LOB_DATA                 YES

Viewing LOB Metadata

You can use any of the DBA/ALL/USER_LOBS views to display information about LOBs in your database:

SQL> select table_name, column_name, index_name, tablespace_name from all_lobs order by table_name;

Also keep in mind that a LOB segment has a corresponding index segment:

SQL> select segment_name, segment_type, tablespace_name from user_segments where segment_name like ‘SYS_LOB%’ or segment_name like ‘SYS_IL%’;

In this way, you can query both the segment and the index in the DBA/ALL/USER_ SEGMENTS views for LOB information.

You can use the DBA/ALL/USER_SEGMENTS to get the space consumed by the LOB segment. Here is a sample query:

SQL> select segment_name, segment_type, segment_subtype, bytes/1024/1024 meg_bytes from user_segments;

You can modify the query to report on only LOBs by joining to the DBA/ALL/USER_ LOBS view:

SQL> select a.table_name, a.column_name, a.segment_name, a.index_name ,b.bytes/1024/1024 meg_bytes

from user_lobs a, user_segments b where a.segment_name = b.segment_name;

You can also use the DBMS_SPACE.SPACE_USAGE package and procedure to report on the blocks being used by a LOB.

Since earlier versions of Oracle, the ability to store large files in the database vastly improved with the CLOB, NCLOB, BLOB, and BFILE data types. These additional LOB data types let you store much more data, with greater functionality. Table 11-1 summarized the types of Oracle LOBs available and their descriptions.

Table11-1.Oracle Large Object Data Types

* NCLOB along with the other national character sets are not needed to store multibyte characters. I normally use CLOB and VARCHAR2 with just making sure the sizing is large enough to handle multibyte character.

As you can see from Table 11-1, the maximum size of a LOB can range from 8 TB to 128 TB depending on how the database is configured.

A CLOB such as text or XML, as well as JSON, can also be stored as a CLOB or JSON data type. Also, as we saw in a previous chapter, you can just use the relational data to create a JSON duality view.

BLOBs are not human readable. Typical uses for a BLOB are spreadsheets, documents, images, and audio and video data.

CLOBs, NCLOBs, and BLOBs are known as internal LOBs. This is because these data types are stored inside the Oracle database in data files. Internal LOBs participate in transactions and are covered by Oracle’s database security as well as its backup and recovery features.

BFILEs are known as external LOBs. BFILE columns store a pointer to a file on the OS that is outside the database.

You can think of a BFILE as a mechanism for providing read-only access to large binary files outside the database on the OS file system.

Sometimes, the question arises as to whether you should use a BLOB or a BFILE. BLOBs participate in database transactions and can be backed up, restored, and recovered by Oracle.

BFILEs do not participate in the database transactions, are read-only, and are not covered by backup and recovery of the database.

BFILEs are more appropriate for large binary files that are read-only and that do not change while an application is running.

For instance, you may have large binary video files that are referenced by a database application. In this scenario, the business determines that you do not need to create and maintain a 500TB database when all the application really needs is a pointer (stored in the database) to the locations of the large files on disk.

You may find yourself in an environment that contains hundreds of databases located on dozens of different servers. In such a scenario, you want to ensure that you do not run the wrong commands or connect to the wrong database, or both. When performing DBA tasks, it is prudent to verify that you are connected as the appropriate account and to the correct database. You can run the following types of SQL commands to verify the currently connected user and database:

SQL> show user;

SQL> select * from user_users; SQL> select name from v$database;

SQL> select instance_name, host_name from v$instance; SQL> show pdbs;

An efficient way of staying aware of your environment is to set your prompt automatically, via the login.sql script, to display user and instance information. This example manually sets the SQL prompt:

SQL> set sqlprompt ‘&_USER.@&_CONNECT_IDENTIFIER.> ‘

Here is what the SQL prompt now looks like:

SYS@mmdb23>

You can also use the SYS_CONTEXT built-in SQL function to extract information from the data dictionary regarding details about your currently connected session. The general syntax for this function is as follows:

SYS_CONTEXT(‘<namespace>’,'<parameter>’,[length])

This example displays the user, authentication method, host, and instance:

SYS@mmdb23> select sys_context(‘USERENV’,’CURRENT_USER’) usr

,sys_context(‘USERENV’,’AUTHENTICATION_METHOD’) auth_mth ,sys_context(‘USERENV’,’HOST’) host ,sys_context(‘USERENV’,’INSTANCE_NAME’) inst from dual;

USERENV is a built-in Oracle namespace. More than 50 parameters are available when you use the USERENV namespace with the SYS_CONTEXT function. See the Oracle SQL Language Reference, which can be freely downloaded from the Technology Network area of the Oracle website (https://docs.oracle.com/database) for a complete list of parameters.

Determining Your Environment’s Details

Sometimes, when deploying code through various development, test, beta, and production environments, it is handy to be prompted as to whether you are in the correct environment.

The technique for accomplishing this requires two files: answer_yes.sql and answer_no.sql. Here are the contents of answer_yes.sql:

— answer_yes.sql PROMPT PROMPT Continuing…

And here is answer_no.sql: — answer_no.sql PROMPT

PROMPT Quitting and discarding changes… ROLLBACK; EXIT;

Now, you can insert the following code into the first part of your deployment script; the code will prompt you as to whether you are in the right environment and if you want to continue:

WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; select host_name from v$instance; select name as db_name from v$database; SHOW user; SET ECHO OFF; PROMPT ACCEPT answer PROMPT ‘Correct environment? Enter yes to continue: ‘ @@answer_&answer..sql

If you type in yes, then the answer_yes.sql script will execute, and you will continue to run any other scripts you call. If you type in no, then the answer_no.sql script will run, and you will exit from SQL*Plus and end up at the OS prompt. If you press the Enter key without typing either, you will also exit and return to the OS prompt.

Sometimes, if you are troubleshooting an issue and are under pressure, you need to quickly extract information from the data dictionary to help resolve the problem.

However, you may not know the exact name of a data dictionary view or its associated columns. If you are like me, it is impossible to keep all the data dictionary view names and column names in your head. Additionally, if you work with databases in different versions, it is sometimes difficult to keep track of which view may be available with a given release of Oracle.

I queried CDB_OBJECTS to get a count of the number of views for 23c:

SQL> select count(1) from dba_objects where object_name like ‘CDB%’;

COUNT(1) ———-

Books, posters, and Google searches can provide this information, but if you cannot find exactly what you are looking for, you can use the documentation contained in the data dictionary itself. You can query from four views, in particular:

CDB_OBJECTS DBA_OBJECTS DICTIONARY DICT_COLUMNS

If you know roughly the name of the view from which you want to select information, you can first query from DBA_OBJECTS. For instance, if you are troubleshooting an issue regarding materialized views and you cannot remember the exact names of the data dictionary views associated with materialized views, you can do this:

SQL> select object_name from dba_objects where object_name like ‘DBA_MV%’;

OBJECT_NAME
DBA_MVIEW_ANALYSIS
DBA_MVIEW_ANALYSIS
DBA_MVIEW_AGGREGATES
DBA_MVIEW_AGGREGATES
DBA_MVIEW_DETAIL_RELATIONS
DBA_MVIEW_DETAIL_RELATIONS
DBA_MVIEW_KEYS
DBA_MVIEW_KEYS
DBA_MVIEW_JOINS
DBA_MVREF_STATS
DBA_MVREF_STATS

38 rows selected.

That may be enough to get you in the ballpark or have a short list to look through. But often you need more information about each view. This is when the DICTIONARY and DICT_COLUMNS views can be invaluable. The DICTIONARY view stores the names of the data dictionary views. It has two columns:

Again, we can look at the dictionary for a description of the MV data dictionary views:

DBA_MVIEW_ANALYSIS    Description of the materialized views accessible to dba

In this manner, you can quickly determine which view you need to access. If you want further information about the view, you can describe it; for example,

SQL> desc dba_mviews

If that does not give you enough information regarding the column names, you can query the DICT_COLUMNS view. This view provides comments about the columns of a data dictionary view; for example,

Owner of the materialized view Name of the materialized view

Name of the materialized view container table

The defining query that the materialized view instantiates

In this way, you can generate and view documentation regarding most data dictionary objects. The technique allows you to quickly identify appropriate views and the columns that may help you in a troubleshooting situation.

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

The dynamic performance data dictionary views are colloquially referred to as the V$ and GV$ views. These views are constantly updated by Oracle and reflect the current condition of the instance and database. Dynamic views are critical for diagnosing real-time performance issues.

The V$ and GV$ views are indirectly based on underlying X$ tables, which are internal memory structures that are instantiated when you start your Oracle instance. Some of the V$ views are available the moment the Oracle instance is started.

For example, V$PARAMETER contains meaningful data after the STARTUP NOMOUNT command has been issued and does not require the database to be mounted or open. Other dynamic views (such as V$CONTROLFILE) depend on information in the control file and therefore contain significant information only after the database has been mounted.

Some V$ views (such as V$DB) provide kernel-processing information and thus have useful results only after the database has been opened.

At the top layer, the V$ views are actually synonyms that point to underlying SYS.V_$ views. At the next layer down, the SYS.V_$ objects are views created on top of another layer of SYS.V$ views. The SYS.V$ views in turn are based on the SYS.GV$ views. At the bottom layer, the SYS.GV$ views are based on the X$ memory structures.

The top-level V$ synonyms and SYS.V_$ views are created when you run the catalog.sql script, which you usually do after the database is initially created. Figure 10-2 shows the process for creating the V$ dynamic performance views.

Figure10-2.CreatingtheV$dynamicperformancedatadictionaryviews

Accessing the V$ views through the topmost synonyms is usually adequate for dynamic performance information needs. On rare occasions, you will want to query internal information that may not be available through the V$ views. In these situations, it is critical to understand the X$ underpinnings.

If you work with Oracle Real Application Clusters (RACs), you should be familiar with the GV$ global views. These views provide global dynamic performance information regarding all instances in a cluster (whereas the V$ views are instance specific). The GV$ views contain an INST_ID column for identifying specific instances in a clustered environment.

You can display the V$ and GV$ view definitions by querying the VIEW_DEFINITION column of the V$FIXED_VIEW_DEFINITION view. For instance, this query displays the definition of the V$CONTROLFILE:

SQL> select view_definition from v$fixed_view_definition where view_ name=’V$CONTROLFILE’; VIEW_DEFINITION

select STATUS , NAME, IS_RECOVERY_DEST_FILE, BLOCK_SIZE, FILE_SIZE_ BLKS,   CON_ID from GV$CONTROLFILE where inst_id = USERENV(‘Instance’)

If you inherit a database and are asked to maintain and manage it, typically you will inspect the contents of the data dictionary to determine the physical structure of the database and see what events are currently transacting. Besides figuring out what you inherited, these views help to automate processes and troubleshoot problems. Toward this end, Oracle provides two general categories of read-only data dictionary views:

•     The contents of your database, such as users, tables, indexes, constraints, privileges, and other objects. These are sometimes referred to as the static CDB/DBA/ALL/USER data dictionary views, and they are based on internal tables stored in the SYSTEM tablespace. The term static, in this sense, means that the information within these views changes only as you make changes to your database, such as adding a user, creating a table, or modifying a column.

•     A real-time view of activity in the database, such as users connected to the database, SQL currently executing, memory usage, locks, and I/O statistics. These views are based on virtual memory tables and are referred to as the dynamic performance views. The information in these views is continuously updated by Oracle as events take place within the database. The views are also sometimes called the V$ or GV$ views. GV$ views are global views across all nodes in the database system and normally have an additional column to let you know which node they are referring to.

These types of data dictionary views are described in further detail in the next two sections.

Static Views

Oracle refers to a subset of the data dictionary views as static and based on the physical tables maintained internally by Oracle. The term static can sometimes be a misnomer. For example, the DBA_SEGMENTS and DBA_EXTENTS views change dynamically as the amount of data in your database grows and shrinks. Regardless, Oracle has made the distinction between static and dynamic, and it is important to understand this architecture nuance when querying the data dictionary. There are four levels of static views:
• USER

• ALL

• DBA

• CDB
The USER views contain information available to the current user. For example, the USER_TABLES view contains information about tables owned by the current user. No special privileges are required to select from the USER-level views.

At the next level are the ALL static views. The ALL views show you all object information the current user has access to. For example, the ALL_TABLES view displays all database tables on which the current user can perform any type of DML operation. No special privileges are required to query from the ALL-level views.

Next are the DBA static views. The DBA views contain metadata describing all objects in the database (regardless of ownership or access privilege). To access the DBA views, a DBA role or SELECT_CATALOG_ROLE must be granted to the current user.

The CDB-level views provide information about all pluggable databases within a container database. The CDB-level views report across all containers (root, seed, and all pluggable databases) in a CDB. For instance, if you wanted to view all users within a CDB database, you would do so from the root container, by querying CDB_USERS.

You will notice that many of the static data dictionary and dynamic performance views have a new column, CON_ID. This column uniquely identifies each pluggable database within a container database. The root container has a CON_ID of 1. The seed has a CON_ID of 2. Each new pluggable database created within the CDB is assigned a unique sequential
container ID.

The static views are based on internal Oracle tables, such as USER$, TAB$, and IND$. If you have access to the SYS schema, you can view underlying tables directly via SQL. For most situations, you need to access only the static views that are based on the underlying internal tables.

The data dictionary tables (such as USER$, TAB$, and IND$) are created during the execution of the CREATE DATABASE command. As part of creating a database, the sql. bsq file is executed, which builds these internal data dictionary tables.

The sql.bsq file is generally located in the ORACLE_HOME/rdbms/admin directory; you can view it via an OS editing utility (such as vi, in Linux/Unix, or Notepad in Windows).

The static views are created when you run the catalog.sql script (usually, you run this script once the CREATE DATABASE operation succeeds). The catalog.sql script is located in the ORACLE_HOME/rdbms/admin directory. Figure 10-1 shows the process of creating the static data dictionary views.

Figure 10–1. Creating the static data dictionary views

When you have a good understanding of the architecture of a fast refresh, you will not have difficulty learning advanced MV concepts. If this is the first time looking at MVs, it is important to realize that an MV’s data is stored in a regular database table. This will help you understand architecturally what is and is not possible. For the most part, because the MV and MV log are based on tables, most features available with a regular database table can also be applied to the MV table and MV log table. For instance, the following Oracle features are readily applied to MVs:

•     Storage and tablespace placement

•     Indexing

•     Partitioning

•     Compression

•     Encryption

•     Logging

•     Parallelism

Numerous MV features are available. Many are related to attributes that you can apply to any table, such as storage, indexing, compression, and encryption. Other features are related to the type of MV created and how it is refreshed.

Creating an Unpopulated MV

When you create an MV, you have the option of instructing Oracle whether to initially populate the MV with data. For example, if it takes several hours to initially build an MV, you may want to first define the MV and then populate it as a separate job.

This example uses the BUILD DEFERRED clause to instruct Oracle not to initially populate the MV with the results of the query:

SQL> create materialized view sales_mv tablespace users build deferred refresh complete on demand as select sales_id, sales_amt from sales;

At this point, querying the MV results in zero rows returned. At some later point, you can initiate a complete refresh to populate the MV with data.

Creating an MV Refreshed on Commit

You may be required, when data are modified in the master table, to have them immediately copied to an MV. In this scenario, use the ON COMMIT clause when you create the MV. The master table must have an MV log created on it for this technique to work:

SQL> create materialized view log on sales with primary key;

Next, an MV is created that refreshes on commit:

SQL> create materialized view sales_mv refresh on commit as select sales_id, sales_amt from sales;

As data are inserted and committed in the master table, any changes are also available in the MV that would be selected by the MV query.

The ON COMMIT refreshable MV has a few restrictions you need to be aware of:

•     The master table and MV must be in the same database.

•     You cannot execute a distributed transaction on the base table.

•     This approach is not supported with MVs that contain object types or Oracle-supplied types.

Also consider the overhead associated with committing data simultaneously in two places; this can affect the performance of a high-transaction OLTP system. Additionally, if there is any problem with updating the MV, then the base table cannot commit a transaction. For example, if the tablespace in which the MV is created becomes full (and cannot allocate another extent), you see an error such as this when trying to insert into the base table:

ORA-12008: error in materialized view refresh path

ORA-01653: unable to extend table MV_MAINT.SALES_MV by 16 in tablespace…

For these reasons, you should use this feature only when you are sure it would not affect performance or availability.

Note you cannot specify that an MV be refreshed with both ON COMMIT and

ON DEMAND. in addition, ON COMMIT is not compatible with the START WITH and NEXT clauses of the CREATE MATERIALIZED VIEW statement.

The previous example had inserts and data modification on a view with one table defined in the FROM clause of the SQL query, but this is also possible with multiple tables defined. This is known as an updatable join view.

For reference purposes, here are the CREATE TABLE statements for the two tables used in the examples in this section:

SQL> create table dept ( dept_id number primary key , dept_name varchar2(15)); —

SQL> create table emp ( emp_id number primary key , emp_name varchar2(15), dep_id number, constraint emp_dept_fk foreign key (dept_id) references dept(dept_id));And let’s seed some data for the two tables:

SQL> insert into dept values (1, ‘HR’), (2, ‘IT’),(3, ‘SALES’);

SQL> insert into emp values (10, ‘John’, 2), (20, ‘George’, 1),

(30, ‘Fred’, 2), (40, ‘Craig’, 1), (50, ‘Linda’, 2), (60, ‘Carrie’, 3);

Here is an example of an updatable join view, based on the two prior base tables:

SQL> create or replace view emp_dept_v asselect a.emp_id, a.emp_name, b.dept_name, b.dept_id from emp a, dept bwhere a.dept_id = b.dept_id;

Underlying tables can be updated only if the following conditions are true:

•     The DML statement must modify only one underlying table.

•     The view must be created without the READ ONLY clause.

•     The column being updated belongs to the key-preserved table in the join view.

An underlying table in a view is key preserved if the table’s primary key can also be used to uniquely identify rows returned by the view. An example with data will help illustrate whether an underlying table is key preserved. In this scenario, the primary key of the EMP table is the EMP_ID column; the primary key of the DEPT table is the DEPT_ID column. Here is some sample data returned by querying the view:

As you can see from the output of the view, the EMP_ID column is always unique. Therefore, the EMP table is key preserved, and its columns can be updated. In contrast, the view’s output shows that it is possible for the DEPT_ID column to not be unique. Therefore, the DEPT table is not key preserved, and its columns can’t be updated.

When you update the view, modifications that result in columns that map to the underlying EMP table should be allowed because the EMP table is key preserved in this view.

SQL> update emp_dept_v set emp_name = ‘Jon’ where emp_id = 10;