datapump practical examples
Oracle 11g has several new features. Here we are going to explore few of them related to datapump which was extended and enhanced EXP/IMP which first time was introduced on Oracle 10g.
Here are main features:
· Compression
· Encryption
· Transportable
· Partition Option
· Data Options
· Reuse Dumpfile(s)
· Remap_table
· Remap Data
One of the main and essential feature is Partition option. Because if table size more than a little bit GB and if table is partitioned how to transport this partition tables using EXPDP?
You can now export one or more partitions of a table without having to move the entire table. On import, you can choose to load partitions as is, merge them into a single table, or promote each into a separate table.
To understand partition feature in expdp-impdp/datapump, let’s create a scenario as below.
We are creating 2 schemas.
1. User1 – This schema is having partition table SALES which needs to be export
2. User2 – schema where we are going to import sales table with new name as SALES_T.
1. Create users:
[oracle@NVMBD01PSR183 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 19 13:15:44 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> grant connect, resource to user1, user2;
Grant succeeded.
2.Create datapump directory.
SQL>CREATE OR REPLACE DIRECTORY EXPDP_DIR AS ' /oracle/data1/expdp';
SQL> GRANT READ, WRITE ON DIRECTORY EXPDP_DIR to user1,user2;
3.Create partition table SALES.
SQL> conn user1/user1
Connected.
SQL> CREATE TABLE sales
( name varchar2(5),
time_id DATE)
PARTITION BY RANGE (time_id)
( PARTITION sales_AUG VALUES LESS THAN (TO_DATE('01-AUG-2014','dd-MON-yyyy
TABLESPACE eodods,
PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-SEP-2014','dd-MON-yyyy
TABLESPACE eodods,
PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy TABLESPACE eodods,
PARTITION sales_NOV VALUES LESS THAN (TO_DATE('01-NOV-2014','dd-MON-yyyy
TABLESPACE eodods,
PARTITION sales_DEV VALUES LESS THAN (TO_DATE('01-DEC-2014','dd-MON
TABLESPACE eodods );
Table created.
SQL> insert into sales values('prod1','01-AUG-2014');
1 row created.
SQL> insert into sales values('prod2','01-SEP-2014');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into sales values('prod3','01-OCT-2014');
1 row created.
Commit;
SQL> SELECT partitioned FROM dba_tables WHERE table_name = ‘SALES’;
partitioned
---
YES
Let`s check:
SQL> conn user1/user1
Connected.
SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = 'SALES';
PARTITION_NAME
------------------------------
SALES_AUG
SALES_DEV
SALES_NOV
SALES_OCT
SALES_SEP
EXPDP:
1. Export entire table including all partitions
[oracle@NVMBD01PSR183 ~]$ expdp user1/user1 directory=EXPDP_DIR dumpfile=sales_table.dmp tables=sales logfile=sales_table.log
Export: Release 11.2.0.3.0 - Production on Fri Dec 19 11:13:37 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USER1"."SYS_EXPORT_TABLE_01": user1/******** directory=EXPDP_DIR dumpfile=sales_table.dmp tables=sales logfile=sales_table.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "USER1"."SALES":"SALES_NOV" 5.421 KB 1 rows
. . exported "USER1"."SALES":"SALES_OCT" 5.421 KB 1 rows
. . exported "USER1"."SALES":"SALES_SEP" 5.421 KB 1 rows
. . exported "USER1"."SALES":"SALES_AUG" 0 KB 0 rows
. . exported "USER1"."SALES":"SALES_DEV" 0 KB 0 rows
Master table "USER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER1.SYS_EXPORT_TABLE_01 is:
/oracle/data1/expdp/sales_table.dmp
Job "USER1"."SYS_EXPORT_TABLE_01" successfully completed at 11:13:46
2. Export specific partition of table:
[oracle@NVMBD01PSR183 ~]$ expdp user1/user1 directory=EXPDP_DIR dumpfile=sales_table_partition.dmp tables=sales:SALES_NOV logfile=sales_table_partition.log
Export: Release 11.2.0.3.0 - Production on Fri Dec 19 12:31:06 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "USER1"."SYS_EXPORT_TABLE_01": user1/******** directory=EXPDP_DIR dumpfile=sales_table_partition.dmp tables=sales:SALES_NOV logfile=sales_table_partition.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "USER1"."SALES":"SALES_NOV" 5.421 KB 1 rows
Master table "USER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER1.SYS_EXPORT_TABLE_01 is:
/oracle/data1/expdp/sales_table_partition.dmp
Job "USER1"."SYS_EXPORT_TABLE_01" successfully completed at 12:31:13
IMPDP
Move dmp file to target host (ftp, scp etc)
Or load data to another schema using remap_schema
1. Import entire partition table into new schema with new name.
[oracle@NVMBD01PSR183 ~]$ impdp user2/user2 directory=EXPDP_DIR dumpfile=sales_table.dmp remap_table=sales:sales_t remap_schema=user1:user2 remap_tablespace=user1:user2 table_exists_action = append;
Import: Release 11.2.0.3.0 - Production on Fri Dec 19 11:19:25 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USER2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER2"."SYS_IMPORT_FULL_01": user2/******** directory=EXPDP_DIR dumpfile=sales_table.dmp remap_table=sales:sales_t remap_schema=user1:user2 remap_tablespace=user1:user2 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "USER2"."SALES_T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER2"."SALES_T":"SALES_SEP" 5.421 KB 1 rows
. . imported "USER2"."SALES_T":"SALES_AUG" 0 KB 0 rows
. . imported "USER2"."SALES_T":"SALES_DEV" 0 KB 0 rows
. . imported "USER2"."SALES_T":"SALES_OCT" 5.421 KB 1 rows
. . imported "USER2"."SALES_T":"SALES_NOV" 5.421 KB 1 rows
Job "USER2"."SYS_IMPORT_FULL_01" successfully completed at 11:19:30
Let`s check: SQL> conn user2/user2 Connected. SQL> select * from sales_t; NAME TIME_ID ----- --------- prod1 01-AUG-14 prod2 01-SEP-14 prod3 01-OCT-14
We have successfully imported entire table with name SALES_T.
2. Now import single partition only.
[oracle@NVMBD01PSR183 ~]$ impdp user2/user2 directory=EXPDP_DIR dumpfile=sales_table_partition.dmp remap_table=sales:sales_t remap_schema=user1:user2 remap_tablespace=user1:user2 table_exists_action = append;
Import: Release 11.2.0.3.0 - Production on Fri Dec 19 13:05:26 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USER2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER2"."SYS_IMPORT_FULL_01": user2/******** directory=EXPDP_DIR dumpfile=sales_table_partition.dmp remap_table=sales:sales_t remap_schema=user1:user2 remap_tablespace=user1:user2 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "USER2"."SALES_T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER2"."SALES_T":"SALES_NOV" 5.421 KB 1 rows
Job "USER2"."SYS_IMPORT_FULL_01" successfully completed at 13:05:28
Here we have successfully imported single partition.
If a partition name is specified, it must be the name of a partition or subpartition in the associated table.
Only the specified set of tables, partitions, and their dependent objects are unloaded.
When you use partition option (PARTITION_OPTIONS) of DataPump you have to select below options:
None - Tables will be imported such that they will look like those on the system on which the export was created.
Departition - Partitions will be created as individual tables rather than partitions of a partitioned table.
Merge - Combines all partitions into a single table.
Comments
Post a Comment