Feeds:
文章
评论

Archive for the ‘Oracle’ Category

 

Dedicated Servers Shared Servers Database Resident Connection Pooling

When a client request is received, a new server process and a session are created for the client.

When the first request is received from a client, the Dispatcher process places this request on a common queue. The request is picked up by an available shared server process. The Dispatcher process then manages the communication between the client and the shared server process.

When the first request is received from a client, the Connection Broker picks an available pooled server and hands off the client connection to the pooled server.

If no pooled servers are available, the Connection Broker creates one. If the pool has reached its maximum size, the client request is placed on the wait queue until a pooled server is available.

Releasing database resources involves terminating the session and server process.

Releasing database resources involves terminating the session.

Releasing database resources involves releasing the pooled server to the pool.

Memory requirement is proportional to the number of server processes and sessions. There is one server and one session for each client.

Memory requirement is proportional to the sum of the shared servers and sessions. There is one session for each client.

Memory requirement is proportional to the number of pooled servers and their sessions. There is one session for each pooled server.

Session memory is allocated from the PGA.

Session memory is allocated from the SGA.

Session memory is allocated from the PGA.

Read Full Post »

What is JDBC and what is it used for?

JDBC is a set of classes and interfaces written in Java to allow other Java programs to send SQL statements to a relational database management system.Oracle provides three categories of JDBC drivers:

  • JDBC Thin Driver (No local Net8 installation required/ handy for applets)
  • JDBC OCI for writing stand-alone Java applications
  • JDBC KPRB driver (default connection) for Java Stored Procedures and Database JSP’s.

Oracle’s JDBC Thin driver uses Java sockets to connect directly to Oracle. It provides its own TCP/IP version of Oracle’s Net8 (SQL*Net) protocol. Because it is 100% Java, this driver is platform independent and can also run from a Web Browser (applets).

Oracle’s JDBC OCI drivers uses Oracle OCI (Oracle Call Interface) to interact with an Oracle database. You must use a JDBC OCI driver appropriate to your Oracle client installation. The OCI driver works through either SQL*Net or Net8.

  • JDBC OCI7 works with an Oracle7 client.
  • JDBC OCI8 works with an Oracle8 client.

Either of these client versions can access Oracle7 or Oracle8 servers.The JDBC OCI drivers allow you to call the OCI directly from Java, thereby providing a high degree of compatibility with a specific Version of Oracle. Because they use native methods, they are platform specific.

Oracle’s JDBC KBPR driver is mainly used for writing Java stored procedures, triggers and database JSPs. It uses the default/ current database session and thus requires no additional database username, password or URL.All three drivers support the same syntax and API’s. Oracle needs three drivers to support different deployment options. Looking at source code, they will only differ in the way you connect to the database. Remember, you must use a JDBC version that matches the version of your Java Development Kit.

How does one connect with the JDBC Thin Driver?

The JDBC thin driver provides the only way to access Oracle from the Web (applets). It is smaller and faster than the OCI drivers, and doesn’t require a pre-installed version of the JDBC drivers.

import java.sql.*;

class dbAccess {
  public static void main (String args []) throws SQLException
  {
        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

        Connection conn = DriverManager.getConnection
             (“jdbc:oracle:thin:@hostname:1526:orcl”, “scott”, “tiger”);
                             // @machineName:port:SID,   userid,  password

        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(“select BANNER from SYS.V_$VERSION”);
        while (rset.next())
              System.out.println (rset.getString(1));   // Print col 1
        stmt.close();
  }
}

How does one connect with the JDBC OCI Driver?

One must have Net8 (SQL*Net) installed and working before attempting to use one of the OCI drivers.

import java.sql.*;

class dbAccess {
  public static void main (String args []) throws SQLException
  {
        try {
              Class.forName ("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
              e.printStackTrace();
        }

        Connection conn = DriverManager.getConnection
             ("jdbc:oracle:oci8:@hostname_orcl", "scott", "tiger");
                     // or oci7 @TNSNames_Entry,    userid,  password

        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
        while (rset.next())
              System.out.println (rset.getString(1));   // Print col 1
        stmt.close();
  }
}

How does one connect with the JDBC KPRB Driver?

One can obtain a handle to the default or current connection (KPRB driver) by calling the OracleDriver.defaultConenction() method. Please note that you do not need to specify a database URL, username or password as you are already connected to a database session. Remember not to close the default connection. Closing the default connection might throw an exception in future releases of Oracle. 

import java.sql.*;
class dbAccess {
  public static void main (String args []) throws SQLException
  {
        Connection conn = (new oracle.jdbc.driver.OracleDriver()).defaultConnection();

        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
        while (rset.next())
              System.out.println (rset.getString(1));   // Print col 1
        stmt.close();
  }
}

Ref: http://www.orafaq.com/faqjdbc.htm#JDBC

Read Full Post »

When using two JDBC connections accessing the same Oracle database, “SqlException : No more data to read from socket” thrown.

This is  because your database doesn’t support connection sharing, or it’s a dedicated server that will not respond to different clients.

Solution 1:

  1. $ dbca
  2. –Configuration & Migration Tool
  3. —-database Configuration Assistant
  4. Select radio button —Configure Data base options in database
  5. Select your database—e.g. Oracle or pspl
  6. Select —– shared server mode

Solution 2 (same to 1 but manual work):

  1. open $ORACLE_HOME/network/admin/tnsnames.ora
  2. change “(SERVER = DEDICATED)” to “(SERVER = SHARED)”
  3. restart database and listener

Now your database is sharable, so we can use from different client.

Thanks http://www.orafaq.com/forum/t/47457/0/

Read Full Post »

Raimonds Simanovskis is "planning to create activerecord-oracle-enhanced-adapter where to put my monkey patches of original adapter". It’s a good news since Rails Oracle Adapter svn respository has been in silence for a few months.

This guy posted an idea "Oracle adapter for Ruby on Rails" on Oracle MIX 3 months ago, but seems that he didn’t get much response/support from Oracle, so he decided to run on his own.

Shall we give him a hand?

Update: He is not planning but doing it now,  see process at  http://github.com/rsim/oracle-enhanced/

Read Full Post »