DataPump Transportable Tablespace Import Raises Errors ORA-39083 ORA-1917 (Doc ID 1082116.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 06-May-2013***

Symptoms

You successfully performed a transportable tablespace export using the DataPump utility expdp. During import, the following errors occurred:

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'A_ROLE' does not exist
Failing sql is:
GRANT SELECT ON "A_USR"."A_TAB" TO "A_ROLE"


The following test case demonstrates this:

1. Create the environment in source database

connect / as sysdba

-- create a directory used by DataPump
create or replace directory dpu as 'd:\databases\o111\dpu';

-- create a role
create role a_role;

-- create a tablespace
create tablespace a_tbs datafile 'd:\databases\o111\dbf\a_tbs.dbf' size 10m;

-- create a user
create user a_usr identified by a_usr default tablespace a_tbs temporary tablespace temp;
grant connect, resource to a_usr;

-- in new created schema create a table and populate this
connect a_usr/a_usr

create table a_tab
(
   id   number,
   text varchar2(10)
);

insert into a_tab values (1, 'Text 1');
commit;

-- grant select to new created role
grant select on a_tab to a_role;


2. Perform the tablespace export in source database

connect / as sysdba
alter tablespace a_tbs read only;

 

#> expdp system/passwd directory=dpu dumpfile=a_tts.dmp reuse_dumpfiles=y transport_tablespaces=a_tbs

 

Export: Release 11.1.0.7.0 - Production on Thursday, 08 April, 2010 10:27:20

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

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
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dpu dumpfile=a_tts.dmp reuse_dumpfiles=y transport_tablespaces=a_tbs
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
D:\DATABASES\O111\DPU\A_TTS.DMP
******************************************************************************
Datafiles required for transportable tablespace A_TBS:
D:\DATABASES\O111\DBF\A_TBS.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:27:59


3. Perform the transportable tablespace import in target database

First, transfer the DataPump export dump file and database file(s) to the target machine (if using ftp then use the binary transfer mode). Second, verify, if the involved schemas (A_USR in this case) exist in target database before starting the import. Then start the DataPump import.

#> impdp system/password directory=dpu dumpfile=a_tts.dmp transport_datafiles=f:\databases\o111\dbf\a_tbs.dbf

 

Import: Release 11.1.0.7.0 - Production on Thursday, 08 April, 2010 10:30:23

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

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
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dpu dumpfile=a_tts.dmp transport_datafiles=f:\databases\o111\dbf\a_tbs.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'A_ROLE' does not exist
Failing sql is:
GRANT SELECT ON "A_USR"."A_TAB" TO "A_ROLE"

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 1 error(s) at 10:30:29

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms