Re: Moving tables to a different tablespace using exp from 9i and imp into 11gR2
- From: Surachart Opun <surachart@xxxxxxxxx>
- To: snelson@xxxxxxx
- Date: Sat, 30 Jan 2010 11:17:31 +0700
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@xxxxxxx> 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@xxxxxxxxxxx [mailto:Joel.Patterson@xxxxxxxxxxx]
> Sent: Friday, January 29, 2010 3:20 PM
> To: Scott W Nelson; oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
