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?
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