| impdp not successfull [message #581893] |
Thu, 11 April 2013 12:03  |
|
|
Hello!
I have exported a schema (gscc) from a Test Server
running Oracle 10g version 10.2.0.5.0 on Windows 2003 SP2, 2GB RAM.
Instance name : gscc and schema name: gscc
The export dump was successfully created as the last line of the export log shows:
"Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at 02:24:04"
I want to import this dumpfile on a stand-alone dedicated pc running Oracle 10g versoin 10.2.0.1.0
on Windows 7 OS with 2GB RAM. Oracle binaries are installed on this system in C-drive whereas I have
created the necessary tablespaces on E-drive as C-drive was not having sufficient free space (around 150GB).
On the target-pc instance name is "test" and I have created a schema for import as below:
SQL> create user jct identified by jct default tablespace PROD temporary tablespace TEMP;
SQL> grant unlimited tablespace to jct;
SQL> grant dba to jct;
SQL> grant exp_full_database to jct;
SQL> grant imp_full_database to jct;
SQL> alter user jct default role all;
Then tried to Import (from 'gscc' schema to 'jct' schema) as follows:
impdp jct/jct directory=dpump dumpfile=gssc-dump.dmp remap_schema=gscc:jct
When I start this job of importing, its taking too much time (more than 4-5 hours) only to finish
unsuccessfully.
=========================================================
here are a few initial lines from the import log file.
=========================================================
Import: Release 10.1.0.4.2 - Production on Wednesday, 10 April, 2013 14:48
Copyright (c) 2003, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "JCT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JCT"."SYS_IMPORT_FULL_01": jct/******** directory=dpump dumpfile=gssc-dump.dmp remap_schema=gscc:jct
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JCT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'EXPRESS_ROLES' does not exist
Failing sql is:
GRANT "EXPRESS_ROLES" TO "JCT"
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'SEQUENCE_ROLE' does not exist
Failing sql is:
GRANT SELECT ON "JCT"."ACCOUNT_GKEY" TO "SEQUENCE_ROLE"
==================================================
here are last lines from the import log file.
==================================================
GRANT DEBUG ON "JCT"."ARCH_TRUCK_VISITS" TO "TABLE_ROLE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'TABLE_ROLE' does not exist
Failing sql is:
GRANT FLASHBACK ON "JCT"."ARCH_TRUCK_VISITS" TO "TABLE_ROLE"
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Job "JCT"."SYS_IMPORT_FULL_01" stopped due to fatal error at 11:07:22
Job SYS_IMPORT_SCHEMA_01 has been reopened at Thursday, 11 April, 2013 11:09
Job "SYS"."SYS_IMPORT_SCHEMA_01" stopped due to fatal error at 11:09:50
Job SYS_IMPORT_FULL_01 has been reopened at Thursday, 11 April, 2013 11:10
Job "SYS"."SYS_IMPORT_FULL_01" stopped due to fatal error at 11:10:30
==================================================
I think there may be thousands of errors. With my little knowledge, I can understand that all these errors are
only because of "ORA-01919: role 'EXPRESS_ROLES' does not exist" error, as this role only contain SEQUENCE_ROLE, PROCEDURE_ROLE,TABLE_ROLE,VIEW_ROLE etc.
Now my question is, when the export was successfully completed (as per the log file), does it not export all those roles
which were part of exported schema (in this case, gscc)?
Please help me how to solve this issue.
Thanks in advance
Aijaz
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: impdp not successfull [message #582058 is a reply to message #582035] |
Fri, 12 April 2013 15:12  |
|
|
If I want to export it again alongwith ROLEs, then what will be the syntax of expdp, will it be like the following
expdp user/password directory=<dir.name> dumpfile=<dumpfile.dmp> logfile=<logfile.log> schemas=<schemaname> GRANTS=Y
If not, plz. tell me the correct "expdp" syntax to export a schema including all the ROLEs granted to objects
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
