Archive for the ‘SQL’ Category

Oracle – Find consuming sql from process id

September 5, 2011 5 comments

If you find an oracle process is consuming a high amount of CPU, you can find the sql_text that is related to that process id using the following code:

FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));

You will need to find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.

Oracle – Find View Source Code

August 21, 2011 1 comment

If you wish to find the source code for a view, you can look in the user_views table:

SQL> SELECT view_name, text FROM user_views;

You can check the v$fixed_view_definition table to check the definition of fixed views (views starting with v$).

SQL> SELECT view_name, view_definition FROM v$fixed_view_definition;

Further Reading

Oracle – Backup Controlfile

August 20, 2011 1 comment

It is necessary, from time to time, to backup the controlfile. This can be done when the database is open or mounted.

Backup controlfile using SQL

If you wish to backup the controlfile using SQLPLUS then you can issue the following to backup the controlfile to a binary file:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

This create a duplicate of the current controlfile.

However, if you wish to produce SQL statements that can later be used to re-create the controlfile, you can issue the following:


Backup controlfile using RMAN

RMAN will automatically backup the controlfile and server parameter file after every backup and after structural database changes, if CONFIGURE CONTROLFILE AUTOBACKUP is set to ON. It is set to OFF by default.

The controlfile autobackup contains metadata about the previous backup, which is essential for database recovery.

Manually backup controlfile


It should be noted that a manual backup of the controlfile cannot be automatically restored. This is because it only contains RMAN repository data for backups within the current RMAN session. However, if controlfile autobackup is set to on, RMAN will manually backup the controlfile then it will create an autobackup of the controlfile and server parameter file after the manual backup has completed so it can record the latest backup information.

If you wish to include the current controlfile in a backup then you can perform something like the following:


If autobackup is enabled then RMAN performs and autobackup of the controlfile after the backup, so the controlfile backup contains details of the latest backup taken.

Backup controlfile as copy

You can also backup the controlfile as a copy. You can use either of the below commands for creating a backup controlfile copy:


RMAN> BACKUP DEVICE TYPE sbt CONTROLFILECOPY  '/tmp/controlfile_copy.ctl';

Futher Reading

Managing Control Files – Oracle Documentation

Backing Up Database Files and Archived Logs With RMAN – Oracle Documentation

Oracle – PL/SQL decode function

August 19, 2011 1 comment

The decode function acts like IF…THEN…ELSE and can be used within PL/SQL code and SQL code much the same.

The syntax is as follows:

decode(expression, search, result [, search , result]... [, default] )


SELECT employee_name
DECODE(employee_id, 1, 'Alvin'
2, 'Simon'
Theodore') result
FROM employees;

The above code acts like the following:

IF employee_id = 1 THEN
result:= 'Alvin'
ELSIF employee_id = 2 THEN
result:= 'Simon'
result:= 'Theodore'

The decode will compare each employee_id one by one.

Oracle – SQL projection

In Oracle projection limits the columns returned by a query. It is nothing more than a named list of columns which can be made use of in an application. Projections are purely for use by the user and have no affect on business rules.

To return all columns you use * after the SELECT.


, age
FROM emp;

Oracle – SQL functions

Numeric results

A single-row function may accept multiple input parameters but always returns one value per row.

Single-row numeric functions always return numeric results. Numeric functions are the only category of functions that always return numeric results.

Character and date functions like LENGTH and MONTHS_BETWEEN return numeric results.

Character results

If the input argument is char or varchar2, then the returned value is varchar2.

For functions that return  char or varchar2, if the length of the return value exceeds the maximum length limit of the datatype, then Oracle truncates it and returns the result without an error message.

For functions that return clob values, if the length of the return value exceeds the maximum length limit of the datatype, then Oracle raises and error and no result is returned.

Character Functions Returning Number Values

Character functions that return number values can take any character datatype as their argument.

Datetime Functions

Datetime functions operate on date, timestamp, and interval.

Some of the datetime functions were designed specifically for the Oracle date datatype. If you provide a timestamp value as their argument, then Oracle internally converts this to a date value and returns a date value. However, the months_between function returns a number, and the round and trunc functions do not accept timestamp or interval at all.

Conversion Functions

Conversion functions convert a value from one datatype to another. You normally do this by datatype1 to datatype2 where datatype1 is the current datatype, and datatype2 is the new datatype.

Aggregate Functions

Distinct causes an aggregate function to consider only unique values of the argument expression.

All causes and aggregate function to consider all values, including duplicates.

All aggregate functions except count(*) and grouping ignore nulls.

You can nest aggregate functions. For example you could find the average maximum salary for an employee in the employees table.

Analytic Functions

Analytic functions compute a value based on a group of rows. They are different from aggregate functions, in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. The window determines the range of rows used to perform the calculations of the current row.

Analytic functions are the last set of operations performed in a query except for the final order by clause. Analytic functions can appear only in the select list or order by clause.

Further Reading

SQL Functions Oracle Documentation

Oracle – PL/SQL instr command

The instr command can be used in PL/SQL to return the location of a substring in a string.


instr( string1, string2 [, start_position [, nth_appearance ] ] )

string1 – string to search

string2 – string to find

start_position – position to start searching from

nth_appearance – appearance of string to search for


instr(‘she_sells_sea_shells_on_the_sea_shore’,’s’,4,3) – returns 14, the 3rd appearance of s after the 4th letter.

Further Reading: