Scripting: Is the DB DataGuarded?

Whilst developing some administrative functions within PL/SQL I needed to know whether the database that the code was being run in was part of an Oracle DataGuard setup, which you would think would be a trivial exercise. My first thought was that we could determine this via v$database. If the DATABASE_ROLE column in v$database contains STANDBY then we know... Continue Reading →

RMAN: Displaying current backup progress

When performing a backup of a large database using RMAN, you often get asked "how is the backup progressing?" and "when it will complete?". Although the log produced by RMAN is detailed in almost every other aspect, there isn't any information recorded that will allow you to give a simple and straightforward answer to these questions However, the information is available within the data dictionary.

Reclaiming LOB space

Oracle 10gR2 introduced the ability to reclaim unused LOB space within a table using the ALTER TABLE ... SHRINK SPACE command. However as is often the case with Oracle, things are a little more complicated than they first appear and there are a number of important things to consider which are discussed in this article.

Database version dependent SQL scripts

I've been working with Oracle databases since version 7.1 and over time I've built up quite a collection of scripts - some of which were improved as later versions of the database provided additional information in the data dictionary (in some cases columns were renamed completely). However because I never know when I might be working on a system running an earlier version of Oracle I sometimes keep multiple versions of scripts, usually with a suffix of v7, v8, etc. However, wouldn't it be nice if you didn't have to specify the version suffix - if somehow the correct script was run automatically? Here's how I do it...

Testing a string for a numeric value

Sometimes it is necessary to determine if a value in a varchar column actually contains a number. The code below gives examples of how to do this. All versions of Oracle The code below created a stored procedure that can be used on all versions of Oracle to test whether a string contains a numeric... Continue Reading →

Blog at

Up ↑