I am running cron job which have following PL/SQL block:
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 ?