Cant figure out what import is doing...wrong

Are you importing the schema using a DBA user or system user?

Try to import the schema using wts_user. I hope this will work.

Thanks

I am inporting as system...and can't import as the user becuase this export came from a customer of ours and they use a different username than I can here...and only a DBA user(or system) can import somone elses export.

I just can't seem to figure out why it is trying to put a few objects in the users tablespace. I have asked the customer for a table listing (but I did a show=y and can see they have a few tables on the users tablespace).

I still want to find out how to override the exports tablespace definition and place all objects on my table space....

Can I drop the USERS tablespace I am assuming NOT, and I revoked access to USERS for a user but it still tried to place the object there - why?

If I understand the problem, you are trying to import some client tables but either running out of room in the USERS tablespace or getting replicate tables.

What I would try:

1- Drop target user cascade; /* as has been discussed */
2- Check for source user tables in the wrong tablespaces.
if they exist, drop them cascade.
3- Get the create statements from the export file (Import, indexfile=xxxxxx)
4- edit xxxxxx to get the table creates for the problem tables.
5- precreate the tables for the correct id
6- rerun the import, fromuser/touser and ignor=y.

Ignore=Y says ignore that the object already exists. Since the tables already exist (step 5), the data will be imported to them, in the proper tablespaces.

Thanks for the reply, here are my findings:

1 & 2). I already dropped the user cascade, several times, I also made new never before used users to ensure there are no objects owned by it.

3-6). The table DOES get created, although it is on the USERS tablespace and that is not wanted, the rows go in also and the index (the create index staement - in this case for PK_ACTIVITY) for the primary key is created BUT the import then errors on the alter table add constraint PK_ACTIVITY.

So, I get the table and the index for the primary key but not the primary key constraint. Note that the create index PK_ACTIVITY and alter table add constraint PK_ACTIVITY use the same object name of PK_ACTIVITY. This always the case as Oracle makes the create unique index when you add a primary key using an alter table command. All the tables that do import fully have the same scenario (the index and pkey name are the same) but only the ACTIVITY table and it's index and pkey are going to the USERS tablespace and it says the pkey is already there. BUT, like I said the ACTIVITY table is there, the index PK_ACTIVITY is there but there is NO primary key constraint on the table?

?

Quote:

---

Originally posted by coubs
Thanks for the reply, here are my findings:

1 & 2). I already dropped the user cascade, several times, I also made new never before used users to ensure there are no objects owned by it.

3-6). The table DOES get created, although it is on the USERS tablespace and that is not wanted, the rows go in also and the index (the create index staement - in this case for PK_ACTIVITY) for the primary key is created BUT the import then errors on the alter table add constraint PK_ACTIVITY.

So, I get the table and the index for the primary key but not the primary key constraint. Note that the create index PK_ACTIVITY and alter table add constraint PK_ACTIVITY use the same object name of PK_ACTIVITY. This always the case as Oracle makes the create unique index when you add a primary key using an alter table command. All the tables that do import fully have the same scenario (the index and pkey name are the same) but only the ACTIVITY table and it's index and pkey are going to the USERS tablespace and it says the pkey is already there. BUT, like I said the ACTIVITY table is there, the index PK_ACTIVITY is there but there is NO primary key constraint on the table?

?

---

If I am right, you have 3 problems. 1. You dont want objects to be created in USERS tablespace. 2. Your primary key is not getting created. 3. You are not sure about the concepts behind export and import.

Few tips for problem 3:
A tablespace may have many tables with same name provided the schemas are different. Same goes for indexes and clusters. Object name does not have anything to do with tablespaces. It has to do with schemas. A schema can not have 2 objects with same name, but a tablespace can have 2 objects with same name. And about primary keys.. we can not create 2 primary keys on a table. And we can not create unique keys on the same columns of a table. Oracle creates indexes for primaty key and unique key irrespective of your key creation statements.

For problem 2:
How many schemas you are importing. Please provide your import and export parfile.

