oracle ORA-30036: 无法在撤消表空间“UNDOTBS”中将段扩展 8
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38615071/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'
提问by Andrew
I am running cron job which have following PL/SQL block:
我正在运行具有以下 PL/SQL 块的 cron 作业：
declare
begin
--insert into DB_LOGS_TEMP table from DB_LOGS table
INSERT INTO DB_LOGS_TEMP SELECT * FROM DB_LOGS WHERE DB_LOG_ID NOT IN(SELECT DB_LOG_ID from DB_LOGS_TEMP );
--keep the lat 10 records and delete other records
DELETE DB_LOGS where rowid in (
select rid from (
select t.rowid rid,
row_number() over(partition by T.DB_LOG_ID order by T.TIMESTAMP desc) as rn
from DB_LOGS t)
where rn > 10);
end;
The DB_LOGS table has 10247302 rows. When the cron job run it throws an error as ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'. Does increasing the tablespce is the only solution for this issue and how to do that? The UNDOTBS has 524288000 bytes.
DB_LOGS 表有 10247302 行。当 cron 作业运行时，它会抛出一个错误为ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'. 增加 tablespce 是否是此问题的唯一解决方案以及如何做到这一点？UNDOTBS 有 524288000 个字节。
回答by Andrew
It works for me while increasing the tablespace and making autoextend on.
它在增加表空间并进行自动扩展的同时对我有用。
ALTER DATABASE DATAFILE '/vg01lv11/oracle//data/undotbs_d1_O2P00R11.dbf' AUTOEXTEND ON MAXSIZE 10g;
ALTER DATABASE DATAFILE '/vg01lv11/oracle//data/undotbs_d1_O2P00R11.dbf'
RESIZE 1000M;
回答by vercelli
If you can afford deleting in different transactions:
如果您能负担得起在不同交易中删除的费用：
DECLARE
i PLS_INTEGER;
BEGIN
--insert into DB_LOGS_TEMP table from DB_LOGS table
INSERT INTO DB_LOGS_TEMP
SELECT *
FROM DB_LOGS
WHERE DB_LOG_ID NOT IN
(SELECT DB_LOG_ID FROM DB_LOGS_TEMP
);
COMMIT;
i:=50;
--keep the lat 10 records and delete other records
WHILE i>=10
LOOP
DELETE DB_LOGS
WHERE rowid IN
(SELECT rid
FROM
(SELECT t.rowid rid,
row_number() over(partition BY T.DB_LOG_ID order by T.TIMESTAMP DESC) AS rn
FROM DB_LOGS t
)
WHERE rn > i
);
COMMIT;
i:=i-5;
END LOOP;
END;
