1 2 Previous Next 15 Replies Latest reply: Jul 29, 2009 2:00 AM by 714495 RSS

    Max connections in Oracle 10g Express

    530677
      Hi,

      How much connections are possible in Oracle 10g Express ?
        • 1. Re: Max connections in Oracle 10g Express
          509719
          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
            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
              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
                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
                  I don't understand your question.
                  • 6. Re: Max connections in Oracle 10g Express
                    27876
                    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
                      Ok,

                      after 19 connections
                      • 8. Re: Max connections in Oracle 10g Express
                        3520
                        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
                          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
                            What's your processes setting in both database ?

                            SQL> show parameter processes
                            • 11. Re: Max connections in Oracle 10g Express
                              509719
                              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
                                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
                                  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
                                    Hi,

                                    >>and restart db :)
                                    Exactly ;-)

                                    Cheers
                                    1 2 Previous Next