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

It was discussed that materialized views can be fast refreshed, but there can still be a lag. Real-time MVs can roll forward information based on the logs, which is like doing a fast refresh, but it is completing it in real time.

Real-time functionality is available if the following is true:

•     QUERY_REWRITE_INTEGRITY is enforced or TRUSTED

•    MV is not set to REFRESH … ON COMMIT

•     MV must be able to do a fast refresh

•     ENABLE ON QUERY COMPUTATION is used

Now when the MV is queried, the data will wind forward of a stale state making the data appear fresh to the statement. The changes are not persisted in the MV does a refresh.

Oracle Views

This chapter covered the database objects and views. However, these were very different views. A view gives you a way to logically present data to an application, reporting,

or other database and data management tools. So, even though views, JSON duality views, and materialized views provide different functionality and have different purposes, they expose data stored in relational tables. JSON duality gives you the data as JSON documents that are generated on demand and organized both relationally and hierarchically.

Materialized views store the snapshot of the data and provide better performance and can be used to perform analytical queries to also store.

The simplicity of using native database commands to create various views and set up access to these views through normal database security is a great tool to provide the needed data for applications and APIs.

Now with Oracle 23c having JSON-relational duality views provides standardized, straightforward joins with all sorts of data including JSON to state-of-the art analytics, machine learning, and reporting.

In many situations, when you base an MV on a query that joins multiple tables, it is deemed complex and therefore is available only for a complete refresh. However, in some scenarios, you can create a fast refreshable MV when you reference two tables that are joined together in the MV query.

This section describes how to use the EXPLAIN_MVIEW procedure in DBMS_MVIEW to determine whether it is possible to fast refresh a complex query. To help you completely understand the example, this section shows the SQL used to create the base tables. Say you have two base tables, defined as follows:

SQL> create table region( region_id number ,reg_desc varchar2(30),constraint region_pk primary key(region_id)); —

SQL> create table sales( sales_id number ,sales_amt number ,region_id number ,sales_dtt date,constraint sales_pk primary key(sales_id),constraint sales_fk1 foreign key (region_id) references region(region_id));

Additionally, REGION and SALES have MV logs created on them, as shown here:

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

Also, for this example, the base tables have these data inserted into them:

SQL> insert into region values(10,’East’), (20,’West’),(30,’South’), (40,’North’); —

SQL> insert into sales values (1,100,10,sysdate), (2,200,20,sysdate-20), (3,300,30,sysdate-30);

Suppose you want to create an MV that joins the REGION and SALES base tables as follows:

SQL> create materialized view sales_mv asselect a.sales_id,b.reg_desc from sales      a,region bwhere a.region_id = b.region_id;

Next, let’s attempt to fast refresh the MV:

SQL> exec dbms_mview.refresh(‘SALES_MV’,’F’);

This error is thrown:

ORA-12032: cannot use rowid column from materialized view log…

The error indicates that the MV has issues and cannot be fast refreshed. To determine whether this MV can become fast refreshable, use the output of the EXPLAIN_ MVIEW procedure of the DBMS_MVIEW package. This procedure requires that you first create an MV_CAPABILITIES_TABLE. Oracle provides a script to do this. Run this script as the owner of the MV:

SQL> @?/rdbms/admin/utlxmv.sql

After you create the table, run the EXPLAIN_MVIEW procedure to populate it:

SQL> exec dbms_mview.explain_mview(mv=>’SALES_MV’,stmt_id=>’100′);

Now, query MV_CAPABILITIES_TABLE to see what potential issues this MV may have:

SQL> select capability_name, possible, msgtxt, related_text from mv_capabilities_table

where capability_name like ‘REFRESH_FAST_AFTER%’ and statement_id = ‘100’order by 1;

Next is a partial listing of the output. The P (for possible) column contains an N (for no) for every fast refresh possibility:

CAPABILITY_NAME             P    MSGTXT                    RELATED_TEXT

REFRESH_FAST_AFTER_INSERT     N
the SELECT list does not have    Bthe rowids of all the detail tablesREFRESH_FAST_AFTER_INSERT     N

REFRESH_FAST_AFTER_INSERT   N
mv log must have ROWID mv log must have ROWID
MV_MAINT.REGION MV_MAINT.SALES

MSGTXT indicates the issues: The MV logs need to be ROWID based, and the ROWID of the tables must appear in the SELECT clause. So, first drop and re-create the MV logs with ROWID (instead of a primary key):

SQL> drop materialized view log on region; SQL> drop materialized view log on sales; —

SQL> create materialized view log on region with rowid; SQL> create materialized view log on sales with rowid; —

SQL> drop materialized view sales_mv; —

SQL> create materialized view sales_mv asselecta.rowid sales_rowid ,b.rowid region_rowid ,a.sales_id ,b.reg_descfrom sales     a ,region bwhere a.region_id = b.region_id;

Next, reset the MV_CAPABILITIES_TABLE, and repopulate it via the EXPLAIN_MVIEW procedure:

