Completed the Configuring Linux for the Installation of Oracle Database 10g lesson

Completed the Installing the Oracle Database 10g on Linux lesson

Download and unzip undoadv.zip into your working directory (i.e. /home/oracle/wkdir)

Open your browser and enter the following URL:

http://localhost:5500/em

Enter sys/<password> and specify SYSDBA then click Login.

Click on the Administration tab.

Click on the Tablespaces link.

Click Create.

Enter UT1 for the name, click on the Type Undo and click Add to add a datafile.

Enter ut1.dbf for the filename and 400KB for the file size and click Continue.

Click OK.

Your undo tablespace was created. Now you can enable undo retention tuning. Click your Database breadcrumb.

Click on Undo Management.

Click Change Tablespace.

Select the radio button next to UT1 and click OK.

The undo tablespace has been changed to the one you just created. Now you are ready to see how it works.

You will first need to determine the date and time of your database. Open a terminal window and enter the following commands:

cd wkdir
sqlplus /nolog
connect / as sysdba;
@datetime

The datetime.sql script looks as follows:

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') 
 from dual;

From the same session, execute the following command:

@newtable

The newtable.sql script looks as follows:

create table newtable(b int, s varchar2(100));
begin
 for i in 1..100 loop
  insert into newtable values(i, rpad('s', 100));
 end loop;
 commit;
end;
/

From the first session, execute the following:

@uscript1

The uscript1.sql script looks as follows:

declare
b number;
cursor c1 is select b from newtable;
begin
 open c1;
 loop
  fetch c1 into b;
  dbms_lock.sleep(6);
  exit when c1%notfound;
 end loop;
 close c1;
 commit;
end;
/

Open another terminal window and execute the following commands:

cd wkdir
sqlplus /nolog
connect / as sysdba
@uscript2

The uscript2.sql script looks as follows:

begin
 for i in 1..1000 loop
  update newtable set b=b+1, s=rpad('t', 100);
  commit;
 end loop;
end;
/

Switch back to the first SQL*Plus session. Soon, you get a "Snapshot too old" error. Although you are using Automatic Undo Retention tuning, your undo tablespace is too small to accommodate the update script.

Use the date and time you determined previously for the Period Starts field. For the Period Ends field, use the same value plus two hours. Once done, press the OK button.

You want perform the recommendation and raise the size of your undo tablespace. Click Edit Undo Tablespace.

Click Add to add a datafile.

Enter ut2.dbf as the filename and specify whatever the recommendation should be. Then click OK.

Click Apply.

Select your Database breadcrumb.

Select Undo Management.

You need to change the time period again. Select the Customized Time Period for the Analysis Time Period and click Update Analysis.

Change the date and time to the same as you did in step 2. Then click OK.

Notice that there are no problems or recommendations any longer. You may try running the uscript1 and uscript2 again to make sure the size of your tablespace is appropriate.