DataPump Import (IMPDP) Changes The Logging Attribute Of Partitioned Index (Doc ID 1329766.1)

Last updated on OCTOBER 27, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

The logging attribute of partition is changed by impdp on 10GR2 databases. Please refer the test case below to understand this behavior.

1. Create partitioned table

CREATE TABLE employees
(
   employee_id NUMBER(4) NOT NULL,
   last_name VARCHAR2(10),
   department_id NUMBER(2)
)
PARTITION BY RANGE (department_id)
(
   PARTITION employees_part1 VALUES LESS THAN (11),
   PARTITION employees_part2 VALUES LESS THAN (21),
   PARTITION employees_part3 VALUES LESS THAN (31)
);

2. Created partitioned index with logging attribute

CREATE INDEX employees_global_part_idx ON employees (employee_id) NOLOGGING
GLOBAL PARTITION BY RANGE (employee_id)
(
   PARTITION p1 VALUES LESS THAN(5000) LOGGING, <== specified at partition level
   PARTITION p2 VALUES LESS THAN(MAXVALUE)
);

3. Verify the attributes

select INDEX_NAME, PARTITION_NAME, LOGGING
from   user_ind_partitions
where  INDEX_NAME = 'EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                     PARTITION_NAME                 LOGGING
------------------------------ ------------------------------ -------
EMPLOYEES_GLOBAL_PART_IDX      P1                             YES
EMPLOYEES_GLOBAL_PART_IDX      P2                             NO

4. Run DataPump export

#> expdp test/test directory=DUMPDIR_NAME dumpfile=testpart.dmp logfile=testpart.log schemas=test

5. After export is done, drop the tables

sqlplus test/test
drop table EMPLOYEES purge;

6. Import the dump file

#>impdp test/test directory=DUMPDIR_NAME dumpfile=testpart.dmp logfile=testpart_imp.log

7. Verify the results

sqlplus test/test
select INDEX_NAME, PARTITION_NAME, LOGGING
from   user_ind_partitions
where  INDEX_NAME = 'EMPLOYEES_GLOBAL_PART_IDX';

INDEX_NAME                     PARTITION_NAME                 LOGGING
------------------------------ ------------------------------ -------
EMPLOYEES_GLOBAL_PART_IDX      P1                             NO
EMPLOYEES_GLOBAL_PART_IDX      P2                             NO

The partition P1 had LOGGING option but IMPDP has changed it to NO-LOGGING.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms