A simple tip on how to retrieve a history of all Patch Set Update applied to a database via SQL.
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 →
Whilst investigating a user issue today, I came across an unusual occurrence of the "ORA-01031: insufficient privileges" error which was not caused to the usual suspects of missing grants and/or tablespace quota.
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.
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.
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...
When writing scripts to be run via SQL*Plus it’s often a good idea to check that the script is being executed as the desired user, and to abort with an error if this is not the case. This is especially true for installation scripts or any scripts which perform dangerous or destructive actions. One method... Continue Reading →
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 →