Re: Oracle datapump problem
From: ddf <oratune_at_msn.com>
Date: Tue, 30 Nov 2010 13:07:39 -0800 (PST)
Message-ID: <0b688e57-6e49-45ae-8427-75108c5bc837_at_37g2000prx.googlegroups.com>
On Nov 30, 6:28 am, Markus Donath <ma_don..._at_t-online.de> wrote:
> 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
>
> Copyright (c) 1982, 2010, Oracle. All rights reserved.
>
> 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
>
> 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
> - 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
>
> Copyright (c) 1982, 2010, Oracle. All rights reserved.
>
> 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
>
> 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
> - 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
>
> Copyright (c) 1982, 2010, Oracle. All rights reserved.
>
> 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
Date: Tue, 30 Nov 2010 13:07:39 -0800 (PST)
Message-ID: <0b688e57-6e49-45ae-8427-75108c5bc837_at_37g2000prx.googlegroups.com>
On Nov 30, 6:28 am, Markus Donath <ma_don..._at_t-online.de> wrote:
> 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
>
> Copyright (c) 1982, 2010, Oracle. All rights reserved.
>
> 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
>
> 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
> - 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
>
> Copyright (c) 1982, 2010, Oracle. All rights reserved.
>
> 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
>
> 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
> - 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
>
> Copyright (c) 1982, 2010, Oracle. All rights reserved.
>
> 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
There are two general places such grants will be listed:
DBA/ALL/USER_TAB_PRIVS <--- table-level grants DBA/ALL/USER_COL_PRIVS <--- column-level grants
It may be that the data dictionary in 11.2.0.1 lists not only the column-level privileges but also an entry for the table (which doesn't happen in 10.2.0.4). If that is the case then the query which generates such statements (likely a union between the two views) picks up not only the correct column grants but also the errant table grant causing the duplication error.
Check these views after such grants have been issued to see if tablelevel grants are also listed when column-level grants are made.
David Fitzjarrell Received on Tue Nov 30 2010 - 15:07:39 CST