Dynamic SQL

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!
Remo Remo | Dynamic SQL | 0 Comments | 62453 views | 13 years ago

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.
Remo Remo | Dynamic SQL | 0 Comments | 27073 views | 13 years ago

Deterministic Functions to improve Performance

Make functions in queries faster by declaring them as deterministic
Remo Remo | Dynamic SQL | 0 Comments | 23302 views | 12 years ago

Monitoring

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.
Remo Remo | Monitoring | 1 Comments | 392745 views | 10 years ago

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.
Remo Remo | Monitoring | 0 Comments | 17083 views | 13 years ago

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!
Remo Remo | Monitoring | 0 Comments | 7947 views | 12 years ago

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.
Remo Remo | Monitoring | 1 Comments | 84912 views | 12 years ago

Determine Database Uptime

Want to let management know how well your database is running?
Remo Remo | Monitoring | 0 Comments | 28858 views | 11 years ago

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?
Remo Remo | Monitoring | 0 Comments | 8112 views | 11 years ago

Monitor Database errors

Want to make sure no one creates code errors in a database? Check this article for an easy monitoring solution.
Remo Remo | Monitoring | 0 Comments | 10545 views | 11 years ago

SQL

Access data from previous record

How to mix values from the current rows with values from previous or preceding rows.
Remo Remo | SQL | 0 Comments | 21637 views | 13 years ago

Using WITH to avoid duplicate code

How to avoid duplicate sub queries with using the SQL-99 clause WITH.
Remo Remo | SQL | 0 Comments | 12879 views | 12 years ago

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.
Remo Remo | SQL | 0 Comments | 42867 views | 12 years ago

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.
Remo Remo | SQL | 0 Comments | 7461 views | 12 years ago

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.
Remo Remo | SQL | 0 Comments | 8447 views | 11 years ago

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.
Remo Remo | SQL | 0 Comments | 46276 views | 11 years ago

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.
Remo Remo | SQL | 0 Comments | 20433 views | 10 years ago

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!
Remo Remo | SQL | 0 Comments | 15698 views | 10 years ago

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.
Remo Remo | SQL | 0 Comments | 16891 views | 10 years ago

Create a table out of nothing

sql table out of nothing
Remo Remo | SQL | 0 Comments | 12834 views | 7 years ago

SQL*Plus

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.
Remo Remo | SQL*Plus | 0 Comments | 12166 views | 12 years ago

Tuning

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.
Remo Remo | Tuning | 0 Comments | 6579 views | 12 years ago

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)
Remo Remo | Tuning | 0 Comments | 6999 views | 12 years ago

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.
Remo Remo | Tuning | 0 Comments | 19990 views | 12 years ago

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.
Remo Remo | Tuning | 0 Comments | 8153 views | 12 years ago

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.
Remo Remo | Tuning | 0 Comments | 11629 views | 12 years ago

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
Remo Remo | Tuning | 0 Comments | 17667 views | 10 years ago