Thursday, December 18, 2008

Bulk Binds

Oracle use two engine to process PLSQL block and subprogram. All SQL code is processed by SQL engine and PLSQL code is processed by PLSQL engine. When you run SQL code in the loop, oracle has to switch between SQL engine and PLSQL engine for each iteration. So there is overhead associated for each context switch. Bulk Bind is introduced in oracle8i to reduce the context switch.. This feature is enhanced in further versions...

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> CREATE TABLE emp(
2 empno NUMBER(10),
3 empname VARCHAR2(50));

Table created.

SQL>
SQL> ALTER TABLE emp ADD (
2 CONSTRAINT emppk PRIMARY KEY(empno));

Table altered.

SQL>


The total time taken to insert the 50,000 records through FOR LOOP is .071 Minuts. Because, 50,000 context switch happens in the FOR LOOP.

SQL> set serveroutput on
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF EMP%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 BEGIN
6 FOR i IN 1 .. 50000 LOOP
7 t_tab.extend;
8 t_tab(t_tab.last).empno := i;
9 t_tab(t_tab.last).empname := 'name:' To_Char(i);
10 END LOOP;
12 FOR i IN t_tab.first .. t_tab.last LOOP
13 INSERT INTO emp (empno, empname)
14 VALUES (t_tab(i).empno, t_tab(i).empname);
15 END LOOP;
16 COMMIT;
17 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
18 END;
19 /
Total time ... .071

PL/SQL procedure successfully completed.

SQL>

Here time taken to insert the 50,000 records through bulk binds is 0.015 Minuts. When we compared to above example, it is much faster.. since it is completed reduced the context switches between SQL engine to PLSQL engine.

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL>
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
4 t_tab emp_tab := emp_tab();
5 BEGIN
6 FOR i IN 1 .. 50000 LOOP
7 t_tab.extend;
9 t_tab(t_tab.last).empno := i;
10 t_tab(t_tab.last).empname := 'name:' To_Char(i);
11 END LOOP;
12
13 FORALL i IN t_tab.first .. t_tab.last
14 INSERT INTO emp VALUES t_tab(i);
15 COMMIT;
16 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
17 END;
18 /
Total time ... .015

PL/SQL procedure successfully completed.

SQL>

Bulk binds can also improve the performance when loading collection from queries. BULK COLLECT INTO clause can collect the data into collections. In the below example, it took 0.021 Minuts to fetch 50,000 records with out bulk binds

SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 CURSOR c_data IS
6 SELECT *
7 FROM emp;
8 BEGIN
9 FOR cur_rec IN c_data LOOP
10 t_tab.extend;
11 t_tab(t_tab.last).empno := cur_rec.empno;
12 t_tab(t_tab.last).empname := cur_rec.empname;
13 END LOOP;
14 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
15 END;
16 /
Total time ... .021

PL/SQL procedure successfully completed.

In the below case, it took 0.004 Minutes to fetch the data into collection. When we compared to above case, it is faster.. since it is using BULK COLLECT to reduce the context switch.

SQL>
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 BEGIN
6 SELECT empno,empname
7 BULK COLLECT INTO t_tab
8 FROM emp;
9 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
10 END;
11 /
Total time ... .004

PL/SQL procedure successfully completed.

SQL>

Oracle 9i Release 2 allows to use ROW key word to update the record. When we update, we can not use record type definition with bulk binds. The below case, we can not use bulk bind to improve the performance, since we are using record type definition. This is one restriction on this version(oracle9i R2).

SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
4 t_tab emp_tab := emp_tab();
5 BEGIN
6 FOR i IN 1 .. 10000 LOOP
7 t_tab.extend;
9 t_tab(t_tab.last).empno := i;
10 t_tab(t_tab.last).empname := 'name' To_Char(i);
11 END LOOP;
13 FOR i IN t_tab.first .. t_tab.last LOOP
14 UPDATE emp
15 SET ROW = t_tab(i)
16 WHERE empno = t_tab(i).empno;
17 END LOOP;
18 COMMIT;
19 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
20 END;
21 /
Total time ... .012

PL/SQL procedure successfully completed.

SQL>
The below case, we are using bulk bind since, we are not using record type definition. The time took to update the 50,000 records is 0.007 Minuts. When we compare with above case, the performance is improved well.

SQL> DECLARE
2 TYPE empno_tab IS TABLE OF emp.empno%TYPE;
3 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 t_empno empno_tab := empno_tab();
6 t_tab emp_tab := emp_tab();
7 BEGIN
8 FOR i IN 1 .. 10000 LOOP
9 t_empno.extend;
10 t_tab.extend;
11 t_empno(t_empno.last) := i;
12 t_tab(t_tab.last).empno := i;
13 t_tab(t_tab.last).empname := 'name ' To_Char(i);
14 END LOOP;
16 FORALL i IN t_tab.first .. t_tab.last
17 UPDATE emp
18 SET ROW = t_tab(i)
19 WHERE empno = t_empno(i);
21 COMMIT;
22 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
23 END;
24 /
Total time ... .007

PL/SQL procedure successfully completed.

SQL>

When to use Bulk Binds: There is no univeral rule exists to dictate when to use Bulk binds. If PLSQL code reads only few hundered records, then you will not see significant performance improvement for bulk binds. When you read huge number of records and have multiple insert/update, then you can think of bulk binds. If you have the luxury of time, you can test your code both with and without bulk binds and decide to go for this feature.

Thursday, November 6, 2008

Convert Non-Partitioned to Partitioned Table

This article is written in oracle10g R2. In any database environment, if we need to covert any huge table to partition table, then first we need to think about two factor.

1. Partition key
2. What type of partition we need to use.

Once we decided the above key factor, then there are couple of ways, we can convert the table to partition table. We have two option to convert the regular table to partition table.
Option 1
This option requires table down time and it might fit if business allows the application down time. Here are the steps to follow.

1. Stop using the table, make sure no one is chaning the data in table.
2. Create the partition table with the same structure of regular table.
3. Copy the data from regular table to partition table.
4. Create the constriant, keys, indexes on partition table.
5. Drop the original table
6. Analyze the partition table.

Option 2

This is ideal option to convert the regular table to partition table. It does not require down time and every one can use the table during the conversion. This option is introduced in oracle9i and enhanced to 10g. We are using online redefinition(DBMS_REDEFINITION) to convert the table to partition table.

Some restriction of using DBMS_REDEFINITION.

1· Cannot belong to SYS or SYSTEM Schema.
2. The table to be redefined online should not be using User-defined data types
3· Should not be a clustered table.
4. Should not have MV Logs or MVs defined
5. You cannot move a table from one schema to another using Online Redefinition feature.

Here are the steps to convert to partition table.

Step 1
Check to make sure that the table can use the online redefintion feature

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP');
PL/SQL procedure successfully completed.

Step 2
Create the temporary partition table as same structure of original table.

SQL> Create table EMP_part
2 (EMPNO NUMBER(4),
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2))
10 partition by range (SAL)
11 (Partition p1 values less than (1000),
12 Partition p2 values less than (2000),
13 Partition p3 values less than (3000),
14 Partition p4 values less than (4000),
15 Partition max values less than (maxvalue))
16 tablespace users;

Table created.


Step 3
Start the online redefinition process.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 4
Here is where oracle10g feature come into play. We do not need to copy any dependent objects to part_emp table. Dependent objects are like grants, synonym, triggers etc.

SQL> VARIABLE NUM_ERRORS NUMBER;
SQL> EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','EMP','EMP_PART', 1,TRUE,TRUE,TRUE,FALSE,
:NUM_ERRORS,FALSE);

PL/SQL procedure successfully completed.

SQL> PRINT NUM_ERRORS

NUM_ERRORS
----------
0


Step 5
Resync the table

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.
Step 6

Complete the online redefinition

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 7

Make sure emp table has all partitions as we created in emp_part table
SQL> Select partition_name, high_value from user_tab_partitions where table_name='EMP';

PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------------------------------
MAX MAXVALUE
P1 1000
P2 2000
P3 3000
P4 4000

