Cant figure out what import is doing...wrong
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: Cant figure out what import is doing...wrong

  1. #1
    Join Date
    Sep 2002
    Posts
    13
    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

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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?
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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.
    -nagarjuna

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Good call nagarjuna.
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Sep 2002
    Posts
    13
    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

  6. #6
    Join Date
    Feb 2002
    Posts
    70
    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
    ________________
    ShanDJ

  7. #7
    Join Date
    Sep 2002
    Posts
    13
    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????



  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    Does this have anything to do with the IGNORE parameter?

    MH
    I remember when this place was cool.

  9. #9
    Join Date
    Sep 2002
    Posts
    13
    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'?

  10. #10
    Join Date
    Jan 2001
    Posts
    3,131
    Did you check the constraints on that table, use dba_constraints to see what is on the table.

    MH
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width