Export and Import Tables Whose Names are Starting From MMM using Oracle Data-Pump

Hi Guys,

In Oracle we can easily Export and Import tables with your desired Names.
You can see in below example I exported tables whose names are starting from MMM


E:\expbab> expdp directory=expbab dumpfile=MMM include=table:\"in\(select table_name from all_tables where table_name like \'MMM%\')\"


Export: Release 11.2.0.3.0 - Production on Wed Nov 30 14:33:56 2016

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

Username: HMIS
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
Starting "HMIS"."SYS_EXPORT_SCHEMA_01":  HMIS/******** directory=expbab dumpfile=MMM include=table:"in\(select table_name from all_tables where table_name like \'MMM%\')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 185.1 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HMIS"."MMMEDREPORT"                        39.59 MB    4976 rows
. . exported "HMIS"."MMMEDFILLOC"                        525.2 KB    5566 rows
. . exported "HMIS"."MMMDISSUM"                          18.32 MB    2644 rows
. . exported "HMIS"."MMMEDORD"                           13.41 MB   44904 rows
. . exported "HMIS"."MMMEDLOC"                           227.2 KB    2684 rows
. . exported "HMIS"."MMMEDREPORT2"                       103.5 KB      11 rows
. . exported "HMIS"."MMMETAFILE"                         40.16 KB       3 rows
. . exported "HMIS"."MMMESSAGE"                          6.335 KB      20 rows
. . exported "HMIS"."MMMLOCCD"                           6.210 KB       1 rows
. . exported "HMIS"."MMM"                                    0 KB       0 rows
. . exported "HMIS"."MMMEDRQST"                              0 KB       0 rows
. . exported "HMIS"."MMMEDSTAT"                              0 KB       0 rows
. . exported "HMIS"."MMMEDTEAM"                              0 KB       0 rows
. . exported "HMIS"."MMMOHCPT"                               0 KB       0 rows
. . exported "HMIS"."MMMRGCAS"                               0 KB       0 rows
Master table "HMIS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HMIS.SYS_EXPORT_SCHEMA_01 is:
  E:\EXPBAB\MMM.DMP
Job "HMIS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:34:19

• Now i will import these tables in SAM schema.

E:\impbab> impdp dumpfile=MMM.DMP directory=impbab remap_schema=hmis:sam

Import: Release 11.2.0.3.0 - Production on Wed Nov 30 14:37:58 2016

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=MMM.DMP directory=impbab remap_schema=hmis:sam
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SAM"."MMMEDREPORT"                         39.59 MB    4976 rows
. . imported "SAM"."MMMEDFILLOC"                         525.2 KB    5566 rows
. . imported "SAM"."MMMDISSUM"                           18.32 MB    2644 rows
. . imported "SAM"."MMMEDORD"                            13.41 MB   44904 rows
. . imported "SAM"."MMMEDLOC"                            227.2 KB    2684 rows
. . imported "SAM"."MMMEDREPORT2"                        103.5 KB      11 rows
. . imported "SAM"."MMMETAFILE"                          40.16 KB       3 rows
. . imported "SAM"."MMMESSAGE"                           6.335 KB      20 rows
. . imported "SAM"."MMMLOCCD"                            6.210 KB       1 rows
. . imported "SAM"."MMM"                                     0 KB       0 rows
. . imported "SAM"."MMMEDRQST"                               0 KB       0 rows
. . imported "SAM"."MMMEDSTAT"                               0 KB       0 rows
. . imported "SAM"."MMMEDTEAM"                               0 KB       0 rows
. . imported "SAM"."MMMOHCPT"                                0 KB       0 rows
. . imported "SAM"."MMMRGCAS"                                0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:38:25



Happy Learning ....