What is rownum?

ANS:- It is a pseudocolumn. It will just give the number of row 
returned by the query. Oracle will  assign rownum before the order by clause.

What is corelated subquery?

ANS:-Corelated subquery is the subquery which depend upon the main 
query for its completion. For eg:-  If you want to find out the 
salary of all employees whose salary is greater than the average 
salary of        their respective departments, you can issue the following corelated subquery.
        Eg:- SELECT ename,sal
             FROM   emp a
             WHERE a.sal > (SELECT avg(sal)
                           FROM emp b
                           WHERE b.deptno = a.deptno);

How to use dynamic sql in PL/SQL?

ANS:-From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. 
   Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
        cur integer;
        rc  integer;
       BEGIN
        cur := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
        rc := DBMS_SQL.EXECUTE(cur);
        DBMS_SQL.CLOSE_CURSOR(cur);
       END;

How to write into a file from PL/SQL?

ANS:-Included in Oracle 7.3 is a UTL_FILE package that can read 
and write files. The directory you intend writing to has to be in 
 your INIT.ORA file (see UTL_FILE_DIR=... parameter).

Which are the comman oracle provided packages?

ANS:-Oracle provids lot of standard packages. Some of them are 
     UTL_FILE, DBMS_OUTPUT, DBMS_JOB, DBMS_SQL etc.

How to do update on a large table?

ANS:-When you try to do update on a large table you may be facing 
the error cann't extent rollback segment. This is because of the 
large redo information generated. In this case you can do one of the       following things:-
        1. Increase the size of rollback segment.
        2. Divide the update into smallar updates.
        3. Write a PL/SQL program and declare a cursor to fetch 
all the rows.Then update row by row with 
        comiting at specific intervel.

In a distributed database evenafter a user gets select previlage for a table, he is geting message 'Table dosn't exist'. Why?

ANS:-This may be due to some problem in the remote connection. Remote database may not be up.

When will we get a error 'Snapshot too old'?

ANS:-This problem occurs when your rollback segment is very small 
and when you are running a long query involving records that are 
frequently updated by other transactions.

Which are the different pseudocolumns in Oracle?

ANS:-A pseudocolumn behaves like a table column, but is not actually 
stored in the table. You can select from pseudocolumns, but you 
cannot insert,update, or delete their values. This section describes these pseudocolumns:

CURRVAL  returns the current value of a sequence.

NEXTVAL  increments the sequence and returns the next value.

LEVEL
For each row returned by a hierarchical query, the LEVEL pseudocolumn 
returns 1 for a root node, 2 for a Child of a root, and so on. 
A root node is the highest node within an inverted tree. A child 
 node is any non-root node. A parent node is any node that has children. 
A leaf node is any node without children.

ROWID
For each row in the database, the ROWID pseudocolumn returns a row's 
address. ROWID values contain information necessary to locate a row:
 which data block in the data file
 which row in the data block (first row is 0)
which data file (first file is 1)

Usually, a ROWID value uniquely identifies a row in the database.

ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns 
a number indicating the order in which Oracle7 selects the row from 
a table or set of joined rows. The first row selected has a ROWNUM 
of 1, the second has 2, and so on.

How to move a datafile from one disk to another?

ANS:-Rename the datafile so that your control file will point to 
the new location. Then copy the datafile to the new location.

What is deffered rollback segment?

ANS:-rollback segments that were created to hold rollback entries 
for tablespaces taken offline until the tablespaces are brought back online

How to export a database of size more than 2GB?

ANS:-You can run export and compress simultaneously. This will prevent 
the need to get enough space for both the export file AND the compressed export file. Eg:

      # Make a pipe
      mknod expdat.dmp p
      # Start compress sucking on the pipe in background
      compress < expdat.dmp  expdat.dmp.Z &
      # Wait a second or so before kicking off the export
      sleep 5
      # Start the export
      export scott/tiger filename=expdat.dmp

How to add a datafile after reaching maxdatafile limit?

ANS:-To change any of the permanent settings of the create database 
command(like changeing the name,maxdatafiles,maxlogfiles etc.) you 
have to recreate the control file. For doing it you can use the 
option alter database backup control file to trace option. It will 
generate a script for creating the control file. Edit the script 
to increase the datafile and create the control file.

Which are the different integrity constraints?

ANS:-Oracle uses integrity constraints to prevent invalid data entry 
into the base tables of the database. You can define integrity constraints 
to enforce the business rules that are associated with the 
 information in a database. If any of the results of a DML statement 
execution violate an integrity constraint, Oracle rolls back the 
statement and returns an error. The different integrity constraints are:-

        NOT NULL constraints

        UNIQUE key constraints

        PRIMARY KEY constraints

        FOREIGN KEY (referential) constraints

        CHECK constraints

What is self join?

ANS:- Self join is the joining of table to itself. Eg:- If you want 
to select the name of the employee with his managers name you can use the following query.

            SELECT a.ename,b.ename
            FROM emp a,emp b
            WHERE b.mgr = a.empno;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips