How to do a full oracle database export or import yet exclude one tablespace

Question

I'm working on creating a scheduled task to do a weekly refresh of an Oracle 11g database from production to test. I want to do a full export / import with the exception of one tablespace named PERFSTAT which we use in production with stats pack, but don't really need in test.

Here is my current export command

EXPDP system/password@instance FULL=Y DIRECTORY=TRPROD_EXPORT DUMPFILE=TRPROD_FULL_EXPORT.DMP LOGFILE=TRPROD_FULL_EXPORT.LOG COMPRESSION=all

When I tested the import, i got the following error

ORA-01119: error in creating database file 'G:\ORACLE\ORADATA\TRPROD\PERFSTAT01.DBF'
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
Failing sql is:
CREATE TABLESPACE "PERFSTAT" DATAFILE 'G:\ORACLE\ORADATA\TRPROD\PERFSTAT01.DBF' SIZE 209715200 AUTOEXTEND ON NEXT 10485760 MAXSIZE 2146435072 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPAC

I've tried adding EXCLUDE=TABLESPACE:"='PERFSTAT'" to the EXPDP command to try and exclude that tablespace, but I gave me a syntax error, so I'm wondering what the correct way to do a full refresh of our test database would be yet exclude this one specific tablespace?

Answer 1

You can create a parameter file as shown below.

[oracle@oratestprod fra]$ cat test.par
DIRECTORY=TRPROD_EXPORT
FULL=Y 
DUMPFILE=TRPROD_FULL_EXPORT.DMP 
LOGFILE=TRPROD_FULL_EXPORT.LOG 
COMPRESSION=all
exclude=tablespace:"IN ('PERFSTAT')"

And run as shown below.

[oracle@oratestprod fra]$ expdp system parfile=test.par