Re: copy data to a new table

Margaret Burwell wrote:
>
> I want to copy all the data in table1 to an identical table called table2.
> One of the columns is datatype LONG. I have tried
>
> create table table2 as select * from table1 ;
>
> and creating the table and then
>
> insert into table2 (select * from table1);
>
> Both of these give me ORA-00997: illegal use of LONG datatype. Does
> anyone have any suggestions?
>
> Marg

Hi Margareth,

I know this problem very well. LONG datatypes are a great probem for ORACLE. You should make an export and an import from taht table, because you will not be able to solve your problem by using SQL or PL/SQL. Type imp help=y to see the different parameters for the import utility. One of the parameters are called "long" or "bytes", something like that. Take that parameter with a very high level ( 65000 ... ). If you encounter problems by using imp please refer to these parameters.

One suggestion more : if your table will have more than 2 GBytes of data think of using named pipes ( when using UNIX, SOLARIS ). Most systems don't allow of making files greater than 2 Gbytes, named pipes work differently.

When you want to use named pipes, do the following :

(under SOLARIS and may more UNIX systems : )

mknod NamedPiped p
exp login/password_at_database file=NamedPipe tables=... & ( let the export be done in the background by using '&' ... )

imp login/password_at_databse file=NamedPipe tables=.... ( do this in the foreground by NOT using '&' )

Good luck,

Hans Pfeiffer Received on Wed Aug 12 1998 - 16:11:11 CDT