|
UNDO
Advisor in the Oracle 10g database
|
More Resources by
Google: |
|
|
|
|
|
‘I
believe in the fundamental truth of all great religions of the world.
I believe that they are all God-given and I believe that they were
necessary for the people to whom these religions were revealed. And I
believe that if only we could all of us read the scriptures of the
different faiths from the standpoints of the followers of these faiths, we
should find that they were at bottom all one and were all helpful to one
another. ‘
Gandhi
|
UNDO
Advisor in the Oracle 10g database
You
can size your UNDO tablespace with the UNDO Advisor. The Snapshot Too Old error
will be noted in the database alert history. Assuming that the UNDO tablespace
is UNDOTBS1, to check the time and problem on the UNDO tablespace do the
following.
SQL>
SELECT time_suggested, reason
FROM
dba_alert_history
WHERE
object_name = ‘UNDOTBS1’
/
You
can use the Database Control home page to utilize the UNDO Advisor to get
recommendations to correct the problem. From the Database Control home page,
click on the Administration tab and then Undo Management. On that page change
the Analysis Time Period field to Last One Hour and click the Update Analysis
button. Now, you should see a recommendation from UNDO Advisor. You should be
able also to change the size and apply the changes.
Hands-On
#1:
Create
the SnapShot Too Old to tablespace and see the error message from the alert
history. First, create a table and then execute the following PL/SQL block until
you get the SnapShot Too Old message.
SQL>
CREATE TABLE myalert_table (c1 number, c2 CHAR2(2000));
SQL>
BEGIN
FOR
this IN 1...10000 LOOP
INSERT INTO myalert VALUES (this, ‘This is 2000 characters’);
END
LOOP;
END:
/
/
/ --
you repeat it until you get the SnapShot Too Old message. Make sure that you
tablespace size be bigger than your UNDO tablespace.
Then
check the time and problem on the UNDO tablespace do the following.
SQL>
SELECT time_suggested, reason
FROM
dba_alert_history
WHERE
object_name = ‘UNDOTBS1’
/
To
correct the problem: You can use the Database Control home page to utilize the
UNDO Advisor to get recommendations to correct the problem. From the Database
Control home page, click on the Administration tab and then Undo Management. On
that page change the Analysis Time Period field to Last One Hour and click the
Update Analysis button. Now, you should see a recommendation from UNDO Advisor.
You should be able also to change the size and apply the changes.
|