Home » RDBMS Server » Server Administration » select a table cause the session hang (11.2.0.1.0 windows)
select a table cause the session hang [message #518617] Fri, 05 August 2011 01:54 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
when i modify a column of table tb_hxl_user,just as flowing:

Alter Table TB_HXL_USER Modify CREATE_DATE Not Null



now i select the table TB_HXL_USER,just as flowing:

select * from TB_HXL_USER;



it is hang,and i found the wait event is library cache lock in v$session_waits,why?
how can i avoid this.



Re: select a table cause the session hang [message #518621 is a reply to message #518617] Fri, 05 August 2011 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no reason.
Are you in RAC?
Is this a big table?
Any message in alert.log?
What is the size of the SGA?

Regards
Michel

[Edit: Add new questions]

[Updated on: Fri, 05 August 2011 02:09]

Report message to a moderator

Re: select a table cause the session hang [message #518624 is a reply to message #518621] Fri, 05 August 2011 02:31 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Hi,
There is a stoneone db, not rac,and the sga is 1GB,the rows of the table is about 428,120,736.
The alert.log have any alert log.
Re: select a table cause the session hang [message #518628 is a reply to message #518624] Fri, 05 August 2011 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does it still take so long now (note that it may take long with 428 million rows, by the way how many blocks?).
Did you execute the "select *" just after the "alter table" or after a while?

Regards
Michel
Re: select a table cause the session hang [message #518631 is a reply to message #518628] Fri, 05 August 2011 02:53 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Hi,
when the alter table command have finish,the sql select * from tb_hxl_user can run quickly.but i don't know why the select hang when modify the table.
Re: select a table cause the session hang [message #518634 is a reply to message #518631] Fri, 05 August 2011 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ahh! you didn't say it is DURING the DDL.
During a DDL all statements are stopped in the library cache until the DDL ends otherwise it could return inconsistent result.

Regards
Michel
Re: select a table cause the session hang [message #518638 is a reply to message #518634] Fri, 05 August 2011 03:18 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
your means is user can not access table when modify the table? but i want to modify a column to not null of a table on 7*24 bussiness,how can i do?
Re: select a table cause the session hang [message #518639 is a reply to message #518638] Fri, 05 August 2011 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_redefinition package.

Regards
Michel
Re: select a table cause the session hang [message #518642 is a reply to message #518639] Fri, 05 August 2011 03:29 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks! i will try it.
Previous Topic: Login failed in Oracle 11g
Next Topic: Upgrade 9i to 11g
Goto Forum:
  


Current Time: Thu Jul 31 03:30:07 CDT 2014

Total time taken to generate the page: 0.09441 seconds