SQL> select partition_name, high_value from user_tab_partitions where table_name='EMP_PART';

no rows selected

Step 8
Make sure all the dependent objects are copied .

SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS
2 WHERE TABLE_NAME='EMP';

TRIGGER_NAME
------------------------------
EMPTRIG

SQL> select constraint_name from user_constraints
2 where table_name='EMP';

CONSTRAINT_NAME
------------------------------
PK_EMP
FK_DEPTNO

SQL>

Note : The only problem i see here is, if we have any index on the original table, it will convert to global index on partition table. If we need the index to be local index, then we have to drop and recreate the index.

Wednesday, November 5, 2008

When to Use Index Organized table?

What is Index Organized table?

In Heap tables, oracle stores the data in data segment with ROWID. When we create the index, oracle stores the index key value and ROWID in the index segment. So the index key value and rowid are stored in both index & data segments. Whenever we search any data record, it first scan the index segment and find the rowid and match the rowid in data segment to get the particular record. So the drawback here is, rowid & index key value is stored in two places. The space consumption is one issue. Another issue is, oracle has to scan both data and index segment to obtain the record.

To overcome this problem, oracle introduced IOT in version 8. In IOT, the data is stored in the primary key index itself with the rows placed in a key-sequenced order, thus eliminating the necessity of duplicate data being stored in index and table. When we search the record, it does scan only on index segment.

IOT do not consider ROWID. IOT stores the data in B-tree index and sorts the data on primary key whenever we insert, update the record.


Example to create IOT

SQL> CREATE TABLE iot_emp
2 (empno number PRIMARY KEY,
3 empname varchar2(10),
4 salary number)
5 ORGANIZATION INDEX
6 TABLESPACE RPTS_STAGE_DATA
7 PCTTHRESHOLD 20
8 INCLUDING empname
9 OVERFLOW TABLESPACE ods_stage_data
10 /

Table created.

SQL>

ORGANIZATION INDEX denotes that the table is index organized table.

PCTTHRESHOLD Specifies the percentage of space reserved in the index block for an index-organized table row. Any portion of the row that exceeds the specified threshold is stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50. PCTTHRESHOLD must be large enough to hold the primary key.

OVERFLOW - Index-organized table data rows exceeding the specified threshold will be placed in overflow segment.

INCLUDING - which can be used to specify nonkey columns that are to be stored in the overflow data segment.

When to use IOT?

Large tables where primary key exact fetch is time critical and there is a low volume of DML operations. In most cases, in the category of exact fetch via primary key, IOT organization is much faster than HEAP. This advantage varies between 15% and 400%, depending upon the row length, number of columns and the use of the PCTTHRESHOLD option.

Heavy volume of DML operations occurring in IOT, usually fragments the table, requiring frequent table reorganization. An index-organized table can be reorganized without invalidating its secondary indexes, and can be performed online thus reducing or even eliminating downtime.

Index-organized tables support parallel features for loading, index creation, and scans required for handling large volumes of data. Partitioned index-organized tables are also supported, so that each partition can be loaded concurrently. Data warehousing applications using star schemas can also gain performance and scalability by implementing fact tables as index-organized tables for efficient execution of star queries. All these features make index-organized tables suitable for handling large scale data.

Advantage of IOT?

1. Scanning both data and index segment is eliminated.
2. Storage requirement is reduced as data is stored only in primary key index segment.

Thursday, October 23, 2008

Timestamp data type in Oracle9i

Oracle9i introduced new date and time related data types.

1. TIMESTAMP
2. TIMESTAMP WITH TIME ZONE
3. TIMESTAMP WITH LOCAL TIME ZONE
4. INTERVAL DAY TO SECOND
5. INTERVAL YEAR TO MONTH

We can also change the database timezone as below. We can use v$timezone_names dictionary to view all the time zone information

ALTER SESSION SET TIME_ZONE = 'PST'

TIMESTAMP datatype is very much like the DATE datatype. it can store both the date and time values. It is not like DATE datatype, you can also specify the precision of the factional seconds value in the TIMESTAMP datatype. The default is 6 digit, but we can specify a value within the range from 0 through 9.

SQL> create table calltrack(
2 callnum integer,
3 calltime TIMESTAMP(4));

Table created.

We can change the NLS_TIMESTAMP_FORMAT as we required...

SQL> alter session set nls_timestamp_format = 'MMDDYYHH24MISSFF';

Session altered.

Now insert one record and select the timestamp field.

SQL> insert into calltrack values(1,sysdate);

1 row created.

SQL> select calltime from calltrack;

CALLTIME
---------------------------------------------------------------------------
1024080953590000

SQL>

TIMESTAMP WITH TIME ZONE data type stores time zone displacement which needs additional bytes of storage. We can also change the NLS_TIMESTAMP_TZ_FORMAT at session level.
SQL> create table calltime(
2 callnum integer,
3 calltime timestamp(4) with time zone);

Table created.

SQL> insert into calltime values(1,sysdate);

1 row created.

SQL> select calltime from calltime;

CALLTIME
-------------------------------------------------------
04-APR-09 11.10.40.0000 PM -04:00

SQL> select vsize(calltime) from calltime;

VSIZE(CALLTIME)
---------------
13

TIMESTAMP WITH LOCAL TIME ZONE includes time zone information, but unlike the TIMESTAMP WITH TIME ZONE datatype. It does not store the time zone displacement in additional bytes of storage. Instead, it stores the time values normalized in terms of the database time zone. When user attempts to retrieve this information, the database dispalys the information in terms of local time zone of the user's session.

Let us create a table with this data type.

SQL> create table calltrack(
2 callnum integer,
3 calltime timestamp(4) with local time zone);

Table created.

SQL> insert into calltrack values(1,sysdate);

1 row created.

SQL> select calltime from calltrack;

CALLTIME
---------------------------------------------------------------------------
24-OCT-08 10.58.34.0000 AM
SQL>

Now the date is displaying with Eastern US time. Now let us change the database time zone to Pacific time on session level.

SQL> ALTER SESSION SET TIME_ZONE = 'PST';

Session altered.

SQL> select calltime from calltrack;

CALLTIME
---------------------------------------------------------------------------
24-OCT-08 07.58.34.0000 AM

SQL>

INTERVAL DAY TO SECOND can store a time interval value in terms of days, hours, minutes and seconds. We can specify the precision for the number of days in a range from 0 to 9, the default being 2. We can also specify the precision for the fractional seconds in a range from 0 to 9, the default being 6.

SQL CREATE TABLE promotions (
2 promotion_id INTEGER,
3 name VARCHAR2(30),
4 duration INTERVAL DAY(3) TO SECOND (4))
5 /

Table created.

SQL
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (1, '10% off Z Files', INTERVAL '3' DAY);

1 row created.

SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (2, '20% off Pop 3', INTERVAL '2' HOUR);

1 row created.

SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (3, '30% off Modern Science', INTERVAL '25' MINUTE);

1 row created.

SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (4, '20% off Tank War', INTERVAL '45' SECOND);

1 row created.

SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (5, '10% off Chemistry', INTERVAL '3 2:25' DAY TO MINUTE);

1 row created.

SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (6, '20% off Creative Yell',
3 INTERVAL '3 2:25:45' DAY TO SECOND);

1 row created.

SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '123 2:25:45.12' DAY(3) TO SECOND(2));

1 row created.

scott@orcl> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '1253 2:25:45.12' DAY(3) TO SECOND(2));
INTERVAL '1253 2:25:45.12' DAY(3) TO SECOND(2))
*
ERROR at line 3:
ORA-01873: the leading precision of the interval is too small


scott@orcl> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '153 2:25:415.12' DAY(3) TO SECOND(2))
4 select duration from promotions;
INTERVAL '153 2:25:415.12' DAY(3) TO SECOND(2))
*
ERROR at line 3:
ORA-01852: seconds must be between 0 and 59


scott@orcl>
SQL select duration from promotions;

DURATION
-------------------------------------------------------------------------
+003 00:00:00.0000
+000 02:00:00.0000
+000 00:25:00.0000
+000 00:00:45.0000
+003 02:25:00.0000
+003 02:25:45.0000
+123 02:25:45.1200

