ORA-39168: Object path GRANT was not found

BACKGROUND & OVERVIEW

The following documentation provides an overview of how the ORA-39168: Object path GRANT was not found error message is created. Understanding the worked demonstration should avoid the situation arising.

WORKED DEMONSTRATION WITHOUT ORA-39168

Create a directory, some test users and some test data

sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 1 08:31:27 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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

SYS> create directory aptest as '/home/oracle';

Directory created.

SYS > create user ap1 identified by ap1;

User created.

SYS > grant dba to ap1;

Grant succeeded.

SYS > create user ap2  identified by ap2;

User created.

SYS > grant dba to ap2;

Grant succeeded.

SYS > conn ap1/ap1
Connected.
AP1 > create table fred (col1 char(10));

Table created.

AP1 > grant select on fred to another_user;

Grant succeeded.

AP1 > insert into fred values('hello');

1 row created.

AP1 > commit;

Commit complete.

AP1 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Perform the expdp

expdp '/ as sysdba' directory=aptest schemas=ap1 dumpfile=ap.dmp

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 01 December, 2010 8:35:23

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  '/******** AS SYSDBA' directory=aptest schemas=ap1 dumpfile=ap.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
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/GRANT/OWNER_GRANT/OBJECT_GRANT
. . exported "AP1"."FRED"                                5.023 KB       1 row
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/ap.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:36:21

Perform the impdp, excluding the GRANT - Ignore the "user exists" warning

impdp '/ as sysdba' directory=aptest REMAP_SCHEMA=ap1:ap2 exclude=grant dumpfile=ap.dmp

Import: Release 10.2.0.4.0 - 64bit Production on Wednesday, 01 December, 2010 8:37:21

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  '/******** AS SYSDBA' directory=aptest REMAP_SCHEMA=ap1:ap2 
exclude=grant dumpfile=ap.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"AP2" 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 "AP2"."FRED"                                5.023 KB       1 row
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 08:37:32

Confirm the grants HAVE NOT been imported, and tidy up for the next worked example

sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 1 08:37:46 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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

SYS > conn ap1/ap1
Connected.
AP1 > select * from user_tab_privs;

GRANTEE                        OWNER
------------------------------ ------------------------------
TABLE_NAME                     GRANTOR
------------------------------ ------------------------------
PRIVILEGE                                GRA HIE
---------------------------------------- --- ---
ANOTHER_USER                  AP1
FRED                           AP1
SELECT                                   NO  NO

AP1 > conn ap2/ap2
Connected.
AP2 > r
  1* select * from user_tab_privs

no rows selected

AP2 > drop table fred;

Table dropped.

AP2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

rm ap.dmp

WORKED DEMONSTRATION WITH ORA-39168

Perform the expdp, excluding the GRANT

expdp \'/ as sysdba\' directory=aptest schemas=ap1 dumpfile=ap.dmp exclude=grant

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 01 December, 2010 8:38:49

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  '/******** AS SYSDBA' directory=aptest schemas=ap1 dumpfile=ap.dmp 
exclude=grant
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
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
. . exported "AP1"."FRED"                                5.023 KB       1 row
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/ap.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:39:17

Perform the impdp, excluding the GRANT - Notice the ORA-39168 is raised

impdp '/ as sysdba' directory=aptest REMAP_SCHEMA=ap1:ap2 exclude=grant dumpfile=ap.dmp

Import: Release 10.2.0.4.0 - 64bit Production on Wednesday, 01 December, 2010 8:41:37

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39168: Object path GRANT was not found.

CONCLUSION

Options can only be excluded from an impdp operation if they are on the dump file in the first place. If they were excluded from the expdp, then there is nothing to exclude on the impdp, hence the error is raised.

© copyright 2001-2014 ABCdba.com | all rights reserved