The Flashback Data Archive is stored in a tablespace and contains transactional changes to every record in a table for the duration of the record's lifetime. The archived data can be retained for a much longer duration than the retention period offered by an undo tablespace.
Using Flashback Data Archive to Access Historical Data you want to be able to retrieve the inventory of all items at the beginning of the year from the table inventory, and to be able to retrieve the stock price for each symbol in your portfolio at the close of business on any specified day of the year from the table stock_data.
Create a default Flashback Data Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data are retained for five years (you must be logged on as SYSDBA):
CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1
QUOTA 10G RETENTION 5 YEAR;
Enable Flashback Data Archive for the tables inventory and stock_data, and store the historical data in the default Flashback Data Archive:
ALTER TABLE inventory FLASHBACK ARCHIVE;
ALTER TABLE stock_data FLASHBACK ARCHIVE;
To retrieve the inventory of all items at the beginning of the year 2007, use this query:
SELECT product_number, product_name, count FROM inventory AS OF
TIMESTAMP TO_TIMESTAMP ('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
To retrieve the stock price for each symbol in your portfolio at the close of business on July 23, 2007, use this query:
SELECT symbol, stock_price FROM stock_data AS OF
TIMESTAMP TO_TIMESTAMP ('2007-07-23 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE symbol IN my_portfolio;
13 January 2010
Translate
Blog Archive
-
▼
2010
(38)
-
▼
January
(13)
- Hot Blocks
- Sun + Oracle
- ASM init.ora Parameters
- Below a link from the professionals about AJAX ADF...
- AJAX (not the soccor team) the technique
- Web 3.0 are we gifted enough?
- SOA link and talk about Web 3.0
- Very useful link to TechRepublic Consulting Tips.
- Total Recall with Flashback Archive
- Flashback from commandline
- Flashback Table in time
- How to use RNAM succesfully on the commandline
- I did receive an invitation to renew and upgrade m...
-
▼
January
(13)
Popular Posts
-
Database Resident Connection Pooling (DRCP) Oracle Database 11g (Technical White paper ) Introduction Web tier and mid-tier applica...
-
Earlier this month, Oracle announced the availability of the latest Exadata machine, the X8-2. As well as the option to swap in bi...
-
Oracle Fusion Middleware 11 g Forum The Foundation for Innovation The Oracle Fusion Middleware 11 g Forum is coming to a city near y...
Powered by Blogger.
0 reacties:
Post a Comment