Finding the N th entry of a column in SQL

Let us take a simple table fruit and look how the query works

SQL> select * from fruit order by name asc;

NAME
----------
apple
banana
grape
orange

#If we need to find the second Maximum entry, The query would be:
#Sort the query in ascending order, then pick the second Maximum entry from the resultant set
SQL> select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name asc) where rownum <= &n );
Enter value for n: 2
old   1: select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name) where rownum <= &n )
new   1: select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name) where rownum <= 2 )

MAX(NAME)
----------
banana


#If we need to find the second Minimum entry, The query would be:

#Sort the query in the descending order, then pick the second Minimum entry from the resultant set

SQL> select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= &n);
Enter value for n: 2
old   1: select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= &n)
new   1: select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= 2)

MIN(NAME)
----------
grape

In order to get proper results, use the right combination of order by desc/asc and max/min feature of Query 

Some common ORA- Errors

ORA-19502 and ORA-27072

For egs :I recently faced an error like this

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/14/2013 06:23:29

ORA-19502: write error on file "/oracle/admin/flash_recovery_area/ENV/backupset/2013_11_14/o1_mf_nnndf_TAG20131114T062322_989j5tgv_.bkp", block number 26880 (block size=8192)

ORA-27072: File I/O error

Additional information: 4

Additional information: 26880

Additional information: 970752

I was wondering what might have gone wrong. It was surprising to figure out the solution for this scenario. This error will be thrown out if you have lack of disk space. So this affect RMAN Backup. So either add additional disk space to the disk or delete some unwanted files. And this worked for me, as RMAN executed the backup without any further errors

-----------------------------------------------------------------

ORA-19870 and ORA-19573

While restoring database this is one of the common ORA errors. 

For Egs:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 10/25/2013 00:27:48

ORA-19870: error while restoring backup piece /oracle/product/11.2.0.3/PROD/dbs/7con65m9_1_1

ORA-19573: cannot obtain exclusive enqueue for datafile 1

The culprit here is that, your DB is in open state. So mount it after a shutdown option. So solution is like

SQL>shutdown immediate;

SQL>startup mount;

and then go for restore database

RMAN>restore database;

ORA-04043

For egs:

SQL> desc dba_data_files;

ERROR:

ORA-04043: object dba_data_files does not exist

This has been one of the most common error produced if you haven't ran catalog.sql or catproc.sql. In some cases if your instance is down also, Oracle throws out this error.

So check the status of DB first. In case if its down, bounce it.

Still if the error persists, go to $ORACLE_HOME/rdbms/admin and run catalog.sql and catproc.sql