Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will be explaining how to do Partition Table Export AND Import in 3 steps.
1. We export the PARTITIONED TABLE, which will be sent to the database to be replicated Goldengate, as follows.
I do not import other database objects such as TRIGGER here because I do not want it, but it may or may not be imported when necessary.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | [root@mwdb01 ~]# su - oracle [oracle@mwdb01.webtv.local]/home/oracle > expdp "'/ as sysdba'" directory=EXPIMP_DUMP tables=webtelreporting.event_data_record flashback_scn=12423319395230 dumpfile=ev_dat_re.dmp EXCLUDE=TRIGGER,GRANTS,POLICY,RLS_POLICY,REF_CONSTRAINT,SYNONYM,JOB,SEQUENCE,CONSTRAINT Export: Release 11.2.0.4.0 - Production on Thu Dec 24 09:49:44 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=EXPIMP_DUMP tables=webtelreporting.event_data_record flashback_scn=12423319395230 dumpfile=ev_dat_re.dmp EXCLUDE=TRIGGER,GRANTS,POLICY,RLS_POLICY,REF_CONSTRAINT,SYNONYM,JOB,SEQUENCE,CONSTRAINT Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 7.110 GB Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P51" 227.0 MB 1961651 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P66" 316.2 MB 3328796 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P41" 297.8 MB 4030596 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P57" 284.3 MB 3878743 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P47" 265.3 MB 3143569 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P53" 267.1 MB 3680969 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P60" 266.1 MB 3718807 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P56" 254.8 MB 3489977 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P43" 248.5 MB 3368920 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P58" 235.0 MB 3191644 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P59" 229.0 MB 3106067 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P61" 228.1 MB 3091750 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P50" 220.3 MB 3217999 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P42" 211.2 MB 2816318 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P45" 202.7 MB 2707552 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P68" 201.2 MB 2704970 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P46" 197.1 MB 2623333 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P48" 192.6 MB 2564821 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P52" 194.9 MB 2613775 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P49" 191.5 MB 2535173 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P65" 190.9 MB 2575599 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P44" 174.9 MB 2324076 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P63" 178.3 MB 2406424 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P64" 159.0 MB 2148558 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P55" 157.3 MB 2094594 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P62" 154.4 MB 2088646 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P54" 151.0 MB 2008654 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P67" 127.0 MB 1694541 rows . . exported "WEBTELREPORTING"."EVENT_DATA_RECORD":"EDR_01" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /log_file/dump_file/ev_dat_re.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Dec 24 09:53:00 2015 elapsed 0 00:03:13 |
2. At this stage, under normal circumstances, I had to move the DUMP file from the PRODUCTION database to the REPORTING database, but I added a NAS information that both of them would see and I got rid of the trouble of sending files with SCP.
3. Now I IMPORT the data with PARTITION_OPTIONS as below. It shows one error below, but that doesn’t matter. This is an error with a non-existent ROLE. This role is not required for my database anyway.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | [root@mwrapordb01 ~]# su - oracle [oracle@mwrapordb01 dump_file]$ impdp "'/ as sysdba'" directory=EXPIMP_DUMP dumpfile=ev_dat_rec.dmp PARTITION_OPTIONS=none; Import: Release 11.2.0.4.0 - Production on Thu Dec 24 09:48:34 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_05" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_05": "/******** AS SYSDBA" directory=EXPIMP_DUMP dumpfile=ev_dat_rec.dmp PARTITION_OPTIONS=none Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P51" 227.0 MB 1961651 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P66" 316.2 MB 3328796 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P41" 297.8 MB 4030596 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P57" 284.3 MB 3878743 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P47" 265.3 MB 3143569 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P53" 267.1 MB 3680969 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P60" 266.1 MB 3718807 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P56" 254.8 MB 3489977 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P43" 248.5 MB 3368920 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P58" 235.0 MB 3191644 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P59" 229.0 MB 3106067 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P61" 228.1 MB 3091750 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P50" 220.3 MB 3217999 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P42" 211.2 MB 2816318 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P45" 202.7 MB 2707552 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P68" 201.2 MB 2704970 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P46" 197.1 MB 2623333 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P48" 192.6 MB 2564821 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P52" 194.9 MB 2613775 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P49" 191.5 MB 2535173 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P65" 190.9 MB 2575599 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P44" 174.9 MB 2324076 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P63" 178.3 MB 2406424 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P64" 159.0 MB 2148558 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P55" 157.3 MB 2094594 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P62" 154.4 MB 2088646 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P54" 151.0 MB 2008654 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"SYS_P67" 127.0 MB 1694541 rows . . imported "WEBTELREPORTING"."EVENT_DATA_RECORD":"EDR_01" 0 KB 0 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT ORA-39083: Object type OBJECT_GRANT failed to create with error: ORA-01917: user or role 'MW_ROLE4' does not exist Failing sql is: GRANT SELECT ON "WEBTELREPORTING"."EVENT_DATA_RECORD" TO "MW_ROLE4" Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_IMPORT_FULL_05" completed with 1 error(s) at Thu Dec 24 09:56:39 2015 elapsed 0 00:08:00 |
