| MOVE table with LONG datatype column - Oracle 8i Release 8.1.7.2.0 [message #60741] |
Tue, 02 March 2004 03:20  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
I'm using Oracle 8i R. 8.1.7.2.0.
I'm trying to move a table from one tablespace (dictionary managed tablespace) to another tablespace (locally managed tablespace). This table contains a column with LONG datatype!
I received this error message: ORA-00997: illegal use of LONG datatype.
Here is what I have tried:
ALTER TABLE table_name MOVE
TABLESPACE USERS_LOB_DATA01;
OR
ALTER TABLE table_name MOVE TABLESPACE USERS_LOB_DATA01
LOB(my_lob_column) STORE AS (TABLESPACE USERS_LOB_DATA01);
OR
ALTER TABLE table_name MOVE
LOB(my_lob_column) STORE AS (TABLESPACE USERS_LOB_DATA01);
Must be LONG datatype treated differently than LOB under MOVE command? Could somebody help me with this problem? Could someone give me the syntax of the MOVE command for objects with LONG datatype?! Thank you in advance.
Regards,
Patrick Tahiri.
|
|
|
|
| Re: MOVE table with LONG datatype column - Oracle 8i Release 8.1.7.2.0 [message #60743 is a reply to message #60741] |
Tue, 02 March 2004 04:35  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
Move doesnt support Long datatypes. You can either convert them to LOBs and then move or do exp/imp of the table with the LONG column or create the table with LONG in the locally managed tablespace and copy the data from the old table using PL/SQL loop or CTAS with to_lob in the locally managed tablespace..
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y LONG
SQL> alter table t move;
alter table t move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
-- You can create the new table in the Locally Managed tablespace
SQL> create table t_lob tablespace users as select x,to_lob(y) y from t;
Table created.
SQL> desc t_lob
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y CLOB
-- Now you can drop the old table and rename the new table
-- Or you can move the LOB table to the locally managed tablespace
SQL> alter table t_lob move;
Table altered.
-- Or you can precreate the new table with LONG in the locally managed tablespace and do exp/imp
-- export the Long table
SQL> !exp / file=t.dmp tables=t compress=n
Export: Release 9.2.0.3.0 - Production on Tue Mar 2 09:32:30 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T 2 rows exported
Export terminated successfully without warnings.
-- just rename the old table for reference purposes
SQL> rename t to tbak;
Table renamed.
-- Create the LONG table in the locally managed tablespace
SQL> create table t(x int,y long) tablespace users;
Table created.
-- now import the data
SQL> !imp / file=t.dmp tables=t ignore=y
Import: Release 9.2.0.3.0 - Production on Tue Mar 2 09:33:43 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$ORACLE's objects into OPS$ORACLE
. . importing table "T" 2 rows imported
Import terminated successfully without warnings.
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y LONG
-Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
