1 2 Previous Next 15 Replies Latest reply on Oct 8, 2012 3:27 PM by Osama_Mustafa

Connecting to the database taking long time to connect database server

ChakravarthyDBA Oct 6, 2012 12:23 PM

Hi

When I execute procedure i am getting the below message at bottom of the Oracle SQL Developer

"Connecting to the database"

it is taking more than 10 min plz guide

1. Re: Connecting to the database taking long time to connect database server

Peter Häusler Oct 6, 2012 12:29 PM (in response to ChakravarthyDBA)

Hello,
have you installed a normal Oracle Client also on your Host?
Did you connect with host:port:sid or with a Oracle Naming Service?
Can you test tnsping <alias>

Did other user have the same problem?
Did you connect through WAN or LAN connection?
Can you tell more about you client/database setup?

regards
Peter
Like Show 0 Likes(0)

Actions
2. Re: Connecting to the database taking long time to connect database server

mseberg Oct 6, 2012 12:51 PM (in response to ChakravarthyDBA)

Hello;

If you are running Unix/Linux try this :

add machine name to /etc/hosts like this

127.0.0.1 localhost.localdomain localhost
10.10.20.154 priimary.localdomain primary

remove any nameserver entry from /etc/resolv.conf on server

Best Regards

mseberg
Like Show 0 Likes(0)

Actions
3. Re: Connecting to the database taking long time to connect database server

ChakravarthyDBA Oct 6, 2012 1:37 PM (in response to Peter Häusler)

Hi

have you installed a normal Oracle Client also on your Host? normal Oracle Client
Did you connect with host:port:sid or with a Oracle Naming Service? through TNS Service
Can you test tnsping <alias> yes, It is working fine

Did other user have the same problem? yes
Did you connect through WAN or LAN connection? LAN (Intranet)
Can you tell more about you client/database setup?

Database setup:
OS: Window 2008 server
version: 11.1.0

Client: 11.1.0
OS: Window 2008 server

Now I am not able to execute single select query which table contains 6 records and 15 columns it is taking long time I have waited 30 min still no resutls

only one table is behaving like this remaining is working fine

Edited by: user9235224 on Oct 6, 2012 7:06 PM
Like Show 0 Likes(0)

Actions
4. Re: Connecting to the database taking long time to connect database server

Mark D Powell Oct 6, 2012 1:53 PM (in response to ChakravarthyDBA)

Does the one table you are having trouble querying contain a LOB column (BLOB, CLOB, or BFILE)?
- -
Did you download the latest version of SQL Developer directly from Oracle or did you install SQL Developer as part of the Oracle client installation? You want to use the most recent version so if you installed via the client go get the most current version.
- -
HTH -- Mark D Powell --
Like Show 0 Likes(0)

Actions
5. Re: Connecting to the database taking long time to connect database server

mseberg Oct 6, 2012 2:03 PM (in response to ChakravarthyDBA)

Hello again;

Would check the network. Get a network packet capture software like Wireshark to determine what the client computer is doing when a connection attempt is initiated.

http://www.wireshark.org/

Try a connect directly from the server, does it still take 10 minutes? If it does make sure parameters like OPEN_CURSORS are not set crazy high.

Best Regards

mseberg
Like Show 0 Likes(0)

Actions
6. Re: Connecting to the database taking long time to connect database server

sanikv Oct 6, 2012 2:11 PM (in response to mseberg)

1) connect to sqlpus
2) identify your sid ( select distinct sid from v$mystat)
3) fire your problematic query

take another session:

1) check what your session is doing
select event, seconds_in_wait from v$session where sid=<sid from step2>
Like Show 0 Likes(0)

Actions
7. Re: Connecting to the database taking long time to connect database server

Osama_Mustafa Oct 7, 2012 7:51 AM (in response to ChakravarthyDBA)

Did you try alternative tools such as
Toad , Pl/sql developer

and did you unchecked Check for updated option , and network ??
Like Show 0 Likes(0)

Actions
8. Re: Connecting to the database taking long time to connect database server

ChakravarthyDBA Oct 8, 2012 6:23 AM (in response to Osama_Mustafa)

Below is the message I got in trace file

*** 2012-10-08 08:38:56.750
*** SESSION ID:(158.1) 2012-10-08 08:38:56.750
*** CLIENT ID:() 2012-10-08 08:38:56.750
*** SERVICE NAME:() 2012-10-08 08:38:56.750
*** MODULE NAME:() 2012-10-08 08:38:56.750
*** ACTION NAME:() 2012-10-08 08:38:56.750

Dead transaction 0x0001.018.00008954 recovered by SMON
Parallel Transaction recovery caught exception 30319

*** 2012-10-08 11:15:01.546
Parallel Transaction recovery caught error 30319

*** 2012-10-08 11:17:32.233
Parallel Transaction recovery caught exception 30319
Parallel Transaction recovery caught error 30319

can you please elaborate where is the problem
Like Show 0 Likes(0)

Actions
9. Re: Connecting to the database taking long time to connect database server

Osama_Mustafa Oct 8, 2012 6:50 AM (in response to ChakravarthyDBA)

SELECT *from X$KTUXE where KTUXECFL='DEAD';
Like Show 0 Likes(0)

Actions
10. Re: Connecting to the database taking long time to connect database server

ChakravarthyDBA Oct 8, 2012 7:07 AM (in response to Osama_Mustafa)

giving an error table not existed
Like Show 0 Likes(0)

Actions
11. Re: Connecting to the database taking long time to connect database server

ChakravarthyDBA Oct 8, 2012 7:32 AM (in response to ChakravarthyDBA)

Here is the result

ADDR INDX INST_ID KTUXEUSN KTUXESLT KTUXESQN
---------------- ---------- ---------- ---------- ---------- ----------
KTUXERDBF KTUXERDBB KTUXESCNB KTUXESCNW KTUXESTA
---------- ---------- ---------- ---------- ----------------
KTUXECFL KTUXEUEL KTUXEDDBF KTUXEDDBB KTUXEPUSN KTUXEPSLT
------------------------ ---------- ---------- ---------- ---------- ----------
KTUXEPSQN KTUXESIZ
---------- ----------
00000000106DBC48 237 1 5 3 53895
0 0 130949497 0 INACTIVE
DEAD -1 0 0 0 0
0 0
Like Show 0 Likes(0)

Actions
12. Re: Connecting to the database taking long time to connect database server

Osama_Mustafa Oct 8, 2012 7:49 AM (in response to ChakravarthyDBA)

KTUXESTA will give you the transaction status for any given transaction and KTUXEFL will give the transaction flag (DEAD if it is DEAD transaction) and the
KTUXESTA will show the STATUS of the transaction (INACTIVE/ACTIVE/COMMITED)
Like Show 0 Likes(0)

Actions
13. Re: Connecting to the database taking long time to connect database server

ChakravarthyDBA Oct 8, 2012 1:26 PM (in response to Osama_Mustafa)

Any more help
Like Show 0 Likes(0)

Actions
14. Re: Connecting to the database taking long time to connect database server

Mark D Powell Oct 8, 2012 1:30 PM (in response to ChakravarthyDBA)

Normally in order to execute a procedure you would have to be connected to an Oracle instance. Exactly what process did you follow to execute the procedure?

What does the procedure do? Does it issue remote queries?

If by "not existed" you mean "table or view does not exist" that is an ORA-00942 error this could be an indicaton you have a missing grant.

Does the target database have any database event logon triggers?

HTH -- Mark D Powell --
Like Show 0 Likes(0)

Actions

1 2 Previous Next

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.