OraFAQ Forum: SQL & PL/SQL » Oracle materialized views workflow

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle materialized views workflow

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle materialized views workflow [message #670693]

Fri, 20 July 2018 02:17

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

Report message to a moderator

Re: Oracle materialized views workflow [message #670699 is a reply to message #670693]

Fri, 20 July 2018 07:29

Bill B
Messages: 1971
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

Report message to a moderator

Re: Oracle materialized views workflow [message #670700 is a reply to message #670693]

Fri, 20 July 2018 11:06

John Watson
Messages: 8976
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.

Report message to a moderator

Re: Oracle materialized views workflow [message #670702 is a reply to message #670693]

Fri, 20 July 2018 13:19

Lalit Kumar B
Messages: 3174
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 11 08:35:57 CDT 2025