|
‘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
Introduction
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-
Recommendation from UNDO Advisor
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.
The EM Database Control
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.
|
“Injustice anywhere
is a threat to justice everywhere.” Martin Luther King Jr. |
Questions:
Questions on
UNDO Advisor
in the Oracle 10g database
Q: What is the UNDO
advisor?
Q: How do you check the
time and problem on the UNDO tablespace?
|