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

Rick Denoire <100.17706_at_germanynet.de> schreef in berichtnieuws 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.
|
| How can recreate such tables in a different tablespace with "normal"
| SQL commands? And what should I be aware of? What about constaints,
| triggers, indexes related to this table? Which will be the
| availability impact while recreating the table in another tablespace?
|
| 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.).
|
| Thanks
|
| Rick Denoire

1. Export the table (with triggers, constraints, indexes, grants) (check options with exp help=y)
2. imp ... indexfile=x.sql rows=n show=y
3. in x.sql you can find create table, create index statements. Build scripts from it. Adjust the tablespace names (and storage clause if needed)
4. drop the table
5. create the table in advance with the script build in step 3.
6. import the table (and triggers, constraints, grants; if its a large table: indexes=no and create indexes with script from step 3 after imp)

If possible test import in another database before dropping the table to make sure everything is there.
If possible, let the new tablespace be a locally managed tablespace. Instead of step 2,3 you can use a tool like TOAD to generate the create scripts. Received on Sun May 11 2003 - 16:23:49 CDT