This discussion is archived
3 Replies Latest reply: Nov 28, 2012 8:52 AM by Richard Harrison . RSS

Import with different tablespace

973337 Newbie
Currently Being Moderated
Hi folks,

i have the following situation, an export dump done with the old IMP utility that point to certain tablespaces.(called A,B,C) located in HD1.
For disk space matter i have created a new tablespace (called D) and a new temporany tablespace (called Temp),located in HD2.
I have created theese new tablespaces in another disk,HD2, for space reasons.
I have creted a new user called User2 with Quota only on tablespace D and Temp,the i tried to use imp utility to import from: user1 to: user2,hoping to force IMP utility to remap the objects comes from tablespace A,B,C of the dump into tablespace D and Temp.

Now i got two problems:
1)Some tables cannot be created because point on tablespace A,B,C that are on Hd1 and that are not quoted by User2.
2)I have found that there is another tablespace system called UNDOTB that increases and that runs out my disk space.


Now my question are:
1)There is a certain method to use with old IMP utility to force Oracle remapping all the objects belonging to tb A,B,C into D ?
2)Which are the system tablespace used by Oracle during import, like undotbs?
3)Can i "moving" theese system tb on Hd2 in order to solve the space problem?


Thank you in advance,

Matt.
  • 1. Re: Import with different tablespace
    L-MachineGun Pro
    Currently Being Moderated
    970334 wrote: ... Etc ...
    Now my question are:
    1) There is a certain method to use with old IMP utility to force Oracle remapping all the objects belonging to tb A,B,C into D ?
    You need to pre-create the objects in the tablespace you want them to be in and use the "IGNORE=Y" parameter.
    2) Which are the system tablespace used by Oracle during import, like undotbs?
    UNDOTBS1?
    3) Can i "moving" these system tb on Hd2 in order to solve the space problem?
    Yes, but you need to know how to do it.

    :p
  • 2. Re: Import with different tablespace
    dazasoft Newbie
    Currently Being Moderated
    Hello Matt:

    About your questions:

    1)There is a certain method to use with old IMP utility to force Oracle remapping all the objects belonging to tb A,B,C into D ?

    You can use Gsar Utility (http://gnuwin32.sourceforge.net/packages/gsar.htm)
    Example:
    gsar -sOLD_TABLESPACE -rNEW_TABLESPACE [Path]\DMPFILE.DMP [Path]\NEW_DMPFILE.DMP

    2)Which are the system tablespace used by Oracle during import, like undotbs?

    This select will show you the Undo Tablespace:

    SELECT name, value
    FROM gv$parameter
    WHERE name LIKE '%undo%';

    3)Can i "moving" theese system tb on Hd2 in order to solve the space problem?

    You can recreate your Undo tablespace, please see: http://psoug.org/reference/tablespaces.html (Change The Current UNDO Tablespace)

    I hope this help you.

    Bye.
  • 3. Re: Import with different tablespace
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    I might be missing something but can't you just add an extra datafile on hd2 to each of the tablesapces?

    i.e. alter tablesapce a add datafile 'hd2/x.a.dbf' size xxM autoextend on;

    A tablespace can have multiple files on different disks.

    Regards,
    Harry

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points