Oracle / Database interview questions PART 02

Explain why cursor variables are easier to use than cursors.

Cursor variables are preferred over a cursor for following reasons:

A cursor variable is not tied to a specific query.

One can open a cursor variable for any query returning the right set of columns. Thus, more flexible than cursors.

A cursor variable can be passed as a parameter.

A cursor variable can refer to different work areas.

What is locking, advantages of locking and types of locking in oracle?

Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data. It is used to implement concurrency control when multiple users access table to manipulate its data at the same time.

Advantages of locking:

a.       Avoids deadlock conditions

b.      Avoids clashes in capturing the resources

Types of locks:

a.       Read Operations: Select

b.      Write Operations:  Insert, Update and Delete

What are transaction isolation levels supported by Oracle?

Oracle supports 3 transaction isolation levels:

a.       Read committed (default)

b.      Serializable transactions

c.       Read only

What is SQL*Loader?

SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high performance data loads.

What is Program Global Area (PGA)?

The Program Global Area (PGA): stores data and control information for a server process in the memory. The PGA consists of a private SQL area and the session memory.

What is a shared pool?

The shared pool is a key component. The shared pool is like a buffer for SQL statements.  It is to store the SQL statements so that the identical SQL statements do not have to be parsed each time they're executed. 

38. What is snapshot in oracle?

A snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data between distributed databases. 

What is a synonym?

A synonym is an alternative name tables, views, sequences and other database objects.

What is a schema?

A schema is a collection of database objects. Schema objects are logical structures created by users to contain data. Schema objects include structures like tables, views, and indexes. 

What are Schema Objects?

Schema object is a logical data storage structure. Oracle stores a schema object logically within a tablespace of the database.

What is a sequence in oracle?

Is a column in a table that allows a faster retrieval of data from the table because this column contains data which uniquely identifies a row. It is the fastest way to fetch data through a select query. This column has constraints to achieve this ability. The constraints are put on this column so that the value corresponding to this column for any row cannot be left blank and also that the value is unique and not duplicated with any other value in that column for any row.

Difference between a hot backup and a cold backup

Cold backup: It is taken when the database is closed and not available to users.  All files of the database are copied (image copy).  The datafiles cannot be changed during the backup as they are locked, so the database remains in sync upon restore. 

Hot backup: While taking the backup, if the database remains open and available to users then this kind of back up is referred to as hot backup.  Image copy is made for all the files.  As, the database is in use the entire time, so there might be changes made when backup is taking place. These changes are available in log files so the database can be kept in sync

What are the purposes of Import and Export utilities?

Export and Import are the utilities provided by oracle in order to write data in a binary format from the db to OS files and to read them back.

These utilities are used:

·         To take backup/dump of data in OS files.

·         Restore the data from the binary files back to the database.

·         move data from one owner to another

Difference between ARCHIVELOG mode and NOARCHIVELOG mode

Archivelog mode is a mode in which backup is taken for all the transactions that takes place so as to recover the database at any point of time.

Noarichvelog   mode is in which the log files are not written. This mode has a disadvantage that the database cannot be recovered when required. It has an advantage over archivelog mode which is increase in performance.

 What are the original Export and Import Utilities?

SQL*Loader, External Tables

What are data pump Export and Import Modes?

It is used for fast and bulk data movement within oracle databases. Data Pump utility is faster than the original import & export utilities.

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE: It returns the error number for the last encountered error.

SQLERRM: It returns the actual error message of the last encountered error. 

Explain user defined exceptions in oracle.

A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as exception. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to raise internal exceptions. 

Exception: 

DECLARE

userdefined  EXCEPTION;

BEGIN

<Condition on which exception is to be raised>

RAISE userdefined;

EXCEPTION

WHEN userdefined THEN

<task to perform when exception is raised>

 END;

Explain the concepts of Exception in Oracle. Explain its type.

Exception is the raised when an error occurs while program execution. As soon as the error occurs, the program execution stops and the control are then transferred to exception-handling part.

There are two types of exceptions:

1.       Predefined : These types of exceptions are raised whenever something occurs beyond oracle rules. E.g. Zero_Divide

2.       User defined: The ones that occur based on the condition specified by the user. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. 

How exceptions are raised in oracle?

There are four ways that you or the PL/SQL runtime engine can raise an exception:

·         Exceptions are raised automatically by the program.

·         The programmer raises a user defined exceptions. 

·          The programmer raises pre defined exceptions explicitly.

What is tkprof and how is it used?

tkprof  is used for diagnosing performance issues.  It formats a trace file into a more readable format for performance analysis. It is needed because trace file is a very complicated file to be read as it contains minute details of program execution.

What is Oracle Server Autotrace?

It is a utility that provides instant feedback on successful execution of any statement (select, update, insert, delete).  It is the most basic utility to test the performance issues.

PLEASE SHARE THIS POST IF YOU THINK POSITIVE 

Please Share it! :)