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、使用非标准块的表空间存储该表
由于时间有限,在此就把大表拆成若干个小表来运算了,以后有时间可以尝试非标准块的表空间!