Skip to main content

ORA-39168: Object path GRANT was not found EXCLUDE=GRANT





C:\Users\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 11 20:55:55 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create user exp identified by exp;

User created.

SQL> create user imp identified by imp;

User created.

SQL> grant dba to exp;

Grant succeeded.

SQL> grant dba to imp;

Grant succeeded.

SQL> conn exp
Enter password:
Connected.
SQL>
SQL> create table Test (col1 char(10));

Table created.

SQL> grant select on Test to rishi;

Grant succeeded.

SQL> insert into Test values('hello');

1 row created.

SQL> commit;

Commit complete.

NOTE:Directory already exists. So used the existng one

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            DATA_PUMP_DIR
D:\XE\app\oracle\admin\XE\dpdump\

SYS                            ORACLECLRDIR
D:\XE\app\oracle\product\10.2.0\server\bin\clr

SYS                            EXPDP_XE
D:\DATA_XE\DPUMP


SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production


C:\Users\ora>expdp '/ as sysdba' directory=EXPDP_XE schemas=exp dumpfile=exp.dmp

 Export: Release 10.2.0.1.0 - Production on Thursday, 11 June, 2013 21:00:07

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

 Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  'sys/******** AS SYSDBA' directory=EXPDP_XE schemas=exp dumpfile=exp.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 "EXP"."Test"                                4.921 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  D:\DATA_XE\DPUMP\EXP.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:01:09


C:\Users\ora>impdp '/ as sysdba' directory=EXPDP_XE REMAP_SCHEMA=exp:imp exclude=grant dumpfile=exp.dmp

 Import: Release 10.2.0.1.0 - Production on Thursday, 11 June, 2013 21:02:12

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

 Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  'sys/******** AS SYSDBA' directory=EXPDP_XE REMAP_SCHEMA=exp:imp exclude=grant dumpfile=exp.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"IMP" 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 "IMP"."Test"                                4.921 KB       1 rows
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 21:02:28
C:\Users\ora>sqlplus exp/exp

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 11 21:02:57 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> show user;
USER is "EXP"
SQL>
SQL> set linesize 200;
SQL> /

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                PRIVILEGE                              
------------------- ------------------------------ ------------------------------ ------------------------------ ----------------------------------------
RISHI                          EXP                            Test                           EXP                    SELECT                                

SQL> conn imp/imp
Connected.
SQL> select * from user_tab_privs;

no rows selected

SQL> drop table Test;

Table dropped.

SQL> commit;

Commit complete.

C:\Users\ora>expdp '/ as sysdba' directory=EXPDP_XE schemas=exp dumpfile=exp1.dmp exclude=grant

 Export: Release 10.2.0.1.0 - Production on Thursday, 11 June, 2013 21:07:59

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

 Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  'sys/******** AS SYSDBA' directory=EXPDP_XE schemas=exp dumpfile=exp1.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 "EXP"."Test"                                4.921 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  D:\DATA_XE\DPUMP\EXP1.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:08:25


C:\Users\ora>impdp '/ as sysdba' directory=EXPDP_XE REMAP_SCHEMA=exp:imp exclude=grant dumpfile=exp1.dmp

 Import: Release 10.2.0.1.0 - Production on Thursday, 11 June, 2013 21:08:45

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

 Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39002: invalid operation
ORA-39168: Object path GRANT was not found.


- CONCLUSION -

1. In the first copy we have exported grants and excluded while doing import.

 2. In the second copy we have excluded grants while performing export itself. So then there is nothing to exclude on the impdp, hence the error is raised.

 3. If we are are excluded in the first place. there is no point of excluding again in the import.

 4. Options can only be excluded from an impdp operation if they are on the dump file in the first place.
Thank You!

Labels:

Comments

Post a Comment

Popular posts from this blog

Issues and Solutions for Oracle 19c Grid Infrastructure Installation

     Common Issues and Solutions for Oracle 19c Grid Infrastructure Installation •       Check Log Files for Details Oracle installation issues often provide valuable clues in the log files. If the installer seems stuck or fails, check the following logs: ▪ Install log : /u01/app/oraInventory/logs/installActions<date>.log •       ▪ Grid Infrastructure log : $ORACLE_BASE/cfgtoollogs/ •         •       These logs can provide error messages and help identify the exact issue. •         •        Check Permissions and Ownership Verify that the Oracle Grid Infrastructure installation directories have the correct ownership and permissions. •        For example: •        Make sure the grid user has permission to write to the directories where the i...
Post a Comment
Read more

How to Improve Oracle Data Pump Performance - IMPDP

  How to Improve Oracle Data Pump Performance:- Use Parallelism : Set the PARALLEL parameter to at least 2 * number of CPUs . This allows multiple worker processes to perform tasks simultaneously, speeding up both export and import processes. Example: PARALLEL=4 for a system with 2 CPUs. Perform Import Using NETWORK_LINK : Use the NETWORK_LINK parameter to import data directly from the source database. This method is particularly helpful when space is constrained, as it streams the data without needing to generate dump files on the source. It can also reduce time compared to exporting and then importing, as data is streamed directly from the source to the target database. Disable Archivelog Mode (Standalone Databases) : For standalone databases, temporarily disable archive logging during the import process. Import operations can generate a lot of redo logs, slowing down the import. Disabling...
Post a Comment
Read more

CHANGING DBID FOR ORACLE DATABASE 11G

C:\Users\computer>sqlplus SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 10 21:03:33 2013 Copyright (c) 1982, 2008, Oracle.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select dbid from v$database;       DBID ---------- 2188161033 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Users\computer>nid DBNEWID: Release 11.1.0.7.0 - Production on Thu Sep 10 21:04:44 2013 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Keyword     Description                    (Default) ---------------------------------------------------- TARGET ...
Post a Comment
Read more