Oracle 11g has several new features.
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@DL-HOST01 ~]$ sqlplus / as
sysdba
SQL*Plus:
Release 11.2.0.3.0 Production on Sat Jun 18 13:15:44 2016
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-2016','dd-MON-yyyy')) TABLESPACE users,
PARTITION sales_SEP VALUES LESS THAN
(TO_DATE('01-SEP-2016','dd-MON-yyyy')) TABLESPACE users,
PARTITION sales_OCT VALUES LESS THAN
(TO_DATE('01-OCT-2016','dd-MON-yyyy')) TABLESPACE users,
PARTITION sales_NOV VALUES LESS THAN
(TO_DATE('01-NOV-2016','dd-MON-yyyy')) TABLESPACE users,
PARTITION sales_DEV VALUES LESS THAN (TO_DATE('01-JUN-2016','dd-MON-yyyy'))
TABLESPACE users );
Table
created.
SQL> insert into sales values('prod1','01-AUG-2016');
1 row created.
SQL> insert into sales
values('prod2','01-SEP-2016');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into sales
values('prod3','01-OCT-2016');
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@DL-HOST01 ~]$ 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 Sat Jun 18 11:13:37 2016
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@DL-HOST01 ~]$ 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 Sat Jun 18 12:31:06 2016
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@DL-HOST01 ~]$ 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 Sat Jun 18 11:19:25 2016
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@DL-HOST01 ~]$ 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 Sat Jun 18 13:05:26 2016
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
