If you get an "ORA-00997: illegal use of LONG datatype" error, metalink
(note 165901.1) advises you to

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

This is annoying, because you have
- a table outage (manual read only table lock needed)
- more admin work (more steps than just "alter table .. move tablespace
...")

Oracle allows you to ".. move tablespace" for any other object
- tables
- table partitions
- indexes
- index partitions
- index organized tables
- lob segments
- ...

Does somebody know a more comfortable method to move tables with LONG
columns?
- Will this feature be implemented by Oracle in the future?
- LONG column type is out-dated according to Oracle. Can you convert
LONG and LONG RAW to BLOB,CLOB,... without the need of testing. Is it
100% interoperable?

Thanx for your discussion

On 17 Sep 2006 18:19:20 -0700, "NitelyJoy" <nitelyjoy@ist-einmalig.de>
wrote:

>If you get an "ORA-00997: illegal use of LONG datatype" error, metalink
>(note 165901.1) advises you to
>
>1. Export the table.
>2. Recreate the table in the new tablespace.
>3. Import the table.
>
>This is annoying, because you have
>- a table outage (manual read only table lock needed)
>- more admin work (more steps than just "alter table .. move tablespace
>..")
>
>Oracle allows you to ".. move tablespace" for any other object
>- tables
>- table partitions
>- indexes
>- index partitions
>- index organized tables
>- lob segments
>- ...
>
>Does somebody know a more comfortable method to move tables with LONG
>columns?

No, LONGs are deprecated since 8.0

>- Will this feature be implemented by Oracle in the future?
No, Why would they? LONGs are deprecated since 8.0
>- LONG column type is out-dated according to Oracle. Can you convert
>LONG and LONG RAW to BLOB,CLOB,... without the need of testing. Is it
>100% interoperable?
>
They should be. Why are you still using LONGs in the first place?
And your version is?


>Thanx for your discussion

--
Sybrand Bakker, Senior Oracle DBA

NitelyJoy a écrit :

> If you get an "ORA-00997: illegal use of LONG datatype" error, metalink
> (note 165901.1) advises you to
>
> 1. Export the table.
> 2. Recreate the table in the new tablespace.
> 3. Import the table.
>
> This is annoying, because you have
> - a table outage (manual read only table lock needed)
> - more admin work (more steps than just "alter table .. move tablespace
> ..")

Getting rid of LONG is not annoying. LONG are annoying by essence.

> Does somebody know a more comfortable method to move tables with LONG
> columns?
No, the method found in Metalink is the good one. No way to do
differently.

> - Will this feature be implemented by Oracle in the future?
Of course not, as Sybrand stated, it's been deprecated a long time ago
(though internal tables still use them, see DBA_TRIGGERS for instance)

> - LONG column type is out-dated according to Oracle. Can you convert
> LONG and LONG RAW to BLOB,CLOB,... without the need of testing. Is it
> 100% interoperable?

As far as I used the TO_LOB function, it is.
Besides, there is always a need of testing.

--
Seb L.

Why are PLAN_TABLEs (created by file
$ORACLE_HOME/rdbms/admin/utlxplan.sql) for Oracle 9i (what about 10g? I
don't know.) still created with a LONG column, when this data type is
deprecated since Oracle 8?

If I convert the LONG column of all PLAN TABLEs with the function
to_lob() on my own, is that way supported and desired by Oracle?

Why does Oracle not provide a create statement with a LOB column
instead in utlxplan.sql?

NitelyJoy wrote:
> Why are PLAN_TABLEs (created by file
> $ORACLE_HOME/rdbms/admin/utlxplan.sql) for Oracle 9i (what about 10g? I
> don't know.) still created with a LONG column, when this data type is
> deprecated since Oracle 8?

Because oracle is not completely consistent yet in terms of dealing
with LONGs.

>
> If I convert the LONG column of all PLAN TABLEs with the function
> to_lob() on my own, is that way supported and desired by Oracle?

Dunno probably not. Open a service request if that is something you
really plan to try.

>
> Why does Oracle not provide a create statement with a LOB column
> instead in utlxplan.sql?

Good question why don't you submit that in a service request?

NitelyJoy wrote:
> Why are PLAN_TABLEs (created by file
> $ORACLE_HOME/rdbms/admin/utlxplan.sql) for Oracle 9i (what about 10g? I
> don't know.) still created with a LONG column, when this data type is
> deprecated since Oracle 8?
>
> If I convert the LONG column of all PLAN TABLEs with the function
> to_lob() on my own, is that way supported and desired by Oracle?
>
> Why does Oracle not provide a create statement with a LOB column
> instead in utlxplan.sql?

In 10g the PLAN_TABLE is deprecated in favour of PLAN_TABLE$ which is
built by catplan.sql. PLAN_TABLE lives on, for backward compatibility,
as a synonym
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group