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)
Can't create a Materialised view [message #646464] Thu, 31 December 2015 04:20 Go to next message
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?
Re: Can't create a Materialised view [message #646465 is a reply to message #646464] Thu, 31 December 2015 04:30 Go to previous messageGo to next message
John Watson
Messages: 8922
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='....';
Re: Can't create a Materialised view [message #646466 is a reply to message #646465] Thu, 31 December 2015 04:47 Go to previous message
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...
Previous Topic: Performance Issue in VARRAY
Next Topic: Help me on Syntax of PL/SQL
Goto Forum:
  


Current Time: Fri Mar 29 04:07:10 CDT 2024