Oracle expdp puts INDEXES to LOGGING mode

datapumpimpdporacle

I've created a table in schema LARRY

create global temporary table TT1 (N number);
create index TT_IX1 on TT1 (N);

then export it, and try to import in schema LARRY2:

expdp LARRY/LARRY   directory=DATA_PUMP_DIR dumpfile=01.dmp logfile=01.dmp.exp tables=TT1
impdp LARRY2/LARRY2 directory=DATA_PUMP_DIR dumpfile=01.dmp logfile=01.dmp.imp remap_schema=LARRY:LARRY2 transform=DISABLE_ARCHIVE_LOGGING:Y,oid:N

But I've got an error in Oracle 12c:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX:"LARRY2"."TT_IX1" failed to create with error:
ORA-14451: unsupported feature with temporary table
Failing sql is:
 ALTER INDEX "LARRY2"."TT_IX1"  LOGGING

Also I've checked

SELECT force_logging FROM v$database;

It gives NO.

I tried to search for this problem in the Internet but didn't find the answer. I'm curious why do Oracle trying to put INDEXES (and indexes of TEMPORARY tables too) to the LOGGING mode? And how to fix it?

Best Answer

This is caused by transform=DISABLE_ARCHIVE_LOGGING:Y. Because of that parameter, the database creates indexes with NOLOGGING property to avoid generating redo, and at the end, it changes the index to the original value, which is in this case LOGGING.

But for indexes on GTTs, specifying NOLOGGING or LOGGING is not supported. Data Pump manages to create the index without specifying NOLOGGING (because that would not make sense), but at the end, it still tries to change its property to LOGGING.

This bug was reported more than 2 years ago, and it is said to be fixed in 12.2.

Bug 18366521 : IMPDP ENDS WITH ERROR USING TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

I have just tried this in a 12.2 instance, and it is indeed fixed.

A workaround is to handle the GTTs seperately, without specifying transform=DISABLE_ARCHIVE_LOGGING:Y.

Related Question