[20140823]在sqlplus使用copy注意.txt

--有时候使用copy来复制表数据,但是前几天我的测试遇到一些小问题,做一个记录,总之还是小心注意这些细节.

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table tt (id number,idx number);
insert into tt values (1,1.1111);
insert into tt values (2,2.2222);
insert into tt values (3,3.3333);
commit ;

SCOTT@test01p> select * from tt;
ID        IDX
--- ----------
  1     1.1111
  2     2.2222
  3     3.3333

SCOTT@test01p> copy to scott/btbtms@test01p create tt1 using select * from tt;

Array fetch/bind size is 200. (arraysize is 200)
Will commit when done. (copycommit is 0)
Maximum long size is 20000000. (long is 20000000)
Table TT1 created.

   3 rows selected from DEFAULT HOST connection.
   3 rows inserted into TT1.
   3 rows committed into TT1 at scott@test01p.

--对比看看.

SCOTT@test01p> select id,idx,dump(idx,16 ) c30  from tt;
        ID        IDX C30
---------- ---------- ------------------------------
         1     1.1111 Typ=2 Len=4: c1,2,c,c
         2     2.2222 Typ=2 Len=4: c1,3,17,17
         3     3.3333 Typ=2 Len=4: c1,4,22,22

SCOTT@test01p> select id,idx,dump(idx,16 ) c30  from tt1;
        ID        IDX C30
---------- ---------- ------------------------------
         1          1 Typ=2 Len=2: c1,2
         2          2 Typ=2 Len=2: c1,3
         3          3 Typ=2 Len=2: c1,4

--很明显idx拷贝后丢失了小数点后的信息,太可怕了,难道没人遇到这个问题吗?重复测试,idx定义后面的小数点

drop table tt purge;
drop table tt1 purge;
create table tt (id number,idx number(12,4));
insert into tt values (1,1.1111);
insert into tt values (2,2.2222);
insert into tt values (3,3.3333);
commit ;
copy to scott/btbtms@test01p create tt1 using select * from tt;

--对比看看.
SCOTT@test01p> select id,idx,dump(idx,16 ) c30  from tt;
        ID        IDX C30
---------- ---------- ------------------------------
         1     1.1111 Typ=2 Len=4: c1,2,c,c
         2     2.2222 Typ=2 Len=4: c1,3,17,17
         3     3.3333 Typ=2 Len=4: c1,4,22,22

SCOTT@test01p> select id,idx,dump(idx,16 ) c30  from tt1;
        ID        IDX C30
---------- ---------- ------------------------------
         1     1.1111 Typ=2 Len=4: c1,2,c,c
         2     2.2222 Typ=2 Len=4: c1,3,17,17
         3     3.3333 Typ=2 Len=4: c1,4,22,22

--这样结果才正确,看看表的数据结构.

SCOTT@test01p> @desc tt
Name                    Null?    Type
----------------------- -------- ----------------
ID                               NUMBER
IDX                              NUMBER(12,4)

SCOTT@test01p> @desc tt1
Name                    Null?    Type
----------------------- -------- ----------------
ID                               NUMBER(38)
IDX                              NUMBER(12,4)

--可以发现字段id 类型从number变成了NUMBER(38).继续测试.

insert into tt1 values (4.4444,4.4444);
insert into tt1 values (5.5555,5.5555);
commit ;

SCOTT@test01p> select id,idx,dump(idx,16 ) c30  from tt1;
        ID        IDX C30
---------- ---------- ------------------------------
         1     1.1111 Typ=2 Len=4: c1,2,c,c
         2     2.2222 Typ=2 Len=4: c1,3,17,17
         3     3.3333 Typ=2 Len=4: c1,4,22,22
         4     4.4444 Typ=2 Len=4: c1,5,2d,2d
         6     5.5555 Typ=2 Len=4: c1,6,38,38

--可以发现插入的id数据丢失了小数点信息.
--正是因为copy后类型从number变成了number(38),导致小数点后面的位数丢失.而且像
--5.5555四舍五入,导致变成了6.这个算oracle的debug吗?

--我们还好,开发定义的数据类型都是包括小数点的,遇到的仅仅是几个字段,定义小数点后再copy问题消失.

--实际上一般讲数据定义是数字类型定义时***包括精度和小数点位数,除非全部是整形不会出现小数点.
--还是拿tt表来说明.

如果插入:
insert into tt values (1/3,4.4444);
commit ;

SCOTT@test01p> column id format 99999999999999999999.999999999999999999999999999999999999999999
SCOTT@test01p> column id format 99999999999999999999.9999999999999999999999999999999999999999999
SP2-0246: Illegal FORMAT string "99999999999999999999.9999999999999999999999999999999999999999999"
SCOTT@test01p> select id,idx,dump(id,16) c60,dump(idx,16 ) c30  from tt;
                                           ID        IDX C60                                                          C30
--------------------------------------------- ---------- ------------------------------------------------------------ ------------------------------
1.000000000000000000000000000000000000000000     1.1111 Typ=2 Len=2: c1,2                                            Typ=2 Len=4: c1,2,c,c
2.000000000000000000000000000000000000000000     2.2222 Typ=2 Len=2: c1,3                                            Typ=2 Len=4: c1,3,17,17
3.000000000000000000000000000000000000000000     3.3333 Typ=2 Len=2: c1,4                                            Typ=2 Len=4: c1,4,22,22
  .333333333333333333333333333333333333333300     4.4444 Typ=2 Len=21: c0,22,22,22,22,22,22,22,22,22,22,22,22,22,22,2 Typ=2 Len=4: c1,5,2d,2d
                                                                            2,22,22,22,22,22

id=1/3,这种运算得到的结果长度占用21个字节,浪费存储空间.

总结:
1.使用copy时遇到定义number不包括小数点类型的要注意,copy后信息丢失的问题.
2.定义number***包括精度和小数点位数,特别是生产系统,避免出现1/3这样的信息占用不必要的磁盘空间.
3.从某种意义讲我认为这个是bug.