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

  • From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
  • To: snelson@xxxxxxx
  • Date: Mon, 1 Feb 2010 12:30:28 +0000

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

1) Lob segments don't seem to remap to the default tablespace
2) 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@xxxxxxx   |
|         |           Sent by:          |
|         |           oracle-l-bounce@fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           29/01/2010 19:42  |
|         |           Please respond to |
|         |           snelson           |
|         |                             |
|---------+----------------------------->
  
>--------------------------------------------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                                                     |
  |       To:       oracle-l@xxxxxxxxxxxxx                                      
                                                                     |
  |       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


Other related posts: