ORA-01711 Generated When Importing Column Privileges In 11g Release 2.

(Doc ID 1282277.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 10-Apr-2013***

Symptoms

. . imported "U2"."T1" 5.406 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01711: duplicate privilege listed
Failing sql is:
GRANT UPDATE, UPDATE ("COL1") ON "U2"."T1" TO "U2"


The following test case illustrates this in more detail:

TEST CASE
---------
-- Create test users
drop user u1 cascade;
create user u1 identified by u1 default tablespace users;
grant connect, resource to u1;

drop user u2 cascade;
create user u2 identified by u2 default tablespace users;
grant connect, resource to u2;

-- Create a test table
create table u1.t1(col1 number, col2 number);
insert into u1.t1 values (1,1);
commit;

grant update (col1) on u1.t1 to u2;
grant update (col2) on u1.t1 to u2;

-- Export table
create or replace directory test_dp as 'D:\TESTCASES\DataPump';
grant read, write on directory test_dp to public;

expdp system/******* directory=data_pump_dir dumpfile=testcase.dmp schemas=u1
impdp system/******* directory=data_pump_dir dumpfile=testcase.dmp remap_schema=u1:u2


Import fails due to ORA-01711:

impdp system/****** directory=data_pump_dir dumpfile=testcase.dmp remap_schema=u1:u2

Import: Release 11.2.0.1.0 - Production on Fri Jan 14 16:03:04 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=data_pump_dir dumpfile=testcase.dmp remap_schema=u1:u2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"U2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."T1" 5.406 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01711: duplicate privilege listed
Failing sql is:
GRANT UPDATE, UPDATE ("COL1") ON "U2"."T1" TO "U2"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01711: duplicate privilege listed
Failing sql is:
GRANT UPDATE, UPDATE ("COL2") ON "U2"."T1" TO "U2"

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 16:03:21



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