11g A Brief History of
Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015
Oracle 11g New Features Tips
The first flashback functionalities came with
packages within the release of Oracle 9.0. Flashback
functionalities enabled the DBA to specify a consistency
point in time for the session. By using
dbms_flashback.enable_at_time(my_timestamp), a session
could be put into a state which read the consistent image of the
database as it was at a point in time in the past.
This was enhanced in version 9.2 when Oracle
introduced the FLASHBACK QUERY functionality. The flashback
query enabled users to query data as it appeared in the past, using
a normal SQL:
AS OF TIMESTAMP|SCN
Reading data by specifying a timestamp or a
system change number (SCN) was also common at the time.
Internally, every SELECT is a SELECT AS OF TIMESTAMP
SYSDATE. Only as of version 9.2. was the normal user able
to specify a timestamp to read the consistent data.
This concept was extended in Oracle's first 10g
release on a row level. Consequently, the FLASHBACK VERSIONS QUERY
functionality could be used to view all versions of a given row
between two timestamps and two SCNs.
Once the transactions had been identified, it
was also possible to look at the
undo_sql column of a view
The transaction's ID, using the pseudo column
also be found. Here, changes which were made by the same transaction
could be found, per the appropriate SELECT ANY TRANSACTION system
This gave the principal option to spool the
undo sql from the flashback_transaction_query view and
execute it to undo changes. However, this was not practicable
because of dependencies in transactions and applications which made
it difficult to undo things in the correct sequence. Luckily, this
has been addressed in Oracle 11g of which there will be a closer
look at the new functionality shortly.
All flashback functionalities discussed so far
have utilized information from the undo segments in order to
read the consistent image of data in the past. These types of
functionalities do not change anything in the database, but instead
read data as it was in the past. UNDO data is the logical
information needed to undo a change. This being, if the logical
information needed to undo a change is overwritten in between, an
error message appears explaining that it is not possible to
reconstruct the consistent data in the past. However, 10gR1
introduced a number of other extensively new functionalities under
the name FLASHBACK.
An example of the unique 10gR1 functionalities
is the FLASHBACK DROP. The flashback drop utilized the
so-called recycle bin which could be disabled with the
RECYCLEBIN parameter in Oracle 10gR2. The RECYCLEBIN had been
enabled by default in prior releases, and in 10gR1 this parameter
was the hidden parameter _recyclebin.. If a DBA
dropped a table in 10g, it would be internally renamed and the
segment would stay where it was. These extents were available for
reuse or review, in dba_free_space, but the server tries not
to use them as long as possible. Oracle starts reusing these extents
before auto extending a datafile. Once the server has reused the
extents it is not possible to flashback to before drop. This enabled
the user to flashback the drop, and simply a renames the object back
to its original name. Indexes and triggers are also flashed back.
Foreign key constraints are not flashed back with the table.
They must be manually recreated!
Since the object remained the same as before the drop operation, it
also became possible for the DBA to read from it using the new name.
The FLASHBACK QUERY also offered the ability to read the object as
it was in the past by using the new name and SELECT.
Another new feature in Oracle Database 10gR1 was
FLASHBACK TABLE. This gave the ability to put a table back into the
state it was in a previous point by applying undo data. FLAHSBACK
had previously only been possible in a tablespace with automatic
segment space management (ASSM) and enabled ROW MOVEMENT on the
table intended to use flashback. Until 10gR2, ASSM had been the
default for tablespaces. ALTER TABLE my_table ENABLE ROW
MOVEMENT allows the server to change the physical address of a row
exceptionally. The ROWID is normally assigned with insertion, and
valid for the life time of a row.
The DBA was limited in the inability to
flashback to before a DDL statements because if the definition of
the table changed in between the flashback operation, it would error