Oracle Database Discussions

Hello,

I'm trying to reorganize tablespace IEFSENCY_TAB that has 15GB and actually is only used 2 GB because we have moved the bigger tables to new tablespaces in order to have better performance, now we are thinking about moving the rest of the tables to new small tablaspaces , drop this one and rename the new one to IEFSENCY_TAB.

I've moved the tables using ALTER TABLE, most of them are moved but there are 4 tables I can't move because give me the error:ORA-00997: illega-l use of LONG datatype

I'm reading some documentation, and I see that the long datatype is deprecated from oracle 8i, we are using 10g now. I'm reading on the metalink webside, the following to solve this error is doing:

Perform following steps to move the table to another tablespace:

1. Export the table.
2. Recreate the table in the new tablespace.
3. Import the table.

The ALTER TABLE ... MOVE command cannot be used to move a table containing a
LONG or LONG RAW column. This is documented in the Oracle9i SQL Reference
manual.

But I'm thinking instead of doing this if is better to do:

1.- "convert type long to clob" on the table
2.- use the alter table since the column is clob now.

The tables that give me the error are:

SYSTEM.DTXT but ther other ones are SYSTEM.USER_PROFILE , SYSTEM.USER_PLAN, SYSTEM.PRODUCT_PROFILE. I don't understand why the tables user_profile, user_plan and product_profile are on the tablespace IEFSENCY_TAB, instead the tablespace SYSTEM.
My question is I can do the convert column in the user_profile, user_plan, product_profile, or is better to re-run $ORACLE_HOME/rdbms/admin/utlxplan.sql to recreate it.-

Thanks in advance,
Beatriz