Home » RDBMS Server » Server Utilities » need help with importing packages/triggers/functions (win 10g)
need help with importing packages/triggers/functions [message #475628] Thu, 16 September 2010 04:07 Go to next message
ultgnp
Messages: 105
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 #475632 is a reply to message #475628] Thu, 16 September 2010 05:20 Go to previous messageGo to next message
julius.sp
Messages: 2
Registered: August 2009
Location: Chennai
Junior Member

Yes.. you need to create all the roles and privileges for the user before you do a import..
Re: Import error in 10g [message #475692 is a reply to message #475628] Thu, 16 September 2010 12:51 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 449
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 Go to previous messageGo to next message
ultgnp
Messages: 105
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 Go to previous messageGo to next message
John Watson
Messages: 4863
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 Go to previous messageGo to next message
ultgnp
Messages: 105
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
Re: need help with importing packages/triggers/functions [message #475893 is a reply to message #475891] Sun, 19 September 2010 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one can help you with the information you gave as if they are correct you should have your packages...

Regards
Michel
import process hangs [message #475903 is a reply to message #475628] Sun, 19 September 2010 09:21 Go to previous messageGo to next message
ultgnp
Messages: 105
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 #475904 is a reply to message #475903] Sun, 19 September 2010 09:23 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should feedback on the previous topics about this same import.
Waiting for these feedbacks and then I will try to help you on this one.

Regards
Michel
Re: import process hangs [message #475905 is a reply to message #475904] Sun, 19 September 2010 09:25 Go to previous messageGo to next message
ultgnp
Messages: 105
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 Go to previous messageGo to next message
ultgnp
Messages: 105
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 #475907 is a reply to message #475905] Sun, 19 September 2010 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
imp system/test file=e:\gnp2000.dmp log=e:\impgnp2k.txt fromuser=gnp2000 touser=gnp2000 ignore=y grants=n
Re: import process hangs [message #475908 is a reply to message #475907] Sun, 19 September 2010 09:33 Go to previous messageGo to next message
ultgnp
Messages: 105
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 #475909 is a reply to message #475908] Sun, 19 September 2010 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
>Do i need to export logging as the schema owner(GNP2000) or can i export logging as "SYSTEM" user?
Answer depends upon what is to be exported.
Normal access/privileges/protections apply regardless of operation involved.
Re: import process hangs [message #475910 is a reply to message #475909] Sun, 19 September 2010 09:42 Go to previous messageGo to next message
ultgnp
Messages: 105
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 #475911 is a reply to message #475910] Sun, 19 September 2010 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
>Any idea why the import could be hanging?
Complete hang is very rare.
What clues exist in e:\impgnp2k.txt

Does OS monitor show system activity?

>Does it have to do anything with the character set?
I doubt it.
Re: import process hangs [message #475912 is a reply to message #475911] Sun, 19 September 2010 10:03 Go to previous messageGo to next message
ultgnp
Messages: 105
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 #475913 is a reply to message #475912] Sun, 19 September 2010 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
>Import terminated successfully without warnings.
so no problem exists
Re: import process hangs [message #475914 is a reply to message #475913] Sun, 19 September 2010 10:08 Go to previous messageGo to next message
ultgnp
Messages: 105
Registered: May 2010
Location: Germany
Senior Member
The export filw is 17GB. The import hangs for about 10-15 mins and terminates. No data is in tables
Re: import process hangs [message #475915 is a reply to message #475914] Sun, 19 September 2010 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
>fromuser=system
SYS & SYSTEM are NEVER exported so there is nothing to import

fromuser=gnp2000
why do you ignore what I post?
Re: import process hangs [message #475917 is a reply to message #475915] Sun, 19 September 2010 10:24 Go to previous message
ultgnp
Messages: 105
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 Smile
Previous Topic: Importing other schema's object
Next Topic: DECODE not working while using with SQL Loader
Goto Forum:
  


Current Time: Sun Dec 21 08:35:03 CST 2014

Total time taken to generate the page: 0.09101 seconds