For problem 1:
Nothing to worry about objects being imported into USERS tablespace as I mentioned above. If you still want to avoid impoting the objects into USERS tablespace, you can do the following.

1. Drop the USERS tablespace if the database is test database and you dont require the USERS tablespace anymore.
2. Just import the structure and move/rebuild the tables/indexes to required tablespace, disable the constraints (or you you can even drop them) and then import the data with rows=y

Please do try this and revert back with your observations and dont forget to provided your parfile.

I realize that tablespaces can have more than one object with the same name and it is the schema (user) name that makes it unique. That is why I tried to import into a new users schema - to ensure there are no objects in any tablespaces owned by it.
I also know you cannot create more than one pkey on a table, but that is the error I am getting when the import is adding it, and like I said if the user was dropped with cascade and recreated or if the user was new it should give this error - and on ony a few tables out of 600+.

If I am getting a imp-0015 error during the primary key constraint (the unique index gets built first) and then after the import there is no primary key made, but there is the unique index, I was wondering if it was something to do with the unique index being created. But again I know it builds the unique index if you just add a primary key and it works for the other 600 tables that do import successfully. It is just so strange.

I can't drop the users tablespace, as there are some objects in it from another user.

Note: this user was created new and after the import if I do a select * from tabs; ALL the objects are in the woodlands_data tablespace and none are in the users tablespace. But the output from the show=y was putting the table in question in the users tablespace (does it get created their temporarily and then moved to the users defalt tablespace?).



I am just importing the one schema, here is the statement:

imp.exe system/XXXXXXXX@oracle817 file=DRVG$Wed.dmp fromuser=wts_office touser=googoo GRANTS=N log=import_DRVG.log

I have been looking at the export file contents (I did a show = y) and I see that there are a lot of objects on the USERS tbale space (rememner this is a customers export I have here).

I started to look at these other objects that had the USERS tablespace and verified that they did go in correctly, and in the default tablepsace (not USERS) AND some of them had primary key constraints too!!!

So I am more confused now :)

So it looks like even though the export has a bunch of tables and keys on the users table space, they are being created on the import users default tablespace which is what I want, but for some reason only a few primary keys are failing becuase it thinks they are already there?

I looked in dba_constraints and there are zero rows for the primary key for the user being imported.

I hope I don't have any corruption or something with object ids or something???

Quote:

---

Originally posted by coubs
I have been looking at the export file contents (I did a show = y) and I see that there are a lot of objects on the USERS tbale space (rememner this is a customers export I have here).

I started to look at these other objects that had the USERS tablespace and verified that they did go in correctly, and in the default tablepsace (not USERS) AND some of them had primary key constraints too!!!

So I am more confused now :)

So it looks like even though the export has a bunch of tables and keys on the users table space, they are being created on the import users default tablespace which is what I want, but for some reason only a few primary keys are failing becuase it thinks they are already there?

I looked in dba_constraints and there are zero rows for the primary key for the user being imported.

I hope I don't have any corruption or something with object ids or something???

---

everything (your parfile and user creation etc) looks fine. There could be 2 reasons for the unique index creation error.

1. Somebody dropped the concerned unique index and recreated it during export. You should check the export logfile.
2. You are using very older version of Oracle. There is a bug in 7.3.1 It's is fixed in 7.3.2 The bug is "bug 294898" Just check the version of your export.

Well that is kinda one of those good new bad news posts!
Don't get me wrong I really REALLY appreciate your feedback, but it is showing me that I am screwed.


I asked the customer for an export log file so we will see what that does, what should I be looking for though? I know that you can have activity while performing a backup but I guess maybe not dropping and recreating primary keys? Maybe though that is why it is trying to use the USERS table space, it is trying to apply changes that were made during the export and it uses the USERS table space to hold temporary objects?

The customer and I are using Oracle 817 so I am not sure what you are refereing to as an old version or Oracle, did you mean old ersion of the import utility?

I can't find anything on Oracles website about that bug number.

go to metalink and search for this bug.
and old version means, something before 7.3.1 (both import and oracle)