OraFAQ Forum: Performance Tuning » While creating of M-View, lock the SYS.OBJ$ table

Home » RDBMS Server » Performance Tuning » While creating of M-View, lock the SYS.OBJ$ table (SunOS 5.8 - 9.2.0.5.0)

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

While creating of M-View, lock the SYS.OBJ$ table [message #663809]

Tue, 20 June 2017 23:45

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi,

can you please suggest.
While creating Materialized view it locks SYS.OBJ$ in Row-Exclusive locked mode.

Thanks in advance.
Hitesh

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663813 is a reply to message #663809]

Wed, 21 June 2017 01:39

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And the question is?

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663817 is a reply to message #663813]

Wed, 21 June 2017 02:00

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi,

it's taking long time and hangs.

Thanks,
Hitesh

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663823 is a reply to message #663817]

Wed, 21 June 2017 02:14

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And what are we suppose to do with the information you give?
Check locks.

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663851 is a reply to message #663823]

Wed, 21 June 2017 07:08

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

9.2.0.5.0

Version no longer supported and not a terminal release.

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663853 is a reply to message #663817]

Wed, 21 June 2017 07:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

hitesh.bhatt wrote on Wed, 21 June 2017 00:00

Hi,

it's taking long time and hangs.

Thanks,
Hitesh

Does it ever finish?

Where is time being spent?

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663854 is a reply to message #663853]

Wed, 21 June 2017 07:39

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi,

Yes its still running from last 22 hours and Time remaining it shows 9 hours
As per the trace file its insert into OBJ$ table.

Kindly let me know for more details

Thanks,
Hitesh

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663855 is a reply to message #663854]

Wed, 21 June 2017 07:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

We need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663856 is a reply to message #663855]

Wed, 21 June 2017 07:59

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

I assume you got the 9 hours from v$session_longops? what exactly does that view say the session is doing.
Also post the trace file.

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663863 is a reply to message #663854]

Wed, 21 June 2017 08:36

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Kindly let me know for more details

Michel Cadot wrote on Wed, 21 June 2017 09:14

...
Check locks.

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663864 is a reply to message #663863]

Wed, 21 June 2017 08:49

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi,

Yes, v$session_longops - it shows TABLE Scan.
In Locks it shows lock on OBJ$ table.

I will help you with all the requested details soon.
I may not be able to provide full Trace file, as it takes long time to complete.
So will execute the same script for some time and provide trace file.

Many Thanks in advance.

Report message to a moderator

Re: While creating of M-View, lock the SYS.OBJ$ table [message #663865 is a reply to message #663864]

Wed, 21 June 2017 09:14

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

In Locks it shows lock on OBJ$ table.

How many? Waiting or holding locks?
What does v$session_wait say for this session?

Quote:

I may not be able to provide full Trace file,

Just provide it while the session is waiting.

Report message to a moderator

Previous Topic:	Gathering Statistics of Partitioned table
Next Topic:	Full table scan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 21:01:05 CST 2025