We help IT Professionals succeed at work.

IMP FROM TO

basirana
basirana asked
on
2,322 Views
Last Modified: 2013-12-18
I am trying to export and import within same database and I am getting error below.
How can I fix this error? I cannot user expdp in our environment.

I have to do regular export and import.

Thanks
imp / file=scott.dmp full=Y show=Y fromuser=scott touser=stest
 
Import: Release 10.2.0.4.0 - Production on Sun Aug 10 01:47:11 2008
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
Password:
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
 
Export file created by EXPORT:V10.02.01 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
IMP-00024: Only one mode (TABLES, FROMUSER, or FULL) can be specified
IMP-00000: Import terminated unsuccessfully

Open in new window

Comment
Watch Question

Commented:
Hi,

Try like this.....

imp system/<password> file=scott.dmp log=stest.log show=Y fromuser=scott touser=stest statistics=safe ignore=y

Here... you are using both the ways i.e. FULL=y and FROMUSER.

As the dump is scott.dmp so just remove FULL=Y ...it works.

Happy dumping.... :-)
1. You should use either full=Y or fromuser=xxx option.. so just remove full=y, then it should be ok
2. There is another warning on the character set conversion; you should set your NLS_LANG to US7ASCII, to let the import do the automatic charset conversion for you. The way to do this is to
a. set the system environment variable through control panel > system
b. in unix ksh, do export NLS_LANG=american_america.US7ASCII


Export file created by EXPORT:V10.02.01 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
IMP-00024: Only one mode (TABLES, FROMUSER, or FULL) can be specified
IMP-00000: Import terminated unsuccessfully

Author

Commented:
when I tried to import to STEST user. Only Global temporary tables go creates I donot use any other objects inside the schema.

below is the import command I am using

imp userid=scott file=sadmin.dmp show=Y fromuser=SADMIN touser=STEST ignore=y log=imp_STEST.log

Thanks
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
can you paste your log file imp_STEST.log to see what it has written there...
CERTIFIED EXPERT
Top Expert 2008

Commented:
full=Y show=Y fromuser=scott touser=stest
is wrong

drop

full=Y show=Y

show=y causes NO IMPORT, only the SQL statements will be shown in the log file

Use this:

SHOW=n
IGNORE=y
GRANTS=y
ROWS=y

Author

Commented:
I see message. And it is for all tables.

 "TABLESPACE "STEST_DATA" LOGGING NOCOMPRESS"
. . skipping table "EMPLOYEES"

But I dont understand why it is skipping tables.

Thanks
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
i think you did not see the update of "schwertner". Can you check that update.

Author

Commented:
Now I am getting new error

Below is the message from imp log
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
 "CREATE TYPE "SYS_PLSQL_12283_175_2" TIMESTAMP '2008-08-07:13:38:37' OID '53"
 "E228AD415B0C0DE0440017A4AB7097'             as table of SCOTT."EMPLOYEES"
 "12283_30_2";"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal

Open in new window

CERTIFIED EXPERT
Top Expert 2008

Commented:
Yoi should import with equal or higher version of Import utility.
Use "SYS as sysdba" account.
btw, could you check what's SYS_PLSQL_12283_174_2? could you describe SCOTT.EMPLOYEES table, and use a type created by SCOTT instead of SYS?

Author

Commented:
I think problem is bcoz I am trying to export and import from one user to another user in same database. And that is giving error. But I am not sure how to resolve this issue.

Thanks
CERTIFIED EXPERT
Top Expert 2008

Commented:
It is possible.
But if you use old Export/Import the tablespace and the new user must exist.

Author

Commented:
The new user is exisit and he is assign to different tablespace...

Thanks
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.