No TableData on Import

I added a larger harddrive to my NT server.  The original instance (Schdora1) was exported to a .dmp file.  I then stopped & disabled the Schdora1 Service, stopped & diabled the Listener, and installed a new Oracle 9i DB on the new drive. The new instance is call Schdora3 (because schdora2 failed, and it wouldn't let me reuse the name).  

After much consternation and cursing about tnsnames.ora and listener.ora files I FINALLY connected to the new schdora3 Service.

I don't really understand this, but the schema "Schedule" is actually a user.  I created my deveopment login of "BX", and the user "Schedule" after reading the IMP error message(s) several times.

I type:
IMP BX/<pwd>@Sch3
answer all the default prompts which include "Import Table Data?" yes.
All the sequences, views, Procedures and Packages come in, and NONE OF THE TABLES or TABLEDATA!!!!!

Its even asks "Import Grants?" to which I answer "yes".  

What the heck?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi BobMiner,

Did you export the database using FULL=Y option ?

Try this when you import:

c:\set ORACLE_SID=Sch3
c:\imp system/<passwod> file=dump_file.dmp full=y ignore=y log=file_name.log

if you want to import by BX, you have to grant IMPORT_FULL_DATABASE to this account

Hope this helps.
You should create the tablespaces for that tables. You should instruct Oracle to use the tablespaces for this schema and set the quota like:


create user lsmedical identified by lsmedical default tablespace TSDMEDICAL;
grant connect to lsmedical;
alter  user lsmedical quota unlimited on  TSDMEDICAL;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

How did you do the EXPORT? Is it a full database, user, or tablespace export?
you have to call IMP differently depending upon how you exported them.

1. if you have exported your Schdora1 database in FULL database mode,
then in the new database Schdora3:

you have to create the same set of users and their corresponding tablespaces as in the old database Schdora1.

like schwertner said, you set the quota for each user as required.

then you do an IMP like this (don't use interactive mode).

C:\>imp sys/password@Sch3 FILE=<dumpfilename> FULL=Y IGNORE=Y

2. if you have exported your Schdora1 database in USER mode:
you can create a arbitary, new user with the default tablespace (it could a new tablespace) in Schdora3 database, and grant this new user quota to this tablespace,

then you import like this;

C:\>imp sys/password@Sch3 FILE=<dumpfilename> FROMUSER=<user_from> TOUSER=<user_to> IGNORE=Y
BobMinerAuthor Commented:
Thanks Schwertner; it was the tablespace issue.  Thanks to Seazodiak and baonguyen1 for the additional information.

Good luck! Soon you will be guru in Oracle!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.