Re: Import table in Different Tablespace

On Thu, 21 Oct 1999 12:00:49 +0800, "Ciem Mehta" <chittesh_at_swi.com.sg> wrote:

>Hi,
>
>I am trying this thing with version 8.0.5
>
>I want to import the list of tables in the different tablespace
>from actually it was exported.
>
>Even when I change the default tablespace for the user still
>it asks for the same Tablespace.
>
>Even I tried with ignoring creation error but it won't work as
>table creation it self gives error while doing import
>
>Any help will be highly appriciated..
>
>Thanks
>
>- Chittesh Mehta
>
>
>

Obviously that user has the resource role, right?! Then he has unlimited tablespace privilege. As the orginal tablespace is in the create table statement in your dump, they will end up in the same tablespace. Additional to changing the default tablespace, you need to
revoke unlimited tablespace from <youruser>;

Then you need to

alter user <youruser> quota unlimited on <new tablespace>;

Now the table import will go OK, indexes will still end up in the wrong tablespace.
Import with indexes=N, do a second import with indexfile=<somefile> This will generate a create index script. Edit the script and run it. You should be all set now.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Oct 21 1999 - 04:58:28 CDT