Oracle DBMS_FLASHBACK package

Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:

Using DBMS_FLASHBACK package
  • Released with Oracle 9i
  • Provides the same functionality as Oracle Flashback Query (Oracle 10g)
  • Acts as a time machine:
    • 1. set time to a previous point in time.
    • 2. perform queries without the "AS OF" and "VERSIONS BETWEEN" clauses
    • 3 return time to the "present"

  • You need EXECUTE privilege on DBMS_FLASHBACK package
  • The UNDO_RETENTION parameter defines how far back in time an object can be flashbacked.
  • UNDO_RETENTION can be set in the init.ora or with:

Using the DBMS_FLASHBACK package:
  2. Perform regular queries (queries without flashback-feature syntax such as AS OF).
    Do not perform DDL or DML operations.
  3. Return to the present by invoking DBMS_FLASHBACK.DISABLE.
(a) Check current data in the emp table

SQL> select * from emp;

 EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900      

SQL> select current_scn, to_char(systimestamp, 'MM-DD-YY HH24:MI:SS') date_time
     from v$database;
CURRENT_SCN            DATE_TIME         
---------------------- ----------------- 
1432709                12-08-10 10:58:45  

(b) modify data in emp table

SQL> Insert into emp values (3, 'Mark', 5000);
SQL> commit;

SQL> Update emp set salary = 0 where empno=1;
SQL> commit;

SQL> Delete from emp where empno=2;
SQL> update emp set empname = 'Lenny' where empno=100;
SQL> commit;

SQL> select * from emp;

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
1                      Jack       0                      
100                    Lenny      900                    
3                      Mark       5000   

SQL> select current_scn, to_char(systimestamp, 'MM-DD-YY HH24:MI:SS') date_time
     from v$database;
CURRENT_SCN            DATE_TIME         
---------------------- ----------------- 
1432739                12-08-10 10:59:30 

To view and restore the original data, you can
(1) create a cursor to hold the original data
(2) go back in time with dbms_flashback.enable_at_system_change_number(n);
(3) Open and fetch the data in the cursor.
(4) return to the present with dbms_flashback.enable;
(5) Insert the data in the cursor in the restore table
(c) Create a table to restore original data

SQL> create table emp_restore
     as select * from emp where 1 = 0;

set serveroutput on
  cursor c_emp is 
     select * from emp;
  v_row c_emp%rowtype;
  dbms_output.put_line('enabling dbms_flashback..');
  Open c_emp;
  dbms_output.put_line('disabling dbms_flashback..');  
    fetch c_emp into v_row;
    exit when c_emp%notfound;
    insert into emp_restore values
  end loop;
  close c_emp;
enabling dbms_flashback..
disabling dbms_flashback..

SQL> select * from emp_restore; 

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900 

Oracle data storage: ROWIDs

About Rowids
  • ROWIDs uniquely identifiy a row in the database. (although there are some exceptions).
  • ROWID may refer to a datatype and a pseudocolumn. In its different forms it is a representation of the physical location of a row within the database.
  • The ROWID for a row can change as the result of dumping and reloading the database.
  • For this reason ROWID values should not be used across transaction lines.
  • You cannot set the value of a ROWID pseudocolumn.
  • ROWID is not physically stored in the database.It is inferred from the file and block address of the data.

ROWIDs are used in the construction of indexes. In addition to this,
  • Rowids are the fastest means of accessing particular rows.
  • Rowids provide the ability to see how a table is organized.
  • Rowids are unique identifiers for rows in a given table.

Types of ROWIDs: Physical Rowid, Logical Rowid and Foreign Rowid

Physical ROWIDs:
  • Store the addresses of rows in heap-organized tables, clustered tables, and table and index partitions.
  • Provide the fastest access to a row.
  • Contain the physical address of a row and allow its retrieval in a single block access.
  • Rowid corresponds to the physical address of a row's row piece (or the initial row piece if the row is chained in multiple row pieces).
  • In case of clustered tables, rows in different tables that are in the same data block can have the same rowid.
  • Physical rowids have two formats: restricted and extended.

  • In a logical rowid, a physical guess identifies the block in which a row was stored at the time the guess is made, and the database uses the guess to search the block directly.
  • However, as new rows are inserted, guesses can become stale.
  • Guess staleness can be monitored with DBMS_STATS (index statistics capture the percentage of rows with valid guesses).
  • Statistic stored in the DBA_INDEXES (PCT_DIRECT_ACCESS column).
  • To refresh guess information:
    • Rebuild secondary indexes (resource intensive).

