BUG: SQL*PLUS Copy command loss data. Be careful!

A serial bug in copy trapped me badly recently. When I used it to copy statistic tables from one host to another, it changed the column has “density” value to 0. That caused bad plan on target host.

SQL> copy from system/xxx@source create TMP_STAT_2 using select * from TMP_STAT;
…   2106 rows committed into TMP_STAT_2 at DEFAULT HOST connection.
SQL> select c4, n2 from TMP_STAT_2 where c1='USER_INFO';
 C4                                     N2
------------------------------ ----------
                                   871858
GUID                                    0
USER_ID                                 0

Using exp/imp:
 SQL> select c4, n2 from TMP_STAT where c1='USER_INFO';
 C4                                     N2
------------------------------ ----------
                                   871858
GUID                           2.7249E-06
USER_ID                        1.0063E-06

As you can see, copy changes the very small data “1.0063E-06” to “0”. It’s better avoid use copy command, who knows if there are other bugs.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: