Cant figure out what import is doing...wrong

I am getting errors importing an export...from a client ti my database but also from my database and back in.

I am getting the following error:

. . importing table "ACTION_TRIGGER" 0 rows imported
. . importing table "ACTIVITY" 0 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "ACTIVITY" ADD CONSTRAINT "PK_ACTIVITY" PRIMARY KEY ("ACTIVITY"
"_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072"
" NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIS"
"T GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE "
. . importing table "ACTIVITY_RESOURCE" 0 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "ACTIVITY_RESOURCE" ADD CONSTRAINT "PK_ACTIVITY_RESOURCE" PRIM"
"ARY KEY ("ACTIVITY_RESOURCE_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS"
" 255 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096 PCTINC"
"REASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER"
"S" ENABLE "
. . importing table "AC_HARV_DETAIL_B4_PROPCUT" 1330 rows imported



The weird thing is that I drop and recreated the user (and revoked unlimited tablespace from him) so I am not sure why it bombs while trying to add the pkey?

I did more digging...the tables prior to the one that blows up have primary keys on them and they do not blow up... I did an import with show=y and the tables, indexes and pkeys that succeed are created on the users default tablespace, but the table that blows up is being created on the USERS tablespace...and then the index and pkey is as well and that is where it blows up...WHY I do not know.

Why would a few tables out of a few hundred all of a sudden get created on the USERS tablespace???

Thanks in advance!

Bill

I'm thinking the USER'S default tablespace is USERS. This is why the object are getting created in there. Also, when you drop the user.. did you do a DROP USER username CASCADE, to get rid of all the objects prior to the import?

If you dont use the default storage parameters while creating segments, the storage parameters are copied to dumpfile while exporting. Same goes for tablespace parameter. It looks like, that one table which is getting imported into USER tablespace was created by spedicifying USER tablespace. That's Y it got imported into USER tablespace.

Good call nagarjuna.

Thanks for the response guys!

I do use the cascade option when dropping a user (see below).

drop user wts_test cascade;
commit;
create user wts_test identified by wts_test
default tablespace woodlands_data
temporary tablespace temp
quota unlimited on woodlands_data
quota unlimited on temp
profile default;

grant resource to wts_test;
grant connect to wts_test;

revoke unlimited tablespace from wts_test;

So I am thinking it is the way the table was created - like nagarjuna said...the thing is that this export came from a customer of ours and we need to import it here so we can give them support. If they created a few tables that speecified the USERS tablespace, how can I override this and have all objects go to the users default table space (from above)???
My other concern is that I must have objects in the USERS table space and there shouldn't be - hence the error in the first place.

Thanks again for the quick replies!

Bill

Revoke the quota on USERS tablespace and import using wts_test instead of using System or DBA privileged user. I hope your problem will solve.

Another thing you can do is change the storage parameters for that particular objects in the export(dump) file and import.

Apart from that you have to give unlimited quota on woodlands_data tablespace for wts_test.

I hope this will solve your problem.

Thanks
ShanDJ

I made another new user and I revoked (I hope this is what I am supposed to do?) quota on USERS (See below) and I still get the error.
drop user test_user cascade;
commit;
create user test_user identified by test_user
default tablespace woodlands_data
temporary tablespace temp
quota unlimited on woodlands_data
quota unlimited on temp
quota 0 on USERS
profile default;

grant resource to test_user;
grant connect to test_user;



revoke unlimited tablespace from test_user;

I cannot import as this user(test_user) becuase it was exported as another user (an offsite client of ours).

I ran the import(see below)...
imp.exe system/@oracle817 file=DRVG$Mon.dmp fromuser=wts_office touser=test_user GRANTS=N log=import_DRVG.log

and I get the same error - a few tables out of hundreds are trying to be created on USERS.


I can try to ask the customer to change their storage parameters and re-export but there has to be a way to prevent or dictate where objects are created on my side?

Is it becuase I am importing as system? Should I create a user with DB priveledges and revoke access to system and sys and USERS tablespace becuase it is the user that is logging on using the imp.exe program (in my case system) and maybe that is how it is getting at the USERS tablespace????

Does this have anything to do with the IGNORE parameter?

MH

I see so much posted about this IGNORE parameter - forgive me I have been a Sybase and MS SQL Server DBa for the last 8 years but I am not even sure why it would be used other than refreshing data over top an existing schema - If I understand it correctly?
This will not work in my case this time becuase this export came from a customer.
And again I am puzzled becuase I drop and recreate the user and therefore shouldnt get any duplicate key errors - and to make things worse the primary key (the blows up) is not on the table after the import is finsihed!? It must be some other users'?

Did you check the constraints on that table, use dba_constraints to see what is on the table.

MH

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)

