Problem refreshing Materialized View

I have two schemas A and B within one database.  I have private synonyms in schema B for all the tables in schema A.  I have procedure in schema B which creates the Materialized view with full referesh.  This materialized view uses these private synonyms.  It has five queries with union.   When I execute this procedure in schema B, it just hangs.  Also, my DBA does not see any activity on database level.  When the DBA runs it on the server, his session also hangs.  When I run these queries individually, it works fine.  

Any suggestions??
dnabatraAsked:
Who is Participating?
 
aalapsharmaConnect With a Mentor Commented:
Use the following parameters while creating the MV:

NOPARALLEL
NOLOGGING
BUILD DEFERRED

Example:

Create materialized view <MV_Name>
NOPARALLEL
NOLOGGING
BUILD DEFERRED
REFRESH COMPLETE
as
(
Your query goes here
);

This will create the MV without populating the data.

Then you can manually refresh the MV using the "dbms_mview.refresh" procedure

Regards,
Aalap
0
 
sathyagiriCommented:
Please could you post the queries?
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.