Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Distributed Oracle Data Warehouses Real-time table updates

CREATE SNAPSHOT unfilled_orders
REFRESH COMPLETE
START WITH TO_DATE ('DD-MON-YY HH23:MI:55)
NEXT SYSDATE + 7
AS
SELECT customer_name, customer_address, order_date
FROM customer@paris, order@london
SEE CODE DEPOT FOR FULL SCRIPT
order_complete_flag = "N";

Here, you can see that a snapshot will be taken on Monday at 11:55 PM, and, thereafter, every seven days. The end users will be told about the refreshing period, and the database software will automatically perform the extract. The Remote DBA could also perform the extract manually by issuing the following command:

EXECUTE unfilled_orders.refreash_all;

Now, let’s take a detailed look at how Oracle snapshots are used to replicate tables.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.