7 rows selected.

SQL
INTERVAL YEAR TO MONTH can store time interval value in terms of years and months. We can specify the precision for the number of years in a range from 0 to , default being 2.

SQL> CREATE TABLE coupons (
2 coupon_id INTEGER,
3 name VARCHAR2(30),
4 duration INTERVAL YEAR(3) TO MONTH);

Table created.

SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (1, '$1 off Z Files', INTERVAL '1' YEAR);

1 row created.

SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (2, '$2 off Pop 3', INTERVAL '11' MONTH);

1 row created.

SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (3, '$3 off Modern Science', INTERVAL '14' MONTH);

1 row created.

SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (4, '$2 off Tank War', INTERVAL '1-3' YEAR TO MONTH);

1 row created.

SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (5, '$1 off Chemistry', INTERVAL '0-5' YEAR TO MONTH);

1 row created.

SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (6, '$2 off Creative Yell', INTERVAL '123' YEAR(3));

1 row created.

SQL>
SQL> SELECT duration FROM coupons;

DURATION
---------------------------------------------------------------------------
+001-00
+000-11
+001-02
+001-03
+000-05
+123-00

6 rows selected.

SQL>

Merge statement in Oracle

Merge statement is introduced in oracle9i and it has enhanced in oracle10g. It is beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow, addressing all the needs of highly scalable data transformation inside the database. In data warehouse environment, when we refresh FACT table periodically, the source system cannot distinguish between newly inserted or changed information during the extraction. We need to scan the FACT table multiple times and it is becoming performance issue.

Prior to Oracle9i, these operations were expressed either as a sequence of DMLs (INSERT/UPDATE) or as PL/SQL loops deciding, for each row, whether to insert or update the data.

In oracle9i, it overcomes this problem and we can write both INSERT, UPDATE in one statement called MERGE. It reduces multiple scans and it improves the performance.

Here is sample Merge statement in oracle9i. The source_table is created as the same structure of ALL_OBJECTS with out any data.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHEN NOT MATCHED THEN
7 INSERT (object_id, status)
8 VALUES (b.object_id, b.status);

22278 rows merged.

SQL>

In oracle10g, merge statement is enhanced. The new enhancements are

1. Optional INSRET or UDPATE clause
2. Conditional operations for INSERT and UPDATE
3. We can use the delete statement on Merge

Optional INSRET or UDPATE clause

The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status);
VALUES (b.object_id, b.status)
*
ERROR at line 6:
ORA-00905: missing keyword

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status;
UPDATE SET a.status = b.status
*
ERROR at line 5:
ORA-00905: missing keyword

SQL>

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status);

28603 rows merged.

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status;

28603 rows merged.

SQL>
Conditional operations for INSERT and UPDATE

Conditional inserts and updates are now possible by using a WHERE clause on these statements

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production


SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 WHEN NOT MATCHED THEN
8 INSERT (object_id, status)
9 VALUES (b.object_id, b.status)
10 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword


SQL>
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status)
7 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 7:
ORA-00905: missing keyword


SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword


SQL>

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 WHEN NOT MATCHED THEN
8 INSERT (object_id, status)
9 VALUES (b.object_id, b.status)
10 WHERE b.status != 'VALID';

33 rows merged.

SQL>
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status)
7 WHERE b.status != 'VALID';

0 rows merged.

SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID';

33 rows merged.

SQL>

Delete statement on Merge

An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 DELETE WHERE (b.status = 'VALID');
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword


SQL>

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 DELETE WHERE (b.status = 'VALID');

33 rows merged.

SQL>

Restriction On Merge : We cannot update a column that is referenced in the ON condition clause.

Oracle External Tables

Oracle External tables are not like regular table. We can apply DML statement on regular table. But in external table, you can not apply DML statement except SELECT. We can not create index on external table. In oracle9i, read only operations are permitted, but in oracle10g, we can also write out data to an external table, although you can't write to an existing table.

Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the DB into a file in an Oracle-proprietary format, and back into the database from files of that format.

This article is written in oracle10g on windows.

Steps to create an External table

Step 1.

Create the directory on server as well as database.


C:\>mkdir ext
C:\>cd ext
C:\ext>

SQL> connect sys as sysdba
Enter password: ********
Connected.
SQL> create directory extdir
2 as 'c:/ext';

Directory created.

SQL> grant read,write on directory extdir to scott;

Grant succeeded.

SQL>

Step2

Place the text file on directory c:/ext location and create the external table as below.

Here is the text file content.

7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30
7698,BLAKE,MANAGER,30
7782,CLARK,MANAGER,10
7788,SCOTT,ANALYST,20
7839,KING,PRESIDENT,10
7844,TURNER,SALESMAN,30
7876,ADAMS,CLERK,20
7900,JAMES,CLERK,30
7902,FORD,ANALYST,20
7934,MILLER,CLERK,10

SQL> show user
USER is "SCOTT"
SQL> create table emptable
2 (empno number,
3 empname varchar2(50),
4 job varchar2(50),
5 deptno number)
6 organization external
7 ( default directory extdir
8 access parameters
9 ( records delimited by newline
10 fields terminated by ',')
11 location ('emp.txt'));

Table created.
SQL>
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from emptable;

EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10

14 rows selected.

SQL>

We could add log file, bad file, discard file for the external table.

SQL> alter table emptable
2 access parameters
3 ( records delimited by newline
4 badfile extdir:'emp.bad'
5 logfile extdir:'emp.log'
6 discardfile extdir:'emp.dsc'
7 fields terminated by ','
8 ) ;

Table altered.

SQL>

Loading data from external table to Regular table

We can load the data from external table to oracle table as below. We can do the same job through SQLLOADER. So, now the question is, when do we use the sqlloader and when do we use the external table for loading data from flat file to oracle table. External table has good filtering capability while loading data from external table to oracle table.

In the below example, we are loading data only for deptno 10 and 20. For this situation, it is worth to try external table... If we want to load only specific set of data from flat file, external table is good option.

SQL> create table empload_db as select * from
2 emptable where deptno in(10,20);

Table created.

SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from empload_db;

EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7566 7566 MANAGER 20
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7876 7876 CLERK 20
7902 7902 ANALYST 20
7934 7934 CLERK 10

8 rows selected.

SQL>

Loading data from regular table to external table.

This feature is introduced in oracle10g. We shoud use oracle_datapump driver to load data into external table.

SQL> create table load_ext
2 organization external
3 ( type oracle_datapump
4 default directory extdir
5 location ('emp.dmp')
6 ) as select * from emptable
7 /

Table created.

SQL>
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from load_ext;

EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10

14 rows selected.

SQL>


You can now move the file you just created, emp.dmp, to another system and create an external table to read the data:

SQL> create table import_load_ext
2 (empno number,
3 empname varchar2(50),
4 job varchar2(50),
5 deptno number)
6 organization external
7 ( type oracle_datapump
8 default directory extdir
9 location ('emp.dmp')
10 );

Table created.

SQL> set linesize 100
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from import_load_ext;

EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10

14 rows selected.

Restriction on external tables :

1. An external table does not allow INSERT, UPDATE, DELETE statement. We get ORA-30657: operation not supported on external organized table error when apply DML statement except SELECT.

2. An external table does not describe how data is stored in the external source.

3. An external table cannot load data into a LONG column.

4. Column name or table names are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier, it must be enclosed in double quotation marks.


If you want to read more on external tables... Please read here..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6611962171229

Friday, September 26, 2008

Create readonly user in oracle?

This article is tested in oracle9i/oracle10g.

Sometime, DBA requires to create a readonly schema to allow non-DBA users to monitor the database or application user wanted to only view the application data . When we create readonly user, it should have an ability to access all the DB dictionary includes v$ tables. We can also grant only select access to the application schema tables.

Here is the steps to setup the readonly schema. The below steps needs to be done in DBA users.

Step 1
create user readonly
identified by readonly;

