Recipes

Get Columns from Ref Cursor

You've got some dynamic application where you want to access some meta data from a ref cursor? Two examples are right here!

Access data from previous record

How to mix values from the current rows with values from previous or preceding rows.

Find Blocking Sessions

Blocking Sessions can cause a lot of problems in Oracle. Tracking these sessions is important because as the name says, they are blocking which means that the blocked session won't be able to finish unless the blocking session has done its work.

Display Oracle Session ID (SID)

When you work with views like v$session you'll often want to know the current session ID. Here's how you can get it.

Finding locked database object

Often when you've got a lock in your system, you'd quickly like to know the object causing the problem. Here's a simple query which does just that.

Deterministic Functions to improve Performance

Make functions in queries faster by declaring them as deterministic

Monitor Code Changes

Ever wanted to know who creates or modifies an object in your database? Want to see what views, tables or packages your co-workers create? This trigger does the trick!

Better Performance by having less Data Type Conversions

Got a number and want to put it in a string - Oracle has to do a conversion. Not a big deal but there's a small and easy to remember detail which leads to slightly faster code. Look at this article to see how the performance can improve by avoiding unnecessary data type conversions.

Statistical Data about Database Events, Waits and more using ASH

Get information about waits and other events of your database by using ASH (Active Session History)

Establish Connection without tnsnames

Connecting to a database is usually an easy thing to do but when you don't know much about the system, don't have the right to modify tnsnames, it can be a bit annoying from time to time. A quick recipe to show you how to connect without using a tnsnames file.

Using WITH to avoid duplicate code

How to avoid duplicate sub queries with using the SQL-99 clause WITH.

Creating a Dummy Table for Tests

Every needed a table with some data to run your tests? Check this article for a simple query to create a table with random text and dates.

Monitor long running Oracle operations

You're probably trying to avoid that but sometimes when you have to run a long running operation like a full table scan on a table with millions of rows, you'll want to know how long you have to wait until that operation finished. Check this article for more information.

Using Flashback to recover lost data

You sure have a backup when running an Oracle database but sometimes it takes a while to restore a single table or even a single row. Oracle comes with a handy feature called "Flashback" which allows you to run a query on a table state from the past.

Using DBMS_PROFILER to find bottleneck in a procedure

Finding a performance issue can be a tricky thing but if you know the tools available at Oracle, you'll sometimes even enjoy it. This article shows you how you can analyze a PL/SQL procedure or function.

Save storage and improve Performance by compressing your indexes

Oracle allows you to compress indexes for a rather long time, but a lot of DBAs still don't know about that feature yet and they probably should.

Find unnecessary indexes

You'll definitely have to use indexes if you want your database to perform but having too many indexes isn't good either. Save some storage and make your database faster by removing unnecessary indexes.

Determine Database Uptime

Want to let management know how well your database is running?

Prohibit Excel from Connecting to your Database

You want to make sure no one connects to your production database using Excel, Sqlplus or another tool?

Monitor Database errors

Want to make sure no one creates code errors in a database? Check this article for an easy monitoring solution.

What Oracle Functions are there

Ever wondered what Oracle function you can use? There's a data dictionary telling you about every available function including some additional information.

Get a list of all available Tables in your Oracle database

Having a list of tables in a database can help you to get a picture of what's there. Here's how you can do that.

Aggregate multiple rows into one as a comma-delimted list

Ever wanted to group a number of rows into a single row as a list? This recipe shows you how to do that.

Limit number of rows in a sorted query

Have you ever tried to limit the number of rows in combination with an order by and didn't get the expected result? Here's why and of course the correct way!

Faster PL/SQL Code by using native compiler

An old feature but rarely used - the native Oracle compiler which will simply make things faster with almost no effort

Working with Arrays - VARRAY

In some rare cases you won't want to normalize a table but simply add a list of values in a single cell. This is what VARRAY types are good for.

Create a table out of nothing

sql table out of nothing