Disabling LOGGING for import Oracle Database 12C release 1 (12.1)

It’s very nice feature available in Data Pump 12C. It allows to disable generation of redo logs during import of  a data into database.

impdp .. TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y|N[:TABLE|INDEX]

Features

  • DISABLE_ARCHIVE_LOGGING as default is set to N
  • it’s possible to set it only for TABLES or INDEXES or for both
  • if used then once import is finished original logging settings for imported objects are restored
  • redo data is not written into disk
  • faster load data into database less I/O operations
  • great for large data loads or populating new databases
  • It’s recommended to make a full database backup  after such load
  • small amount of log activity is still still generated by the import
  • FORCE LOGGING set on database level will ignore the settings
  • operations against master data pump table(the table stores activity of data pump jobs)  are always using logging option

Examples

Disable logging for whole imported file so for tables and indexes

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp SCHEMAS=hr 
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Disable logging just for indexes

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp SCHEMAS=hr 
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

Disable logging just for indexes – different way

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp SCHEMAS=hr 
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
TRANSFORM=DISABLE_ARCHIVE_LOGGING:N:TABLE

Have a fun 🙂
Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *