Oracle datapump problem

From: Markus Donath <ma_donath_at_t-online.de>
Date: Tue, 30 Nov 2010 12:28:42 +0100
Message-ID: <id2n5a$bcf$03$1_at_news.t-online.com>

Hello!

I have got a problem with Oracle Datapump. I want to import and export a scheme. The tables in the scheme have different user grants. The export is ok, but the import cannot restore the grants:

ORA-01711: duplicate privilege listed

The grant command used by Oracle is buggy:

GRANT INSERT, UPDATE, UPDATE ("FLD02") ON "BONGO"."TEST" TO "BONGO3" Why does Oracle use this wrong command?

Here comes a reproducible record:

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 25 14:36:52 2010

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/tmp/test';

Directory created.

SQL> CREATE USER bongo IDENTIFIED BY "bongo";

User created.

SQL> GRANT CONNECT, RESOURCE TO bongo;

Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO bongo;

Grant succeeded.

SQL> CREATE USER bongo2 IDENTIFIED BY "bongo";

User created.

SQL> GRANT CONNECT, RESOURCE TO bongo2;

Grant succeeded.

SQL> CREATE USER bongo3 IDENTIFIED BY "bongo";

User created.

SQL> GRANT CONNECT, RESOURCE TO bongo3;

Grant succeeded.

SQL> connect bongo/bongo
Connected.
SQL> CREATE TABLE TEST (FLD01 INTEGER, FLD02 INTEGER, FLD03 INTEGER); Table created.

SQL> GRANT INSERT, UPDATE(FLD01) on TEST to bongo2;

Grant succeeded.

SQL> GRANT INSERT, UPDATE(FLD02, FLD03) on TEST to bongo3;

Grant succeeded.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.2$ expdp bongo/bongo schemas=bongo directory=test_dir dumpfile=bongo.dmp logfile=bongo.exp.log

Export: Release 11.2.0.1.0 - Production on Thu Nov 25 15:24:07 2010

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BONGO"."SYS_EXPORT_SCHEMA_01": bongo/******** schemas=bongo directory=test_dir dumpfile=bongo.dmp logfile=bongo.exp.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "BONGO"."TEST"                                  0 KB 0 rows

Master table "BONGO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Dump file set for BONGO.SYS_EXPORT_SCHEMA_01 is:

/tmp/test/bongo.dmp
Job "BONGO"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:24:33

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 25 15:24:49 2010

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DROP USER bongo CASCADE;

User dropped.

SQL> CREATE USER bongo IDENTIFIED BY "bongo";

User created.

SQL> GRANT CONNECT, RESOURCE TO bongo;

Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO bongo;

Grant succeeded.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.2$ impdp bongo/bongo schemas=bongo directory=test_dir dumpfile=bongo.dmp logfile=bongo.imp.log

Import: Release 11.2.0.1.0 - Production on Thu Nov 25 15:26:26 2010

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "BONGO"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "BONGO"."SYS_IMPORT_SCHEMA_01": bongo/******** schemas=bongo directory=test_dir dumpfile=bongo.dmp logfile=bongo.imp.log

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 "BONGO"."TEST"                                  0 KB 0 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 INSERT, UPDATE, UPDATE ("FLD02") ON "BONGO"."TEST" TO "BONGO3" ORA-39083: Object type OBJECT_GRANT failed to create with error: ORA-01711: duplicate privilege listed
Failing sql is:
GRANT INSERT, UPDATE, UPDATE ("FLD03") ON "BONGO"."TEST" TO "BONGO3" ORA-39083: Object type OBJECT_GRANT failed to create with error: ORA-01711: duplicate privilege listed
Failing sql is:
GRANT INSERT, UPDATE, UPDATE ON "BONGO"."TEST" TO "BONGO3" Job "BONGO"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at 15:26:27

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 25 15:26:34 2010

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DROP USER bongo CASCADE;

User dropped.

SQL> DROP USER bongo2;

User dropped.

SQL> DROP USER bongo3;

User dropped.

SQL> DROP DIRECTORY test_dir;

Directory dropped.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
bash-3.2$

Any ideas?

Markus Received on Tue Nov 30 2010 - 05:28:42 CST

This message: [ Message body ]
Next message: jodleren: "Array as parameter"
Previous message: Maxim Demenko: "Re: Question about XML and Oracle."
Next in thread: ddf: "Re: Oracle datapump problem"
Reply: ddf: "Re: Oracle datapump problem"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message