iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 10g New Features

 

UNDO Advisor in the Oracle 10g database

 

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.

 

 

Google
 
Web web site