SQL> delete from mv_capabilities_table where statement_id=100; SQL> exec dbms_mview.explain_mview(mv=>’SALES_MV’,stmt_id=>’100′);

The output shows that it is now possible to fast refresh the MV:

CAPABILITY_NAME          P MSGTXT   RELATED_TEXT

REFRESH_FAST_AFTER_ANY_DML           Y REFRESH_FAST_AFTER_INSERT          Y

REFRESH_FAST_AFTER_ONETAB_DML  Y

Execute the following statement to see if the fast refresh works:

SQL> exec dbms_mview.refresh(‘SALES_MV’,’F’); PL/SQL procedure successfully completed.

The EXPLAIN_MVIEW procedure is a powerful tool that allows you to determine whether a refresh capability is possible and, if it is not possible, why it is not and how to potentially resolve the issue.

Oracle Database 23c also supports fast refresh on ANSI join syntax for MVs. Our query in the example had the following:

from sales     a ,region b

where a.region_id = b.region_id

But now this syntax is also valid:

from sales      a join region b

on (a.region_id = b.region_id)

You may never want an MV to be refreshed. For example, you may want to guarantee that you have a snapshot of a table at a point in time for auditing purposes. Specify the NEVER REFRESH clause when you create the MV to achieve this:

SQL> create materialized view sales_mv never refresh asselect sales_id, sales_amt from sales;

If you attempt to refresh a nonrefreshable MV, you receive this error:

ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view

You can alter a never refreshable view to be refreshable. Use the ALTER MATERIALIZED VIEW statement to do this:

SQL> alter materialized view sales_mv refresh on demand complete;

You can verify the refresh mode and method with the following query:

SQL> select mview_name, refresh_mode, refresh_method from user_mviews;

Creating MVs for Query Rewrite

The query rewrite feature allows the optimizer to recognize that an MV can be used to fulfill the requirements of a query instead of using the underlying master (base) tables. If you have an environment in which users frequently write their own queries and are unaware of the available MVs, this feature can greatly help with performance. There are three prerequisites for enabling query rewrite:

•     Oracle Enterprise Edition

•     Setting database initialization parameter QUERY_REWRITE_ ENABLED to TRUE

•     MV either created or altered with the ENABLE QUERY REWRITE clause

This example creates an MV with query rewrite enabled:

SQL> create materialized view sales_daily_mv segment creation immediate refresh complete on demand enable query rewrite as select sum(sales_amt) sales_amt ,trunc(sales_dtt) sales_dtt from sales group by trunc(sales_dtt);

You can verify that query rewrite is in use by examining a query’s explain plan via the autotrace utility:

SQL> set autotrace trace explain

Now, suppose a user runs the following query, unaware that an MV exists that already aggregates the required data:

SQL> select sum(sales_amt) sales_amt,trunc(sales_dtt) sales_dtt from sales group by trunc(sales_dtt);

Here is a partial listing of autotrace output that verifies that query rewrite is in use:

—————————————————————————–| Id | Operation                                    | Name                   | Cost (%CPU)| Time                                                   | —————————————————————————–| 0 | SELECT STATEMENT                      |                             | 3        (0)     | 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL | SALES_DAILY_MV | 3                                                             (0)                     | 00:00:01 | —————————————————————————–

As you can see from the prior output, even though the user selected directly from the SALES table, the optimizer determined that it could more efficiently satisfy the results of the query by accessing the MV.

You can tell if query rewrite is enabled for an MV by selecting the REWRITE_ENABLED column from USER_MVIEWS:

SQL> select mview_name, rewrite_enabled, rewrite_capability from user_mviews

where mview_name = ‘SALES_DAILY_MV’;

If for any reason a query is not using the query rewrite functionality and you think it should be, use the EXPLAIN_REWRITE procedure of the DBMS_MVIEW package to diagnose issues.

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 two most common configurations used are as follows:

•     Creating complete refresh MVs that are refreshed on demand

•     Creating fast refresh MVs that are refreshed on demand

It is important to understand these basic configurations. They lay the foundation for everything else you do with the MV feature.

The SALES table will be used as the basis for the MV examples.

SQL> create table sales( sales_id number primary key , sales_amt number, region_id number , sales_dtt date);

SQL> insert into sales values (1,101,10,sysdate-10), (2,511,20,sysdate-20),(3,11,30,sysdate-30); commit;

Keep in mind the performance of these queries, which are being executed thousands/millions of times a day and consuming a large amount of database resources. These examples will not be able to simulate that but will show how to create and maintain these views.

For creating an MV, you need both the CREATE MATERIALIZED VIEW and CREATE TABLE system privileges. If a user creating MVs doesn’t own the base table, then SELECT access on the base table is also required to perform an ON COMMIT REFRESH.

Suppose you wanted to create an MV that reports on daily sales. Use the CREATE MATERIALIZED VIEW…AS SELECT statement to do this. The following statement names the MV, specifies its attributes, and defines the SQL query on which the MV is based:

