IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement 11g new sql feature

With INSERT INTO TARGET...SELECT...FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted. A PL/SQL program could achieve the same effect by first selecting the source rows and by then inserting them one-by-one into the target in a block that has a null handler for the DUP_VAL_ON_INDEX exception. However, the PL/SQL approach would take effort to program and is much slower than the single SQL statement that this hint allows. This hint improves performance and ease-of-programming when implementing an online application upgrade script using edition-based redefinition.

How to define Order Import source in Order Management

When you are planning to import the orders from different sources, you need to setup the order source.
Here are steps to setup of the Order Import Source.

Once you login to Order Management switch to Order Management Superuser.

Setup --> Orders --> Import Sources

Once the Import Source form is opened, Click on the new button or press Ctrl+ down arrow and then
Enter the Order Import Source , Description and the check the Enabled check box. Here is screen shot for the same.

REUSE SETTINGS in the alter trigger

Prevents Oracle Database from dropping and reacquiring compiler switch settings. With this clause, Oracle preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in this statement.

ALTER TRIGGER oe.get_bal COMPILE REUSE SETTINGS

PURGE new SQL feature in Oracle 11g

Purpose

Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.

To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:

SELECT * FROM RECYCLEBIN;

SELECT * FROM USER_RECYCLEBIN;

Caution:

You cannot roll back a PURGE statement, nor can you recover an object after it is purged.

Prerequisites

The database object must reside in your own schema or you must have the DROP ANY ... system privilege for the type of object to be purged, or you must have the SYSDBA system privilege.

Semantics

TABLE or INDEX

Specify the name of the table or index in the recycle bin that you want to purge. You can specify either the original user-specified name or the system-generated name Oracle Database assigned to the object when it was dropped.

• If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
• System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database purges that specified object.

When the database purges a table, all table partitions, LOBs and LOB partitions, indexes, and other dependent objects of that table are also purged.

RECYCLEBIN

Use this clause to purge the current user's recycle bin. Oracle Database will remove all objects from the user's recycle bin and release all space associated with objects in the recycle bin.

DBA_RECYCLEBIN

This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user. This operation is useful, for example, before backward migration.

TABLESPACE tablespace

Use this clause to purge all the objects residing in the specified tablespace from the recycle bin.

USER user Use this clause to reclaim space in a tablespace for a specified user. This operation is useful when a particular user is running low on disk quota for the specified tablespace.

Examples

Remove a File From Your Recycle Bin: Example The following statement removes the table test from the recycle bin. If more than one version of test resides in the recycle bin, then Oracle Database removes the version that has been there the longest:

PURGE TABLE test;

To determine system-generated name of the table you want removed from your recycle bin, issue a SELECT statement on your recycle bin. Using that object name, you can remove the table by issuing a statement similar to 

the following statement. (The system-generated name will differ from the one shown in the example.)

PURGE TABLE RB$$33750$TABLE$0;

Remove the Contents of Your Recycle Bin: Example To remove the entire contents of your recycle bin, issue the following statement:

PURGE RECYCLEBIN;