Home » SQL & PL/SQL » SQL & PL/SQL » 'Create table' statement is hanging (SQL*Plus: Release 11.2.0.3.0 Production)
'Create table' statement is hanging [message #681091] Mon, 15 June 2020 05:19 Go to next message
aidi-h
Messages: 45
Registered: November 2005
Member
The 'CREATE TABLE' statement is hanging when I am running "CREATE TABLE m2sescal_calls AS SELECT...".
Test 1
Session 1) open up a Linux session using my personal account, login to sqlplus to the DB and run the above 'CREATE TABLE....' statement, it hangs
Test 2
Session 1) open up a Linux session using my personal account, login to sqlplus to the DB
Session 2) open up a 2nd Linux session using the same account and login to sqlplus to the DB and run the same above 'CREATE TABLE....' statement, the statement completes and builds the table.
Test 3
Session 1) logged in as the oracle user and this built the table successfully.
Checking v$locks didnt find anything. Any clues what might cause this as it seems very strange.
Re: 'Create table' statement is hanging [message #681092 is a reply to message #681091] Mon, 15 June 2020 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I'd say the same thing than I did in your previous SQL query from v$session and v$lock hangs topic:

Michel Cadot wrote on Fri, 07 March 2008 18:25
Activate trace and see where it is waiting.

Regards
Michel

Note: besides we didn't receive any feedback from you in this topic.

[Updated on: Mon, 15 June 2020 05:40]

Report message to a moderator

Re: 'Create table' statement is hanging [message #681094 is a reply to message #681091] Mon, 15 June 2020 10:58 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Possibly easier than tracing (though less comprehensive) is
select sid,event from v$session where wait_time=0 and wait_class <> 'Idle';
that will show the session(s) that are hanging and why.
Re: 'Create table' statement is hanging [message #681103 is a reply to message #681094] Tue, 16 June 2020 07:59 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Also posted to https://community.oracle.com/thread/4334700
Re: 'Create table' statement is hanging [message #681104 is a reply to message #681103] Tue, 16 June 2020 12:44 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Remember the create table command is NOT hanging, the query to make the data rows is what is hanging. If you just need the structure without any data rows (for now) add the line "and 1=2" into the where clause and it will build the table instantly but not bother to populate it
Previous Topic: get closer to datetime in two tables
Next Topic: Query not returning correct values (merged)
Goto Forum:
  


Current Time: Thu Mar 28 11:57:33 CDT 2024