Oracle/Table : 表格內有資料時, 仍可把欄位類別由 varchar2 變更成 nvarchar2

一次在檢查user的資料庫時, 突然發現有個不尋常的地方, 如下圖:

持續有近兩個小時平均每秒有400KB的寫入資料, 實在是太誇張了, 後來查出是user下了一個變更表格欄位類別的指令, 後來又因為執行太久(rows 非常多), 又把 session 刪除 (rollback非常多的rows)

原因找到後, 突...然...想...到... 當初教材裡寫表格中沒有資料時方能變更欄位類別, 難道 n 系列的欄位類別不在此限嗎?

不囉嗦, 馬上做個 lab

mozark@TEST.US.ORACLE.COM> drop table t1;

Table dropped.

mozark@TEST.US.ORACLE.COM> create table t1 as select * from dba_objects;

Table created.

mozark@TEST.US.ORACLE.COM> desc t1;

 Name                    Null?    Type
 ----------------------- -------- ----------------
 OWNER                            VARCHAR2(40)
 OBJECT_NAME                      VARCHAR2(128)
 SUBOBJECT_NAME                   VARCHAR2(30)
 OBJECT_ID                        NUMBER
 DATA_OBJECT_ID                   NUMBER
 OBJECT_TYPE                      VARCHAR2(18)
 CREATED                          DATE
 LAST_DDL_TIME                    DATE
 TIMESTAMP                        VARCHAR2(19)
 STATUS                           VARCHAR2(7)
 TEMPORARY                        VARCHAR2(1)
 GENERATED                        VARCHAR2(1)
 SECONDARY                        VARCHAR2(1)

mozark@TEST.US.ORACLE.COM> select bytes from dba_segments where segment_name = 'T1' and owner = 'MOZARK';

     BYTES
----------
   4194304

-- 重新連線,將統計資料清空

mozark@TEST.US.ORACLE.COM> conn mozark
Enter password:
Connected.

mozark@TEST.US.ORACLE.COM> alter table t1 modify (object_name nvarchar2(130)) ;

Table altered.

mozark@TEST.US.ORACLE.COM> select a.*, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
 and b.name like '%redo%' ;

       SID STATISTIC#      VALUE NAME
---------- ---------- ---------- ------------------------------
        11         72          1 redo synch writes
        11         73          0 redo synch time
        11        114      36858 redo entries
        11        115   11074568 redo size
        11        116          0 redo buffer allocation retries
        11        117          0 redo wastage
        11        118          0 redo writer latching time
        11        119          0 redo writes
        11        120          0 redo blocks written
        11        121          0 redo write time
        11        122          0 redo log space requests
        11        123          0 redo log space wait time
        11        124          0 redo log switch interrupts
        11        125          0 redo ordering marks

14 rows selected.

經實驗證明, 高x潔, 啊, 不 , Oralce 果然是可以的, 而且產生的 redo 量也太可怕了, 總共也才 4MB 的表格, 可以產生 11MB 的 redo.

Oracle 我對你的佩服如滔滔井水,綿延不絕丫!