[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.