One solution for your problem is

1. Open the export (dump) file using any textpad (if it too big don't use notepad). You can download textpad from net also.
2. Replace the string "TABLESPACE USERS" with your string "TABLESPACE yourtablespace".
3. Save the file
4. Now import the dump file into your database.

Note: If you are having database on Unix box, ftp it to NT box through Binary mode. Change the dump file and copy back to Unix box and import it.

By doing this way, your objects will import into your specified tablespace without any problem.

Thanks,

Grant import_full_Database privilege to the new user and try to import using this user.

regards
anandkl

[Edited by anandkl on 10-01-2002 at 06:56 AM]

Quote:

---

Originally posted by shandj
One solution for your problem is

1. Open the export (dump) file using any textpad (if it too big don't use notepad). You can download textpad from net also.
2. Replace the string "TABLESPACE USERS" with your string "TABLESPACE yourtablespace".
3. Save the file
4. Now import the dump file into your database.

Note: If you are having database on Unix box, ftp it to NT box through Binary mode. Change the dump file and copy back to Unix box and import it.

By doing this way, your objects will import into your specified tablespace without any problem.

Thanks,

---

Noway you can import the data using this method. I have used such method (opening the dump using wordpad and editing) to import the structure. But, it does not work with complete data(rows=y).

I can maunally add the keys after the import but that is not the long term solution. I need to know why it is overriding the users defaults and trying to put objects in USERS. And ,like I said before the output from show = y shows me this:

"CREATE TABLE "ACTIVITY" ("ACTIVITY_ID" NUMBER(10, 0) NOT NULL ENABLE, "CROP"
"_ID" NUMBER(10, 0) NOT NULL ENABLE, "ACTIVITY_CLASS" VARCHAR2(20), "ACTIVIT"
"Y_TYPE" VARCHAR2(20), "PLANNED_EARLY_START_DATE" DATE, "PLANNED_LATEST_END_"
"DATE" DATE, "START_DATE" DATE, "END_DATE" DATE, "START_DESC" VARCHAR2(20), "
""ACTIVITY_STATUS" VARCHAR2(30), "TECHNOLOGY" VARCHAR2(15), "COMMENTS" VARCH"
"AR2(2000), "AREA" NUMBER(15, 5), "AIR_TEMPERATURE" NUMBER(15, 5), "AIR_TEMP"
"ERATURE_UNIT" VARCHAR2(20), "WIND_VELOCITY" NUMBER(15, 5), "WIND_VELOCITY_U"
"NIT" VARCHAR2(20), "WIND_DIRECTION" VARCHAR2(10), "TIME_OF_DAY" VARCHAR2(30"
"), "DELETED" VARCHAR2(3), "PRODUCT_PRICE" NUMBER(15, 5), "SERVICE_PRICE" NU"
"MBER(15, 5), "DELIVERY_CHARGE" VARCHAR2(3), "PRODUCT_PURCHASED" VARCHAR2(3)"
", "SERVICE_PURCHASED" VARCHAR2(3), "START_TIME" VARCHAR2(10), "END_TIME" VA"
"RCHAR2(10), "SOIL_TEMPERATURE" NUMBER(15, 5), "CUSTOM_APPLICATION" VARCHAR2"
"(3), "CANCEL_REASON" VARCHAR2(255), "ALERT" VARCHAR2(3), "PRIORITY_ID" NUMB"
"ER(10, 0), "GROWTH_STAGE_ID" NUMBER(10, 0), "REQUESTOR_RESOURCE_ID" NUMBER("
"10, 0), "PARENT_ACTIVITY_ID" NUMBER(10, 0), "RESOURCED" VARCHAR2(3), "SEQUE"
"NCE_NUMBER" NUMBER(10, 0), "PERCENT_COMPLETE" NUMBER(15, 5), "CUSTOM_DATA_E"
"NTERED" VARCHAR2(3), "PORTION_RESOURCED" NUMBER(15, 5)) PCTFREE 10 PCTUSED"
" 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 131072 NEXT 131072 MINE"
"XTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER"
"_POOL DEFAULT) TABLESPACE "USERS""
. . skipping table "ACTIVITY"

"CREATE UNIQUE INDEX "PK_ACTIVITY" ON "ACTIVITY" ("ACTIVITY_ID" ) PCTFREE 1"
"0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 M"
"AXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFA"
"ULT) TABLESPACE "USERS" LOGGING"
"ALTER TABLE "ACTIVITY" ADD CONSTRAINT "PK_ACTIVITY" PRIMARY KEY ("ACTIVITY"
"_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072"
" NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIS"
"T GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE "

Note the USERS table space in the create table, index and primary key. BUT when I do a select * from tabs as this user it says that the ACTIVITY table resides on the woodlands_data tablespace and not the USERS table space???

Quote:

---

Originally posted by coubs


Note the USERS table space in the create table, index and primary key. BUT when I do a select * from tabs as this user it says that the ACTIVITY table resides on the woodlands_data tablespace and not the USERS table space???

---

mmmm, how can you see the tablespace name from "tab" synonym???

and, now I doubt... what is your exact problem?? can you post/explain it again???

One thing to remember when doing an import. If you do a SHOW=Y one can obtain the DDL statement necessary to the objects without data in the preferred tablespace. Then using the IGNORE=Y the import will detect the object exists and just bring in the data. Once the inserts have completed the import will proceed to build the constraints or indexes. If you have trouble with indexes and constraints then export using INDEXES=N and CONSTRAINTS=N, also be advised if the export is done with COMPRESS=Y the import will try to place the data in one initial extent upon load (in some cases this can cause problems). Hope this helps.

From reviewing all the threads this problem is escalating farther than necessary. Export/Imports are powerful tools that are not the complex beast many DBAs envision them to be, so with that said ---
1) If you drop and recreate the schema prior to importing you run the risk of object dependency errors (rare but possible). A possible solution is to build a drop table cascade script.

select 'drop table '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where owner = 'USERNAME';

2) Export just the DDL. This is done using the ROWS=N option.

3) Export with the following options: CONSTRAINTS=N, INDEXES=N, ROWS=Y

4) Import with SHOW=Y to obtain object creation DDL. Then precreate tables in thier appropriate tablespaces.

5) Import with IGNORE=Y, ROWS=Y and INDEXES=N. Promise no errors, unless export is corrupt. Specify the USERID or FROMUSER, TOUSER parameters. FULL=Y generally causes errors from the SYSTEM user.

6) Import the DDL export using IGNORE=Y. This will put the indexes and constraints, as well as the other necessary objects, in place.

P.S. Step 3 in the previous reply is a separate export from the DDL export.

Thanks so much everyone... :)

I do know how to get the import in..like the manual steps in the last post.

But I can't for the life of me figue out WHY it is doing this...

I'm on 8.1.7.2. I've noticed this problem too. But when I abort the import, dropped the schema and tried the import again, it worked fine. I always use the option Import_Full_File = 'Y'.

Sridhar