|
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.
|