Step 2
grant create synonym,
create session,
select any dictionary to readonly;

Step 3
create the synonym in readonly schema for application tables.

Now if we connect readonly schema, you can see application tables as well as all the database dictionary tables. You can also monitor the database. If you are using any version below oracle9i, you need to grant SELECT ANY CATALOG role instead of SELECT ANY DICTIONARY privilege.

How do we kill inactive session in oracle?

This article is witten in oracle9i. It should apply to oracle1og and further release too.

Inactive session
Oracle Marks the session as INACTIVE when the moment, session is ideal. These sessions are remain connected to the database with a status in v$session of INACTIVE. A user starts a program/session, then leaves it running and idle for an extended period of time. Some time, user connect to the database and go for coffee and chat with somebody and come back to desk and check mails. During this time, the session becomes INACTIVE.

Dead Connection
Sometime, user shutdown the machine without logout the database connection or network prevent to connect the database, then connection becomes dead connection. SQL NET detect the dead connection and release the resource. Please refer metalink id (Note:395505.1, Note:151972.1) for more details about DCD(dead connection deduction).

How do we kill the inactive session?

There are couple of ways to kill the inactive session.

Option 1
We can write the script to kill the session. The below script kills any session which is INACTIVE.

SELECT 'ALTER SYSTEM KILL SESSION '''''sid','serial#''''' immediate;'
FROM v$session
WHERE status ='INACTIVE'

In case, if you need to kill all the session which are inactive for last four hours, then the above script is not an option. The above command kills all the session regardless of when it becomes inactive. Next option is best option to kill the session which are inactive for certain period of time.

Option 2
We can disconnect the inactive session through Oracle profile. We can change the idle_time in the user profile. Here are the steps to change the idle_time.

Step1 First we need to change the resource_limit to TRUE. By default, it is FALSE.

SQL> select value from gv$parameter
2 where name='resource_limit';

VALUE
--------------------------------------------------------------------------------
FALSE

SQL> ALTER SYSTEM SET resource_limit=TRUE;
System altered.

Step2 Alter the profile to change the idle_time. Here i am using custom profile named developer.

SQL> ALTER profile developer limit idle_time 240;

Profile altered.

Step 3 If you are not using developer profile for application schema, then assign this profile to application schema.

SQL> alter user app_data profile developer;

User altered.

Tuesday, September 23, 2008

Steps to build Oracle Table level Streams?

Oracle Streams were introduced in oracle 9.2. Oracle Streams are a generic mechanism to replicate data between one database to another database. The processing of streams is divided into three main processes(Capture, Staging and Apply).

This article provides step by step instruction to build the three way streams. This article is tested in oracle 9.2.0.8. We have three database DB1, DB2, DB3. The steam is setup on dept table for scott schema on all three instance. Any changes(DML/DDL) made on any of the database will be replicate to other two database.

Instance Setup

The below init parmeters needs to be set on intance level for all three database(DB1,DB2,DB3).

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;
ALTER SYSTEM SET AQ_TM_PROCESSES = 2;
ALTER SYSTEM SET GLOBAL_NAMES = TRUE;
ALTER SYSTEM SET COMPATIBLE='9.2.0' SCOPE = SPFILE;
ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Stream admin Setup on DB1

CONNECT SYS/password@db1 AS SYSDBA;


CREATE TABLESPACE LOGMNRTS DATAFILE 'C:/ORACLE/ORADATA/DB1/logmnrts.dbf' SIZE 25M
AUTOEXTEND ON MAXSIZE UNLIMITED
/

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/

create user strmadmin identified by strmadmin
/

alter user strmadmin default tablespace LOGMNRTS
/

grant connect,resource,aq_administrator_role,dba to strmadmin
/

GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;


BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

Stream admin Setup on DB2

CONNECT SYS/PASSWORD@DB2 AS SYSDBA;

CREATE TABLESPACE LOGMNRTS DATAFILE 'C:/ORACLE/ORADATA/DB2/logmnrts.dbf' SIZE 25M
AUTOEXTEND ON MAXSIZE UNLIMITED
/


BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/

create user strmadmin identified by strmadmin
/

alter user strmadmin default tablespace LOGMNRTS
/

grant connect,resource,aq_administrator_role,dba to strmadmin
/

GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;


BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

Stream admin Setup on DB3

CONNECT SYS/password@db3 AS SYSDBA;

CREATE TABLESPACE LOGMNRTS DATAFILE 'C:/ORACLE/ORADATA/DB3/logmnrts.dbf' SIZE 25M
AUTOEXTEND ON MAXSIZE UNLIMITED
/

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/

create user strmadmin identified by strmadmin
default tablespace LOGMNRTS
/

grant connect,resource,aq_administrator_role,dba to strmadmin
/

GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;


BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

Define and start the Apply Process in DB1
connect strmadmin/strmadmin@db1;

CREATE DATABASE LINK DB2
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2)
)
)'
/

CREATE DATABASE LINK DB3
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db3)
)
)'
/


begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_IN_QUEUE',
queue_user => 'STRMADMIN' );
end;
/

begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_OUT_QUEUE',
queue_user => 'STRMADMIN' );
end;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB2',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB3',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/


BEGIN
dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB2',
apply_user => 'SCOTT');

dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB3',
apply_user => 'SCOTT');

END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB2',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB3',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

END;
/


declare
v_started number;
begin
select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB2';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB2' );
end if;

select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB3';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB3' );
end if;

end;
/


Define and start the Apply Process in DB2

CONNECT strmadmin/strmadmin@DB2;

CREATE DATABASE LINK DB1
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)'
/


CREATE DATABASE LINK DB3
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db3)
)
)'
/


begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_IN_QUEUE',
queue_user => 'STRMADMIN' );
end;
/

begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_OUT_QUEUE',
queue_user => 'STRMADMIN' );
end;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB1',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB3',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/


BEGIN
dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB1',
apply_user => 'SCOTT');

dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB3',
apply_user => 'SCOTT');

END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB1',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB3',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

END;
/


declare
v_started number;
begin
select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB1';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB1' );
end if;

select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB3';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB3' );
end if;

end;
/


Define and start the Apply Process in DB3

CONNECT strmadmin/strmadmin@DB3;

CREATE DATABASE LINK DB1
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)'
/


CREATE DATABASE LINK DB2
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING
' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2)
)
)'
/


begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_IN_QUEUE',
queue_user => 'STRMADMIN' );
end;
/

begin
dbms_streams_adm.set_up_queue(
queue_table => 'DB_STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE TOOLS',
queue_name => 'DB_STREAMS_OUT_QUEUE',
queue_user => 'STRMADMIN' );
end;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB1',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'APPLY',
streams_name => 'DB_APPLY_DB2',
queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/



BEGIN
dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB1',
apply_user => 'SCOTT');

dbms_apply_adm.alter_apply(
apply_name => 'DB_APPLY_DB2',
apply_user => 'SCOTT');

END;
/

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB1',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'DB_APPLY_DB2',
parameter => 'DISABLE_ON_ERROR',
value => 'N' );

END;
/



declare
v_started number;
begin
select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB1';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB1' );
end if;

select decode(status,'ENABLED',1,0) into v_started
from dba_apply
where apply_name = 'DB_APPLY_DB2';

if ( v_started = 0 ) then
dbms_apply_adm.start_apply( apply_name => 'DB_APPLY_DB2' );
end if;

end;
/

Setup Propagation Rule for DB1


CONNECT SYS/PASSWORD@DB1 AS SYSDBA
ALTER TABLE SCOTT.DEPT DROP SUPPLEMENTAL LOG GROUP log_group_dept_pk;

ALTER TABLE SCOTT.DEPT ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (DEPTNO,DNAME,LOC) ALWAYS;

CONNECT STRMADMIN/STRMADMIN@DB1

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'CAPTURE',
streams_name => 'DB_CAPTURE',
queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB2',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB2',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB3',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB3',
include_dml => true,
include_ddl => true,
source_database => 'DB1');
END;
/

Setup Propagation Rule for DB2


CONNECT SYS/PASSWORD@DB2 AS SYSDBA

