Exclude Supplemental Logs from Import

goldengateimpdporacle

I am trying to import tables from a production environment, to a test environment. The production environment has supplemental logs on a number of tables for golden gate replication. The supplemental logs are being included in the import, which isn't a problem, but they aren't needed so I would like to exclude them.

I am using a par file to do the import currently, and while I feel like I should be able to exclude the supplemental logs, but I don't see an obvious exclusion type

SCHEMAS=PROD_SCHEMA
REMAP_SCHEMA=PROD_SCHEMA:TEST_SCHEMA
REMAP_TABLESPACE=\"TBS_1\":TEST_TBS
REMAP_TABLESPACE=\"TBS_2\":TEST_TBS
REMAP_TABLESPACE=\"TBS_3\":TEST_TBS
parallel=32
cluster=n
DIRECTORY=DPUMP
TABLE_EXISTS_ACTION=REPLACE
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
EXCLUDE=TABLE:"IN ('TABLE1','TABLE2')"
EXCLUDE=TRIGGER
EXCLUDE=CONTRAINT

Best Answer

Supplemental logging options are treated as contraints by Data Pump.

create table t1(c1 number);
alter table t1 add supplemental log data (all) columns;

$ expdp bp/bp tables=t1
...
$ impdp bp/bp sqlfile=t1.sql
...
$ cat /u01/app/oracle/homes/OraDB19Home1/rdbms/log/t1.sql
-- CONNECT BP
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "BP"."T1"
   (    "C1" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT --   <---------------------
ALTER TABLE "BP"."T1" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;    --   <---------------------
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXCLUDE=CONSTRAINT will take care of it, which you already have in your parfile. However this will exclude the real constraints too. If you want to keep the other constraints, just drop the supplemental log groups after the import.

Related Question