Toad World® Forums

Altering tables to change tablespace


#1

Hello Fellow Toad Users and Toad Gurus,

We have a legacy system where 451 tables were improperly placed in the USERSPACE1 tablespace instead of the tablespace defined for them. I have been tasked to fix this. Using Toad, when I select 1 table I can change the tablespace and it generates the correct code, but when I select 2 to 451 tables it does not give me the option to change the tablespace. Does anyone have a methodology I can use to make these changes without going to 451 separate tables and making the change. Thanks for your help!


#2

You can accomplish what you are trying to do in Toad for DB2.
You can use the Object Migration Feature to do this.
Select your tables, right click and select ‘Migrate Object’.
Specify the same database as your source/target.
Make sure you check the option ‘Include DROP statements’.
If your tables have data and you want that data preserved, also check ‘Migrate Data’.
Then on the Migration Wizards ‘Define Transformaiton Rules’ page, specify the new tablespace name. All the selected tables will be recreated in that tablespace. The attached images should assist you in understanding how to accomplish this.
At the end of the wizard scripts will be generated to perform the alters.
The ‘Object Migration Feature’ is only available when you have a DBA License Key.
(If that feature is enabled you have such key)
Jeff
s3.png


#3

You can accomplish what you are trying to do in Toad for DB2.
You can use the Object Migration Feature to do this.
Select your tables, right click and select ‘Migrate Object’.
Specify the same database as your source/target.
Make sure you check the option ‘Include DROP statements’.
If your tables have data and you want that data preserved, also check ‘Migrate Data’.
Then on the Migration Wizards ‘Define Transformaiton Rules’ page, specify the new tablespace name. All the selected tables will be recreated in that tablespace. The attached images should assist you in understanding how to accomplish this.
At the end of the wizard scripts will be generated to perform the alters.
The ‘Object Migration Feature’ is only available when you have a DBA License Key.
(If that feature is enabled you have such key)
Jeff
s2.png


#4

You can accomplish what you are trying to do in Toad for DB2.
You can use the Object Migration Feature to do this.
Select your tables, right click and select ‘Migrate Object’.
Specify the same database as your source/target.
Make sure you check the option ‘Include DROP statements’.
If your tables have data and you want that data preserved, also check ‘Migrate Data’.
Then on the Migration Wizards ‘Define Transformaiton Rules’ page, specify the new tablespace name. All the selected tables will be recreated in that tablespace. The attached images should assist you in understanding how to accomplish this.
At the end of the wizard scripts will be generated to perform the alters.
The ‘Object Migration Feature’ is only available when you have a DBA License Key.
(If that feature is enabled you have such key)
Jeff
s1.png