Default Schema in Oracle Connection URL

oracle jdbc connection-string
netic picture netic · Mar 1, 2010 · Viewed 77k times · Source

I'd like to set default database schema in Oracle Connection URL

jdbc:oracle:thin:@<server>:<port1521>:<sid>

My sample SQL statement:

select monkey_name from animals.monkey

I need to query database without schema prefix animals. i.e. when I run this statement

select monkey_name from monkey

it will use animals schema by default.

What do I need to specify in connection URL above get such effect?

Thanks.

Answer

Gary Myers picture Gary Myers · Mar 1, 2010

You can't put anything in the connection URL.

In Oracle each user has their own schema (even if doesn't contain any objects) and that is their default schema. Once logged in/connected, they can change their default schema with an

ALTER SESSION SET CURRENT_SCHEMA=animals

So you'd need to do the extra statement after connecting. It is possible to have a logon trigger on the user and/or database that will run this when they log in. I'd personally prefer an explicit statement when an application connects.

Related questions

Java JDBC - How to connect to Oracle using Service Name instead of SID

I have a Java application that uses JDBC (via JPA) that was connecting to a development database using hostname, port and Oracle SID, like this: jdbc:oracle:thin:@oracle.hostserver1.mydomain.ca:1521:XYZ XYZ was the Oracle SID. Now I …

Read More
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

I have installed Oracle 11g Express Edition Release 2 in my windows 7 64 bit OS and tried to execute JDBC program, then I got the following error: java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does …

Read More
No more data to read from socket error

We are using Oracle as the database for our Web application. The application runs well most of the time, but we get this "No more data to read from socket" error. Caused by: java.sql.SQLRecoverableException: No more data to …

Read More