Change tablespace during import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Change tablespace during import

Hybrid View

  1. #1
    Join Date
    Apr 2002
    Posts
    73

    Change tablespace during import

    Is it possible to change the tablespace which the user is imported to (during import)?

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    I would think no, but in all honesty I never tried.

    You can change the user default tablespace before import, than run a schema level import. That would be cleaner, and less risky.

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Aug 2002
    Posts
    176
    Create the table in that tablespace and then import it with ignore =y.
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    oh joy, new 10g toy

    impdp Scott/Scott DIRECTORY=dpump_dir1 DUMPFILE=newtbs.dmp REMAP_TABLESPACE='USERS':'NEW_USERS'

  5. #5
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Originally posted by pando
    oh joy, new 10g toy

    impdp Scott/Scott DIRECTORY=dpump_dir1 DUMPFILE=newtbs.dmp REMAP_TABLESPACE='USERS':'NEW_USERS'
    I've heard good things about the new Data Pump. Much faster! Can't wait to try it myself. But I have to wait till it's released for AIX. :(

  6. #6
    Join Date
    Feb 2004
    Location
    Russia
    Posts
    13

    Re: Change tablespace during import

    1. drop all of the objects this user owns. You can 'select' the drop statements you need (script attached) in much the same way we 'select' the alter table/index statements
    2. revoke UNLIMITED TABLESPACE from the user
    3. alter the users default tablespace to the target tablespace
    4. give the user an unlimited quota on this new tablespace and their temporary tablespace
    5. IMP this users data.

  7. #7
    Join Date
    Apr 2003
    Posts
    353

    Re: Re: Change tablespace during import

    Originally posted by Markelenkov
    1. drop all of the objects this user owns. You can 'select' the drop statements you need (script attached) in much the same way we 'select' the alter table/index statements
    2. revoke UNLIMITED TABLESPACE from the user
    3. alter the users default tablespace to the target tablespace
    4. give the user an unlimited quota on this new tablespace and their temporary tablespace
    5. IMP this users data.
    During import issue the below command
    will split the tables into different tablespaces.

    alter user xx default tablespace <>;
    give quota on that tablespace

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