OraFAQ Forum: Server Administration » select a table cause the session hang

Home » RDBMS Server » Server Administration » select a table cause the session hang (11.2.0.1.0 windows)

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

select a table cause the session hang [message #518617]

Fri, 05 August 2011 01:54

andy huang
Messages: 485
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.

Report message to a moderator

Re: select a table cause the session hang [message #518621 is a reply to message #518617]

Fri, 05 August 2011 02:04

Michel Cadot
Messages: 54129
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

andy huang
Messages: 485
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.

Report message to a moderator

Re: select a table cause the session hang [message #518628 is a reply to message #518624]

Fri, 05 August 2011 02:46

Michel Cadot
Messages: 54129
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

Report message to a moderator

Re: select a table cause the session hang [message #518631 is a reply to message #518628]

Fri, 05 August 2011 02:53

andy huang
Messages: 485
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.

Report message to a moderator

Re: select a table cause the session hang [message #518634 is a reply to message #518631]

Fri, 05 August 2011 03:10

Michel Cadot
Messages: 54129
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

Report message to a moderator

Re: select a table cause the session hang [message #518638 is a reply to message #518634]

Fri, 05 August 2011 03:18

andy huang
Messages: 485
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?

Report message to a moderator