ALTER TABLE SCOTT.DEPT DROP SUPPLEMENTAL LOG GROUP log_group_dept_pk;

ALTER TABLE SCOTT.DEPT ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (DEPTNO,DNAME,LOC) ALWAYS;


CONNECT STRMADMIN/STRMADMIN@DB2

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'CAPTURE',
streams_name => 'DB_CAPTURE',
queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB1',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB1',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB3',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB3',
include_dml => true,
include_ddl => true,
source_database => 'DB2');
END;
/

Setup Propagation Rule for DB3

CONNECT SYS/PASSWORD@DB3 AS SYSDBA

ALTER TABLE SCOTT.DEPT DROP SUPPLEMENTAL LOG GROUP log_group_dept_pk;
ALTER TABLE SCOTT.DEPT ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (DEPTNO,DNAME,LOC) ALWAYS;


CONNECT STRMADMIN/STRMADMIN@DB3

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'CAPTURE',
streams_name => 'DB_CAPTURE',
queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB1',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB1',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'DB_PROPAGATE_DB2',
source_queue_name => 'STRMADMIN.DB_STREAMS_OUT_QUEUE',
destination_queue_name => 'STRMADMIN.DB_STREAMS_IN_QUEUE@DB2',
include_dml => true,
include_ddl => true,
source_database => 'DB3');
END;
/
Setup Instantiation SCN for DB1

CONNECT STRMADMIN/STRMADMIN@DB1

set echo on
set feedback on
set serverout on

declare
iscn number;
begin
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: 'iscn );
end;
/

CONNECT STRMADMIN/STRMADMIN@DB2

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB1',
instantiation_scn => iscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB3

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB1',
instantiation_scn => iscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB1

EXEC DBMS_CAPTURE_ADM.START_CAPTURE( CAPTURE_NAME => 'DB_CAPTURE')
/
Setup Instantiation SCN for DB2

CONNECT STRMADMIN/STRMADMIN@DB2

set echo on
set feedback on
set serverout on

declare
iscn number;
begin
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: 'iscn );
end;
/

CONNECT STRMADMIN/STRMADMIN@DB1

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB2',
instantiation_scn => vscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB3

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB2',
instantiation_scn => vscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB2

EXEC DBMS_CAPTURE_ADM.START_CAPTURE( CAPTURE_NAME => 'DB_CAPTURE')
/
Setup Instantiation SCN for DB3

CONNECT STRMADMIN/STRMADMIN@DB3

set echo on
set feedback on
set serverout on

declare
iscn number;
begin
iscn :=
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is: 'iscn );
end;
/

CONNECT STRMADMIN/STRMADMIN@DB1

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB3',
instantiation_scn => vscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB2

BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.DEPT',
source_database_name => 'DB3',
instantiation_scn => vscn);
END;
/

CONNECT STRMADMIN/STRMADMIN@DB3

EXEC DBMS_CAPTURE_ADM.START_CAPTURE( CAPTURE_NAME => 'DB_CAPTURE')
/
Stream Testing

Scenario 1

Here is the records in dept table for DB1 instance
scott@DB1.US.ORACLE.COM> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

scott@DB1.US.ORACLE.COM>

Now insert one record in DB1 instance and check this record is replicated in DB2, DB3 instance.

scott@DB1.US.ORACLE.COM> insert into
2 dept values(50,'IT','HOUSTON');

1 row created.

scott@DB1.US.ORACLE.COM> COMMIT;

Commit complete.

scott@DB1.US.ORACLE.COM>

scott@DB2.US.ORACLE.COM> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT HOUSTON

scott@DB2.US.ORACLE.COM>

scott@DB3.US.ORACLE.COM> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT HOUSTON

scott@DB3.US.ORACLE.COM
>

Now the changes in DB1 is replicated to DB2, DB3.

Scenario 2

Let us update the record in DB2 and see the changes are replicating to DB1, DB3.

scott@DB2.US.ORACLE.COM> UPDATE dept
2 set dname='Testing'
3 where deptno=50;

1 row updated.

scott@DB2.US.ORACLE.COM> commit;

Commit complete.

scott@DB2.US.ORACLE.COM>

scott@DB1.US.ORACLE.COM> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 Testing HOUSTON

scott@DB1.US.ORACLE.COM>

scott@DB3.US.ORACLE.COM> SELECT * FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 Testing HOUSTON

scott@DB3.US.ORACLE.COM>

Now the changes in DB2 is replicated to DB1, DB3.

Scenario 3

Now delete one record in DB3 instance and check this record is replicated in DB1, DB2 instance.

scott@DB3.US.ORACLE.COM> delete dept
2 where deptno=50;

1 row deleted.

scott@DB3.US.ORACLE.COM> commit;

Commit complete.

scott@DB3.US.ORACLE.COM>
scott@DB1.US.ORACLE.COM> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

scott@DB1.US.ORACLE.COM>
scott@DB2.US.ORACLE.COM> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

scott@DB2.US.ORACLE.COM>

Optimizer Mode - FIRST_ROWS Vs ALL_ROWS

What circumstances we use ALL_ROWS and what circumstances we use FIRST_ROWS optimizer mode? This article is written in oracle9i.

First_rows attempts to optimize the query to get the very first row back to the client as fast as possible. This is good for an interactive client server environment where the client runs a query and shows the user the first 10 rows or so and waits for them to page down to get more.

All_rows attempts to optimize the query to get the very last row as fast as possible. This makes sense in a stored procedure for example where the client does not regain control until the stored procedure completes. You don't care if you have to wait to get the first row if the last row gets back to you twice as fast. In a client server/interactive application you may well care about that.

In TOAD or SQL Navigator, When we select the data, it display immediately. But it does not mean that, it is faster. If we scroll down, it might be fetching the data in the background mode. First_rows is best place for OLTP environment. Also in some reporting environment, if user wants to see initial data first and later see the rest of the data, then first_rows is good option. When we run the query in the stored procedure, first_rows would not be a good choice, all_rows is good option here, because, there is no use to fetch the first few records immediatley inside the stored procedure.

Let us demonstrate the FIRST_ROWS/ALL_ROWS optimizer hint.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table testtable as select * from user_objects;

Table created.

SQL> create index idx on testtable(object_type);

Index created.

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCREPORT',TABNAME => 'TESTTABLE',ESTIMATE_PER
CENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> select count(*) from testtable;

COUNT(*)
----------
5619712

SQL> select count(*) from testtable where object_type='TABLE';

COUNT(*)
----------
2392064

SQL> set autotrace traceonly exp;

SQL> select /*+ all_rows */ * from testtable where object_type='TABLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=4316 Card=62
3914 Bytes=53032690)

1 0 TABLE ACCESS (FULL) OF 'TESTTABLE' (Cost=4316 Card=623914
Bytes=53032690)

In TESTTABLE table, we have around 5 million records, the above query returns half of the records. Optimizer use full table scan when we use all_rows hint. Because, it needs to read all the rows before it display the data in the screen. The cost for the all_rows is 4316.


SQL> select /*+ first_rows */ * FROM TESTTABLE where object_type='TABLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=51502 Card
=623914 Bytes=53032690)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTTABLE' (Cost=51502 C
ard=623914 Bytes=53032690)

2 1 INDEX (RANGE SCAN) OF 'IDX' (NON-UNIQUE) (Cost=1604 Card
=623914)

Optimizer use the index scan when we use the first_rows hint. Because, it use the index scan and reads first few rows to display in the screen, then it reads the rest of the data. The cost is 51502.
so first_rows hint looks faster, but it is really not. In this example, the cost is 12 times more in index scan(first_rows) when we compared to All_rows(full table scan).

When do we use FIRST_ROWS?

To answer this question, we can use first_rows when user want to see the first few rows immediately. It is mostly used in OLTP, some reporting environment.

When do we use ALL_ROWS?

To answer this question, we can use all_rows when user want to process all the rows before we see the output.. Mostly used in OLAP. All_rows use less resource when compared to first_rows.

Important factor in FIRST_ROWS

