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.
                SELECT ename,dname
                FROM   emp,dept
                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 INSERT.
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#
            FROM v$session
            WHERE username = 'JWARD';
After locating session you can kill the session using the following command.
          
The following statement terminates the session whose SID is 7 and serial number is 15:
          ALTER SYSTEM KILL SESSION '7,15';

What is the max. number of extent you can create in 7.2version and in 8.0version?

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,
         MAXLOGFILES, MAXLOGMEMBERS,MAXDATAFILES, MAXLOGHISTORY, and
         MAXINSTANCES.
         Eg:- CREATE DATABASE patients
              LOGFILE
              GROUP 1('/usr/oracle/dbs/patient1.log',
                      '/u2/usr/oracle/dbs/patient1.log') SIZE 50K
              GROUP 2('/usr/oracle/dbs/patient2.log',
                      '/u2/usr/oracle/dbs/patient2.log') SIZE 50K
              MAXLOGFILES 10
              MAXLOGMEMBERS 4
              DATAFILE '/usr/oracle/dbs/patientsystem1.ora' SIZE 40M
              MAXDATAFILES 100
              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.
          ORACLE_SID  Identifies the Oracle database of interest.
          ORACLE_TERM Terminal definition.

Which are the different sqlnet files?

ANS:- The important sqlnet files are listener.ora, tnsnames.ora and sqlnet.ora.

What are the main informations stored in tnsnames.ora file?

ANS:- tnsnames file contains all hostnames and sid of the distributed database. 

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
          $ORACLE_HOME/rdbms/admin  directory. It will create PLAN_TABLE.
          Oracle stores 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 
CREATE/ALTER/DROP 
       DATABASE/TABLESPACE/ROLLBACK SEGMENT 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?

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