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

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