1. It prefer to use the index scan
2. It prefer to use nested loop join over hash joins. Because, nested loop joins data as selected. but hash join hashes the data in hash table which takes time.
3. Good for OLTP

Important factor in ALL_ROWS

1. It use both index scan & full table scan depends on how many blocks optimizer is reading in the table.
2. Good for OLAP
3. It most likly to use hash join, again depends upon other factors.

Saturday, September 13, 2008

Nested Loop, Hash Join, Sort Merge Join, Cartesian join difference

This article is written in oracle 9.2.0.8.

Nested loop Joins The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. It drives from the outer loop to the inner loop. The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. It is inefficient when join returns large number of rows (typically, more than 10,000 rows is considered large), and the optimizer might choose not to use it.

The cost is calculated as below.
cost = access cost of A + (access cost of B * no_of_rows from A)

A nested loop join involves the following steps:

1. The optimizer determines the driving table and designates it as the outer table.
2. The other table is designated as the inner table.
3. For every row in the outer table, Oracle accesses all the rows in the inner table.

For instance,
scott@DB1.US.ORACLE.COM> SELECT emp.empno, dept.dname
2 FROM EMP , DEPT
3 WHERE dept.deptno = 10
4 AND emp.deptno = dept.deptno
5 /

EMPNO DNAME
---------- --------------
7782 ACCOUNTING
7839 ACCOUNTING
7934 ACCOUNTING

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=5 Bytes=85)
1 0 NESTED LOOPS (Cost=3 Card=5 Bytes=85)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=10)
3 2 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=5 Bytes=35)

We can also force the Nested loop join hint as below.

SELECT /*+ USE_NL(emp dept) */ emp.empno, dept.dname
FROM EMP , DEPT WHERE dept.deptno = 10
AND emp.deptno = dept.deptno

Hash Join Hash joins are used for joining large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then scans the larger table, probing the hash table to find the joined rows. This method is best used when the smaller table fits in available memory. The optimizer uses a hash join to join two tables if they are joined using an equijoin(joins with equals predicates) and large amount of data needs to be joined.

The cost of a Hash loop join is calculated by the following formula:
cost=(access cost of A*no_of_hash partitions of B) + access cost of B

For instance,
scott@DB1.US.ORACLE.COM> ;
1 SELECT emp.empno, dept.dname
2 FROM EMP , DEPT
3* WHERE emp.deptno = dept.deptno
scott@DB1.US.ORACLE.COM>
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=238)
1 0 HASH JOIN (Cost=5 Card=14 Bytes=238)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=7 Bytes=70)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)

We can also force the Hash join hint as below.
SELECT /*+USE_HASH(emp dept) */ emp.empno, dept.dname
FROM EMP , DEPT
WHERE emp.deptno = dept.deptno

Sort-Merge Join Sort merge joins can be used to join rows from two independent sources. Hash joins generally perform better than sort merge joins. On the other hand, sort merge joins can perform better than hash joins if both of the following conditions exist:

1. The row sources are sorted already.
2. A sort operation does not have to be done.

Sort merge joins are almost exclusively used for non-equi joins (>, <, BETWEEN). Sort merge joins perform better than nested loop joins for large data sets. (You cannot use hash joins unless there is an equality condition). In a merge join, there is no concept of a driving table.
The join consists of two steps:
Sort join operation: Both the inputs are sorted on the join key.
Merge join operation: The sorted lists are merged together.

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

1. The join condition between two tables is not an equi-join.
2. OPTIMIZER_MODE is set to RULE.
3. HASH_JOIN_ENABLED is false.
4. Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.
5. The optimizer thinks that the cost of a hash join is higher, based on the settings of HASH_AREA_SIZE and SORT_AREA_SIZE.

The cost of a sort merge join is calculated by the following formula:
cost = access cost of A + access cost of B + (sort cost of A + sort cost of B)

For instance, scott@DB1.US.ORACLE.COM> SELECT emp.empno, dept.dname
2 FROM EMP , DEPT
3 WHERE emp.deptno < dept.deptno

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=5 Bytes=85)
1 0 MERGE JOIN (Cost=6 Card=5 Bytes=85)
2 1 SORT (JOIN) (Cost=2 Card=7 Bytes=70)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=7 Bytes=70)
4 3 INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card =7)
5 1 SORT (JOIN) (Cost=4 Card=14 Bytes=98)
6 5 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)

We can also force the Hash join hint as below.

scott@DB1.US.ORACLE.COM> SELECT /*+USE_MERGE(emp dept) */
emp.empno, dept.dname
2 FROM EMP , DEPT
3 WHERE emp.deptno < dept.deptno

Cartesian join A Cartesian join is used when one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with all the row from the other data source, creating the Cartesian product of the two sets.

For instance,
scott@DB1.US.ORACLE.COM> select * from emp,dept;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=98 Bytes=5194)
1 0 MERGE JOIN (CARTESIAN) (Cost=16 Card=98 Bytes=5194)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=7 Bytes=112)
3 1 BUFFER (SORT) (Cost=14 Card=14 Bytes=518)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=518)

Friday, September 12, 2008

Shutdown startup procedure for Standby database

This article is written  and tested in oracle 9.2.0.8. Please remember, Oracle11g has Active Data Guard and it will be different steps. Please do not follow this topic for Oracle 11g and above version!

Please refer Click for Oracle11g standby database bounce.

Startup and shutdown the standby database is different then the Primary database. If we want to shutdown the primary and standby database both, it would be better to shutdown primary DB first, then shutdown the standby DB. If we shutdown the PRIMARY DB first, then all the changes will be shipped to standby.
Shutdown procedure for Primary/standby
Step1 : Tail the alert log for primary and standby.
Login to primary and standby server and tail the alert log as below.
tail -f  alert_$ORACLE_SID.log

Step2 : Make sure application is down and no activity on the database.

Step3 :  To be safer side, switch the log file few times and flush out the redo logs. Then shutdown the primary database.
alter system archive log current;
alter system archive log current;
alter system archive log current;
shutdown immediate;
Just make sure all the archive logs are shipped to standby.

Step4 : Once the primary DB shutdown is successful, then steps to shutdown standby DB.
alter database recover managed standby database cancel;
shutdown immediate

Startup procedure for Primary/standby
Step1 :  Start the primary database
startup
Application team can start the application against the primary database.
Enable the log shipping in primary database.
alter system set log_archive_dest_state_2='ENABLE' scope=both;
At this point, you might see some error message on alert log. since primary is trying to connect standby and still standby is not yet up and running.

Step2 : Start the standby database
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session

Watch out the alert log and make sure log shipping is working fine and no error on the alert log. Verify the v$archived_log and make sure archive logs are applying on standby database.
Please remember, the above steps works till Oracle10g. Starting from Oracle11g, Oracle introduced Active Data Guard and it will be different steps to bounce the standby data base. Please click here to view the steps to bounce the Oracle11g Active Data Guard.

Monday, August 25, 2008

Expdp / Impdp

Data pump is a new feature in Oracle10g that provides fast parallel data load. With direct path and parallel execution, data pump is several times faster then the traditional exp/imp. Traditional exp/imp runs on client side. But impdp/expdp runs on server side. So we have much control on expdp/expdp compared to traditional exp/imp. When compared to exp/imp, data pump startup time is longer. Because, it has to setup the jobs, queues, and master table. Also at the end of the export operation the master table data is written to the dump file set, and at the beginning of the import job the master table is located and loaded in the schema of the user.

Following are the process involved in the data pump operation:

Client Process : This process is initiated by client utility. This process makes a call to the data pump API. Once the data pump is initiated, this process is not necessary for the progress of the job.

Shadow Process : When client log into the database, foreground process is created. It services the client data pump API requests. This process creates the master table and creates Advanced queuing queues used for communication. Once client process ends, shadow process also go away.

Master Control Process : MCP controls the execution of the data pump job. There is one MCP per job. MCP divides the data pump job into various metadata and data load or unload jobs and hands them over to the worker processes.

Worker Process : MCP creates worker process based on the valule of the PARALLEL parameter. The worker process performs the task requested by MCP.

