More Oracle Interview Questions and Answers

  1. Which command is used for locking a user from performing DML Operation?
A.LOCK TABLE tablename IN EXCLUSIVE MODE;EX:LOCK TABLE emp IN EXCLUSIVE MODE;

    2. Why is sys_context used for providing privileges in Oracle?
A.  The sys_context function can be used to retrieve information about the Oracle
environment.The syntax for the sys_context function is:sys_context( namespace, parameter, [ length ]
)namespace is an Oracle namespace that has already been created.

3. Is there any syntactical diff between Oracle 9i & 10g or any change in which the Queries are used....It
uses SQL isn't it?
A.  In 9i we does not have the facility to bring the table that is been dropped, but in 10g we
            have something like flashback and also in 10g they have added one extra regular expression.

4. SQL>DECLARE 2 dayofweek VARCHAR2(200); 3 BEGIN 4 SELECT TO_CHAR(sysdate,'Day') INTO
  dayofweek FROM DUAL ; 5 6 IF dayofweek = 'Tuesday' 7 THEN 8

  A.  TO_CHAR(sysdate,'Day') is always return day name with some blank space, So its better
   to use TRIM function to exact match.Like TRIM(TO_CHAR(sysdate,'Day'))Or You can match initial 3
   character in the by using substr function. ...

5. What is the difference between the tools and utilities in Oracle 9i?

A.  Utility is something which comes along with software where it is intended to help us to
solve a particular purpose but tool is something which enable us to develop more delebrately we can
say as helpful for innovative development.
 
6. What steps server process has to take to execute an update statement?
A.  When a user issues a update statement the server process checks for weather the same
  type of command is executed recently or not if that command is executed very recently then it directly
proceed to the dictionary cache to fecth the metadata presented .

7. What is the different between Stand Alone Procedure and Package?
A. 1. Procedure Overloading and Function Overloading Possible in Package.
This Overloading Concept Not Possible in Procedure.
2. Variables in Package we Can use as a Global Variables. it's Not Possible in Procedure.

8. What is the difference between cold backup and hot backup?
A.  Cold Backup is possible only when the database is down means it is not available to users
to access. Disadvantage is we effort downtime. Here we need to take backup of datafiles, control files,
redo files. Here we get consistent backup image.Hot backup..

9. What is the Life of an SQL Statement?
A. LIFE OF AN SQL STATEMENT Step 1: Oracle Create a CursorFor every SQL, first a
  cursor is created (cursor creation can occur implicitly, or explicitly by declaring a cursor).
Step 2: Oracle

10. What is the difference between Instance and a Database?
A. Instance is the memory structures and background processes used to interact with
Database.Instance = SGA + Background ProcessDatabase is the physical files used to store information.
the 3 types of physical files are: Datafiles, Control File and ...
Parse SQL StatementA SQL statement is parsed only if an identical ...

Related Interview Questions and Answers:

Oracle DBA Interview Questions

SQL Server DBA Interview Questions