Oracle Merge Statements

Syntax: -
MERGE (hint) INTO (table_name)
USING (table_view_or_query)
ON ((condition))
WHEN MATCHED THEN (update_clause)
DELETE (where_clause)
WHEN NOT MATCHED THEN (insert_clause)
[LOG ERRORS (log_errors_clause) (reject limit (integer unlimited)];

Ex: -

CREATE TABLE employee (employee_id NUMBER(5),first_name VARCHAR2(20),last_name VARCHAR2(20),dept_no NUMBER(2),salary NUMBER(10));
INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);INSERT INTO employee VALUES (2, 'Helen', 'Lofstrom', 20, 100000);INSERT INTO employee VALUES (3, 'Akiko', 'Toyota', 20, 50000);INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses (employee_id) VALUES (1);INSERT INTO bonuses (employee_id) VALUES (2);INSERT INTO bonuses (employee_id) VALUES (4);INSERT INTO bonuses (employee_id) VALUES (6);INSERT INTO bonuses (employee_id) VALUES (7);COMMIT;

SELECT * FROM employee;
SELECT * FROM bonuses;

MERGE INTO bonuses bUSING ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20) eON (b.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 DELETE WHERE (e.salary <> 40000);

SELECT * FROM bonuses;

ORA-00600 Metalink Notes

ORA - 600 -- Look at these Metalink notes for Help
ORA-600 Errors 1 to 3000
Note 138300.1 "ORA-600 [105]"
Note 263295.1 "ORA-600 [106]"
Note 28104.1 "ORA-600 [504]"
Note 138871.1 "ORA-600 [510]"
Note 138888.1 "ORA-600 [525]"
Note 138939.1 "ORA-600 [711]"
Note 39308.1 "ORA-600 [723]"
Note 31056.1 "ORA-600 [729]"
Note 131490.1 "ORA-600 [733]"
Note 217860.1 "ORA-600 [1013]"
Note 138123.1 "ORA-600 [1100]"
Note 41767.1 "ORA-600 [1113]"
Note 40514.1 "ORA-600 [1114]"
Note 137262.1 "ORA-600 [1115]"
Note 66387.1 "ORA-600 [1158]"
Note 138354.1 "ORA-600 [1236]"
Note 28045.1 "ORA-600 [2103]"
Note 28929.1 "ORA-600 [2662]"
Note 31057.1 "ORA-600 [2845]"
Note 138733.1 "ORA-600 [2865]"


ORA-600 Errors 3001 to 6000
Note 30866.1 "ORA-600 [3020]"
Note 93665.1 "ORA-600 [3668]"
Note 47456.1 "ORA-600 [4000]"
Note 96642.1 "ORA-600 [4036]"
Note 43914.1 "ORA-600 [4137]"
Note 28226.1 "ORA-600 [4146]"
Note 39282.1 "ORA-600 [4193]"
Note 39283.1 "ORA-600 [4194]"
Note 138822.1 "ORA-600 [4400]"
Note 29702.1 "ORA-600 [4414]"
Note 138836.1 "ORA-600 [4454]"
Note 39553.1 "ORA-600 [4511]"
Note 73455.1 "ORA-600 [4512]"
Note 27955.1 "ORA-600 [4519]"
Note 204536.1 "ORA-600 [4820]"
Note 41840.1 "ORA-600 [4882]"


ORA-600 Errors 6001 to 9000
Note 47449.1 "ORA-600 [6002]"
Note 116552.1 "ORA-600 [6034]"
Note 40640.1 "ORA-600 [6101]"
Note 99300.1 "ORA-600 [6122]"
Note 138913.1 "ORA-600 [6193]"
Note 39399.1 "ORA-600 [6731]"
Note 41719.1 "ORA-600 [6856]"


ORA-600 Errors 9001 to 15000
Note 138325.1 "ORA-600 [12209]"
Note 33174.1 "ORA-600 [12235]"
Note 138332.1 "ORA-600 [12261]"
Note 138340.1 "ORA-600 [12304]"
Note 35928.1 "ORA-600 [12333]"
Note 28229.1 "ORA-600 [12700]"
Note 67496.1 "ORA-600 [13009]"
Note:28185.1 "ORA-600 [13013]"


ORA-600 Errors 15001 to 17000
Note 138428.1 "ORA-600 [15160]"
Note 138430.1 "ORA-600 [15201]"
Note 138431.1 "ORA-600 [15203]"
Note 131186.1 "ORA-600 [15212]"
Note 137266.1 "ORA-600 [15419]"
Note 216277.1 "ORA-600 [15456]"
Note 138457.1 "ORA-600 [15709]"
Note 67490.1 "ORA-600 [15851]"
Note 76528.1 "ORA-600 [15868]"
Note 138499.1 "ORA-600 [16201]"
Note 106607.1 "ORA-600 [16365]"
Note 138523.1 "ORA-600 [16515]"
Note 138526.1 "ORA-600 [16607]"

ORA-600 Errors 17001 to 30000
Note 138537.1 "ORA-600 [17003]"
Note 138541.1 "ORA-600 [17012]"
Note 41472.1 "ORA-600 [17034]"
Note 138554.1 "ORA-600 [17059]"
Note 39616.1 "ORA-600 [17069]"
Note 29616.1 "ORA-600 [17090]"
Note 138565.1 "ORA-600 [17099]"
Note 47411.1 "ORA-600 [17112]"
Note 39453.1 "ORA-600 [17113]"
Note 34782.1 "ORA-600 [17114]"
Note 138576.1 "ORA-600 [17128]"
Note 138580.1 "ORA-600 [17147]"
Note 34781.1 "ORA-600 [17148]"
Note 138586.1 "ORA-600 [17172]"
Note 263110.1 "ORA-600 [17175]"
Note 34779.1 "ORA-600 [17182]"
Note 45725.1 "ORA-600 [17271]"
Note 138597.1 "ORA-600 [17274]"
Note 134139.1 "ORA-600 [17280]"
Note 39361.1 "ORA-600 [17281]"
Note 138602.1 "ORA-600 [17285]"
Note 138621.1 "ORA-600 [17585]"
Note 138640.1 "ORA-600 [18209]"
Note 216278.1 "ORA-600 [18261]"
Note 138678.1 "ORA-600 [20084]"
Note 100073.1 "ORA-600 [25012]"


ORA-600 Errors kc
Note 138981.1 "ORA-600 [kcbgcur_2]"
Note 70097.1 "ORA-600 [kcbgcur_3]"
Note 114058.1 "ORA-600 [kcbgcur_9]"
Note 138990.1 "ORA-600 [kcbgtcr_4]"
Note 138991.1 "ORA-600 [kcbgtcr_5]"
Note 261264.1 "ORA-600 [kcbgtcr]"
Note 248874.1 "ORA-600 [kcbgtcr_6]"
Note 233612.1 "ORA-600 [kcbgtcr_12]"
Note 204512.1 "ORA-600 [kcbnew_3]"
Note 216104.1 "ORA-600 [kcbrbo1]"
Note 139011.1 "ORA-600 [kcbzpb_1]"
Note 139012.1 "ORA-600 [kcbzpb_2]"
Note 229467.1 "ORA-600 [kcbzwb_4]"
Note 139013.1 "ORA-600 [kccsbck_first]"
Note 216108.1 "ORA-600 [kcllcu_0]"
Note 263225.1 "ORA-600 [kclwcrs_15]"
Note 76434.1 "ORA-600 [kcoapl_blkchk]"
Note 248718.1 "ORA-600 [kcratr1_lostwrt]"


ORA-600 Errors kd to kw
Note 139037.1 "ORA-600 [kdddgb2]"
Note 139042.1 "ORA-600 [kdisle:nrows]"
Note 139051.1 "ORA-600 [kghalo2]"
Note 139052.1 "ORA-600 [kghasp1]"
Note 139066.1 "ORA-600 [kghxhdr1]"
Note 229809.1 "ORA-600 [kgliep_1]"
Note 66501.1 "ORA-600 [kkrqtab2]"
Note 139095.1 "ORA-600 [kkslgop1]"
Note 139116.1 "ORA-600 [kohdtf048]"
Note 264061.1 "ORA-600 [kqludp2]"
Note 139162.1 "ORA-600 [kssrmp1]"
Note 247822.1 "ORA-600 [ksmals]"
Note 139153.1 "ORA-600 [ksmguard2]"
Note 233864.1 "ORA-600 [kteuproptime-2]"
Note 139180.1 "ORA-600 [ktsgsp5]"
Note 139193.1 "ORA-600 [kttmupd_segment-]"
Note 228480.1 "ORA-600 [kwqitnmptme:read]"
Note 228480.1 "ORA-600 [kwqitnmptme:ready]"
Note 228364.1 "ORA-600 [kwqitnmptme:wait]"


ORA-600 Errors q to z
Note 248095.1 "ORA-600 [qctcte1]"
Note 216273.1 "ORA-600 [qctstc2o1]"
Note 209363.1 "ORA-600 [qerrmofbu1]"
Note 237598.1 "ORA-600 [qertqtableallocate2]"
Note 226887.1 "ORA-600 [qkagby4]"
Note 222876.1 "ORA-600 [qmxiunppacked2]"
Note 244365.1 "ORA-600 [rworupo.1]"
Note 139263.1 "ORA-600 [srsget_1]"
Note 260951.1 "ORA-600 [ttcgcshnd]"
Note 216452.1 "ORA-600 [ttcgcshnd-1]"
Note 216453.1 "ORA-600 [ttcgcshnd-2]"

Table Partitioning

Now a days enterprises run databases of hundred of Gigabytes in size. These databases are known as Very Large Databases (VLDB). From Oracle Ver. 8.0 Oracle has provided the feature of table partitioning i.e. you can partition a table according to some criteria . For example you have a SALES table with the following structure

Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993
and 1994. And most of the time you are concerned about only one year i.e. you give queries like the following

select sum(amt) from sales where year=1991;

select product,sum(amt) from sales where year=1992 Group by product;

Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to year, then the performance is improve since oracle will scan only a single partition instead of whole table.

CREATING PARTITION TABLES

To create a partition table give the following statement

create table sales (year number(4),
product varchar2(10),
amt number(10,2))
partition by range (year)
partition p1 values less than (1992) tablespace u1,
partition p2 values less than (1993) tablespace u2,
partition p3 values less than (1994) tablespace u3,
partition p4 values less than (1995) tablespace u4,
partition p5 values less than (MAXVALUE) tablespace u5;

In the above example sales table is created with 5 partitions. Partition p1 will contain rows of year 1991 and it will be stored in tablespace u1. Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2. Similarly p3 and p4.
In the above example if you don’t specify the partition p4 with values less than MAVALUE, then you will not be able to insert any row with year above 1994.
Although not required, you can place partitions in different tablespaces. If you place partitions in different tablespaces then you can isolate problems due to failures as only a particular partition will not be available and rest of the partitions will still be available.

The above example the table is partition by range.

In Oracle you can partition a table by
*Range Partitioning
*Hash Partitioning
*List Partitioning
*Composite Partitioning

Range Partitioning: -
This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, value of year. Performance is best when the data evenly distributes across the range

Hash partitioning: -
Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key

The following example shows how to create a hash partition table.

The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).