Advantage of Data pump

1. We can perform export in parallel. It can also write to multiple files on different disks. (Specify parameters PARALLEL=2 and the two directory names with file specification DUMPFILE=ddir1:/file1.dmp, DDIR2:/file2.dmp)

2. Has ability to attach and detach from job, monitor the job progress remotely.

3. Has more option to filter metadata objects. Ex, EXCLUDE, INCLUDE

4. ESTIMATE_ONLY option can be used to estimate disk space requirements before performs the job

5. Data can be exported from remote database by using Database link

6. Explicit DB version can be specified, so only supported object types are exported.

7. During impdp, we can change the target file names, schema, and tablespace. Ex, REMAP_SCHEMA, REMAP_DATAFILES, REMAP_TABLESPACE

8. Has the option to filter data rows during impdp. Traditional exp/imp, we have this filter option only in exp. But here we have filter option on both impdp, expdp.

9. Data can be imported from one DB to another without writing to dump file, using NETWORK_LINK parameter.

10. Data access methods are decided automatically. In traditional exp/imp, we specify the value for the parameter DIRECT. But here, it decides where direct path can not be used , conventional path is used.

11. Job status can be queried directly from data dictionary(For example, dba_datapump_jobs, dba_datapump_sessions etc)

Exp & Expdp common parameters: These below parameters exists in both traditional exp and expdp utility.

FILESIZE
FLASHBACK_SCN
FLASHBACK_TIME
FULL
HELP
PARFILE
QUERY
TABLES
TABLESPACES
TRANSPORT_TABLESPACES(exp value is Y/N, expdp value is name of the tablespace)

Comparing exp & expdp parameters: These below parameters are equivalent parameters between exp & expdp. Exp and corresponding Expdp parameters...

FEEDBACK => STATUS
FILE => DUMPFILE
LOG => LOGFILE
OWNER => SCHEMAS
TTS_FULL_CHECK => TRANSPROT_FULL_CHECK

New parameters in expdp Utility

ATTACH Attach the client session to existing data pump jobs

CONTENT Specify what to export(ALL, DATA_ONLY, METADATA_ONLY)

DIRECTORY Location to write the dump file and log file.

ESTIMATE Show how much disk space each table in the export job consumes.

ESTIMATE_ONLY It estimate the space, but does not perform export

EXCLUDE List of objects to be excluded

INCLUDE List of jobs to be included

JOB_NAME Name of the export job

KEEP_MASTER Specify Y not to drop the master table after export

NETWORK_LINK Specify dblink to export from remote database

NOLOGFILE Specify Y if you do not want to create log file

PARALLEL Specify the maximum number of threads for the export job

VERSION DB objects that are incompatible with the specified version will not be exported.

ENCRYPTION_PASSWORD The table column is encrypted, then it will be written as clear text in the dump file set when the password is not specified. We can define any string as a password for this parameter.

COMPRESSION Specifies whether to compress metadata before writing to the dump file set. The default is METADATA_ONLY. We have two values(METADATA_ONLY,NONE). We can use NONE if we want to disable during the expdp.

SAMPLE - Allows you to specify a percentage of data to be sampled and unloaded from the source database. The sample_percent indicates the probability that a block of rows will be selected as part of the sample.

Imp & Impdp common parameters: These below parameters exist in both traditional imp and impdp utility.

FULL
HELP
PARFILE
QUERY
SKIP_UNUSABLE_INDEXES
TABLES
TABLESPACES

Comparing imp & impdp parameters: These below parameters are equivalent parameters between imp & impdp. imp and corresponding impdp parameters...

DATAFILES => TRANSPORT_DATAFILES
DESTROY =>REUSE_DATAFILES
FEEDBACK =>STATUS
FILE =>DUMPFILE
FROMUSER =>SCHEMAS, REMAP_SCHEMAS
IGNORE =>TABLE_EXISTS_ACTION(SKIP,APPEND,TRUNCATE,REPLACE)
INDEXFILE, SHOW=>SQLFILE
LOG =>LOGFILE
TOUSER =>REMAP_SCHEMA

New parameters in impdp Utility

FLASHBACK_SCN Performs import operation that is consistent with the SCN specified from the source database. Valid only when NETWORK_LINK parameter is used.

FLASHBACK_TIME Similar to FLASHBACK_SCN, but oracle finds the SCN close to the time specified.

NETWORK_LINK Performs import directly from a source database using database link name specified in the parameter. The dump file will be not be created in server when we use this parameter. To get a consistent export from the source database, we can use the FLASHBACK_SCN or FLASHBACK_TIME parameters. These two parameters are only valid when we use NETWORK_LINK parameter.

REMAP_DATAFILE Changes name of the source DB data file to a different name in the target.

REMAP_SCHEMA Loads objects to a different target schema name.

REMAP_TABLESPACE Changes name of the source tablespace to a different name in the target.

TRANSFORM We can specify that the storage clause should not be generated in the DDL for import. This is useful if the storage characteristics of the source and target database are different. The valid values are SEGMENT_ATTRIBUTES, STORAGE. STORAGE removes the storage clause from the CREATE statement DDL, whereas SEGMENT_ATTRIBUTES removes physical attributes, tablespace, logging, and storage attributes.

TRANSFORM = name:boolean_value[:object_type], where boolean_value is Y or N.

For instance, TRANSFORM=storage:N:table

ENCRYPTION_PASSWORD It is required on an import operation if an encryption password was specified on the export operation.

CONTENT, INCLUDE, EXCLUDE are same as expdp utilities.

Prerequisite for expdp/impdp:

Set up the dump location in the database.

system@orcl> create directory dumplocation
2 as 'c:/dumplocation';

Directory created.

system@orcl> grant read,write on directory dumploc to scott;

Grant succeeded.

system@orcl>

Let us experiment expdp & impdp utility as different scenario...... We have two database orcl, ordb. All the below scenarios are tested in Oracle10g R2 version.


Scenario1 Export the whole orcl database.

Export Parfile content:

userid=system/password@orcl
dumpfile=expfulldp.dmp
logfile=expfulldp.log
full=y
directory=dumplocation

Scenario2 Export the scott schema from orcl and import into ordb database. While import, exclude some objects(sequence,view,package,cluster,table). Load the objects which came from RES tablespace into USERS tablespace in target database.

Export Parfile content:

userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
schemas=scott

Import parfile content:

userid=system/password@ordb
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace
remap_tablespace=res:users
exclude=sequence,view,package,cluster,table:"in('LOAD_EXT')"

Scenario3 Export the emp table from scott schema at orcl instance and import into ordb instance.

Expdb parfile content:

userid=system/password@orcl
logfile=tableexpdb.log
directory=dumplocation
tables=scott.part_emp
dumpfile=tableexpdb.dmp

Impdp parfile content:

userid=system/password@ordb
dumpfile=tableexpdb.dmp
logfile=tabimpdb.log
directory=dumplocation
table_exists_action=REPLACE

Scenario4 Export only specific partition in emp table from scott schema at orcl and import into ordb database.

Expdp parfile content:

userid=system/password@orcl
dumpfile=partexpdb.dmp
logfile=partexpdb.log
directory=dumplocation
tables=scott.part_emp:part10,scott.part_emp:part20

Impdp parfile content: If we want to overwrite the exported data in target database, then we need to delete emp table for deptno in(10,20).

scott@ordb> delete part_emp where deptno=10;

786432 rows deleted.

scott@ordb> delete part_emp where deptno=20;

1310720 rows deleted.

scott@ordb> commit;

Commit complete.

userid=system/password@ordb
dumpfile=partexpdb.dmp
logfile=tabimpdb.log
directory=dumplocation
table_exists_action=append

Scenario5 Export only tables in scott schema at orcl and import into ordb database.

Expdp parfile content:

userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
include=table
schemas=scott

Impdp parfile content:

userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace

Scenario6 Export only rows belonging to department 10 and 20 in emp and dept table from orcl database. Import the dump file in ordb database. While importing, load only deptno 10 in target database.

