Re: Copying a table

amar nagi wrote:
>
> Can somebody please help me.
>
> I have a table with a lot of columns. I need to make a copy of the table
> into a new table (in a different schema).
>
> How can I use the my variable of type ROWTYPE to insert the columns instead
> of actually typing in each column name
>
> thanks in advance
>
> Amar

Most of the replies to this give and example of

        create table x as select * from table y;

This works fine if the table is relatively small, and if it does not contain any LONG datatypes.

However, since this is a single command, all of the work being done must fit into a single rollback segment. If this is a large table, there is a good change that you will run out of rollback segment space.

A common approach is to create a stored procedure, open a cursor, and do and occasional commit. When this is done, you run into the inconvenience that you describe. You can select into a record, but you can't use that record in your insert statement.

An alternative is the use the SQL*Plus COPY command. This command gives you simplicity of using SQL, but also allows you to take occasional commits.

A couple of points:

• You use COPYCOMMIT and ARRAYSIZE to determine how often the COPY command commits
• You have to do the copy across sql*net.
• This is a SQL*Plus command, not a SQL command, so you must use a '-' if you wish to continue to a second line.

Here is an example that creates table user2.a from user1.a and commits every 1000 records

connect user2/password
set copycommit 100
set arraysize 10
copy from user1/password_at_p: -
create a -
using select * from a

The 'create' can be replaced with 'append','insert', or 'replace'. SQL*Plus uses the session information in the from clause to retrieve the data specified in the 'using' clause, and then creates the table in the current session (because you didn't specify a 'to' clause).

Note the 'p:'. This is a sql*net v1 named pipe string that causes you to connect back to the current instance. If you are using sql*net v2, your tnsnames.ora service name could be set up to use IPC so that you don't incur the overhead of TCP/IP.

Hope this helps

-- 
Bob Swisshelm
Eli Lilly and Company
nospam.swisshelm_at_lilly.com  
(remove 'nospam' if you want to send mail to me)