Link to home
Start Free TrialLog in
Avatar of ku916
ku916

asked on

update without undo

Is there any ways to execute an update to a table without Oracle using the UNDO. I get the following errors while trying to update a table. The Drive where the UNDO Tablespace is located has 40MB of space left.

ORA-30036: unable to extend segment by 8192 in undo tablespace
O/S-Error: (OS 112) There is not enough space on the disk
ORA-27059: skgfrsz: could not reduce file size
Avatar of sudhar
sudhar

ku916,

Oracle provides this UNDO facility for read consistency.  You cannot execute queries without generating undo.
Wait for the active transactions to commit, then the extents can be reused.

Move the undo tablespace to a different disk with sufficient space.

Thanks,
Sudhar
Avatar of ku916

ASKER

How do I know the disk space I'll need to increase? is there any to find out so I can create or re-size the undo tablepsace?
Hi ku916,
Read consistency is achieved using the UNDO mechanism, but queries do not generate undo, they simply read the undo block, pointed by the data block changed.
Only DML (insert, update, delete) generate undo.

cheers,
dbaora.
ASKER CERTIFIED SOLUTION
Avatar of dbaora
dbaora

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ku916

ASKER

So if I'm trying to update a table that has 25 million records, it would try to store 25 million update stmt? I need to know the size I need to increase my UNDO datafile?
WHy not commit after every 10000 rows updated? You'll need as much redo as the size of the table.

select sum(bytes) from dba_extents where segment_name = '<TABLE NAME>';