You may be running an Oracle 10g Release 2 database server, but supporting some applications may have been written very early. Data tables created prior to Oracle 8i typically have a long data type to store large text.
With the introduction of the large Object (LOB) data type, the long and long raw data types are discarded. If you are copying a data table that uses a Long data type, then using the CREATE table as SELECT syntax will not complete the task, you will return an error: Ora-00997:illegal use of a Long data type.
You can export the table and then import it, but doing a lot of work. The copy command in Sql*plus can still use a single command to complete replication of such data tables. The following is the format for using the Copy command (note: Use the short line "-" to wrap).
COPY from to CREATE tablename-
USING select-statement;
However, the functionality of the Oracle 8.0 Copy command is frozen because it can only replicate data tables that consist of the following data types: Char,date,long,number and VARCHAR2. The addition of new data types in the 8i version is not supported by the latter, which is a completely inherited method.
Sql> CREATE TABLE My_views as 2
SELECT * from User_views;
SELECT * FROM User_views *
ERROR at line 2:
Ora-00997:illegal Use of LONG datatype
Sql> SET LONG 100000
Sql> COPY from CREATE my_views-
>using select * from User_views;
Array fetch/bind size is 15.
(ArraySize is 15) would commit
When done. (Copycommit is 0)
Maximum long size is 100000.
(Long is 100000)
Table My_views created.
1 rows selected from.
1 rows inserted into my_views.
1 rows committed into my_views at
DEFAULT HOST connection.
Sql> descmy_views; Name
Null? Type----------------------
------------------- -------- -------
---------------------
View_name not NULL VARCHAR2 (30)
Text_length Number (38)
TEXT LONG
Type_text_length Number (38)
Type_text VARCHAR2 (4000)
Oid_text_length Number (38)
Oid_text VARCHAR2 (4000)
View_type_owner VARCHAR2 (30)
View_type VARCHAR2 (30)
Superview_name VARCHAR2 (30)
Sql> Spool Off
List A
The error of using CREATE table and the method of successfully using the Copy command are shown in List A.