Re: Can't move tables with columns of type long

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:vkctbvck5oacsgd8drfbct6gudgodc3ir1_at_4ax.com...
> When issuing the command "alter table THIS_TABLE move tablespace
> ANOTHER_TS" this command fails when the table has columns of type long
> or lob. This is annoying me for a while now.

It failing with LONGS I can understand. It failing with LOBS, I don't. However, the rules about what works where are Byzantine, to say the least, and I'll buy for the moment that LOBS won't work (but I reckon they would in 9i!).

>
> How can recreate such tables in a different tablespace with "normal"
> SQL commands?

Create Table As Select seems about your only option.

>And what should I be aware of? What about constaints,

Have to be re-created with a CTAS

> triggers,

Likewise

>indexes related to this table?

Recreated from scratch, I'm, afraid.

>Which will be the
> availability impact while recreating the table in another tablespace?
>

Huge impact. That's why it's so vital you house the thing properly in the first place. Even a 'move' requires you to recreate all indexes on the table... re-organization is *not* cheap. Prevention is a million times better than cure.

Regards
HJR
> Please consider that I am not acquainted with the data as such. I
> don't do any development work and refrain myself from doing anything
> that put the logic of relations in the DB in danger. (I must recognize
> that this is also due to my lack of skillness in this area). I am the
> Unix and DB admin and just keep things working. Recently I had to
> reorganize the whole DB because this collegues of mine doing
> development work quite soon rendered the DB almost unusuable
> (fragmentation, migrated rows etc.).

Come now.... you're telling me that you are letting a developer loose upon a production database? Surely not. And if it wasn't production, then row migration is a trivial issue, and fragmentation is *never* a performance issue, but merely a waste of space... so production or development, with a fist-full of hard disks at the ready, I wouldn't worry.

Regards
HJR
>
> Thanks
>
> Rick Denoire
>
Received on Sun May 11 2003 - 22:40:03 CDT