Re: How to duplicate data

Hi,

Pete Cruz wrote:
>
> I have a production and development server running Oracle 7.3.3 on both.
>
> How can I duplicate data entered into production, into the development
> instance, this process can be run at night. I have tried running an SQL
> to copy the data. The problem is several tables have long (diary) fields
> and do not get copied.
>
> I have made a few attempts using the copy command but have not been
> successful.
>
> Thanks for any suggestions.
> --
>
> Pete Cruz
> petec_at_eden.com

You'll need to look more deeply into the SQL*PLUS copy command. Remember, it's not stored in the SQL buffer, so you'll need the continuation mark (default the "-") if you want to continue on the next line). The Copy command is the only default copy that will take longs.

Though, you should be able to use direct inserts ... (insert into select) but that requires huge rollback segments if you have large amounts of data. COPY is definately the way to go ....

If you know the maxsize of your longs, PL/SQL might be a solution too. But remember, with large amount of data, a commit will clear/close your cursor, and you cannot continue your select.

• Peter H. Larsen Oracle Consultant