Note using an alias with GROUP BY is a new 23c feature.

Let’s look at the USER_MVIEWS data dictionary to verify that the MV was created as expected. Run this query:

SQL> select mview_name, refresh_method, refresh_mode , build_mode, fast_refreshable from user_mviews where mview_name = ‘SALES_DAILY_MV’;

MVIEW_NAME   REFRESH_ REFRES BUILD_MOD FAST_REFRESHABLE

————— ——– —— ——— —————–SALES_DAILY_MV COMPLETE DEMAND IMMEDIATE DIRLOAD_LIMITEDDML

If new data is inserted, this MV will be refreshed on demand only. To initiate a fast refresh of the MV, use the REFRESH procedure of the DBMS_MVIEW package.

This example passes two parameters to the REFRESH procedure: the name and the refresh method. C is for complete, and F is for fast, and to run a fast refresh, the MV needs to have a MV log.

Now, you attempt to initiate a fast refresh of the MV, using the REFRESH procedure of the DBMS_MVIEW package. This example passes two parameters to the REFRESH procedure: the name and the refresh method. The name is SALES_DAILY_MV, and the parameter is F (for fast):

SQL> exec dbms_mview.refresh(‘SALES_DAILY_MV’,’F’);

Because this MV was not created in conjunction with an MV log, a fast refresh is not possible. The following error is thrown:

ORA-23413: table “MV_MAINT”.”SALES” does not have a materialized view log

Instead, a complete refresh is initiated. The parameter passed in is C (for complete):

SQL> exec dbms_mview.refresh(‘SALES_DAILY_MV’,’C’);

The output indicates success:

PL/SQL procedure successfully completed.

To make this MV fast refreshable, a log needs to be created. Here is the criteria for a fast refreshable MV:

1. A base table recommended with primary (rowid can be used if no primary key)

2. Create an MV log on the base table.

3. Create an MV as fast refreshable.

SQL> create materialized view log on sales with primary key; SQL> create materialized view sales_rep_mv segment creation immediate refresh with primary key fast on demand as select sales_id ,sales_amt,trunc(sales_dtt) sales_dtt from sales;

First, when an MV log is created, a corresponding table is also created that stores the rows in the base table that changed and how they changed (insert, update, or delete). The MV log table name follows the format MLOG$_<base table name>.

A table is also created with the format RUPD$_<base table name>. Oracle automatically creates this RUPD$ table when you create a fast refreshable MV, using a primary key.

The table is there to support the updatable MV feature. You do not have to worry about this table unless you are dealing with updatable MVs (see the Oracle Advanced Replication Guide for more details on updatable MVs). If you’re not using the updatable MV feature, then you can ignore the RUPD$ table.

Oracle creates an index with the format <base table name>_PK1. This index is automatically created for primary key-based MVs and is based on the primary key column(s) of the base table. If this is a ROWID instead of a primary key, then the index name has the format I_SNAP$_<table_name> and is based on the ROWID. If you do not explicitly name the primary key index on the base table, then Oracle gives the MV table primary key index a system-generated name, such as SYS_C008780.

Now that you understand the underlying architectural components, let’s look at the data in the MV:

SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_rep_mvorder by 2;Here is some sample output:

SALES_AMT TO_CHAR(SALES_DTT,’D ———- ——————–511 10-jan-2023 101 20-jan-2023 127 30-jan-2023

Let’s add two records to the base SALES table:

SQL> insert into sales values (6, 99, 20, sysdate-6), (7, 127, 30, sysdate-7);

SQL> commit;

At this point, it is instructional to inspect the M$LOG table. You should see two records that identify how the data in the SALES table have changed:

SQL> select count(1) from mlog$_sales;

There are two records:

Next, let’s refresh the MV. This MV is fast refreshable, so you call the REFRESH procedure of the DBMS_MVIEW package with the F (for fast) parameter:

SQL> exec dbms_mview.refresh(‘SALES_REP_MV’,’F’);A quick inspection of the MV shows two new records:

SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_rep_mvorder by 2; Here is some sample output:

SALES_AMT TO_CHAR(SALES_DTT,’D ———- ——————–511 10-jan-2023101 20-jan-2023 127 23-jan-2023 99 24-jan-2023 127 30-jan-2023

Additionally, the count of the MLOG$ has dropped to zero. After the MV refresh is complete, those records are no longer required:

SQL> select count(1) from mlog$_sales;

Here is the output:

COUNT(1)

———-0

You can verify the last method whereby an MV was refreshed by querying the USER_ MVIEWS view:

SQL> select mview_name, last_refresh_type, last_refresh_date from user_mviews order by 1,3; Here is some sample output:

MVIEW_NAME             LAST_REF    LAST_REFR

SALES_DAILY_MV COMPLETE 30-JAN-23

SALES_REP_MV        FAST    30-JAN-23