Can you EXCLUDE tablespaces from Full Transportable Export/Import?

Posted on May 17, 2017 by Mike.Dietrich Transportable 16

Question: Can you EXCLUDE one or more tablespaces when doing a Full Transportable Export/Import?

First of all, this question came up already twice in real world customer migrations. So it’s not a totally unusual question. In one case a tablespace called USERS got precreated and some data did get stored. In the second case we did use RMAN incremental backups to migrate a very large database (>100TB) and some tablespaces weren’t part of the backup set.

I did brainstorm with Roy – and I dug into my notes from some years ago when the question was raised to me as part of a migration project to Oracle 12.1.0.1.

Roy and I had basically 2 main approaches to validate.

Run expdp with FULL=Y and TRANSPORTABLE=ALWAYS but EXCLUDE some tablespaces
Run expdp with FULL=Y and TRANSPORTABLE=ALWAYS but EXCLUDE some tablespaces during impdp only

In addition for the customer case where some tablespaces where initially not included into the RMAN Incremental Backupset strategy, the files for these tablespaces can be copied (and converted) in addition. The impdp parameter file will need to take care of it.

First of all, here’s my test setup. The two tablespaces MIKE1 and MIKE2 are the ones I will treat separately.

SQL> select status, tablespace_name from dba_tablespaces;

STATUS	  TABLESPACE_NAME
--------- ------------------------------
ONLINE	  SYSTEM
ONLINE	  SYSAUX
ONLINE	  UNDOTBS1
ONLINE	  TEMP
READ ONLY USERS
ONLINE	  MIKE1
ONLINE	  MIKE2

SQL> create directory oradump as '/home/oracle';
SQL> grant read, write on directory oradump to SYSTEM;

SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'MIKE1, MIKE2', incl_constraints => TRUE);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;
no rows selected

Case 1 – Run expdp but EXCLUDE some tablespaces

l will use this par file:

directory=oradump
dumpfile=exp.dmp
logfile=exp.log
transportable=always
full=y
metrics=y
version=12
exclude=table_statistics,index_statistics
exclude=tablespace:"IN ('MIKE1', 'MIKE2')"

Now invoking Data Pump with expdp:

$ expdp system/oracle parfile=exp.par

Export: Release 11.2.0.4.0 - Production on Tue May 16 13:17:41 2017
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, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** parfile=ex.par 
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
     Completed 1 PLUGTS_BLK objects in 1 seconds
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
     Estimated 1 TABLE_DATA objects in 1 seconds

[...]

     Completed 1 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 0 seconds
     Completed 30 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 1 seconds
     Completed 5 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 0 seconds
     Completed 59 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 0 seconds
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /home/oracle/exp.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
  /u02/oradata/FTEX/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue May 16 13:18:37 2017 elapsed 0 00:00:56

As next step I copy the datafile(s) to the destination’s directory and configure my import par file:

directory=oradump
dumpfile=exp.dmp
logfile=imp.log
metrics=y
logtime=all
transport_datafiles='/u02/oradata/DB12/users01.dbf'

And importing into my new destination database with impdp:

$ impdp system/oracle parfile=imp.par

Import: Release 12.1.0.2.0 - Production on Tue May 16 13:52:46 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
16-MAY-17 13:57:08.681: Startup took 0 seconds
16-MAY-17 13:57:09.299: Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
16-MAY-17 13:57:09.544: Source time zone is +02:00 and target time zone is +01:00.
16-MAY-17 13:57:09.546: Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=imp.par
16-MAY-17 13:57:09.596: Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

[...]

16-MAY-17 13:57:45.160: Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
16-MAY-17 13:57:45.238: Completed 1 PLUGTS_BLK objects in 0 seconds
16-MAY-17 13:57:45.243: Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
16-MAY-17 13:57:47.868: Completed 1 MARKER objects in 2 seconds
16-MAY-17 13:57:47.875: Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
16-MAY-17 13:57:48.368: Completed 2 PROCACT_SCHEMA objects in 1 seconds
16-MAY-17 13:57:48.373: Processing object type DATABASE_EXPORT/AUDIT
16-MAY-17 13:57:48.499: Completed 29 AUDIT objects in 0 seconds
16-MAY-17 13:57:48.504: Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
16-MAY-17 13:57:49.501: Completed 1 MARKER objects in 1 seconds
16-MAY-17 13:57:49.552: Completed 30 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 0 seconds
16-MAY-17 13:57:49.558: Completed 5 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 2348 seconds
16-MAY-17 13:57:49.567: Completed 57 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 2349 seconds
16-MAY-17 13:57:49.628: Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 44 error(s) at Tue May 16 13:57:49 2017 elapsed 0 00:00:41

I silently ignore the errors here. Some additional EXCLUDE statements will make this run flawless. Main reason for the errors is simply that with a 11.2.0.4 source database not everything gets excluded internally what should be excluded.

Anyhow, Case 1 looks ok.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME 	       STATUS
------------------------------ ---------
SYSTEM			       ONLINE
SYSAUX			       ONLINE
UNDOTBS1		       ONLINE
TEMP			       ONLINE
USERS			       ONLINE

But there are some details which may cause trouble. Quick look into the log file:

16-MAY-17 13:57:15.553: ORA-39083: Object type USER:"MIKE1" failed to create with error:
ORA-00959: tablespace 'MIKE1' does not exist
Failing sql is:
 CREATE USER "MIKE1" IDENTIFIED BY VALUES 'S:67D1974017CA6517A6A9BA1655182FEA352800F1460501DCD29602A666B3;CF5B00ED3595B543' DEFAULT TABLESPACE "MIKE1" TEMPORARY TABLESPACE "TEMP"
16-MAY-17 13:57:15.553: ORA-39083: Object type USER:"MIKE2" failed to create with error:
ORA-00959: tablespace 'MIKE2' does not exist
Failing sql is:
 CREATE USER "MIKE2" IDENTIFIED BY VALUES 'S:8915BFF218BB5A49958F5C12F4ED161BBB9EC71A54E1FFA471F94002F8C2;335808EE482DFA17' DEFAULT TABLESPACE "MIKE2" TEMPORARY TABLESPACE "TEMP"

Sure, as tablespace MIKE1 does not exist, user MIKE1 who had the default tablespace MIKE1 can’t be created either. This is ok in my case. But I’d like to point out that you have to pay close attention to such errors.

And of course, the same applies to objects in this tablespace(s) as well:

