PROBLEM:

While trying to create a table using CTAS , got below error:(ORA-00997)

create table test8 AS select DIRECTIONS from siebel.s_org_ext;
create table test8 AS select DIRECTIONS from siebel.s_org_ext
                             *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SOLUTION:

In general, we can’t create an object_type OF LONG datatype using CTAS method.

desc siebel.s_org_ext

 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 DIRECTIONS                                   LONG

However, There are two workarounds for this one.

Solution 1:(USE TO_LOB utility)

create table test8 AS select TO_LOB(DIRECTIONS) directions from siebel.s_org_ext;

Table created.

Solution 2:(datapump )

Use datapump utility to do expdp impdp of that table.

Solution 3:(sqlcl utility).

We can use the copy command of sqlcl utility to copy LOB segment data.

SEE – SQLCL UTILITY IN ORACLE 

/export/home/oracle/Utility/sqlcl/sqlcl/bin

./sql / as sysdba

SQLcl: Release 17.2.0 Production on Mon Aug 07 10:34:46 2017

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options



------Use copy command: (TESTDB is the tns entry of source database) 

SQL> copy from system/oracle@TESTDB create TEST_SQLCL  using select directions from siebel.s_org_ext where  rownum < 2; Array fetch/bind size is 15 (less if there are longs or blobs). (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80) Table TEST_SQLCL created. 1 rows selected from system@d2retro. 1 rows inserted into TEST_SQLCL. 1 rows committed into TEST_SQLCL at DEFAULT HOST connection. SQL> select count(*) from TEST_SQLCL;

  COUNT(*)
----------
         1