Changing Tablespaces

[hummy]

Hi....

Does anyone know how i can change the tablespace of a table that uses blobs. I want to change the tablespace for the LOB Storage but have so far been unsuccesful. I am using the following SQL statement:-

ALTER TABLE IMAGE MOVE TABLESPACE XSU001

But when i go and check the table the tablespace being used for the LOB_STAORAGE is the old one and the tablespace being used for the Table is XSU001. But ideally i would like the whole database in 1 tablespace.

Appreciate your help!!!! and thanks in advance

[kgb]

That is because you move TABLE segment, but not LOB segment.
Please check documentation:

http://tahiti.oracle.com/pls/db901/d...25&preference=

[supower]

exp the table and recreate table structure in new tablespace and imp the table

[hummy]

I tried the export and import method mentioned but that did solve the problem as it still looked for the old tablespace name because the LOB segment was stored in the old tablespace.

I'll have a look at the link mentioned above and try that.

[dba1]

I have a table using RAW datatype. There is no problem to move it between different table spaces. Do you have enough space in the new tablespace?

[hummy]

well according to the lin k i should be using the following syntax:-

alter table image_data move lob(image) store as tablespace xsu001;

But when i run this command it gives me the following error message
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

Anyone any ideas?

[kgb]

Originally posted by hummy
I tried the export and import method mentioned but that did solve the problem as it still looked for the old tablespace name because the LOB segment was stored in the old tablespace.

I'll have a look at the link mentioned above and try that.

It is impossible, if you create object and import data with option ignore=Y, you will not get any errors at all.

[julian]

Originally posted by hummy
well according to the lin k i should be using the following syntax:-

alter table image_data move lob(image) store as tablespace xsu001;

But when i run this command it gives me the following error message
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

Anyone any ideas?

Hi, try this:

ALTER TABLE image_data MOVE TABLESPACE xsu001 LOB (image) STORE AS lobsegment (TABLESPACE xsu001);

[hummy]

Ok thanks managed to do it with the import export method. Forgot the ignore=y parameter the first time round.

Thanks everyone