ORACLE 10g單表最多是可以新增到1000個欄位的，但是資料量比較大時，使用procedure為表動態新增欄位，並對新新增的列執行相關操作，在新增到256個欄位時，該過程就停止了

create table customer_distcnty1 as select ideadata_id,customer_name from customer;--該表有11萬條資料，當選取2000條資料時，下面的過程可以順利完成

declare
cursor distcnty1_cursor is select * from distcnty1;
distcnty1_row distcnty1_cursor%rowtype;
distcnty_did distcnty1_row.did%type;
distcnty_name distcnty1_row.distcnty%type;
sqlstr1 varchar2(200);
sqlstr2 varchar2(200);
sqlstr3 varchar2(1000);
begin
for distcnty1_row in distcnty1_cursor loop
distcnty_did:=distcnty1_row.did;
distcnty_name:=distcnty1_row.distcnty;

    --新增新列
    sqlstr1:='alter table customer_distcnty1 add d'||distcnty_did||' varchar2(100)';
    dbms_output.put_line(sqlstr1);
    execute immediate sqlstr1;

    --對新新增的列進行更新
    sqlstr2:='update customer_distcnty1 set d'||distcnty_did||'=instr(customer_name,'''||distcnty_name||''')';
    dbms_output.put_line(sqlstr2);
    execute immediate sqlstr2;

    --對新新增的列進行更新
    sqlstr3:='update customer_distcnty1 set d'||distcnty_did||'=''0'' where d'||distcnty_did||'<>''0'' and substr(customer_name,instr(customer_name,'''||distcnty_name||''')+length('''||distcnty_name||'''),1)=''省'' and substr(customer_name,instr(customer_name,'''||distcnty_name||''')+length('''||distcnty_name||'''),1)=''市''';
    dbms_output.put_line(sqlstr3);
    execute immediate sqlstr3;
    commit;
end loop;
end;
/

嘗試手段：

1、kill掉該程式，手工為該表新增幾個欄位試試

orcl@ SYS> Select p.spid,a.serial#, c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id;

SPID SERIAL# OBJECT_NAME SESSION_ID ORACLE_USERNAME OS_USER_NAME
------------ ---------- ------------------------------ ---------- ------------------------------ ------------------------------
18876 13339 CUSTOMER_DISTCNTY1 528 LENOVO oracle

Elapsed: 00:00:00.01
orcl@ SYS> alter system kill session '528,13339';

System altered.

Elapsed: 00:00:05.00

再對錶做如下操作時，發現都沒有問題

alter table CUSTOMER_DISTCNTY1 add test1 varchar2(100);
alter table CUSTOMER_DISTCNTY1 add test2 varchar2(100);
alter table CUSTOMER_DISTCNTY1 add test3 varchar2(100);
alter table CUSTOMER_DISTCNTY1 add test4 varchar2(100);
alter table CUSTOMER_DISTCNTY1 add test5 varchar2(100);

可見並不是無法新增表列。

2、試試執行過程中的第二條sql語句，對新增上的最後一列進行更新，發現對此列的更新超級的慢，這樣就可以鎖定原因是對列的更新使得程式hung住了。

那麼這種現象又是怎麼回事呢？是什麼原因導致執行update語句時程式停住呢，無疑是資料庫在進行比較大的操作。What is this?

3、由於新增的欄位都設定為varchar2(100)，改成varchar2(3)試試如何。

結果無濟於事！

4、有可能是行的資料太大了，更新行時資料庫進行了行遷移。

可能又兩種解決辦法：1、減少表的列數2、使用非標準塊的表空間儲存該表

由於時間有限，在此就把大表拆成若干個小表來運算了，以後有時間可以嘗試非標準塊的表空間！

動態為10g資料庫的表新增欄位，到256個欄位，hung住了？

相關文章