Partitioning a Table

Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition". From an "Application Development" perspective, there is no difference between a partitioned and a non-partitioned table. Oracle introduced partitioning with 8.0. With this version only, " Range Partitioning" was supported. Then with Oracle 8i " Hash and Composite Partitioning" was also introduced and with 9i " List Partitioning", it was introduced with lots of other features with each upgrade. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions.

Advantages of using Partition’s in Table

1. Smaller and more manageable pieces of data ( Partitions )
2. Reduced recovery time
3. Failure impact is less
4. import / export can be done at the " Partition Level".
5. Faster access of data
6. Partitions work independent of the other partitions.
7. Very easy to use

Types of Partitioning Methods

1. RANGE Partitioning
2. HASH Partitioning
3. List Partitioning ( Only with 9i)
4. Composite Range-Hash Partitioning
5. Composite Range-List Partitioning ( Only with 9i) 

For More detail : Example  1   Example 2   Example 3   Example 4

Oracle Lock

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. The resources can be either user objects, such as tables and rows, or system objects not visible to users, such as shared data structures in memory and data dictionary rows. Oracle Database automatically obtains and manages necessary locks when executing SQL statements, so you need not be concerned with such details. However, the database also lets you lock data manually.

Use the LOCK TABLE statement to lock one or more tables, table partitions, or table sub-partitions in a specified mode. This lock manually overrides automatic locking and permits or denies access to a table or view by other users for the duration of your operation.Some forms of locks can be placed on the same table at the same time. Other locks allow only one lock for a table. A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table. A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers.

The syntax for a Lock table is:

LOCK TABLE tables IN lock_mode MODE [NOWAIT];

Tables is a comma-delimited list of tables.

Lock_mode is one of:

ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE.

NoWait specifies that the database should not wait for a lock to be released.

ROW SHARE

ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.

ROW EXCLUSIVE

ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.

SHARE ROW EXCLUSIVE

SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.

EXCLUSIVE

EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.

NOWAIT

Specify NOWAIT if you want the database to return control to you immediately if the specified table, partition, or table subpartition is already locked by another user. In this case, the database returns a message indicating that the table, partition, or subpartition is already locked by another user.

If you omit this clause, then the database waits until the table is available, locks it, and returns control to you.

The following statement locks the employees table in exclusive mode but does not wait if another user already has locked the table:

LOCK TABLE employees
   IN EXCLUSIVE MODE 
   NOWAIT; 

The following statement locks the remote employees table that is accessible through the database link remote:

LOCK TABLE employees@remote 
   IN SHARE MODE;

For more details click here  Click here