Oracle Database Administration/Undo Data

This lesson introduces Oracle database undo data.

Objectives and SkillsEdit

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

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

ReadingsEdit

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

MultimediaEdit

  1. YouTube: Managing Undo

ActivitiesEdit

  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;

See AlsoEdit

ReferencesEdit