Ora-01555
Hi All,
Please let me know the steps how we can tune undo_retention for ora-01555. I am listing stats below:
INST_ID BEGIN_TIME Stolen Reused Error Error Length TUNED_UNDORETENTION
---------- ---------------- ----------- ---------- ---------- ------------ ---------- -------------------
3 12/02/2011 09:22 9352 41374 5 6 49806 6005
3 12/02/2011 09:32 6619 35034 6 1 50413 6036
3 12/02/2011 09:42 7884 37289 4 4 51018 6094
3 12/02/2011 09:52 8542 38552 3 4 34399 6212
3 12/02/2011 10:02 9660 39705 2 3 27839 6185
3 12/02/2011 10:12 9064 37144 5 4 28140 6215
3 12/02/2011 10:22 8367 38316 1 2 28745 6287
3 12/02/2011 10:32 8685 38672 6 3 29351 6300
3 12/02/2011 10:42 8667 40706 2 5 29957 6315
3 12/02/2011 10:52 9734 40536 3 3 30562 6257
3 12/02/2011 11:02 9045 36636 3 6 31169 6233
3 12/02/2011 11:12 9617 39758 2 9 31774 6223
3 12/02/2011 11:22 7874 38278 6 5 32377 6202
3 12/02/2011 11:32 7634 35825 4 2 32981 6119
3 12/02/2011 11:42 7576 35509 2 2 33587 6007
3 12/02/2011 11:52 9665 41675 4 4 34192 5952
3 12/02/2011 12:02 10783 43405 2 7 34796 5941
3 12/02/2011 12:12 12371 43544 4 4 35402 5900
3 12/02/2011 12:22 21737 61278 6 13 36007 5751
3 12/02/2011 12:32 25378 63692 8 16 36613 5577
3 12/02/2011 12:42 22190 60706 6 12 37219 5461
# Unexpired # Expired ORA-1555 Out-Of-space Max Query
INST_ID BEGIN_TIME Stolen Reused Error Error Length TUNED_UNDORETENTION
---------- ---------------- ----------- ---------- ---------- ------------ ---------- -------------------
3 12/02/2011 12:52 21148 57513 4 6 37824 5270
3 12/02/2011 13:02 15745 49115 7 6 38431 5246
3 12/02/2011 13:12 11750 40563 3 4 39036 5324
3 12/02/2011 13:22 10865 38043 6 4 39643 5391
3 12/02/2011 13:32 11975 36886 4 4 40248 5553
3 12/02/2011 13:42 1360 3939 1 0 40553 5508
1728 rows selected.
SQL> select status,count(*) from dba_undo_extents group by status;
STATUS COUNT(*)
--------- ----------
EXPIRED 17964
UNEXPIRED 90296
ACTIVE 300003
Regards,
LS
Please let me know the steps how we can tune undo_retention for ora-01555. I am listing stats below:
INST_ID BEGIN_TIME Stolen Reused Error Error Length TUNED_UNDORETENTION
---------- ---------------- ----------- ---------- ---------- ------------ ---------- -------------------
3 12/02/2011 09:22 9352 41374 5 6 49806 6005
3 12/02/2011 09:32 6619 35034 6 1 50413 6036
3 12/02/2011 09:42 7884 37289 4 4 51018 6094
3 12/02/2011 09:52 8542 38552 3 4 34399 6212
3 12/02/2011 10:02 9660 39705 2 3 27839 6185
3 12/02/2011 10:12 9064 37144 5 4 28140 6215
3 12/02/2011 10:22 8367 38316 1 2 28745 6287
3 12/02/2011 10:32 8685 38672 6 3 29351 6300
3 12/02/2011 10:42 8667 40706 2 5 29957 6315
3 12/02/2011 10:52 9734 40536 3 3 30562 6257
3 12/02/2011 11:02 9045 36636 3 6 31169 6233
3 12/02/2011 11:12 9617 39758 2 9 31774 6223
3 12/02/2011 11:22 7874 38278 6 5 32377 6202
3 12/02/2011 11:32 7634 35825 4 2 32981 6119
3 12/02/2011 11:42 7576 35509 2 2 33587 6007
3 12/02/2011 11:52 9665 41675 4 4 34192 5952
3 12/02/2011 12:02 10783 43405 2 7 34796 5941
3 12/02/2011 12:12 12371 43544 4 4 35402 5900
3 12/02/2011 12:22 21737 61278 6 13 36007 5751
3 12/02/2011 12:32 25378 63692 8 16 36613 5577
3 12/02/2011 12:42 22190 60706 6 12 37219 5461
# Unexpired # Expired ORA-1555 Out-Of-space Max Query
INST_ID BEGIN_TIME Stolen Reused Error Error Length TUNED_UNDORETENTION
---------- ---------------- ----------- ---------- ---------- ------------ ---------- -------------------
3 12/02/2011 12:52 21148 57513 4 6 37824 5270
3 12/02/2011 13:02 15745 49115 7 6 38431 5246
3 12/02/2011 13:12 11750 40563 3 4 39036 5324
3 12/02/2011 13:22 10865 38043 6 4 39643 5391
3 12/02/2011 13:32 11975 36886 4 4 40248 5553
3 12/02/2011 13:42 1360 3939 1 0 40553 5508
1728 rows selected.
SQL> select status,count(*) from dba_undo_extents group by status;
STATUS COUNT(*)
--------- ----------
EXPIRED 17964
UNEXPIRED 90296
ACTIVE 300003
Regards,
LS
0