Home » RDBMS Server » Performance Tuning » While creating of M-View, lock the SYS.OBJ$ table (SunOS 5.8 - 9.2.0.5.0)
While creating of M-View, lock the SYS.OBJ$ table [message #663809] Tue, 20 June 2017 23:45 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the question is?

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 Go to previous messageGo to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Hi,

it's taking long time and hangs.

Thanks,
Hitesh
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
9.2.0.5.0
Version no longer supported and not a terminal release.
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 Go to previous messageGo to next message
BlackSwan
Messages: 25751
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?
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 25751
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
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 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Kindly let me know for more details
Michel Cadot wrote on Wed, 21 June 2017 09:14

...
Check locks.
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 Go to previous messageGo to next message
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.
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 Go to previous message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
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.

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


Current Time: Sun Dec 17 19:59:26 CST 2017

Total time taken to generate the page: 0.00841 seconds