OraFAQ Forum: SQL & PL/SQL » Can't create a Materialised view

Home » SQL & PL/SQL » SQL & PL/SQL » Can't create a Materialised view (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)

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

Can't create a Materialised view [message #646464]

Thu, 31 December 2015 04:20

brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom

Member

I'm trying to create a materialised view using:

CREATE MATERIALIZED VIEW my_view
AS SELECT blah from my_table;

There are no other parameters specified.

The command just hangs and won't create the view. The identical SELECT statement used in CREATE TABLE tmp_table AS <same select statement> works fine and completes in 80 seconds.

I can't see any sessions blocking other sessions or locks affecting my problem.

What can I be doing wrong - please help?

Report message to a moderator

Re: Can't create a Materialised view [message #646465 is a reply to message #646464]

Thu, 31 December 2015 04:30

John Watson
Messages: 9002
Registered: January 2010
Location: Global Village

Senior Member

A work around would be to create the table, and then create the MV with the ON PREBUILT TABLE clause.

Otherwise, you'll need to show the wait event on which your session is hanging. This should get it:

select event,seconds_in_wait from v$session where username='....';

Report message to a moderator

Re: Can't create a Materialised view [message #646466 is a reply to message #646465]

Thu, 31 December 2015 04:47

brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom

Member

Thanks John.

I found in v$session "direct path read temp" that has my username, sid and the waitime of how long my statement is running.

I can't see what's causing it or how to free it though...

Report message to a moderator

Previous Topic:	Performance Issue in VARRAY
Next Topic:	Help me on Syntax of PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 30 08:59:00 CDT 2026