I have a 14GB tablespace which about 3,000 tables (and only tables) in. Only 2.6GB is used so I would like to resize the tablespace to about 3GB initially.
I moved the tables with "alter table <owner>.<tablename> move tablespace <tablespace>" and this worked for most of the tables but failed on some with this kind of error:
alter table IFSAPP.COMMAND_SYS_BUFFER_TAB move tablespace IFSAPP_DATA_TEMP
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
I then moved the other tables back to the original a little frustrated.
Further reading suggested exporting and importing the tables is the way to go.
I prefer the alter table because Oracle manages the lock whilst it moves. If I export and import, the table could change in between, potentially ending with me importing an old copy, couldn't it? [It seems neigh on impossible to guarantee an available window to do much!]
So I though I could you the alter table move for ALL tables and those that are left, use export/import, to minimise the risk.
How should I proceed?
-
Create TEMP_TABLESPACE as 3GB
-
"alter table ... move tablespace ..." for all tables in ORIGINAL_TABLESPACE to TEMP_TABLESPACE
-
For the remaining tables, individually:
a) lock table (not sure how)
b) export table: exp user/pass tables=tablename file=exp.dmp rows=yes
c) drop table
d) create table in TEMP_TABLESPACE*
e) import table: imp user/pass tables=tablename
* this means I need to know how to re-create the table. It also means views and indexes and packages are going to become invalid.
-
drop tablespace ORIGINAL_TABLESPACE
-
Create ORIGINAL_TABLESPACE as 3GB
-
"alter table ... move tablespace ..." for all tables in TEMP_TABLESPACE to ORIGINAL_TABLESPACE
-
For the remaining tables, individually:
a) lock table (not sure how)
b) export table: exp user/pass tables=tablename file=exp.dmp rows=yes
c) drop table
d) create table in ORIGINAL_TABLESPACE
e) import table: imp user/pass tables=tablename
This seems high risk and messy.
Has anyone advice or tips to make it simpler?
As it stands I am considering leaving alone, the risks worry me!
Thanks