Exporting Tablespace with Partitioned Table in Oracle

Hi Guys,

I got this task to take export of a Tablespace which is having 8 partitions in my production database and Import on Test Server.

Steps :-


D:\iecexp>expdp dumpfile=tspart1 directory=iecexp tablespaces=tspart1 log=tspart1.log

Export: Release 11.2.0.3.0 - Production on Sat Jan 7 11:14:41 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=tspart1.log" Location: Command Line, Replaced with: "logfile=tspart1.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** dumpfile=tspart1 directory=iecexp tablespaces=tspart1 logfile=tspart1.log reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.085 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2016"          432.8 MB 3074761 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2017"          333.0 MB 2352610 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2015"          148.5 MB 1100876 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2014"          7.037 MB   61045 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2013"          311.2 KB    2386 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2018"          65.96 KB     271 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2020"          27.09 KB       4 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2019"              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
  D:\IECEXP\TSPART1.DMP
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 11:16:35


=================================
NOW IMPORT THIS PARTITION TABLE
=================================

> Create Tablespace with same name at target machine.

> Create same user with the same privileges as source.


E:\impbab>impdp dumpfile=TSPART1.DMP directory=impbab remap_tablespace=tspart1:tspart1

Import: Release 11.2.0.3.0 - Production on Sat Jan 7 11:34:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=TSPART1.DMP directory=impbab remap_tablespace=tspart1:tspart1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2016"          432.8 MB   3074761 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2017"          333.0 MB   2352610 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2015"          148.5 MB   1100876 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2014"          7.037 MB     61045 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2013"          311.2 KB      2386 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2018"          65.96 KB       271 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2020"          27.09 KB         4 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2019"              0 KB            0 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 11:40:40



>> NOTE:- you can observe here when we imported tablespace, 8 partitions are automatically imported with their rows.



Happy Learning...