Re: Moving tables to a different tablespace using exp from 9i and imp into 11gR2

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Mon, 1 Feb 2010 12:30:28 +0000
Message-ID: <OFE3F694C0.0A9571C3-ON802576BD.00444B5E-802576BD.0044B772_at_ons.gsi.gov.uk>

I think there are a couple of circumstances in which that technique doesn't work.

Lob segments don't seem to remap to the default tablespace
If the user has UNLIMITED TABLESPACE the default tablespace remapping is ignored ( I seem to recall this being granted as part of the resource role which is easy to miss)

One "trick" you might want to try is to temporarily rename the 11g tablespaces to their 9i equivalent, perform the import and then rename them back again.

Cheers,

Ian

|---------+----------------------------->


|         |           snelson_at_rtt.com   |

|         |           Sent by:          |

|         |           oracle-l-bounce_at_fr|

|         |           eelists.org       |

|         |                             |

|         |                             |

|         |           29/01/2010 19:42  |

|         |           Please respond to |

|         |           snelson           |

|         |                             |

|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |                                                                                                                                                  |
  |       To:       oracle-l_at_freelists.org                                                                                                           |
  |       cc:                                                                                                                                        |
  |       Subject:  Moving tables to a different tablespace using exp from 9i and imp into 11gR2                                                     |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|

In the past I have changed the owners default tablespace, allocated quota on the new tablespace, revoked any quota for the old tablespace, dropped the existing table then done an import of that table and had it successfully create it in the users new default tablespace.

I can not get this to work importing into 11gR2. I receive an ORA-01536: space quota exceeded on the old tablespace.

Datapump seems to not be an option for this as the source of the export is a 9i database.

Any assistance is greatly appreciated.

Scott Nelson

--
http://www.freelists.org/webpage/oracle-l






For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 01 2010 - 06:30:28 CST

This message: [ Message body ]
Next message: D'Hooge Freek: "1 oracle user per server or 1 per database"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message