Import table in Different Tablespace 
Author Message
 Import table in Different Tablespace
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



Wed, 18 Jun 1902 08:00:00 GMT
 Import table in Different Tablespace



Quote:
> 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

I think that you can pre-create the tables in the tablespace that you
want (with the approprite storage clause), then run the import.  It
will complain that the tables already exist, but will work...(I think)

Another thing that "may" work is editing the dump file and changing the
tablespace name.

Suck it an see I reckon!

Cheers
Tony Adolph

Quote:
> Any help will be highly appriciated..

> Thanks

> - Chittesh Mehta

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 Import table in Different Tablespace


Quote:
>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



Wed, 18 Jun 1902 08:00:00 GMT
 Import table in Different Tablespace
I think You have to create tablespaces and table (empty tables!) before
running import and of course run import whit ignore creation set.

I hope it works.


Quote:
> 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



Wed, 18 Jun 1902 08:00:00 GMT
 Import table in Different Tablespace

Quote:

>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,

Unfortunately this will not work OK, the tables will still end up in
their original tablespace, because the most important part is missing
(and the first part with removing unlimited tablespace privilege is
not really relevant here): you have to revoke any quota on the
original tablespace from your user.

ALTER USER <youruser> QUOTA 0 ON <original_tablespace>;

Now the tables will end up in user's default tablespace.

Quote:
> 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

HTH,


Certified Oracle DBA (7.3 & 8.0 OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Import table into different tablespace

2. Import Tables with blob variables into different tablespaces

3. Importing tables to a different tablespace?

4. Importing lob tables to different tablespace

5. Importing a .dmp into a different tablespace (RH7.1, Oracle 81610)

6. importing dmp on different tablespace

7. importing into a different tablespace

8. Import to a different tablespace

9. import to different tablespace?

10. 1 table in different tablespaces

11. Availability of table or index while moving to a different tablespace

12. Move table to a different tablespace


 
Powered by phpBB® Forum Software