Intermittent ORA-12519 error on 10g XE

There’s an Oracle error that seems to raise its ugly head every now and again and it occurs on Oracle 10g Express Edition running under 32 bit Windows (which in my case was XP). It happened to me after transferring my test Oracle database from a real to a virtual copy of Windows XP, and when running a series of unit tests on a database module.

The error manifests itself as an ORA-12519 connection refusal error as highlighted by the following partial stack trace:

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:
junit:1521:xe
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:414)

The suggested fix, and this was written two years ago, is to increase the number of available processes by logging on as SYS and issuing the following command:

ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE

…which doesn’t seem like a real fix to me as it just pushes the problem further away.

Yes, I know that Oracle 10g is obsolete and the 11g Express Edition is freely available, but 11g adds yet another layer of unwanted complexity to my requirement for a simple Oracle database.

Installing a MySQL Datasource on Tomcat 7

The default installation of Tomcat doesn’t come with any datasource access, so it’s down to you to set this up and it has to be done manually as there is no wizzo web frontend that’ll do it for you.

The first thing to do is to copy database driver JAR file into the Tomcat lib directory. In my case I’m using the MySQL Community Edition, so I copied mysql-connector-java-5.1.6-bin.jar to Tomcat 7.0\lib.

Oracle EZCONNECT

Oracle EZCONNECT is a database connection technique that does away with the need to look up service names in tnsnames.ora when connecting via a network. It provides for connection by enhancing the original connection string format of:

CONNECT username/password@service_name

The updated format allows for the use of a connection URL to enhance the service_name:

CONNECT username/password@//host[:port][/service_name]

Where host is either the host name or IP address of the database, port is an optional port number defaulting to 1521, whilst service_name is the database service name, which defaults to the host name.

For example:

CONNECT Scott/tiger@//10.101.0.45/employee.world

A complete description of this is available on wikipedia.

Using an Oracle Function Index as a Constraint

Two previous blogs of mine have talked about Oracle’s Function Based Indexes:

• Oracle Function Based Indexes
• Enabling Oracle Function Based Indexes

In what should be the final blog on the subject I’m going to cover a more complex use of function based indexes to enforce a rule-based constraint.

Oracle JDBC Connection Properties

There are several things to remember when configuring an Oracle JDBC driver connection. Firstly, you need to specify the driver. The driver class is oracle.jdbc.driver.OracleDriver located in ojdbc14.jar or ojdbc14_g.jar (the debug version) archive files.

MySQL JDBC Connection Properties

There are several things to remember when configuring a MySQL JDBC driver connection. Firstly, you need to specify the driver and the driver class is com.mysql.jdbc.Driver located in the mysql-connector-java-5.x.x.jar archive file and available for download from http://dev.mysql.com/downloads/connector/j/.

Enabling Oracle Function Based Indexes

A few days ago, I described a simple example of how to create and use Oracle Function Based Indexes. However, and this is a big ‘however’, before you can use Oracle Function Based Indexes, there are several tasks to complete in setting up your database correctly.

SQL*Plus Reminder

This is a reminder of all those Oracle SQL*Plus commands that you and I should know.
1. Saving SQL to a file:
Save filename[.ext] [cre[ate]]|[replace]|[append]
Default ext is SQL.

2. Running a SQL script without loading first:
Start filename
Or on the command line:
Sqlplus user/password@database @filename

Oracle Function Based Indexes

Oracle’s function based indexes are a powerful feature that, from experience, is rarely seen or used by Java developers. I guess that this is because if a Java developer has anything to do with SQL at all, then they’ll usually write their SQL, test it and deliver it to the source control system. At that point, the project’s DBA (if there is one and if he has the time) takes a look at the new SQL and does a bit of tuning if necessary. This makes me think that function based indexes are really a ‘DBA thing’. So, for Java programmers, in this blog I’m going to demonstrate function based indexes using a simple EMPLOYEE table, which we’ll first need to create and populate, so bare with me as this is the boring bit.

SQL Performance - Avoiding Full Table Scans and Other Tips

When and How to Avoid Full Table Scans

You need to avoid full tables scans because they usually slow down the performance of your application. Therefore, you should avoid using them when reading large tables and this can be achieved by judicious use of indexes.

Improving SQL Performance Part - 1

Proper Arrangement of Tables in the FROM Clause

The order in of tables specified in a FROM clause may make a difference to database performance. This dependency is based on how the parser reads the SQL statement. Depending upon the parser, some users have found that listing the larger tables in a from clause last proves to be more efficient.

More SQL Examples

This is a further crib sheet, with lots of SQL examples - well a few any way. It's just a quick of finding things that I don't use very often.

SQL Example Select Statements

SQL Select Clauses Crib Sheet

Adding to yesterday's simple crib-sheet, today's blog is my SQL clauses crib-sheet...

SQL Crib Sheet

This is my SQL crib sheet - 

Database Referential Integrity

Database Referential Integrity is something everyone should know. I know I do; it’s like knowing the colour green, it’s something that is obvious, in fact it’s so obvious that I couldn’t explain it the other day when asked. So here is a brief overview and a quick link to the more in-depth wikipedia explanation.

Database ACID Acronym

This is something I knew, but like a lot of things forgot... and I got asked but couldn’t remember - although I could remember the theory behind it. There is a really good full description of this on wikipedia, so the table below is just something to jog your memory.

Entity Relationship Diagram Reminder

This little blog is a gentle reminder of the components of a simple entity relationship diagram. As with any ‘little’ example, the entities are involved are somewhat contrived. In this case we have a customer table linked to an address table.

PL/SQL Implicit Cursor Attributes

Oracle allows you to access information about the most recently executed SQL statement (INSERT, UPDATE, DELETE and MERGE) by referencing one of the following special implicit cursor attributes:

Installing MySQL on Solaris (Part 4 - Initializing the Database)

Once you've installed MySQL, the next step is to create your database and get the whole thing running. To do this:

1. Change the operating user from root to mysql by typing:

Installing MySQL on Solaris (Part 3 - Running the mysql Demon)

Running MySQL as a Demon
This small blog demonstrates the steps required to start-up MySQL as a demon at boot time. The first step is to copy the mysql file from the installation directory (/opt/mysql/mysql/bin)
to the /etc/init.d directory and ensure that it’s executable (chmod 755 mysql).