Oracle Database Discussions

I am experiencing a few weird situations with IMP earlier today. Using Oracle 10g.

Some background:
I have a backup file - SCHEMA1.DMP that was an export from Schema 1. I am trying to import TABLE_A into SCHEMA2. The structure for TABLE_A for both SCHEMA1 (USERNAME1/PASSWORD) and SCHEMA2 (USERNAME2/PASSWORD) is the same. I have truncated TABLE_A in SCHEMA2.

Weird Issue 1)
I set ORACLE_SID=SCHEMA2. Then I tried the syntax IMP USERNAME2/PASSWORD. I got an error - TNS error of some sort. I have already set the ORACLE_SID. I'm not sure why I can't put in that syntax. It requires IMP USERNAME2@SCHEMA2

Weird Issue 2)
I tried the syntax IMP username2@SCHEMA2 (trying to invoke the IMP process).

Went through the whole process -
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Import file: EXPDAT.DMP > C:\schema1.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by schema1, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): no > yes

Import grants (yes/no): yes > no

Import table data (yes/no): yes > yes

Import entire export file (yes/no): no > no
Username: USERNAME1

Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: TABLE_A

Enter table(T) or partition(T:P) name or . if done:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
but, there was an error importing to SCHEMA2. It seems like it doesn't like to import to SCHEMA2 because the table was in SCHEMA1. No data was imported to SCHEMA2 table. However, I was able to import to SCHEMA1.

Weird Issue 3)

I tried syntax IMP schema2/password FROMUSER=SCHEMA1 TOUSER=SCHEMA2 FILE=c:\schema1.dmp TABLES=(TABLE_A)
But the import was unsuccessful at all. Totally terminated right there. Is there something wrong with my syntax?


Hope my explanations are clear as to the issues I am experiencing. In short, Issue (1) was not able to connect even though I have set ORACLE_SID. Issue (2) was not able to import to SCHEMA2 if the table is from SCHEMA1. Issue (3) syntax totally didn't work. I'm not sure where I go wrong.

I was able to IMP using option 2 to the same schema at the end; but, curious as to the errors and how to resolve them.

Thanks for any suggestions.