Oracle Database Administration/Undo Data

This lesson introduces Oracle database undo data.

Objectives and Skills

Objectives and skills for the undo data portion of Oracle Database Administration I certification include:

• Managing Undo Data
  • Overview of Undo
  • Transactions and undo data
  • Managing undo

Readings

1. Oracle: Database 2-Day DBA Managing Undo Data
2. Oracle: Database Administrator's Guide Managing Undo

Multimedia

1. YouTube: Managing Undo

Activities

1. Complete the tutorial Oracle: Managing Database Storage Structures.
2. View Undo storage.
   1. Use the following queries to view undo tablespaces and rollback segments:
      SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO';
SELECT * FROM V$PARAMETER WHERE NAME = 'undo_tablespace';
SELECT * FROM DBA_ROLLBACK_SEGS;
   2. Use the following queries to view undo statistics:
      SELECT * FROM V$ROLLSTAT;
SELECT * FROM V$UNDOSTAT;
3. View transactions.
   1. Use the following query to begin a transaction:
      INSERT INTO HR.REGIONS VALUES (5, 'Antarctica');
   2. Use the following query to view transaction information:
      SELECT * FROM V$TRANSACTION;
   3. Open a second connection to the database and use the following query to select HR REGIONS:
      SELECT * FROM HR.REGIONS;
   4. Use the following query in the first session to roll back the transaction:
      ROLLBACK;
   5. Use the following queries to verify the transaction was rolled back:
      SELECT * FROM V$TRANSACTION;
SELECT * FROM HR.REGIONS;
4. Use Flashback.
   1. Use the following query to view HR REGIONS as of an hour ago:
      SELECT * FROM HR.REGIONS AS OF TIMESTAMP (SYSTIMESTAMP - 1 / 24);
   2. Use Enterprise Manager Database Control / Server / Automatic Undo Management / Edit Undo Retention to set the undo retention to 3600 seconds (1 hour).
   3. Use the following query to configure undo retention to 86400 seconds (1 day):
      ALTER SYSTEM SET UNDO_RETENTION = 86400;
5. Create undo tablespaces.
   1. Use Enterprise Manager Database Control / Server / Tablespaces to create an undo tablespace named UNDOTBS2 with a datafile named UNDOTBS02.
   2. Use the following query to identify the location of existing datafiles:
      SELECT NAME FROM V$DATAFILE;
   3. Use the following query to create an undo tablespace:
      CREATE UNDO TABLESPACE UNDOTBS3
DATAFILE '<data file path>\UNDOTBS03.DBF'
SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
6. Manage undo tablespaces.
   1. Use Enterprise Manager Database Control / Server / Tablespaces to edit UNDOTBS2 and enable Undo Retention Guarantee.
   2. Use Enterprise Manager Database Control / Server / Automatic Undo Management to change the undo tablespace to UNDOTBS2.
   3. Use the following query to enable undo retention guarantee on UNDOTBS3:
      ALTER TABLESPACE UNDOTBS3 RETENTION GUARANTEE;
   4. Use the following query to change the undo tablespace to UNDOTBS3:
      ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS3';
7. Delete undo tablespaces.
   1. Use Enterprise Manager Database Control / Server Automatic Undo Management and Tablespaces or the following queries to change the undo tablespace to UNDOTBS1 and delete the tablespaces UNDOTBS2 and UNDOTBS3:
      ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS1';
DROP TABLESPACE UNDOTBS2;
DROP TABLESPACE UNDOTBS3;

References

← Storage

Oracle Database Administration

Data Concurrency →