Materialized Views in Oracle

A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

In this article, we shall see how to create a Materialized View in Oracle and discuss Refresh Option of the view.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views

The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL>  CREATE MATERIALIZED VIEW mv_emp_pk
    REFRESH FAST START WITH SYSDATE 
 NEXT  SYSDATE + 1/48
 WITH PRIMARY KEY 
 AS SELECT * FROM emp@remote_db;

Materialized view created.

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.

Rowid Materialized Views

The following statement creates the rowid materialized view on table emp located on a remote database:

SQL>  CREATE MATERIALIZED VIEW mv_emp_rowid 
 REFRESH WITH ROWID 
    AS SELECT * FROM emp@remote_db; 

Materialized view log created.

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

SQL> CREATE MATERIALIZED VIEW  mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
     (SELECT * FROM dept@remote_db d
     WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE
[refresh [fast|complete|force]
 [on demand | commit]
 [start with date] [next date]
 [with {primary key|rowid}]]


The refresh option specifies:

The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed


Refresh Method - FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.





Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method - COMPLETE Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method - FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause




WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations


Timing the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
SQL>  CREATE MATERIALIZED VIEW mv_emp_pk
    REFRESH FAST 
 START WITH SYSDATE 
 NEXT  SYSDATE + 2
 WITH PRIMARY KEY 
 AS SELECT * FROM emp@remote_db;

Materialized view created.


In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Summary

Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes.

Debug Workflow Notifications and Workflow problems

Troubleshooting Workflow Notification Mailer Issues

Troubleshooting Workflow Notification Mailer Issues
Troubleshooting Workflow Notification Mailer Issues
Find Workflow Notification Mailer is up and Running?

1. Check if WF mailer component is running.

SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = ‘WF_MAILER’;

Restart the WF mailer with the help of DBAs if WF mailer is not running.

2. If WF mailer is running then perform the below steps

Find the Failed One’s?

Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS=’FAILED’;

Check pending e-mail notification that was pending for process.

Sql> SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘MAIL’
GROUP BY message_name;

Sql> SELECT * FROM wf_notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘SENT’
ORDER BY begin_date DESC

Check the Workflow notification has been sent or not?

select mail_status, status from wf_notifications where notification_id= :p_notification_id

–If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
–If mail_status is SENT, its means mailer has sent email
–If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”
–Notification preference of user can be set by user by logging in application + click on preference + the notification preference

1. Verify whether the message is processed in WF_DEFERRED queue

select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()=  notification id

2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue

select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = ‘
To check what all mails have went and which all failed ?

Select from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = ‘OPEN’;

Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = ‘OPEN’;

Users complain that notifications are stuck ?

Use the following query to check to see whatever the users are saying is correct

SQL> select message_type, count(1) from wf_notifications
where status=’OPEN’ and mail_status=’MAIL’ group by message_type;

E.g o/p of query –

MESSAGE_Type COUNT(1)
——– ———-
POAPPRV 40 — 40 mails of Po Approval not sent —
INVTROAP 12
REQAPPRV 9
WFERROR 10 — 10 mails have error

If Mail not received by User ?

select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’name,name’ ;

Status – Active
Notification_preference-> Mailtext (Should not be disabled)
Email Address should not be null

Notification not sent waiting to be mailed ?

IF user preference is not setup correctly :- 

1. Log into applications as the sysadmin user.

2. In the upper right hand corner of the screen, click on Preferences.

3. Within the Notifications section for Email Style, select a valid option other than: Do not send
me email or Disabled.

4. Now run run the Synchronize WF LOCAL tables request.

5. Retest the issue after the Synchronize WF LOCAL tables request is complete.

6. Migrate the solution as appropriate to other environments.

SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?

Below are the oracle important note to debug workflow notifications:-
******************************

Note: 1054215.1 – How to Check if the Workflow Mailer is Running
Note: 415516.1 – How to Check Whether Notification Mailer is Working or Not

Note: 831982.1 – 11i/R12 – A guide for troubleshoting Workflow Notification Emails – Inbound and Outbound
Note: 1012344.7 – Notifications Not Being Sent In Workflow
Note: 560472.1 – Workflow Mailers Not Sending Notifications