Expdp parfile content:

userid=system/password@orcl
dumpfile=data_filter_expdb.dmp
logfile=data_filter_expdb.log
directory=dumplocation
content=data_only
schemas=scott
include=table:"in('EMP','DEPT')"
query="where deptno in(10,20)"

Impdp parfile content:

userid=system/password@ordb
dumpfile=data_filter_expdb.dmp
logfile=data_filter_impdb.log
directory=dumplocation
schemas=scott
query="where deptno = 10"
table_exists_action=APPEND

Scenario7 Export the scott schema from orcl database and split the dump file into 50M sizes. Import the dump file into ordb datbase.

Expdp parfile content:

userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
filesize=50M
schemas=scott
include=table

As per the above expdp parfile, initially, schemaexp_split_01.dmp file will be created. Once the file is 50MB, the next file called schemaexp_split_02.dmp will be created. Let us say, the dump file size is 500MB, then it creates 10 dump file as each file size is 50MB.
Impdp parfile content:

userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant

Scenario8 Export the scott schema from orcl database and split the dump file into four files. Import the dump file into ordb datbase.

Expdp parfile content:

userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
parallel=4
schemas=scott
include=table

As per the above parfile content, initially four files will be created - schemaexp_split_01.dmp, schemaexp_split_02.dmp, schemaexp_split_03.dmp, schemaexp_split_04.dmp. Notice that every occurrence of the substation variable is incremented each time. Since there is no FILESIZE parameter, no more files will be created.

Impdp parfile content:

userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant

Scenario9 Export the scott schema from orcl database and split the dump file into three files. The dump files will be stored in three different location. This method is especially useful if you do not have enough space in one file system to perform the complete expdp job. After export is successful, import the dump file into ordb database.

Expdp parfile content:

userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
filesize=50M
schemas=scott
include=table

As per above expdp par file content, it place the dump file into three different location. Let us say, entire expdp dump file size is 1500MB. Then it creates 30 dump files(each dump file size is 50MB) and place 10 files in each file system.

Impdp parfile content:

userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
table_exists_action=replace

Scenario10 We are in orcl database server. Now export the ordb data and place the dump file in orcl database server. After expdp is successful, import the dump file into orcl database. When we use network_link, the expdp user and source database schema users should have identical privileges. If there no identical privileges, then we get the below error.

C:\impexpdp>expdp parfile=networkexp1.par

Export: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 12:06:40

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

Expdp parfile content:

userid=scott/tiger@orcl
logfile=netwrokexp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
schemas=scott
include=table
network_link=ordb

As per the above parfile, expdp utility exports the ordb database data and place the dump file in orcl server. Since we are running expdp in orcl server. This is basically exporting the data from remote database.

Impdp parfile content:

userid=system/password@orcl
logfile=networkimp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
table_exists_action=replace

Scenario11 Export scott schema in orcl and import into ordb. But do not write dump file in server. The expdp and impdp should be completed with out writing dump file in the server. Here we do not need to export the data. We can import the data without creating the dumpfile.

Here we run the impdp in ordb server and it contacts orcl DB and extract the data and import into ordb database. If we do not have much space in the file system to place the dump file, then we can use this option to load the data.

Impdp parfile content:

userid=scott/tiger@ordb
network_link=orcl
logfile=networkimp2.log
directory=dumplocation
table_exists_action=replace

Scenario12 Expdp scott schema in ordb and impdp the dump file in training schema in ordb database.

Expdp parfile content:

userid=scott/tiger@orcl
logfile=netwrokexp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
schemas=scott
include=table

Impdp parfile content:

userid=system/password@ordb
logfile=networkimp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
table_exists_action=replace
remap_schema=scott:training

Scenario 13 Expdp table on orcl database and imdp in ordb. When we export the data, export only 20 percent of the table data. We use SAMPLE parameter to accomplish this task.

SAMPLE parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported. The sample_percent indicates the probability that a block of rows will be selected as part of the sample. It does not mean that the database will retrieve exactly that amount of rows from the table. The value you supply for sample_percent can be anywhere from .000001 up to, but not including, 100.

If no table is specified, then the sample_percent value applies to the entire export job. The SAMPLE parameter is not valid for network exports.

Expdp parfile content:

userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
tables=scott.part_emp
SAMPLE=20

As per the above expdp parfile, it exports only 20 percent of the data in part_emp table.

Impdp parfile content:

userid=system/password@ordb
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace

Managing Data Pump jobs

The datapump clients expdp and impdp provide an interactive command interface. Since each expdp and impdp operation has a job name, you can attach to that job from any computer and monitor the job or make adjustment to the job.

Here are the data pump interactive commands.

ADD_FILE Adds another file or a file set to the DUMPFILE set.

CONTINUE_CLIENT Changes mode from interactive client to logging mode

EXIT_CLIENT Leaves the client session and discontinues logging but leaves the current job running.

KILL_JOB Detaches all currently attached client sessions and terminates the job

PARALLEL Increase or decrease the number of threads

START_JOB Starts(or resume) a job that is not currently running. SKIP_CURRENT option can skip the recent failed DDL statement that caused the job to stop.

STOP_JOB stops the current job, the job can be restarted later

STATUS Displays detailed status of the job, the refresh interval can be specified in seconds. The detailed status is displayed to the output screen but not written to the log file.

Scenario14 Let us start the job and in between, we stop the job in middle and resume the job. After some time, let us kill the job and check the job status for every activity....

We can find what jobs are running currently in the database by using the below query.

SQL> select state,job_name from dba_datapump_jobs;

STATE JOB_NAME
------------------------------ ------------------------------
EXECUTING SYS_IMPORT_FULL_01

SQL>

C:\impexpdp>impdp parfile=schemaimp1.par

Import: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 14:06:51

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": parfile=schemaimp1.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE

Import> stop_job
Are you sure you wish to stop this job ([yes]/no): yes

C:\impexpdp>

When we want to stop the job, we need press Control-M to return Import> prompt. Once it is returned to prompt(Import>), we can stop the job as above by using stop_job command.

After the job is stoped, here is the job status.

SQL> select state,job_name from dba_datapump_jobs;

STATE JOB_NAME
------------------------------ ------------------------------
NOT RUNNING SYS_IMPORT_FULL_01

SQL>

Now we are attaching job again..... Attaching the job does not restart the job.

C:\impexpdp>impdp system/password@ordb attach=SYS_IMPORT_FULL_01

Import: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 14:17:11

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Job: SYS_IMPORT_FULL_01
Owner: SYSTEM
Operation: IMPORT
Creator Privs: FALSE
GUID: 54AD9D6CF9B54FC4823B1AF09C2DC723
Start Time: Sunday, 17 May, 2009 14:17:12
Mode: FULL
Instance: ordb
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND parfile=schemaexp1.par
IMPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND parfile=schemaimp1.par
TABLE_EXISTS_ACTION REPLACE
State: IDLING
Bytes Processed: 1,086,333,016
Percent Done: 44
Current Parallelism: 1
Job Error Count: 0
Dump File: c:/impexpdp\networkexp1.dmp

Worker 1 Status:
State: UNDEFINED
Import>

After attaching the job, here is the job status.

SQL> select state,job_name from dba_datapump_jobs;

STATE JOB_NAME
------------------------------ ------------------------------
IDLING SYS_IMPORT_FULL_01

SQL>

Attaching the job does not resume the job. Now we are resuming job again.....

Import> continue_client
Job SYS_IMPORT_FULL_01 has been reopened at Sunday, 17 May, 2009 14:17
Restarting "SYSTEM"."SYS_IMPORT_FULL_01": parfile=schemaimp1.par

SQL> select state,job_name from dba_datapump_jobs;

STATE JOB_NAME
------------------------------ ------------------------------
EXECUTING SYS_IMPORT_FULL_01

SQL>

Now again we are killing the same job.... Before we kill, we need to press Control-C to return the Import> prompt.

Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

C:\impexpdp>

Now the job is disappared in the database.

SQL> select state,job_name from dba_datapump_jobs;

no rows selected

SQL>