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

Registering The Database to RMAN catalog database:

Registering The Database to RMAN catalog database: Need to start RMAN as follows: RMAN target=sys/password@database_to_backup rcvcat=sys/password@recovery_catalog_database Another variation on the command, if the recovery catalog and the database were on the same server, might be as shown: oraenv ORACLE_SID = [KKUY] ? KKUY RMAN rcvcat=sys/password@recovery_catalog_database RMAN> connect target Recovery Manager: Release 8.0.5.1.0 - Production RMAN-06005: connected to target database: KKUY RMAN-06008: connected to recovery catalog database Use the below command to register the database. RMAN>register database; Want to verify if a database is registered in the recovery catalog. To do this, connect to RMAN and issue the command LIST INCARNATION OF DATABASE. RMAN> list incarnation of database; RMAN-03022: compiling command: list RMAN-06240: List of Database Incarnations RMAN-06241: DB Key Inc Key DB Name DB ID      CUR Reset SCN   Reset Time RMAN
Post a Comment
Read more

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