Home » SQL & PL/SQL » SQL & PL/SQL » Oracle materialized views workflow
Oracle materialized views workflow [message #670693] Fri, 20 July 2018 02:17 Go to next message
camillelola
Messages: 1
Registered: November 2017
Junior Member
I am new to Oracle. I wanted to know the solution for below-posted questions based on the details provided.

Questions:
1. What most likely happened? 2. How would you go about figuring out why it happened?

Details: The materialized view named SOMESCHEMA.SOMETABLE is present in an Oracle database. It replicates rows from another Oracle database. While the materialized view has tens of millions of rows in it, it typically does an incremental refresh and the refresh completes in less than a minute. One morning sessions performing writes on your database are failing with many "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'".

When I run the following query: SELECT owner, mview_name, master_link, refresh_method, fast_refreshable, last_refresh_type FROM dba_mviews WHERE owner = 'SOMESCHEMA' AND mview_name = 'SOMETABLE'

I got the following output:

SOMESCHEMA, SOMETABLE, @"SOME_REMOTE_DB.EXAMPLE.COM", COMPLETE, DIRLOAD_DML, COMPLETE


Thanks & Regards
Camillelola
Re: Oracle materialized views workflow [message #670699 is a reply to message #670693] Fri, 20 July 2018 07:29 Go to previous messageGo to next message
Bill B
Messages: 1870
Registered: December 2004
Senior Member
Talk to your database administrator. They have made the UNDO tablespace too small and need to increase the size or set it to autoextend with a maximum size.

When you say an incremental update, do you mean it is doing a "fast refresh"?

Do you have an MLOG defined for SOMESCHEMA.SOMETABLE? If you do, how many rows are in it. You really need to give us more information
Re: Oracle materialized views workflow [message #670700 is a reply to message #670693] Fri, 20 July 2018 11:06 Go to previous messageGo to next message
John Watson
Messages: 7954
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read


Quote:
When I run the following query: SELECT owner, mview_name, master_link, refresh_method, fast_refreshable, last_refresh_type FROM dba_mviews WHERE owner = 'SOMESCHEMA' AND mview_name = 'SOMETABLE'

I got the following output:

SOMESCHEMA, SOMETABLE, @"SOME_REMOTE_DB.EXAMPLE.COM", COMPLETE, DIRLOAD_DML, COMPLETE
This shows that the materialized view is set up for complete refresh, not fast refresh. So I don't see how it ever refreshed in a few seconds. If you want to do complete refresh, you cam minimize the undo space needed by setting atomic_refresh=false.
Re: Oracle materialized views workflow [message #670702 is a reply to message #670693] Fri, 20 July 2018 13:19 Go to previous message
Lalit Kumar B
Messages: 3148
Registered: May 2013
Location: World Wide on the Web
Senior Member
camillelola wrote on Fri, 20 July 2018 12:47
I am new to Oracle.
A challenging topic for you to start working with Oracle. Just curious, do you have a DBA to help or are you on your own?

Quote:
it typically does an incremental refresh and the refresh completes in less than a minute
Your statement implies it is a fast refresh method.

Quote:

SELECT owner, mview_name, master_link, refresh_method, fast_refreshable, last_refresh_type FROM dba_mviews WHERE owner = 'SOMESCHEMA' AND mview_name = 'SOMETABLE'

I got the following output:

SOMESCHEMA, SOMETABLE, @"SOME_REMOTE_DB.EXAMPLE.COM", COMPLETE, DIRLOAD_DML, COMPLETE
refresh_method is complete refresh. However, this method could be overridden through the API. Please check if that's the case, you think it is fast refresh, but the actual method is complete refresh.

Also, you didn't mention your database version, since when it is happening, anything changed recently since you observed the problem, etc. Please inspect the materialized view log, and post it here.

[Updated on: Fri, 20 July 2018 13:26]

Report message to a moderator

Previous Topic: ORA-04091: table Table_Name is mutating, trigger/function may not see it
Next Topic: PL/SQL: ORA-04053: error occurred when validating remote object
Goto Forum:
  


Current Time: Sun Jul 21 01:54:19 CDT 2019