Importing table to a specific Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Importing table to a specific Tablespace

  1. #1
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    6

    Importing table to a specific Tablespace

    Hi Everybody,

    I have got this export dump which has to be imported into the database and into a particular schema. For that particular schema i have granted quota to 2 tablespaces in which one of them is default. How do i import tables from the export file into the non default tablespace.

    Eg : user1 has got quota on ts1 and ts2 tablespaces. ts1 is default.
    How do i import tables into ts2 tablespace.

    thanx
    bat

  2. #2
    Join Date
    Feb 2004
    Location
    Champaign, Illinois USA
    Posts
    10
    dba_bat,

    You can try this IF:

    1) You have DBA privs
    and 2) You have exlusive control over the DB account into which you
    are importing. ie. temporarily change the password.

    Set the default tablespace to wherever you want the objects for the
    user to which you are connecting and also give a tablespace quota for it (using ALTER USER ...). When you are done with the import, simply change the default tablespace back to the original value and you will probably want to leave the quota that you gave. Then you can allow access to the account again.

    This is the simplest method I can think of. You could also edit the dumpfile itself with a binary editor and do a global search and replace on the tablespace name. If you were more comfortable with doing that, then you don't have to limit access to the account while you do your import which would be a bonus.

    Saundman2000

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    This is the simplest method I can think of. You could also edit the dumpfile itself with a binary editor and do a global search and replace on the tablespace name. If you were more comfortable with doing that, then you don't have to limit access to the account while you do your import which would be a bonus.
    Or you could import using the indexfile parameter. The create table statements will be REM'd out in the index file, but you can extract them and do a find and replace in a text editor. Oracle supports this, but they don't support editing the dump file in a hex editor.

  4. #4
    Join Date
    Aug 2002
    Posts
    176
    Create the Tables in the required tablespaces and import with ignore =Y
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

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