Recipes
You've got some dynamic application where you want to access some meta data from a ref cursor? Two examples are right here!
How to mix values from the current rows with values from previous or preceding rows.
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.
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.
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.
Make functions in queries faster by declaring them as deterministic
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!
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.
Get information about waits and other events of your database by using ASH (Active Session History)
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.
How to avoid duplicate sub queries with using the SQL-99 clause WITH.
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.
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.
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.
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.
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.
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.
Want to let management know how well your database is running?
You want to make sure no one connects to your production database using Excel, Sqlplus or another tool?
Want to make sure no one creates code errors in a database? Check this article for an easy monitoring solution.
Ever wondered what Oracle function you can use? There's a data dictionary telling you about every available function including some additional information.
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.
Ever wanted to group a number of rows into a single row as a list? This recipe shows you how to do that.
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!
An old feature but rarely used - the native Oracle compiler which will simply make things faster with almost no effort
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.