CREATE TABLE products
(partno NUMBER,
description VARCHAR2 (60))
PARTITION BY HASH (partno)
PARTITIONS 4
STORE IN (tab1, tab2, tab3, tab4);

List Partitioning: -
Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping.

List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally

The following example creates a table with list partitioning

Create table customers (custcode number(5),
Name varchar2(20),
Addr varchar2(10,2),
City varchar2(20),
Bal number(10,2))
Partition by list (city),
Partition north_India values (‘DELHI’,’CHANDIGARH’),
Partition east_India values (‘KOLKOTA’,’PATNA’),
Partition south_India values (‘HYDERABAD’,’BANGALORE’,’CHENNAI’),
Partition west India values (‘BOMBAY’,’GOA’);
If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the city column the row is stored in that partition.

COMPOSITE PARTITIONING: -
Composite partitioning partitions data using the range method, and within each partition, sub-partitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

When creating composite partitions, you specify the following:

* Partitioning method: range
* Partitioning column(s)
* Partition descriptions identifying partition bounds
* Sub-partitioning method: hash
* Sub-partitioning column(s)
* Number of sub-partitions for each partition or descriptions of subpartitions

The following statement creates a composite-partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the
STORE IN clause distributes them across the 4 specified tablespaces (tab1, ...,tab4).

CREATE TABLE PRODUCTS (partno NUMBER,
description VARCHAR(32),
costprice NUMBER)
PARTITION BY RANGE (partno)
SUBPARTITION BY HASH(description)
SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4)
(PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

ALTERING PARTITION TABLES: -

To add a partition
You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.

For example to add a partition to sales table give the following command.
alter table sales add partition p6 values less than (1996);

To add a partition to a Hash Partition table give the following command.
Alter table products add partition;

Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace. To add a partition by user define name and in your specified tablespace give the following command.

Alter table products add partition p5 tablespace u5;

To add a partition to a List partition table give the following command.
alter table customers add partition central_India values (‘BHOPAL’,’NAGPUR’);

Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.

Coalescing Partitions: -

Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of sub-partitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.

To coalesce a hash partition give the following statement.
Alter table products coalesce partition;

This reduces by one the number of partitions in the table products.

DROPPING PARTITIONS

To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.
Alter table sales drop partition p5;

Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.

Alter index sales_ind rebuild;

To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then drop
the partition like this

Delete from sales where year=1994;

Alter table sales drop partition p4;

This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Another method of dropping partitions is give the following statement.

ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;

This causes the global index to be updated at the time the partition is dropped.

Exchanging a Range, Hash, or List Partition: -

To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned.

ALTER TABLE stocks EXCHANGE PARTITION p3 WITH stock_table_3;

Merging Partitions: -
Use the ALTER TABLE ... MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.
You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.
You can only merged two adjacent partitions, you cannot merge non adjacent partitions.
For example the merge the partition p2 and p3 into one partition p23 give the following statement.

Alter table sales merge partition p2 and p3 into partition p23;

Modifying Partitions: Adding Values

Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition's value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.

The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list.

ALTER TABLE customers
MODIFY PARTITION south_india
ADD VALUES ('KOCHI', 'MANGALORE');

Modifying Partitions: Dropping Values

Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.
You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE ... DROP PARTITION statement instead.
The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.
The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list.

ALTER TABLE customers
MODIFY PARTITION south_india
DROP VALUES (‘KOCHI’,’MANGALORE’);

SPLITTING PARTITIONS: -
You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command.

Alter table sales split partition p5 into
(Partition p6 values less than (1996),
Partition p7 values less then (MAXVALUE));

TRUNCATING PARTITON: -
Truncating a partition will delete all rows from the partition.
To truncate a partition give the following statement

Alter table sales truncate partition p5;

LISTING INFORMATION ABOUT PARTITION TABLES: -

To see how many partitioned tables are there in your schema give the following statement
Select * from user_part_tables;

To see on partition level partitioning information
Select * from user_tab_partitions;

Oracle Background processes

You can see the Oracle background processes with this queries:
* select * from v$session where type ='BACKGROUND';
* select name,description from v$bgprocess;

Here are some of the most important Oracle background processes:

ARCH - (Optional) Archive process writes filled redo logs to the archive log location(s). In RAC, the various ARCH processes can be utilized to ensure that copies of the archived redo logs for each instance are available to the other instances in the RAC setup should they be needed for recovery.

CJQ - Job Queue Process (CJQ) - Used for the job scheduler. The job scheduler includes a main program (the coordinator) and slave programs that the coordinator executes. The parameter job_queue_processes controls how many parallel job scheduler jobs can be executed at one time.

CKPT - Checkpoint process writes checkpoint information to control files and data file headers.

CTWR - This is a new process Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.

CQJ0 - Job queue controller process wakes up periodically and checks the job log. If a job is due, it spawns Jnnnn processes to handle jobs.

DBWR - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from the database block cache to the database data files. Generally, DBWR only writes blocks back to the data files on commit, or when the cache is full and space has to be made for more blocks. The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.

FMON - The database communicates with the mapping libraries provided by storage vendors through an external non-Oracle Database process that is spawned by a background process called FMON. FMON is responsible for managing the mapping information. When you specify the FILE_MAPPING initialization parameter for mapping data files to physical devices on a storage subsystem, then the FMON process is spawned.

LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC, each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.

LMON - Lock Manager process

MMON - The Oracle 10g background process to collect statistics for the Automatic Workload Repository (AWR).

MMNL - This process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation.

MMAN - is used for internal database tasks that manage the automatic shared memory. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components.

PMON - Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON’s role as service registration agent is particularly important.

Pnnn - (Optional) Parallel Query Slaves are started and stopped as needed to participate in parallel query operations.

RBAL - This process coordinates rebalance activity for disk groups in an Automatic Storage Management instance.

SMON - System Monitor process recovers after instance failure and monitors temporary segments and extents. SMON in a non-failed instance can also perform failed instance recovery for other failed RAC instance.

WMON - The "wakeup" monitor process


Data Guard/Streams/replication Background processes

DMON - The Data Guard Broker process.

SNP - The snapshot process.

MRP - Managed recovery process - For Data Guard, the background process that applies archived redo log to the standby database.

ORBn - performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB - is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.

RFS - Remote File Server process - In Data Guard, the remote file server process on the standby database receives archived redo logs from the primary database.

QMN - Queue Monitor Process (QMNn) - Used to manage Oracle Streams Advanced Queuing.


Oracle Real Application Clusters (RAC) Background Processes


The following are the additional processes spawned for supporting the multi-instance coordination:

DIAG: Diagnosability Daemon – Monitors the health of the instance and captures the data for instance process failures.

LCKx - This process manages the global enqueue requests and the cross-instance broadcast. Workload is automatically shared and balanced when there are multiple Global Cache Service Processes (LMSx).
LMON - The Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage the global enqueues and the resources. LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON-provided services are also known as cluster group services (CGS)

LMDx - The Global Enqueue Service Daemon (LMD) is the lock agent process that manages enqueue manager service requests for Global Cache Service enqueues to control access to global enqueues and resources. The LMD process also handles deadlock detection and remote enqueue requests. Remote resource requests are the requests originating from another instance.

LMSx - The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages. Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster.

The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send it to the requesting instance. The LMSx also controls the flow of messages to remote instances.

The LMSn processes handle the blocking interrupts from the remote instance for the Global Cache Service resources by:

*Managing the resource requests and cross-instance call operations for the shared resources.
*Building a list of invalid lock elements and validating the lock elements during recovery.
*Handling the global lock deadlock detection and Monitoring for the lock conversion timeouts