Imported tables do go where they should.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Imported tables do go where they should.

  1. #1
    Join Date
    Feb 2001
    Posts
    16

    Unhappy

    Using DBA Studio, I created a table space called TEST_TBL_SPC and a user called TEST_USR. The new user's default table space is TEST_TBL_SPC. I use imp.exe to import tables into TEST_TBL_SPC by logging in as TEST_USR, but the imported tables go into the SYSTEM table space.

    Why isn't this working? I want tables to go into the TEST_TBL_SPC tablespace not SYSTEM.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    in export file, it says the objects should go to system tablespace. You have to import with

    fromuser=
    touser=
    ignore=y

    and default 'touser' to TEST_TBL_SPC.


  3. #3
    Join Date
    Feb 2001
    Posts
    34

    Question Import not working

    I have followed the advice of this post, and it still does not work for me!

    I need to seperate 2 users that share the same tablespace. I have taken the following steps:

    1) Created new tablespace "staging".
    2) Created a new user "stage", with connect and resource to the new tablespace "staging" and made it's default tablespace to the new tablespace "staging".
    3) Using exp, userid=DBA, I have exported the tables belonging to the old user "old_stage"
    4) Using imp, userid=DBA, fromuser='old_stage' touser='stage' ignore=y.

    The import still goes to the old tablespace.

    What am I missing?
    Ken Hammer

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Try this way.

    Create Old tablespace and New tablespace. Grant quota "None" on old tablespace and "Unlimited" on new tablespace and default the tablespace to "New tablespace". It should work.

    It was not working cuz, import was creating old tablespace and dumping objects in it. If old tablespace exists and it doesn't have permission to use the tablespace it has to keep the objects in deafult tablespace where it has its quota unlimited.

    use the same options as in my prv. posting.

  5. #5
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    When you grant Resource role to user, then automatically user gets Unlimited Tablespace privilege also. The old table could have been in the system tablespace, so when exported the storage clause might have contained the tablespace as system itself. So you have to see that user does not have unlimited tablespace privilege and has quota only on his default tablespace.

  6. #6
    Join Date
    Feb 2001
    Posts
    16

    Thumbs up

    Thanks. I got it working. I granted my new user connect priveleges only. The tables went into the specified default tablespace without a hitch when I imported them.

  7. #7
    Join Date
    Feb 2001
    Posts
    34

    Still Having A Problem With Import

    I'm sorry to keep bother everybody about this, but my import is still going to the wrong tablespace...

    Update:

    1) I have altered the "new_user" to have 0 quota on the old_tablespace (as suggested here).
    2) I have granted this user unlimited quota on the new_tablespace.
    3) I re-exported the data from the old_tablespace using with the following options:

    a) userid=newuser, fromuser=new_user, touser=new_user
    b) userid=old_user, fromuser=old_user, touser=new_user
    c) userid=new_user, fromuser=new_user, touser=new_user
    d) userid=new_user, fromuser=old_user, touser=new_user
    e) userid=DBA, fromuser=old_user, touser=new_user
    f) userid=DBA, fromuser=new_user, touser=new_user

    The reason I tried the export with the "new_user" is a query of dba_tables (and user_tables logged in as the new_user), shows the tablespace the tables or now located (after 2 imports into the wrong tablespace), in as the old tablespace.

    Using the "show=y" parameter on the import tells me that all the tables in the export file are going to be created in the old_tablespace, regardless of which options (a-f) above I choose.

    How can the import insert rows in tables in the old tablespace when I have set the quota to 0 for the new_user on the old tablespace, and running the import with the new_user as the "userid=", with it's default tablespace defined as the new tablespace ?
    Ken Hammer

  8. #8
    Join Date
    Feb 2001
    Posts
    16
    create your user like this:

    CREATE USER "NEW" PROFILE "DEFAULT" IDENTIFIED BY "PWDNEW" DEFAULT TABLESPACE "NEW_TBL_SPC" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON NEW_TBL_SPC ACCOUNT UNLOCK;
    GRANT "CONNECT" TO "NEW";

    import like this:

    c:\> imp NEW/PWDNEW file=c:\EXP.dmp fromuser=OLD touser=NEW ignore=y

  9. #9
    Join Date
    Feb 2001
    Posts
    34

    Thumbs up

    Thank-you, Thank-you, Thank-you!!

    It worked!!
    Ken Hammer

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