根据extend初始化数据，速度飞快，而且有中断保证！
适合一次完成初始化的情况。 当然也可以根据条件去做增量
[cc lang="sql"]create table MY_ROWID
(
ID NUMBER,
ROWID_MIN VARCHAR2(100),
ROWID_MAX VARCHAR2(100),
HAS_DEAL NUMBER
);
create table tmp_count
(
P_ID NUMBER,
UPDATE_SUM NUMBER,
GMT_MORDIFID DATE,
GMT_START DATE
)
tablespace TBS_FEEL_DAT;
delete from my_rowid;
insert into my_rowid(id,rowid_min,rowid_max,has_deal)
select rownum,
DBMS_ROWID.ROWID_CREATE(1,o.data_object_id,e.RELATIVE_FNO,e.BLOCK_ID,0),
DBMS_ROWID.ROWID_CREATE(1,o.data_object_id,e.RELATIVE_FNO,e.BLOCK_ID+e.BLOCKS-1,10000),
0
from dba_extents e,dba_objects o
where e.segment_name='ACUTION_TABLE'
and e.owner='FBADMIN'
AND o.object_name = 'ACUTION_TABLE'
AND o.owner='FBADMIN';
如果是分区表 加上该条件：AND o.SUBOBJECT_NAME=e.PARTITION_NAME;
commit;
delete from tmp_count;
insert into tmp_count(p_id) values(0);
insert into tmp_count(p_id) values(1);
insert into tmp_count(p_id) values(2);
insert into tmp_count(p_id) values(3);
commit;
[/cc]
--------------------
procedure for Action:
[cc lang="sql"]
create or replace procedure sp_update_fb_init(mod_n number) as
/*
create by xuanfeng
date: 2009-2-2
*/
n number := 0;
begin
--更新启动时间
update tmp_count
set gmt_start = sysdate, update_sum = 0
where p_id = mod_n;
commit;
for c in (select id, rowid_min, rowid_max
from my_rowid
where mod(id, 4) = mod_n
and has_deal = 0) loop
--更新评价表,给新字段赋初值。
for d in (select rowid rid
from ACUTION_TABLE
where rowid >= chartorowid(c.rowid_min)
and rowid <= chartorowid(c.rowid_max)
and parenttradeid is null) loop
update ACUTION_TABLE au
set au.parenttradeid = au.trade_id,
au.validfeedback = decode(au.feedback,
null,
0,
'默认好评!',
0,
1)
where au.rowid = d.rid;
n := n + sql%rowcount;
if mod(n, 100) = 0 then
--更新记数器
update tmp_count
set update_sum = n, gmt_mordifid = sysdate
where p_id = mod_n;
commit;
end if;
end loop;
--给已处理的extent打标志
update my_rowid set has_deal = 1 where id = c.id;
commit;
end loop;
--更新记数器
update tmp_count
set update_sum = n, gmt_mordifid = sysdate
where p_id = mod_n;
commit;
end sp_update_fb_init;
[/cc]
-------do_prc.sh-------------
#!/bin/ksh
date
#first u must set environment
export ORACLE_SID=feel
export ORACLE_HOME=/u01/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=american_america.zhs16gbk
$ORACLE_HOME/bin/sqlplus -S xxx/xxxx <
begin
sp_update_fb_init(0);
end;
/
exit
EOF
------
nohup /home/oracle/worksh/do_prc.sh > /tmp/abc.log 2>&1 &
--
注意关注undo表空间和系统负载
This work is licensed under a CC A-S 4.0 International License.