How to Move LOB Data to Another Tablespace When the Table Also Contains a LONG Column

(Doc ID 453186.1)

Last updated on MAY 24, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.
***Checked for relevance on 15-Feb-2017***



Goal

Trying to move LOB data to a different tablespace for a table containing both LOB and LONG columns.

The following statement will error with ORA-00997: illegal use of LONG datatype :

ALTER TABLE foo MOVE LOB(lobcol) STORE AS lobsegment (TABLESPACE new_tbsp STORAGE (new_storage));

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.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms