This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jul 29, 2009 12:00 AM by 714495 RSS

Max connections in Oracle 10g Express

530677 Newbie
Currently Being Moderated
Hi,

How much connections are possible in Oracle 10g Express ?
  • 1. Re: Max connections in Oracle 10g Express
    509719 Newbie
    Currently Being Moderated
    I dont think Oracle enforces max user connections internally. You can restrict them using initialization parameters though. There are restrictions on CPUs and nodes for licensing.

    from Oracle documentaiton:

    Any use of the Oracle Database Express Edition is subject to the following limitations;
    1. Express Edition is limited to a single instance on any server;
    2. Express Edition may be installed on a multiple CPU server, but may only be executed on one processor in any server;
    3. Express Edition may only be used to support up to 4GB of user data (not including Express Edition system data);
    4. Express Edition may use up to 1 GB RAM of available memory.
  • 2. Re: Max connections in Oracle 10g Express
    3520 Explorer
    Currently Being Moderated
    As other poster already said there aren't any hardcoded limits of connections rather these are limited by limits of available memory to this Oracle version.

    Gints Plivna
    http://www.gplivna.eu
  • 3. Re: Max connections in Oracle 10g Express
    530677 Newbie
    Currently Being Moderated
    I do the following test:

    I format the test PC, install the O.S. and install Oracle 10g Express and run the following code:

    package testedb;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;

    public class TesteDB {

         /**
         * @param args
         */
         public static void main(String[] args) {

              List<Connection> connections = new ArrayList<Connection>();
              
              try
         {

         Class.forName ("oracle.jdbc.driver.OracleDriver");
         
         while(true)
         {
              connections.add(DriverManager.getConnection("jdbc:oracle:thin:@albatroz:1521:XE", "system", "xxx"));
              
              //connections.add(DriverManager.getConnection("jdbc:oracle:thin:@albatroz:1521:MYDB", "system", "xxx"));
              
              System.out.println("connections: "+connections.size());
              
         }
         
         }
         catch (ClassNotFoundException e)
         {

         System.out.println ("Could not load the driver");

         e.printStackTrace ();

         } catch (SQLException e) {
              System.out.println("----------------------------------------------------------");
                   System.out.println("Qtd of connections: "+connections.size());
                   System.out.println("----------------------------------------------------------");
                   e.printStackTrace();
              }
         }
    }


    After the first test I format again the PC, install the O.S. and install Oracle 10g Standard and run the same code, changing only the database name.

    Well, I get the following results:

    - Oracle 10g Express: I get 19 connections;
    - Oracle 10g Standard: I get 181 connection;


    Final report:

    With the same hardware (1GB RAM) and O.S. (Windows XP Professional) test, the Oracle 10g Express has any restrictions at the question max connections.

    Message was edited by: Hugo Haas
    Hugo Haas
  • 4. Re: Max connections in Oracle 10g Express
    27876 Newbie
    Currently Being Moderated
    And assuming that there was an error returned in both cases, what the errors were in the two tests?
  • 5. Re: Max connections in Oracle 10g Express
    530677 Newbie
    Currently Being Moderated
    I don't understand your question.
  • 6. Re: Max connections in Oracle 10g Express
    27876 Newbie
    Currently Being Moderated
    When you ran your code to test how many connections can be opened, after a certain number of connections, you would have received an Oracle error.

    If you did, what was the output of the e.printStackTrace() ?
  • 7. Re: Max connections in Oracle 10g Express
    530677 Newbie
    Currently Being Moderated
    Ok,

    after 19 connections
  • 8. Re: Max connections in Oracle 10g Express
    3520 Explorer
    Currently Being Moderated
    Not quite sure that SGA size and other memory related parameters are set on the possible maximum by default
    for example for my home computer these are as follows (I'm rather sure haven't touched them):
    Connected to:
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

    SQL> show parameter sga

    NAME TYPE VALUE
    ------------------------------------ ----------- ----------------------
    lock_sga boolean FALSE
    pre_page_sga boolean FALSE
    sga_max_size big integer 140M
    sga_target big integer 140M
    SQL> show parameter pga

    NAME TYPE VALUE
    ------------------------------------ ----------- ----------------------
    pga_aggregate_target big integer 40M
    SQL>

    Gints Plivna
    http://www.gplivna.eu
  • 9. Re: Max connections in Oracle 10g Express
    530677 Newbie
    Currently Being Moderated
    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12519, TNS:no appropriate service handler found
    The Connection descriptor used by the client was:
    localhost:1521:XE

         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:380)
         at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
         at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:441)
         at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
         at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:839)connections: 16
    connections: 17
    connections: 18
    connections: 19
    ----------------------------------------------------------
    Qtd of connections: 19
    ----------------------------------------------------------

         at java.sql.DriverManager.getConnection(Unknown Source)
         at java.sql.DriverManager.getConnection(Unknown Source)
         at testedb.TesteDB.main(TesteDB.java:27)
  • 10. Re: Max connections in Oracle 10g Express
    247514 Expert
    Currently Being Moderated
    What's your processes setting in both database ?

    SQL> show parameter processes
  • 11. Re: Max connections in Oracle 10g Express
    509719 Newbie
    Currently Being Moderated
    I think you are connecting too fast to Oracle. There is a limit on how many connections can be handled concurrently by listener.
    This may be an issues.

    Change the value of QUEUESIZE as defined below and try it again. Also you can put a sleep of 2 seconds between connections and try.

    listener_name=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521)(QUEUESIZE=50)))
  • 12. Re: Max connections in Oracle 10g Express
    Eduardo Legatti Explorer
    Currently Being Moderated
    Hi,

    >>How much connections are possible in Oracle 10g Express ?

    By default, Oracle XE has the parameter PROCESSES=40. This parameter specifies the maximum number of OS user processes that connect to the Oracle instance. This number must take into account the background processes and the login process that started the instance.

    Take a look on this below, I just open one connection:
    SQL> show parameter processes

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    processes                            integer     40

    SQL> select count(*) from v$process;

      COUNT(*)
    ----------
            21
    SQL>
    This mean that left just 19 processes that Oracle will accept. In this case, you are able to open just 19 sessions, for example using SQL*PLUS.

    >>Well, I get the following results: - Oracle 10g Express: I get 19 connections;
    It is therefore that you it is able to only open 19 connections. If you try to open one more session after 19th, you will receive this error below:
    ERROR:
    ORA-00020: maximum number of processes (%s) exceeded


    Then in your case, I advice you to increase the value of the PROCESSES parameter.
    e.g: alter system set processes=100 scope=spfile.

    Cheers
  • 13. Re: Max connections in Oracle 10g Express
    3520 Explorer
    Currently Being Moderated
    Then in your case, I advice you to increase the value
    of the PROCESSES parameter.
    e.g: alter system set processes=100
    scope=spfile.
    and restart db :)

    Yea I was wrong, default value for processes is the first barrier you'll meet.

    Gints Plivna
    http://www.gplivna.eu
  • 14. Re: Max connections in Oracle 10g Express
    Eduardo Legatti Explorer
    Currently Being Moderated
    Hi,

    >>and restart db :)
    Exactly ;-)

    Cheers
1 2 Previous Next