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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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>';
select sum(bytes) from dba_extents where segment_name = '<TABLE NAME>';
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