Imp using default tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Imp using default tablespace

  1. #1
    Join Date
    Aug 2001
    Posts
    391
    I am trying to import tables from user A to another user B on the same database. Both the users have different default tablespaces. But when I try to import into user B , it puts all the tables in user A's tablespace . I want the tables to be imported in user B's tablespace.
    Here what I did but I still get problem

    1. REVOKE UNLIMITED TABLESPACE FROM UBER_B
    2. ALTER USER USERNAME QUOTA 0 ON TABLESPACE_B

    I don't know what I did wrong but it's not wokring. The question I have is on item 2:USERNAME, is it USER_A or USER_B. The second question I have is TABLESPACE, is it tablespace in tablespace_A or tablespace_B ??? What if I have index on separate tablespaces with data files. Please give me the detail instructions b/c I tried couple times and it's not working right.

    What is solutions and please give me a detial step of how to make this work.

    Thanks



  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968

    Smile Try To exports

    Try doing two exports. On one of the exports use the parameter rows=N. Edit this export and do a find/replace for the tablespace name.

    You can do this for the exports where rows = Y but if it is a large export it will take a while. save the file and import the file with the db structure only first. Disable RI, Triggers and drop sequences in that schema and import the second export. Then reenable RI and Triggers. The sequences will be imported. Then check for invalid objects. You can use the same procedure for changing initial extents sizes on tables.

  3. #3
    Join Date
    Aug 2001
    Posts
    391
    Does anyone know how to change the dedault tablespace without edit the DDL file???

    Please let me know


  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    IF you want those tables to be created without editing the default tablespace, then you can go ahead and create those tables under user B and then do an import with ignore=Y. But the first suggestion would be the better way.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mike73
    1. REVOKE UNLIMITED TABLESPACE FROM UBER_B
    2. ALTER USER USERNAME QUOTA 0 ON TABLESPACE_B

    I don't know what I did wrong but it's not wokring. The question I have is on item 2:USERNAME, is it USER_A or USER_B. The second question I have is TABLESPACE, is it tablespace in tablespace_A or tablespace_B ???
    It is:

    1. REVOKE UNLIMITED TABLESPACE FROM UBER_B
    2. ALTER USER user_B QUOTA 0 ON tablespace_A;

    Here, user_B is the user that will ow the imported tables and tablespace_A is the tablespace, where tables resided when you exported them (user_A's tablespace).

    All tables and indexes that were originaly in tablespace_A will be imported now be imported into user_B's default tablespace.

    If any of the original indexes (or tables) were originaly in tablespace_C, they will still be imported ito the same tablespace_C if you don't set user_A's qouta on that tablespace to 0.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2001
    Posts
    391
    Jmodic,

    Thanks for your reply, but I have some problem following exactly for your prcedures.


    I create an user TEST and defautl tablespace is TESTD and I got


    ORA-01950: no privileges on tablespace 'TESTD'

    Please let me know why TEST had no privileges on TESTD even I specified its default tablespace is TESTD. how to fix this problem

    Thanks so much


  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    alter user test quota unlimited on testd
    /

  8. #8
    Join Date
    Aug 2001
    Posts
    391
    thanks so much for all of your response and I still have one more error:

    What if I have seperate index tablespace say for user A and user B, it can find the index tablespace for B even I have this command:

    ALTER USER TEST QUOTA UNLIMITED TESTX

    my indexes are created but none of the primary keys are created in TEST schema. Please tell me how to fix this problem

    Thanks

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    spool rebuildidx.sql
    select 'alter index '||index_name||' rebuild tablespace testx;'
    from user_indexes
    /
    spool off
    @rebuildidx.sql

  10. #10
    Join Date
    Aug 2001
    Posts
    391
    Pando thank your for your reply and sorry I still have some problem


    Here is what happened: From USER A, I have INDEX_A for index and primary key, when I imp to USER B, it automatically create all of the primary key from USER A into index of the USER B. Here is the short snapshot of my imp log.

    import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
    . . importing table "AFIS_ACCT_IFACE" 53006 rows imported
    IMP-00015: following statement failed because the object already exists:
    "ALTER TABLE "AFIS_ACCT_IFACE" ADD CONSTRAINT "AFIS_ACCT_IFACE_PRIM" PRIMAR"
    "Y KEY ("IFACE_KEY") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE("
    "INITIAL 1064960 NEXT 516096 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 0 FREEL"
    "ISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "HOUSTONX" ENABLE "
    . . importing table "AFIS_ACCT_IFACE_HOLD" 848 rows imported
    . . importing table "AFIS_ACCT_OVERRIDES" 0 rows imported


    Please let me know how to fix it


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