Oracle Questions

 - When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock.
 - The reasons for it to happen are:
 * lack of proper row lock commands.
 * Poor design of front-end application
- It reduces the performance of the server severely.
- These locks get automatically released automatically when a commit/rollback operation is performed or any process is killed externally.

WATER MARK is a divided segment of used and free blocks. Blocks which are below high WATER MARK i.e. used blocks, have at least once contained some data. This data might have been deleted later. Oracle knows that blocks beyond high WATER MARK don’t have data; it only reads blocks up to the high WATER MARK during a full table scan. 

DocumentDB is a NoSQL document-oriented database and records saves in key-value pairs. It's same as the other NoSQL document-oriented such as MongoDB.

Shared pool in oracle contains cache information that collects, parses, interprets and executes SQL statements that goes against database. This shared pool acts like a buffer for these SQL statements.

Control File is used for:
- Database recovery.
- Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database operation to go ahead.

Personal Oracle lite is single user database for distributed and mobile computing. It supports a full range of Java database features as well as advanced replication. Or in other words it is Oracle's lightweight, fully-functional, single-user object- relational database. A small, zero administration embedded database.

Some of the features supported:

- Java Support

- Java Store Procedure Support

- Java triggers Supported

- Multi-column indexing and outer joins.

- Easy to use graphical user interface

- Supports both object and relational database models

- Simultaneous viewing of classes and relational tables

A package is a collection of related PL/SQL objects. The package contains a body and a specification. The package specification has the declaration which is public and can be used in the PL/SQL sub programs inside the package.

The package body holds the implementation of all the PL/SQL objects declared in the specification.

Example of a PL/SQL Package.

CREATE OR REPLACE PACKAGE emp_data AS 

PROCEDURE add_employee (

      ename VARCHAR2,

      job VARCHAR2,

      mgr NUMBER,

      sal NUMBER,

      deptno NUMBER);

END emp_actions;

CREATE OR REPLACE PACKAGE BODY emp_data AS 

PROCEDURE add_employee (

       ename VARCHAR2,

       job VARCHAR2,

       mgr NUMBER,

       sal NUMBER,

       deptno NUMBER) IS

BEGIN

         INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, comm, deptno);

END add_employee;

END emp_data;

Advantages of  PL/SQL packages :

Packages are easier for application designing, encapsulating data, additional functionality and better performance. An application has various modules which can be placed in packages and handled easier.

- Rename : It is a permanent name provided to a table or column.
- Alias : It is a temporary name provided to a table or column which gets over after the execution of SQL statement.