Logical ROWIDs:
  • Strore the addresses of rows in index-organized tables (IOTs).
  • Used in index-organized tables (IOT), these are based on the table's primary key.
  • Logical rowids change when the IOT's primary key is updated.
  • This is because table data is stored in the index leaves and thus do not have a permanent physical address.
  • Indexes that use logical rowids include a physical guess, which identifies the block location of the row in the IOT at the time the guess was made.
  • Since logical rowids do not indicate the exact physical location of a row, they cannot be used to see how a table is organized.

Restricted x Extended ROWIDs
Restricted ROWIDs:
  • Provided for backward compatibility with applications developed with Oracle 7 and earlier
  • Until Oracle 7, ROWIDs used 8 bytes
  • The old format is known as Restricted rowid format.
  • Restricted ROWID format stores (block number, Row number, file number)
  • Restricted Rowid (8 bytes)
  • Block# (bytes 1-4).Row#(bytes 5-6).File#(bytes 7-8)

Extended ROWIDs
  • Starting with Oracle 8 Rowid's format changed.
  • Extended rowid: four-piece format.
  • Uses a base 64 encodig (A-Z,a-z,0-9,+,/)
  • It is now 10 bytes long and includes the ID of the Object to which the row belongs.
  • Until Oracle 7 file numbers were unique within a database.
  • With the new format, ROWIDs stores the relative file number (the file number within the tablespace in which the object is stored).
  • Until Oracle 7, the 2-byte representation of file numbers limited the maximum number of files a database could have to 65533.
  • This limit now is for the number of files each tablespace can have.
  • An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row.

  • AAAC9E: The data object number identifies the segment. Note:
    • A data object number is assigned to every database segment (table, index, etc).
    • HOWEVER, Schema objects in the same segment (such as a table cluster) have the SAME Object ID.
  • AAE: The data file number: Tablespace-relative. Identifies the file that contains the row.
  • AAAABX: The data block number Identifies the block that contains the row. Relative to the datafile.
  • AAA: The row number: identifies the row in the block.
  • ObjID(bytes 1-4) . RelativeFN(byte 5 + 4bits) . Block#(4bits + byte 7) . Row#(bytes 9-10)

ROWID Pseudocolumn
  • Every Oracle table has a pseudocolumn named ROWID. Its value, however, is not actually stored in the table.
  • You can select from pseudocolumns, but you cannot insert, update, or delete their values.
  • Values of the ROWID pseudocolumn are strings representing ,in hexadecimal format, the address of each row.
  • You can parse the value returned from rowid to understand the physical storage of rows in the database.

(a) Query the extended rowid
SQL> select rowid, last_name from hr.employees where employee_id=100;

ROWID               LAST_NAME
------------------  -----------

(b) To see the old rowid format (restricted rowid), you can use the DBMS_ROWID package:
SQL> select dbms_rowid.rowid_to_restricted(rowid,1) old_rowid
  2  from hr.employees
  3* where employee_id = 100


Restriced rowid format:
000000CF: data block that contains the row.
  • Block numbers are relative to the datafile, not tablespace.
0000: row numbers start with 0. 0005: data file that contains the row.

  • With the restricted format, to find out the segment (table, index) a rowid belonged to it was necessary to compare ROWID's block and file numbers with entries in the DBA_EXTENTS view.
  • With extended format, you can use DBMS_ROWID as below:
SQL> select first_name, dbms_rowid.rowid_object(rowid) "ObjectID",
  2                     dbms_rowid.rowid_relative_fno(rowid) "File#",
  3                     dbms_rowid.rowid_block_number(rowid) "Block#",
  4                     dbms_rowid.rowid_row_number(rowid)   "Row#"
  5  from hr.employees
  6  where employee_id = 100;

FIRST_NAME        ObjectID      File#     Block#     Row#
-------------------- ---------- ---------- ---------- ----------
Steven     73301   5   207        0

Now query DBA_OBJECTS to find out what database object the row belongs to

SQL> select owner, object_name, object_type, data_object_id, object_id
  2  from dba_objects
  3* where data_object_id =  73301;

----------- --------------- -------------- --------------- ----------
HR          EMPLOYEES       TABLE          73301           73933

When does a ROWID change?
  • If row movement is enabled: ROWID can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.
  • If row movement is disabled: ROWID can change if the row is exported and imported using Oracle Database utilities

Can you use ROWIDs as a table's primary key?
  • No. Oracle won't let you create a PK constraint on a rowid column.
  • In addition, besides not being always unique (rows in clustered tables may share a rowid), a row's rowid can change (i.e., after and export/import or partition movement operation).

Can I store ROWIDs in a table column?
  • Yes. You can create table column using the ROWID data type.
  • However, ROWID values should not be used across transaction lines.
  • Storing a rowid with the intent of using it latter (i.e. as an audit trail record), may lead to errors and incorrect information, since rowids may change as the result of:
    • Export/import operation
    • Partition operation (split, combination)
    • Data update that results in row being moved to another partition