Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Move tables (some with LONG datatypes) across tablespaces in Oracle 9i

401820Jul 25 2007 — edited Jul 25 2007

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?

  1. Create TEMP_TABLESPACE as 3GB

  2. "alter table ... move tablespace ..." for all tables in ORIGINAL_TABLESPACE to TEMP_TABLESPACE

  3. 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.

  1. drop tablespace ORIGINAL_TABLESPACE

  2. Create ORIGINAL_TABLESPACE as 3GB

  3. "alter table ... move tablespace ..." for all tables in TEMP_TABLESPACE to ORIGINAL_TABLESPACE

  4. 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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 22 2007
Added on Jul 25 2007
12 comments
1,649 views