16-MAY-17 13:57:44.958: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
16-MAY-17 13:57:45.068: ORA-39083: Object type TABLE:"MIKE1"."TEST1" failed to create with error:
ORA-00959: tablespace 'MIKE1' does not exist
Failing sql is:
CREATE TABLE "MIKE1"."TEST1" ("OBJ#" NUMBER NOT NULL ENABLE, "DATAOBJ#" NUMBER, "OWNER#" NUMBER NOT NULL ENABLE, "NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, "NAMESPACE" NUMBER NOT NULL ENABLE, "SUBNAME" VARCHAR2(30 BYTE), "TYPE#" NUMBER NOT NULL ENABLE, "CTIME" DATE NOT NULL ENABLE, "MTIME" DATE NOT NULL ENABLE, "STIME" DATE NOT NULL ENABLE, "STATUS" NUMBER NOT NULL ENABLE, "R

Be careful!

Case 2 – Run expdp but EXCLUDE some tablespaces during impdp

Parameter files look like this for exp.par:

directory=oradump
dumpfile=exp.dmp
logfile=exp.log
transportable=always
full=y
metrics=y
version=12
exclude=table_statistics,index_statistics

and for imp.par:

directory=oradump
dumpfile=exp.dmp
logfile=imp.log
metrics=y
logtime=all
exclude=tablespace:"IN ('MIKE1', 'MIKE2')"
transport_datafiles='/u02/oradata/DB12/users01.dbf

But the result unfortunately is different. While the expdp runs smoothly of course, the impdp fails:

$ impdp parfile=imp.par

Import: Release 12.2.0.1.0 - Production on Thu May 16 17:18:54 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39352: Wrong number of TRANSPORT_DATAFILES specified: expected 3, received 1

Case 2 does not work.

Summary

You can EXCLUDE one or more tablespaces when doing a Full Transportable Export/Import but you have to be careful. My approach from above is simple and does not have any dependencies. Be aware thst the Full Transportable Export/Import will try to create the users and objects – and will fail of course if the tablespace is not part of the operatio

–Mike

Tags: EXCLUDE FTEX Full Transportable Full Transportable Export/Import Tablespace Transportable Tablespaces

Foued says:

May 17, 2017 at 12:10

Thank you for this nice post.

Reply
Roya Yazdani says:

October 12, 2018 at 16:44

Hi,

Can you exclude schemas as well?

I tried the method with expdp including exclude for USERS and some other tablespaces However my impdp failed immediately, because in the target database, we no longer had ORDSYS. The new database does not include components like OJVM, OLAP Analytic Workspace, Oracle Multimedia, and others.

So can I add the “exclude=schema” to get around this issue? and if so, should I add it to expdp, or impdp? or both?

Thanks,
Roya Yazdani

Reply
- Mike.Dietrich says:
  
  October 15, 2018 at 07:12
  
  Roya,
  
  I’m not 100% sure. I think, Yes, you can. But please try it by yourself.
  
  Thanks,
  Mike
  
  Reply
VN says:

June 10, 2019 at 08:29

Hi Mike,

Thank you for the nice post.
You say …
“I silently ignore the errors here. Some additional EXCLUDE statements will make this run flawless. Main reason for the errors is simply that with a 11.2.0.4 source database not everything gets excluded internally what should be excluded.”

So, can you point out the additional EXCLUDE statements (for 11.2.0.3) that would make the procedure run flawlessly?

Thank you,
VN

Reply
- Mike.Dietrich says:
  
  June 10, 2019 at 18:27
  
  VN,
  
  you need to set them based on what errors you get. I don’t have the correct sequence and commands written down somewhere.
  
  Thanks,
  Mike
  
  Reply
Philip says:

October 11, 2019 at 19:54

Hi Mike,
Thanks much for this TTS article.
We are exporting two tablespaces and importing the same two tablespaces into the target database.
But the challenge we are facing is.
We want to exclude some object/schema owners who have objects on these two tablespaces while doing the import.
Example is…. there are 18 schemas that have objects on these two tablespaces in the source database.
But in the target database, we only created 12 of the 18 schema owners, because we wont need the other 6 schemas in the target database.
But while doing the impdp of these 2 tablespaces, we are getting errors that there are some missing users.
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user does not exist in the database

Appreciate any help/guidance on this.
Thanks.

Reply
- Mike.Dietrich says:
  
  October 16, 2019 at 14:46
  
  This is not possible due to the nature of the meta data export/import (“what’s in this tablespace”).
  If you’d exclude something, it would be still in the structure – hence, this is not possible. You need to do a schema-based migration then.
  
  Cheers,
  Mike
  
  Reply
  - Philip Eben says:
    
    October 19, 2019 at 15:48
    
    Thanks much Mike for the response.
    
    Cheers,
    Philip.
    
    Reply
NGN says:

June 19, 2020 at 01:50

Mike ,

Is it possible to exclude tables while performing a full datapump transportable export ?

Reply
- Mike.Dietrich says:
  
  June 19, 2020 at 12:23
  
  Hi,
  
  no – and IMHO this does not make sense. While you can experiment with EXCLUDE options as I show in this blog post, excluding a table does not make any sense as the table’s blocks are in the tablespace. At least the feature is not intended for this.
  
  Cheers,
  Mike
  
  Reply
  - ipast says:
    
    February 1, 2021 at 12:24
    
    Hello Mike,
    
    In order to provide some more information, I’ve tried to EXCLUDE TABLE (XMLTYPES table) in the import transportable process and it worked succesfully.
    I hope this information could be usefull.
    Greetings!
    Ignacio
    
    Reply
    - Mike.Dietrich says:
      
      February 1, 2021 at 16:47
      
      Thanks Ignacio!
      
      Reply
Baiju Raju says:

December 4, 2022 at 21:27

Hello Mike

For case-2 – if we edit the imp.par and exclude the tablespace files that we don’t want to migrate, then will it work?

Regards
Baiju.R

Reply
- Mike.Dietrich says:
  
  December 7, 2022 at 20:07
  
  Hi Baiju,
  
  you need to experiment and test with it – I only can give a hint but no guarantee that it will work.
  
  Cheers
  Mike
  
  Reply
Naveen says:

January 31, 2023 at 22:41

Hi Mike,

Does TTS support migration of tablespaces which contains tables that are referential partitioned ?. If Yes how do we make sure all tables are created in target database post TTS import ?

Thanks
Naveen

Reply
- Mike.Dietrich says:
  
  February 1, 2023 at 11:50
  
  Hi Naveen,
  
  yes, this is supported – and the meta import creates the necessary entries in the dictionary. But you need to check whether the tablespace(s) you are transporting are “self contained”. If there would be something missing which is required during the import of the tablespace(s), the transport check will fail – as will the subsequent transport too.
  
  Cheers,
  Mike
  
  Reply