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

From: Surachart Opun <surachart_at_gmail.com>
Date: Sat, 30 Jan 2010 11:17:31 +0700
Message-ID: <1dacf81e1001292017s19b43e92y7bb319e75e56003c_at_mail.gmail.com>

If you need to use exp/imp...
Import to new database, Can you create that table before and then import + ignore=Y ???

old database:
- export table
- get metadata of table
Example:
set long 1000
select dbms_metadata.get_ddl('TABLE','TABLE_EXAMPLE') from dual;

new database:
- modify metadata script to create table on new tablespace create table aaa tablespace users ....
To

create table aaa tablespae NEW_USERS ...

• import + ignore=Y

However you can use Data Pump to help .... impdp + remap_tablespace http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/dp_import.htm

SuracharIt Opun
http://surachartopun.com

On Sat, Jan 30, 2010 at 3:31 AM, Scott W Nelson <snelson_at_rtt.com> wrote:

> 9i export and 11gR2 import.
>
> Using the same import file into a 9i database with 9i imp and the same
> setup described in my initial message works like a charm. Tables are
> created in the users default tablespace NOT in the tablespace where they
> used to reside.
>
>
> -----Original Message-----
> From: Joel.Patterson_at_crowley.com [mailto:Joel.Patterson_at_crowley.com]
> Sent: Friday, January 29, 2010 3:20 PM
> To: Scott W Nelson; oracle-l_at_freelists.org
> Subject: RE: Moving tables to a different tablespace using exp from 9i
> and imp into 11gR2
>
>
> Can you use 11g exp/imp combo?
>
> Joel Patterson
> Database Administrator
> 904 727-2546
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Scott W Nelson
> Sent: Friday, January 29, 2010 2:43 PM
> To: oracle-l
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Fri Jan 29 2010 - 22:17:31 CST