Further Oracle Sample Questions
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
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
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur := DBMS_SQL.OPEN_CURSOR;
TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
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
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
1. Increase the size of
2. Divide the update into
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
CURRVAL returns the current value of a sequence.
NEXTVAL increments the sequence and returns the next value.
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
A leaf node is any node without children.
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.
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
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
compress < expdat.dmp expdat.dmp.Z
# Wait a second or so before kicking
off the export
# 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
NOT NULL constraints
UNIQUE key constraints
PRIMARY KEY constraints
FOREIGN KEY (referential)
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.
FROM emp a,emp b
WHERE b.mgr = a.empno;
Have a Oracle Question
Do you have
an Oracle Question?
Certification, Database Administration, SQL, Application, Programming Reference
Application Hints and Tips
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.