A collection of tutorials, code and tools to help you get better using Oracle
July 28, 2016
Using the JDBC setStringAtName method when binding a lot of columns can be much slower by binding by position with the setString method
April 09, 2016
A cursor leak caused by an invalid SQL statement does not show an open cursor in v$open_cursor
July 18, 2014
The steps required to manually create a database on an ASM system.
July 17, 2014
Some instructions on how to create a cold backup of an Oracle database and restore it to a new server.
July 17, 2014
A quick note on how to apply a patchset to an Oracle 11gr2 database.
July 17, 2014
Steps to move a non ASM database into ASM on the same host
March 07, 2014
In a reporting context, a problem that comes up fairly often is having a master table, such as Orders than joins to a detail table, such as Order_Line_Items in a one to many relationship.
January 09, 2014
Many applications connect to the database using a connection pool, and if you need to run a SQL trace on the application, it is slightly more tricky than a process that uses a single database session.
May 15, 2013
If you need to store strings longer than 4000 bytes, then one option is to use CLOBs, but what is the performance cost of doing so?
March 06, 2013
Previously I explored how I created a simple test framework using Ruby, but I skipped discussing more complicated test scenarios which require some pre-test set up. This post will explore some ideas on how to tackle these sort of tests
January 10, 2013
More information on the error ORA-04068 existing state of packages has been discarded when compiling stand alone functions.
October 09, 2012
Expanding on the previous Unit Testing post, this article explores in more detail how to write basic unit test for PLSQL with Ruby
October 03, 2012
I do a lot of programming with Ruby, and a lot of Oracle PLSQL development, and one thing that is hard to miss between the two communities is the attitude toward unit testing.
September 18, 2012
A real world example of a production database that got taken down by a simple procedure that introduced a hard parsing problem.
August 22, 2012
A subtle problem to watch out for when coding TOP N queries against large result sets
June 12, 2012
An interesting but possibly dangerous technique that may be useful in coding up PLSQL APIs whereby a new ref cursor can be returned for each row in a query.
June 11, 2012
It is pretty well known, that you can bind an array of values into a query using PLSQL, and have Oracle treat it like a table, but how does the optimizer handle the lack of statistics?
May 17, 2012
Every now and then a problem appears that seems impossible to solve in a single SQL query, but with Oracle Analytic functions, SQL can be more powerful that you think.
April 24, 2012
If you need to modify tables while the application is online, especially in 10g, give careful thought to how the code is organised to avoid unexpected ORA-04068 errors in the application.
April 17, 2012
Bringing together two previous articles to demonstrate how to pass arrays of oracle record types into Oracle from Java
April 17, 2012
An example of how to pass Oracle record types between Java and Oracle
March 19, 2012
A common pattern is to take take a set of values and update an existing record in the database if it exists or insert a new record if it does not. Simple, but often done wrong.
February 07, 2012
An exploration of an application design I encountered recently that made very little sense to me and the importance of benchmarking your code.
December 09, 2011
All of the steps and working code required to pass arrays from Java to Oracle and back.
December 08, 2011
Inserting records in batches is much faster than inserting the same data one row at a time. This article explains how.
December 08, 2011
How to transparently reduce soft parsing overhead in Java applications with a few connection settings
September 02, 2011
You have a table that is continuously being inserted and you want to copy new records to a different table or system, or take some action on each new row, but you better not forget about consistent reads.
August 25, 2011
Partitioned tables with a foreign key relationship have often led to broken normalisation and problems dropping partitions until Oracle introduced Reference Partitioning in 11g.
August 15, 2011
A common question on the forums, and one that is frequently implemented wrong makes for an interesting discussion
July 19, 2011
Just losing one days work for a team is much more painful than coding and versioning DDL migration scripts, and if done carefully there are powerful side effects, such as selectively removing parts of a release, developer sandboxes and eventually continuous integration, automated testing and all that other good stuff the rest of the software development world enjoys.
July 14, 2011
How do I find the 10 newest rows - this question seems to come up time and time again, and as far as I can tell, just about everyone implements it wrong when they first start out.
July 13, 2011
All these constraints are well and good, but they sure don't come for free. All this checking must cost something in CPU time, but how much?
July 06, 2011
Should you use a Primary Key or just a Unique Index, and what on earth is a Foreign Key?
June 30, 2011
This will not be the last application that uses this database, you can be sure of that. Applications come and go, but the data lives on forever. No matter how good and fancy your application, it isn't going to please your users if the INTEGRITY of the data is not 100%.
June 23, 2011
SQL Trace is an incredibly powerful tool, and allows you to get all sorts of information about a single query, or set of queries. When it is enabled, it is like turning on SQL debug mode, so how does it work?
June 14, 2011
Of all the things I have seen done wrong over the years, selecting functions needlessly from DUAL is probably the most frequent.
June 12, 2011
Written by Tom Kyte, the runstats package can be used to compare the performance of two pieces of code by comparing the number of latches required by each approach.
June 10, 2011
Assuming you have a query, getting the execution plan for it is easy. Log into SQLPLUS as usual and then use the 'explain plan for' command to generate the explain plan.
June 10, 2011
Autotrace is a tool built into SQLPlus that helps a developer evaluate how expensive a piece of SQL is at execution time. It is probably the easiest method of getting information about an SQL statement.
June 06, 2011
Like most software, there is always more than one way to do something, and each way has its own set of compromises and limitations. So how do you know which method to use? You benchmark of course, which leads to the point of this introduction and upcoming topics.
June 06, 2011
Notes on how to create a new database from the command line, without using the DBCA (Database Configuration Assistant)
May 26, 2011
A summary of the key points and commands necessary for online upgrades
May 26, 2011
When you need to change the definition of an existing database column, or fundamentally change the structure of a live table it can usually be done online with careful design and cross edition triggers.
May 25, 2011
Not all application changes are as simple as just a code change - sometimes the underlying database schema needs changed too. So what are the options there?
May 16, 2011
Fixing a bug in a live PLSQL application without an outage has always been a tricky problem in Oracle, until 11gR2 when a new feature changed things completely ...