需要帮助更新Oracle Table中的数百万条记录... [英] Need help to Update Millions of record in Oracle Table...

问题描述

oracle表'TBLUSERACTIVITIES'中有1600万条记录。如果我在查询下运行100条记录，则其更新记录成功。但如果运行整个表得到以下错误。



查询：

更新EAITEMP.TBLUSERACTIVITIES b

设置b.EventID1 =（选择REGEXP_REPLACE（REGEXP_SUBSTR（a.ACTION，'Event [0-9] +'），'Event'）作为EventId1

来自EAITEMP.TBLUSERACTIVITIES a其中a.Key = b.Key），

b.EventID2 =（选择REGEXP_REPLACE（REGEXP_SUBSTR（a.ACTION，'Event [0-9] +'，1,2，'i'），'Event'） as EventId2

来自EAITEMP.TBLUSERACTIVITIES a其中a.Key = b.Key）





错误报告：

SQL错误：ORA-30036：无法在撤消表空间'UNDO_DATA'中将段延长8

30036. 00000 - 撤消时无法按％s扩展段表空间'％s'

*原因：指定的撤消表空间没有更多可用空间。

*操作：在重试之前为撤消表空间添加更多空间
操作。另一种方法是等到活跃的

交易提交。



急需帮助

谢谢

There are 16 Million of Records in oracle table 'TBLUSERACTIVITIES'. If I am running below query for 100 records, its updating record successfully. but if running for whole table getting the below error.

Query :
update EAITEMP.TBLUSERACTIVITIES b
set b.EventID1 = (select REGEXP_REPLACE(REGEXP_SUBSTR(a.ACTION, 'Event [0-9]+'),'Event ') as EventId1
from EAITEMP.TBLUSERACTIVITIES a where a.Key =b.Key) ,
b.EventID2 = (select REGEXP_REPLACE(REGEXP_SUBSTR(a.ACTION, 'Event [0-9]+',1,2,'i'),'Event ') as EventId2
from EAITEMP.TBLUSERACTIVITIES a where a.Key =b.Key)


Error report:
SQL Error: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_DATA'
30036. 00000 - "unable to extend segment by %s in undo tablespace '%s'"
*Cause: the specified undo tablespace has no more space available.
*Action: Add more space to the undo tablespace before retrying
the operation. An alternative is to wait until active
transactions to commit.

Need urgent help
Thanks

推荐答案

是的，这是正确的。您的查询导致UNDO表空间大幅增加。



如果您真的必须在一个查询中执行此操作，那么您必须为此表空间分配更多空间。这意味着如果您还没有这种额外的存储空间，就可以在基础设施上花钱。我真的怀疑为什么你会需要它。



我建议你分解一下大约100000个更新批次，并在每个批次之后发出提交。



[如果适用于您并且鼓励参与，请将解决方案投票。]

Yes, this is correct. Your query is causing the UNDO tablespace to drastically size-up.

If you REALLY have to do it in one query then you HAVE to allocate more space for this tablespace. This means spending money on infrastructure if you do not already have such kind of additional storage. I really doubt why you would need that though.

I suggest you break down into, say, batches of about 100000 updates and issue a commit after each such batch.

[Please vote-up the solution if it works for you and encourage participation.]

这篇关于需要帮助更新Oracle Table中的数百万条记录...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！