| need help with importing packages/triggers/functions [message #475628] |
Thu, 16 September 2010 04:07  |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
Hi,
I am doing an export/import job from 8.0.6 to 10.2.0.1, i have hundreds of users in this database.
1) I did an full=y export from 8.0.6
exp system/manager file=e:\export.dmp log=export.txt full=y
2) I created same database structure as of 8.0.6 in 10g.
3) I imported the dump file in 10g
imp system/manager file=e:\export.dmp log=e:\import.txt full=y ignore=y
when i start importing, i am getting errors that i dont have particular users.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V08.00.06 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
export server uses AR8MSWIN1256 NCHAR character set (possible ncharset conversion)
. importing ULTGNP's objects into ULTGNP
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT ALTER ON "ULT_ADJ_COMP_SEQ" TO "ULT_CASHIER""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULT_CASHIER' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_ADJ_COMP_SEQ" TO "ULT_CASHIER""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULT_CASHIER' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT ALTER ON "ULT_ADJ_COMP_SEQ" TO "ULTGNP_SYNM_GRANTS""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULTGNP_SYNM_GRANTS' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_ADJ_COMP_SEQ" TO "ULTGNP_SYNM_GRANTS""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULTGNP_SYNM_GRANTS' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_APPL_TRANX_SEQ" TO "ULTGNP_SYNM_GRANTS""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULTGNP_SYNM_GRANTS' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_APPL_TRANX_SEQ" TO "ULT_MEDLAB""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULT_MEDLAB' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_APPL_TRANX_SEQ" TO "ULT_DOCTOR""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULT_DOCTOR' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_APPL_TRANX_SEQ" TO "ULT_CASHIER""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULT_CASHIER' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_APPL_TRANX_SEQ" TO "ULT_CORPORATE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULT_CORPORATE' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_APPL_TRANX_SEQ" TO "ULT_DENAPP""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULT_DENAPP' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "ULT_APPL_TRANX_SEQ" TO "ULT_DENTIST""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'ULT_DENTIST' does not exist
My question is do i need to pre-create all the users,roles etc in 10g before i import?
thanks
[Updated on: Thu, 16 September 2010 04:10] Report message to a moderator |
|
|
|
|
|
| Re: Import error in 10g [message #475692 is a reply to message #475628] |
Thu, 16 September 2010 12:51  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Sure you got a FULL export backup?
Unless you did not post the complete messages from the beginning of your log file, this does NOT look like one:
Export file created by EXPORT:V08.00.06 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
export server uses AR8MSWIN1256 NCHAR character set (possible ncharset conversion)
. importing ULTGNP's objects into ULTGNP
... etc ...
[Updated on: Thu, 16 September 2010 13:21] by Moderator Report message to a moderator |
|
|
|
| Re: Import error in 10g [message #475835 is a reply to message #475692] |
Sat, 18 September 2010 01:36 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
I got the full backup with following statement:
exp80 userid=system/oracle file=d:\full.dmp log=d:\full.txt full=y
As there are hundreds of users in my database, do i need to create all the users before i import? If yes, then what is the use of full=y?
Please clarify
[Updated on: Sat, 18 September 2010 01:58] Report message to a moderator |
|
|
|
| Re: Import error in 10g [message #475839 is a reply to message #475835] |
Sat, 18 September 2010 02:17 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Are you using the correct file? You say you exported to full.dmp, but imported from export.dmp?
And by the way, if you have hundreds of users and are not using the "consistent" switch, your export may be useless.
|
|
|
|
| need help with importing packages/triggers/functions [message #475891 is a reply to message #475628] |
Sun, 19 September 2010 01:54 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
Hi,
I am doing an export/import job from 8.0.6 to 10.2.0.1, i have hundreds of users in this database.
1) Did a full export (full=y) export from 8.0.6
(exp system/manager file=e:\export.dmp log=e:\export.txt full=y). Export finished successfully.
2) Created same database structure as of 8.0.6 in 10g.
3) Created all the roles and users.
4) Imported the dump file in 10g
(imp system/manager file=e:\export.dmp log=e:\import.txt full=y ignore=y grants=n)
5) Granted grants to all users.
When i start the database, i cannot see any packages/functions/triggers for two of my main users in Oracle Enterprise Manager. Can Someone please tell me how to import these users packages/functions/triggers. I Thought doing full export will import all these things for users. Please explain what might be the reason
|
|
|
|
|
|
| import process hangs [message #475903 is a reply to message #475628] |
Sun, 19 September 2010 09:21 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
I am doing import of a schema in 10g which i exported from 8.0.6.
I did the export logging as "SYSTEM" user.
exp system/test file=e:\gnp2000.dmp log=e:\gnp2k.txt owner=gnp2000 consistent=y
The import process just hangs and never proceeds to do its job. Please suggest what might be the problem.
imp system/test file=e:\gnp2000.dmp log=e:\imp
gnp2k.txt fromuser=system touser=gnp2000 ignore=y grants=n
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
E:\oracle\product\10.2.0\db_1\BIN>imp system/test file=e:\gnp2000.dmp log=e:\imp
gnp2k.txt fromuser=system touser=gnp2000 ignore=y grants=n
Import: Release 10.2.0.1.0 - Production on Sun Sep 19 17:14:21 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V08.00.06 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
export server uses AR8MSWIN1256 NCHAR character set (possible ncharset conversio
n)
|
|
|
|
|
|
| Re: import process hangs [message #475905 is a reply to message #475904] |
Sun, 19 September 2010 09:25 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
MIchel, regarding previous topics,
I did an full export there. But when i try to import, the procedures/triggers/functions aren't getting imported except for SYS. So i did schema level export and trying to import it.
I did similar import for other schema and it got imported without any errors. I used similar commands for both schemas except different file names.
Please suggest if i am making a mistake with this commands.
[Updated on: Sun, 19 September 2010 09:29] Report message to a moderator |
|
|
|
| Re: Import error in 10g [message #475906 is a reply to message #475839] |
Sun, 19 September 2010 09:27 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
|
I am using the correct file. Since i did not document the exact export command, i forgot the file name here. So i am using a dummy name.
|
|
|
|
|
|
| Re: import process hangs [message #475908 is a reply to message #475907] |
Sun, 19 September 2010 09:33 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
BlackSwan,
Could you please clarify 1 point.
imp system/test file=e:\gnp2000.dmp log=e:\impgnp2k.txt fromuser=gnp2000 touser=gnp2000 ignore=y grants=n
Do i need to export logging as the schema owner(GNP2000) or can i export logging as "SYSTEM" user?
|
|
|
|
|
|
| Re: import process hangs [message #475910 is a reply to message #475909] |
Sun, 19 September 2010 09:42 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
E:\oracle\product\10.2.0\db_1\BIN>imp system/test file=e:\gnp2000.dmp log=e:\imp
gnp2k.txt fromuser=system touser=gnp2000 ignore=y grants=n
Import: Release 10.2.0.1.0 - Production on Sun Sep 19 17:14:21 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V08.00.06 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
export server uses AR8MSWIN1256 NCHAR character set (possible ncharset conversio
n)
Any idea why the import could be hanging? Does it have to do anything with the character set?
|
|
|
|
|
|
| Re: import process hangs [message #475912 is a reply to message #475911] |
Sun, 19 September 2010 10:03 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
The Log file contents are:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V08.00.06 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
export server uses AR8MSWIN1256 NCHAR character set (possible ncharset conversion)
Import terminated successfully without warnings.
|
|
|
|
|
|
|
|
|
|
| Re: import process hangs [message #475917 is a reply to message #475915] |
Sun, 19 September 2010 10:24 |
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
|
Senior Member |
|
|
Thanks Blackswan,
I did an export logging as schema owner (GNP2000).
Then i imported logging as schema owner (GNP2000) and using parameters fromuser=GNP2000, touser=GNP2000
Thanks everyone for your valuable suggestions 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
