Error: ORA 1594

To see how much undo information has been written in the past hour.


select begin_time, end_time, undoblks, txncount, maxconcurrency as maxcon,nospaceerrcnt from V$UNDOSTAT where begin_time > sysdate-(4/24);



BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT MAXCON NOSPACEERRCNT

-------------- -------------- ---------- ---------- ---------- -------------

01-04-30 14:07 01-04-30 14:14 1883 7 3 0

01-04-30 13:57 01-04-30 14:07 488 34 3 0

01-04-30 13:47 01-04-30 13:57 0 2 1 0

01-04-30 13:37 01-04-30 13:47 0 2 1 0

01-04-30 13:27 01-04-30 13:37 0 2 1 0

01-04-30 13:17 01-04-30 13:27 0 2 1 0



This query shows how much redo has been used over the last hour. With the last 20 minutes being the only time that has used undo space. Here we can see that there have been 34 transactions from 13:57 – 14:07 using 488 undo blocks and none of them received any errors due to space management. And from 14:07 until present there are 7 transactions in total using 1883 undo blocks and so far no space problems.



If space problems occur so a user receives a segment out of space error then the column NOSPACEERRCNT will increase. If this happens then the undo tablespace is now to small and will need to be increased via enlarging a datafile or adding another data file to the tablespace.





To find the time of the longest query in the instance:



select begin_time, MAXQUERYLEN, undoblks from V$UNDOSTAT where begin_time > sysdate-(4/24);



BEGIN_TIME MAXQUERYLEN UNDOBLKS

-------------- ----------- ----------

01-04-30 15:27 15 482

01-04-30 15:17 14 1895

01-04-30 15:07 0 1

01-04-30 14:57 0 0

01-04-30 14:47 0 0

01-04-30 14:37 0 1

01-04-30 14:27 738 3426 <--- 738 seconds

01-04-30 14:17 377 2564

01-04-30 14:07 236 2277

01-04-30 13:57 21 488

01-04-30 13:47 0 0

01-04-30 13:37 0 0



This shows that the longest query that has been run was 738 seconds long. To ensure this query never gets ORA-1555 set undo_retention to at least 750 seconds. If undo_retention is set to high the UNDO tablespace will need to be very large. If to low ORA-1555 may occur to often.


SQL> select begin_time, end_time, undoblks, txncount, maxconcurrency as maxcon,nospaceerrcnt from V$UNDOSTAT where begin_time > sysdate-(4/24);



BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT MAXCON NOSPACEERRCNT

--------- --------- ---------- ---------- ---------- -------------

08-AUG-08 08-AUG-08 0 0 0 0

08-AUG-08 08-AUG-08 1 269 1 0

08-AUG-08 08-AUG-08 0 0 0 0

08-AUG-08 08-AUG-08 0 239 0 0

08-AUG-08 08-AUG-08 0 226 0 0

08-AUG-08 08-AUG-08 0 161 0 0

08-AUG-08 08-AUG-08 2 135 1 0

08-AUG-08 08-AUG-08 2 29 1 0

08-AUG-08 08-AUG-08 5 1 1 0

9 rows selected.