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

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more

ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes ...
2 comments
Read more