Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: Import help with oracle 9i

    Hi, I have a queston regarding import options.
    I exported a schema with
    imp system/manager file=filename.dmp owner=someone
    user SOMEONE was created with default tablespace TBSPACE.
    After exporting the dmp, I want to import the dmp file into another user NEWUSER whose default tablespace was TBSPACE_TEMP.

    But, when I initiated the import, all the objects are being imported into the first tablespace, TBSPACE instead of the TBSPACE_TEMP. How can I restrict the TBSPACE_TEMP tablespace instead?
    Kishore

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by kingno1
    Hi, I have a queston regarding import options.
    I exported a schema with
    imp system/manager file=filename.dmp owner=someone
    user SOMEONE was created with default tablespace TBSPACE.
    After exporting the dmp, I want to import the dmp file into another user NEWUSER whose default tablespace was TBSPACE_TEMP.

    But, when I initiated the import, all the objects are being imported into the first tablespace, TBSPACE instead of the TBSPACE_TEMP. How can I restrict the TBSPACE_TEMP tablespace instead?
    Oracle offers no parameter to specify a different tablespace to import data into. Objects will be re-created in the tablespace they were originally exported from. One can alter this behaviour by following one of these procedures:

    Pre-create the table(s) in the correct tablespace:

    Import the dump file using the INDEXFILE= option
    Edit the indexfile. Remove remarks and specify the correct tablespaces.
    Run this indexfile against your database, this will create the required tables in the appropriate tablespaces
    Import the table(s) with the IGNORE=Y option.

    Change the default tablespace for the user:

    Revoke the "UNLIMITED TABLESPACE" privilege from the user
    Revoke the user's quota from the tablespace from where the object was exported. This forces the import utility to create tables in the user's default tablespace.
    Make the tablespace to which you want to import the default tablespace for the user
    Import the table

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Hi, Thanks..That is for the indexes. What about the table data?
    Kishore

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Both options are for data not for INDEXES. Use the second one, seems be easier...

  5. #5
    Join Date
    Mar 2004
    Posts
    84
    Hi, Thanks..What do you mean by ""Revoke the user's quota from the tablespace from where the object was exported."" How do I do it?
    Kishore

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    As you mentioned SOMEONE has default tablespace TBSPACE.
    NEWUSER has default tablespace TBSPACE_TEMP.

    Now you want to avoid store tables into TBSPACE when importing tables into NEWUSER schema (user). So you have to make sure NEWUSER can not use TBSPACE tablespace.

    ALTER USER NEWUSER
    IDENTIFIED EXTERNALLY
    DEFAULT TABLESPACE TBSPACE_TEMP
    TEMPORARY TABLESPACE temp_ts -- here type your temporary tablespace
    QUOTA 100M ON TBSPACE_TEMP
    QUOTA 0 ON TBSPACE

    So now your NEWUSER has limit 100 MB on TBSPACE_TEMP
    But has 0MB limit on TBSPACE so after import tables should be imported into TBSPACE_TEMP (default tablespae) as user can not store anything into TBSPACE

    Aftrer that you can alter user and give him some quota on TBSPACE if you want

    Hope it helps. I'm not sure about syntax, but you can search on internet for exact syntax.

  7. #7
    Join Date
    Mar 2004
    Posts
    84
    Hmm..I did
    Alter user newuser quota 0M on tbspace;

    NO USE. IT IS STILL GOING TO TBSPACE eventhough the default is tbspace_temp.
    Kishore

  8. #8
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Please make sure the user does not have the UNLIMITED TABLESPACE privilege.
    If so, revoke this privilege and give quota on the correct tablespace( s )
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  9. #9
    Join Date
    Mar 2004
    Posts
    84
    Thanks Guys. I am able to get it working with
    revoke unlimited tablespace;
    alter user quota 0m on first;
    alter user quota 20000m on test;

    and importing with indexes=n option.
    Kishore

Posting Permissions

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