How to move table containing LONG or LOB columns?

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 🙂

5 thoughts on “How to move table containing LONG or LOB columns?

  1. 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

  2. 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 ABCD
    
    

    SQL> 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 ABCD
    

    anand/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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s