Today a friend of mine asked me how to move a table having LONG datatype.As per him, when he fired alter table table_name move tablespace , he got “ORA-00997: illegal use of LONG datatype”.In past, i had moved the tables to the other tablespace but had not faced this error.So, it was time to do it 🙂
ANAND@MATRIX> create table long_test (x number, y long,z raw(16)) tablespace test;
Table created.
ANAND@MATRIX> insert into long_test values (1,'ABC','');
1 row created.
ANAND@MATRIX> insert into long_test values (1,'ABCD','');
1 row created.
ANAND@MATRIX> commit;
Commit complete.
ANAND@MATRIX> select * from long_test;
X Y Z
---------- -------------------------------------------------------------------------------- --------------------------------
1 ABC
1 ABCD
ANAND@MATRIX> alter table long_test move tablespace users;
alter table long_test move tablespace users
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
Hmmm…thats true.We can move the table.Just to know the error tried
ANAND@MATRIX> ALTER TABLE LONG_TEST MOVE LOB(Y) STORE AS (TABLESPACE USERS); ALTER TABLE LONG_TEST MOVE LOB(Y) STORE AS (TABLESPACE USERS) * ERROR at line 1: ORA-00997: illegal use of LONG datatype
So, the only way is export-import.Till 9i use exp-imp, from 10g and above use expdp-impdp.Though my database version is 11.2.0.1.0 , still i used exp/imp.
ANAND@MATRIX> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
D:\>exp file=long_test.dmo log=long_test.log tables=long_test feedback=1
Export: Release 11.2.0.1.0 - Production on Wed Jan 5 19:48:49 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: anand
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table LONG_TEST
..
2 rows exported
Export terminated successfully without warnings.
Drop the table and re-create it in new tablespace.
D:\>sqlplus anand/anand123 SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 5 19:49:03 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ANAND@MATRIX> drop table long_test purge; Table dropped. ANAND@MATRIX> create table long_test (x number, y long,z raw(16)) tablespace users; Table created. ANAND@MATRIX> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Import Session
D:\>imp file=long_test.dmo log=long_test.log full=y feedback=1 ignore=y
Import: Release 11.2.0.1.0 - Production on Wed Jan 5 19:49:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: anand
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing ANAND's objects into ANAND
. importing ANAND's objects into ANAND
. . importing table "LONG_TEST"
..
2 rows imported
Import terminated successfully without warnings.
D:\>
Checking the table’s tablespace
19:43:54 SYS@MATRIX> @table_info
Enter value for table_name: long_test
old 9: WHERE table_name like UPPER('%&table_name%')
new 9: WHERE table_name like UPPER('%long_test%')
TABLE_NAME OWNER TABLESPACE_NAME NUM_ROWS LAST_ANAL AVG_ROW_LEN BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ------------------------------ ---------- --------- ----------- ---------- ------------
LONG_TEST ANAND USERS
If you are using expdp-impdp, simply take the export and then user table_exists_action and remap_tablespace parameters in impdp.
impdp directory=DATA_PUMP_DIR dumpfile=long_test.dmp logfile=long_test.log full=y remap_tablespace=test:users table_exists_action=replace
Moving Tables Having LOB datatypes
======================================
ANAND@MATRIX> CREATE TABLE LOB_TEST 2 ( 3 TEST_ID NUMBER NOT NULL, 4 TEST_NAME CLOB, 5 CONSTRAINT TEST_PK PRIMARY KEY(TEST_ID) 6 ) 7 / Table created. ANAND@MATRIX> select segment_name,tablespace_name from user_segments where segment_name='LOB_TEST'; no rows selected ANAND@MATRIX> select table_name,tablespace_name from user_tables where table_name='LOB_TEST'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ LOB_TEST TEST ANAND@MATRIX> alter table LOB_TEST move tablespace users; Table altered. ANAND@MATRIX>
Above command will move the table to new tablespace but will not move the CLOB segment and it will still be in original tablespace. This is because LOB data is stored outside of the table.Check the tablespace of the CLOB column by issuing following sql.
ANAND@MATRIX> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'LOB_TEST'; INDEX_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TEST_PK TEST SYS_IL0000075250C00002$$ TEST ANAND@MATRIX> select table_name,tablespace_name from user_tables where table_name='LOB_TEST'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ LOB_TEST USERS ANAND@MATRIX> ALTER TABLE LOB_TEST MOVE LOB(TEST_NAME) STORE AS (TABLESPACE USERS); Table altered. ANAND@MATRIX> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'LOB_TEST'; INDEX_NAME TABLESPACE_NAME ------------------------------ ------------------------------ SYS_IL0000075250C00002$$ USERS TEST_PK TEST
Now,if you have table, with both LONG and LOB columns and you want to move the LOB segment to new tablespace,how to do that?
ANAND@MATRIX> create table lob_mv_tab (a number,b long, c clob) tablespace test;
Table created.
ANAND@MATRIX> insert into lob_mv_tab values(1,'X','Y');
1 row created.
ANAND@MATRIX> insert into lob_mv_tab values(1,'Y','Z');
1 row created.
ANAND@MATRIX> commit;
Commit complete.
ANAND@MATRIX> select * from lob_mv_tab;
A B
---------- --------------------------------------------------------------------------------
C
--------------------------------------------------------------------------------
1 X
Y
1 Y
Z
ANAND@MATRIX> ALTER TABLE LOB_MV_TAB MOVE LOB(C) STORE AS (TABLESPACE USERS);
ALTER TABLE LOB_MV_TAB MOVE LOB(C) STORE AS (TABLESPACE USERS)
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
00:03:31 ANAND@MATRIX> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
So, in order to move the LOB segment to new tablespace,export the table,recreate the table in new tablespace and then import the dump.
D:\>exp file=lob_mv_tab.dmo log=lob_mv_tab_test.log tables=lob_mv_tab feedback=1
Export: Release 11.2.0.1.0 - Production on Thu Jan 6 00:05:14 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: anand
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table LOB_MV_TAB
..
2 rows exported
Export terminated successfully without warnings.
Recreate the table
D:\>sqlplus anand/anand123 SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 6 00:05:25 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ANAND@MATRIX> drop table LOB_MV_TAB purge; Table dropped. ANAND@MATRIX> create table lob_mv_tab (a number, b long, c clob) lob(c) store as (tablespace users) tablespace test; Table created. ANAND@MATRIX> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'LOB_MV_TAB'; INDEX_NAME TABLESPACE_NAME ------------------------------ ------------------------------ SYS_IL0000075269C00003$$ USERS ANAND@MATRIX> select segment_name,tablespace_name from user_segments where segment_name='LOB_MV_TAB'; no rows selected ANAND@MATRIX> select table_name,tablespace_name from user_tables where table_name='LOB_MV_TAB'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ LOB_MV_TAB TEST ANAND@MATRIX> 00:07:19 ANAND@MATRIX> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Import the data from dump
D:\>imp file=lob_mv_tab.dmo log=lob_mv_tab_test.log full=y feedback=1 ignore =y
Import: Release 11.2.0.1.0 - Production on Thu Jan 6 00:07:49 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: anand
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing ANAND's objects into ANAND
. importing ANAND's objects into ANAND
. . importing table "LOB_MV_TAB"
..
2 rows imported
Import terminated successfully without warnings.
Enjoy 🙂
Learn. Share. Repeat. 
Hi Anand,
Very informative and after seeing the posting, I just re-collected how to use the pre-defined function to_lob().
It might convert the long raw to lob column.
Hi Anand,
I really appreciate your informative posting.
Isn’t there anyway except using exp/imp utility?
I mean I urgently need to do something like the following:
create table copy_table
as select column_id, column_name, column_long_raw from original_table;
I searched a lot but I didn’t find a solution, may you please give me any solution if available?
Thanks & Best Regards
Mahmoud
Hi Mohmoud,
No you cannot use CTAS of tables containing LONG datatype. You can use COPY command to get the data into new table.E.g..
SQL> desc long_test; Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------- -------- - X NUMBER Y LONG Z RAW(16) SQL> select * from long_test; X Y Z ---------- -------------------------------------------------------------------------------- -------------------------------- 1 ABC 1 ABCDSQL> copy to anand/anandXXX@matrix create newlong (x,y,z) using select * from long_test;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table NEWLONG created.
2 rows selected from DEFAULT HOST connection.
2 rows inserted into NEWLONG.
2 rows committed into NEWLONG at anand@matrix.
SQL> select * from newlong; X Y Z ---------- -------------------------------------------------------------------------------- -------------------------------- 1 ABC 1 ABCDanand/anandXXX –> username/password where the table has to be created.
@[tna_alias]–> Should have the tns_alias for the database where it has to be created.Can be the same database.TNSPING to the tns_alias should work.
Hope this helps
Regards,
Anand
Thanks Anand for posting such a good document. It’s really helpfull
Hi Anand,
For moving the table with long column, may I know if it’s possible if I just take a table export and import it to another tablespace? It looks like in your example you’re using full import/export? Will the command be the same? Should I backup the table (create table as ) first before dropping it ? Thank you Anand for this great post.
Regards,
Soeraya.
Hello Anand,
I have a table with 6 columns, and one of them is long datatype (let us call it C6.) My very simple select query below takes very long:
select c1, c2, c3, c4, c5 from table_long; — Note that C6 is excluded. There is no where clause etc.
My DBA’s theory is that Oracle is selecting C6 also and discarding it later while returning the data. He may be right on this, since long columns were stored on the same data page as rest of the columns.
Environment: 11g 11.2.0.3.0
Can you please suggest anything to make this query faster.
Thanks.
Hey,
Since you are selecting the whole table (except 1 column), but it needs to read all the data and then later exclude the column which will take time depending on how big the table is. 2 ways to optimize is either to use where clause or use parallel .
And in case you are using JBDC, have a look at http://ipalopezhentsev.github.io/java/jdbc/oracle/2014/08/06/beware-clobs/
Parallel — https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
—
Anand