Basic Replication : Basic replication is implemented using standard CREATE SNAPSHOT or CREATE MATERIALIZED VIEW statements. It can only replicate data and not procedures, indexes replication is always one-way, and snapshot copies are read only.

Advanced Replication : Advanced replication supports various configurations of updatable snapshot, multi-master and update anywhere replication. It is more difficult to configure but allows data and other database objects like indexes and procedures to be replicated.

Differences between Basic and Advanced replications:

- With basic replication, data replicas provide read-only access to the table data whereas advanced replication features extend the capabilities of basic read-only replication by allowing applications to update table replicas throughout a replicated database system.

- With Basic Replication applications can query data from local data replicas. On the other hand with advanced replication, data replicas anywhere in the system can provide both read and update access to a table's data.

The following are the areas of concern for tuning:

- Memory Usage

- Data Storage

- Data Manipulation

- Physical Storage

- Logic Storage

- Network Traffic

Tuning Memory Usage: In Oracle 10g, we can use the Automatic Workload Repository (AWR) toolset to gather and manage statistical data but in case of Oracle 11g, we can use new initialization parameters such as MEMORY_TARGET to further control the overall memory used by Oracle. It helps us to tune the database automatically when we don’t have time to read the AWR report.

Tuning Data Manipulation: There are two ways to tune Data manipulation in Oracle one way is through Conventional Path mode and second is Direct Path mode.

Tuning Physical Storage: We need to tune the physical storage because it limits the disk in database. This can be done by: Distributing I/O, Striping and Mirroring.

The CACHE option means how many sequences will be stored in memory for access by the application objects. The performance is faster. However in case of the database is down the data is memory is lost for the sequence.

The NO CACHE option means values are not stored in memory. So there might be some performance issue. 

1)Divide by zero        – This is raised when any number is attempted to divide by zero.

2)TOO MANY ROWS   - A SELECT INTO statement returns more than one row.

3)CASE_NOT_FOUND - No choice in the WHEN clause of a case statement is selected.

4)LOGIN_DENIED      - An attempt to login with an invalid username or password.

5)PROGRAM_ERROR  - An internal PL/SQL problem.

Even though both PL/SQL and Java (or) .NET can be used, PL/SQL stands above these two in terms of integration overhead. This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java. 

- Because of the block nature, multiple statements are processed at once thereby improving performance. 

- PL/SQL handles exceptions on catching which, action can be taken. 

- PL/SQL is highly portable as it works with all procedural languages and is highly secured because of privileges.

The number of rows returned by a select query can be restricted by the LIMIT clause.
Example:
SELECT * from employee
LIMIT 20

Oracle check constraint is used to ensure that before inserting the data in the database, it is validated and checked for the condition.
Example:
Below, the constraint is that the id has to be between 0 and 1000.
create table employee ( id number check (id between 0 and 1000), Name varchar(200) );