Self Test Oracle Questions
How will you do outerjoin?
ANS:-For outer join in oracle you have to add (+) sign at the end of
the column of the table in the where clause.
Eg:- If you want all the employees with there corresponding names and
list of all departments without employees then you can use following query.
WHERE emp.deptno(+) = dept.deptno;
How do you control the size of the control file?
Ans:- Adjust the size of MAXLOGHISTORY
What is conventional loading and direct loading?
Ans:- SQL*Loader can load data into a database using two different paths:
Conventional path loads and Direct path loads.
A conventional path load proceeds through the normal SQL processing
layer of Oracle7. SQL*Loader creates arrays of records to insert
and then ships the insert arrays to the database server using the SQL command
A direct path load bypasses the SQL processing layer of Oracle7 to
minimize the time necessary to complete a data load. When you use a direct
load, SQL*Loader creates data blocks in the oracle format and directly
add them to the data files in a database. Consequently direct path load
is typically much faster than a comparable conventional data load. If you
want to perform a direct path load, simply specify DIRECT=TRUE on the command
line when invoking SQL*Loader.
How do you kill a session? Which are the parameters required?
Ans:-To identify which session to terminate, specify the session's index
number and serial number. To identify the index (SID) and serial numbers
of a session, query the V$SESSION dynamic performance table.
The following query identifies all sessions for the user JWARD:
SELECT sid, serial#
WHERE username = 'JWARD';
After locating session you can kill the session using the following
The following statement terminates the session whose SID is 7 and serial
number is 15:
KILL SESSION '7,15';
What is the max. number of extent you can create in 7.2version and in
ANS:- In version7.2 for 2k datablock size you can create maximmum of
121extents. There is no limitation in oracle8.0
How to pass arguments to Stored Procedure?
ANS:- We can pass arguments to Stored procedure using IN, OUT and IN
OUT keywords. IN is used to pass value into stored procedure.
OUT is used to pass value from the stored procedure. IN OUT is used
to pass the value into stored procedure and modify it and pass it back
to the calling enviornment.
What are the parameters of Create database?
ANS:- We have to give the name and size of the datafile which forms
the system tablespace,Name and size of logfile and logmembers,
Eg:- CREATE DATABASE
'/u2/usr/oracle/dbs/patient1.log') SIZE 50K
'/u2/usr/oracle/dbs/patient2.log') SIZE 50K
DATAFILE '/usr/oracle/dbs/patientsystem1.ora' SIZE 40M
CHARACTER SET us7ascii
Which are the operating system variable to be set for creating database?
ANS:- ORACLE_HOME Points to the directory location where oracle is installed.
Identifies the Oracle database of interest.
Which are the different sqlnet files?
ANS:- The important sqlnet files are listener.ora, tnsnames.ora and
What are the main informations stored in tnsnames.ora file?
ANS:- tnsnames file contains all hostnames and sid of the distributed
Which are the different tuning utilities?
ANS:- EXPLAIN PLAN can be used to find the Execution Plan for a statement.
For using EXPLAIN PLAN you have to run the script UTLXPLAN.SQL in
directory. It will create PLAN_TABLE.
the information of EXPLAIN_PLAN in PLAN_TABLE.
What is trace file and alert file?
ANS:-Each server and background process can write to an associated trace
file. When a process detects an internal error, it dumps information about
the error to its trace file.
All filenames of trace files associated with a background process contain
the name of the process that generated the trace file. The one exception
to this is trace files generated by Snapshot Refresh processes.
Trace file information can also provide information for tuning applications
or an instance.
Each database also has an ALERT file. The ALERT file of a database is
a chronological log of messages and errors, including all internal errors
(ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60)
that occur administrative operations, such as
SQL statements and STARTUP,
SHUTDOWN, ARCHIVE LOG, and RECOVER Server Manager statements errors
during the automatic refresh of a snapshot.
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.