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 User/pw@dblink to User/pw@dblink 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 in line 2:ora-00997:illegal use of LONG datatype sql> SET LONG 100000 SQL
> COPY from HR/HR@ORCL 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 HR@ORCL.
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 () text_length number (TEXT) LONG type_text_length Number (type_text) VARCHAR2 (4000) oid_text_length NU Mber (Oid_text) VARCHAR2 (4000) View_type_owner VARCHAR2 (View_type)2 () Superview_name VARCHAR2 () 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.