I meant to post this ages ago but forgot until I came across the issue again today.

When developing a system which runs against an Oracle Database, Oracle XE can be very useful. XE is a cut down version of Oracle's standard edition Database and is available completely free for development purposes. It is built from the same code base as the standard edition Database, albeit with a very restricted configuration to stop people deploying it in production in lieu of a paid for version.

One of the restrictions in XE is that the number of concurrent sessions and processes that the Database will allow is set to a very low figure, again presumably to deter from production use. This can result in issues during development, whereby seemingly innocuous tasks such as running automated test suites will cause the database to hit the default limits and fail.

The errors that are raised when these limits are reached are often apparently unrelated to the task being performed. Once learned however these errors are easy to spot, this is one reason why it's useful to know about this default limitation of XE. Below are some of the errors which can manifest from Oracle running out of sessions/ processes:

• ORA-00018: maximum number of sessions exceeded
• ORA-00020: maximum number of processes (%s)
• TNS Name Not Found - yep this really can be raised when the Database runs out of  sessions or processes!
  

 1: Log on to the Database with a sysdba account.

sqlplus system@XE as sysdba

2: Check the current session and processes limits

SQL> show parameter session

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     20
shared_server_sessions               integer

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     4
log_archive_max_processes            integer     2
processes                            integer     20

3: Increase the session and processes limits to something sensible, the important thing here is to choose values which solve your particular issue. I tend to find that anything around 250 is fine for most development projects, but remember that the higher the values the more resources Oracle will try and consume when it needs to maintain those extra sessions and processes.

SQL> alter system set sessions=300 scope=spfile;

System altered.

SQL> alter system set processes=300 scope=spfile;

System altered.

4: Shutdown the Database instance.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

5: Restart the Database instance.

I don't know why but issuing the 'startup' command doesn't work for me when using XE. I think this may have something to do with the Oracle Listener not being able to identify the instance once it has stopped but I haven't yet figured it out. Instead I have to restart the Oracle service/ daemon using the following command:

C:\>net start OracleServiceXE
The OracleServiceXE service is starting.....
The OracleServiceXE service was started successfully.

Note: The above is obviously a windows example, I believe that there is usually an /etc/init.d script which has the same effect under a Linux installation.

6: Close the SQLPLUS session and open a new one (otherwise you will receive an error when entering commands as the session cannot persist across the Database restart).

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

C:\development\stsworkspace\compliance>sqlplus system@XE as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 20 17:13:49 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

7: Check that the new values have been persisted across the restart and are now in effect. 

SQL> show parameter session

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     335
shared_server_sessions               integer
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     4
log_archive_max_processes            integer     2
processes                            integer     300

Note: I'm not yet sure why but my 'sessions' value always seems to be higher than the value I set. Regardless the value should be different from the initial value and close to the one you set (in my case it is usually above by a value of 15 or 35!)

et voila! That should do it.

Hope that helps someone,

EdD

I recently inherited an application which has a few issues with referential integrity, unfortunately the existing constraints were not named when they were created so I see lots of this:

ORA-02291: integrity constraint (CCRS_OWNR2.SYS_C00293422) violated - parent key not found

At this point I usually end up yelling something along the lines of ' What the badgering folly is SYS_C00293422?!?! Why didn't you name your constraints you massive uberbastards!!!' at the closest unsuspecting random in the office - I fear this practice is making me unpopular.

So to lessen the damage to my office popularity I set about figuring out how to trace the table from which a constraint originates, based on the constraint name. Turns out it's pretty easy as Oracle (I'm using 10g) provides a table (or maybe a view?)  called 'all_constraints' which describes constraint definitions on tables accessible to the current user.

describe all_constraints;
Name                           Null     Type
------------------------------ -------- ------------
OWNER                          NOT NULL VARCHAR2(30)
CONSTRAINT_NAME                NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE                         VARCHAR2(1)
TABLE_NAME                     NOT NULL VARCHAR2(30)
SEARCH_CONDITION                        LONG()
R_OWNER                                 VARCHAR2(30)
R_CONSTRAINT_NAME                       VARCHAR2(30)
DELETE_RULE                             VARCHAR2(9)
STATUS                                  VARCHAR2(8)
DEFERRABLE                              VARCHAR2(14)
DEFERRED                                VARCHAR2(9)
VALIDATED                               VARCHAR2(13)
GENERATED                               VARCHAR2(14)
BAD                                     VARCHAR2(3)
RELY                                    VARCHAR2(4)
LAST_CHANGE                             DATE
INDEX_OWNER                             VARCHAR2(30)
INDEX_NAME                              VARCHAR2(30)
INVALID                                 VARCHAR2(7)
VIEW_RELATED                            VARCHAR2(14)

20 rows selected

We can use this table to identify various properties of a given constraing, based on the constraint name, like in the following example:

select * from all_constraints where owner = '&&schemaName' and constraint_name = '&&constraintName';

One great thing about this method is that it does not require sysdba privileges, this is a major benefit for developers working on locked down schemas as it allows for cracking straight on rather than having to borrow the time of a neighbouring DBA.

This approach is useful in identifying constraint origins however it is frustrating to have to do in the first place, instead I would advocate coming up with a simple naming convention as demonstrated here and using that throughout the Database, this is something that will save the development team time and energy, particularly when your Database grows to tens or hundreds of tables each with several constraints.

Cheers,

EdD

I discovered today that in Oracle 10g (and I presume in earlier versions also) it is not possible to directly assign a sequence value in to a PLSQL variable, hence neither of the 2 following examples will compile in 10g:

1:

PROCEDURE set_up_test_data(no_of_days NUMBER)
IS
  -- Doesn't compile
  nice_unique_number NUMBER := mysequence.nextval;
BEGIN
  -- do stuff here...
END set_up_test_data;

2:

PROCEDURE set_up_test_data(no_of_days NUMBER)
IS
  nice_unique_number NUMBER;
BEGIN
  -- Doesn't compile.
  nice_unique_number := mysequence.nextval;
  -- do stuff here...
END set_up_test_data;

A bit of googling suggests that a common workaround to this is to be do a SELECT INTO using the dual table, as below:

PROCEDURE set_up_test_data(no_of_days NUMBER)
IS
  nice_unique_number NUMBER;
BEGIN
  --Works nicely
  SELECT mysequence.nextval INTO nice_unique_number FROM dual;
  -- do stuff here...
END set_up_test_data;

Note: This no appears to be an issue as of Oracle 11g (search for Straight Sequences).

EdD