My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.