Objectives
After completing this lesson,you should be able to :
- Describe Flashback technology
- Perform Flashback Query
- Use Flashback Version Query
- Enable row movement on a table
- Perform Flashback Table operations
- Use Flashback Transaction Query
- Use Flashback Transaction
Preparing Your Database for Flashback
- Creating an undo tablespace
- Enabling Automatic Undo Management
- Specifying versus guaranteeing undo retention
Default database initialization parameters:
SQL> show parameter UNDO NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
如何估算undo tablespace的容量？
首先将undo tablespace设置成，自动管理方式；然后DBA根据业务周期进行观察，由此判断出undo tablespace实际尺寸的大小.
参考动态性能试图v$undostat
SQL> select to_char(begin_time,'yyyy-mm-dd hh24:mi:ss') begin_time,to_char(end_time,'yyyy-mm-dd hh24:mi:ss') end BEGIN_TIME END_TIME UNDOBLKS ------------------- ------------------- ---------- 2013-05-15 23:09:15 2013-05-15 23:19:15 2 2013-05-15 23:19:15 2013-05-15 23:29:15 48 2013-05-15 23:29:15 2013-05-15 23:39:15 0 2013-05-15 23:39:15 2013-05-15 23:49:15 0 2013-05-15 23:49:15 2013-05-15 23:59:15 1 2013-05-15 23:59:15 2013-05-16 00:09:15 57 2013-05-16 00:09:15 2013-05-16 00:19:15 2 2013-05-16 00:19:15 2013-05-16 00:29:15 0 2013-05-16 00:29:15 2013-05-16 00:39:15 1 2013-05-16 00:39:15 2013-05-16 00:49:15 2 2013-05-16 00:49:15 2013-05-16 00:59:15 0 BEGIN_TIME END_TIME UNDOBLKS ------------------- ------------------- ---------- 2013-05-16 00:59:15 2013-05-16 01:09:15 69 2013-05-16 01:09:15 2013-05-16 01:19:15 1 2013-05-16 01:19:15 2013-05-16 01:29:15 0 2013-05-16 01:29:15 2013-05-16 01:39:15 1 2013-05-16 01:39:15 2013-05-16 01:49:15 0 2013-05-16 01:49:15 2013-05-16 01:59:15 1 2013-05-16 01:59:15 2013-05-16 02:09:15 58 2013-05-16 02:09:15 2013-05-16 02:19:15 0 2013-05-16 02:19:15 2013-05-16 02:29:15 1 2013-05-16 02:29:15 2013-05-16 02:39:15 0 2013-05-16 02:39:15 2013-05-16 02:49:15 2 BEGIN_TIME END_TIME UNDOBLKS ------------------- ------------------- ---------- 2013-05-16 02:49:15 2013-05-16 02:59:15 1 2013-05-16 02:59:15 2013-05-16 03:09:15 82 2013-05-16 03:09:15 2013-05-16 03:19:15 1 2013-05-16 03:19:15 2013-05-16 03:29:15 1 2013-05-16 03:29:15 2013-05-16 03:39:15 0 2013-05-16 03:39:15 2013-05-16 03:49:15 0 2013-05-16 03:49:15 2013-05-16 03:59:15 0 2013-05-16 03:59:15 2013-05-16 04:09:15 72 2013-05-16 04:09:15 2013-05-16 04:19:15 1 2013-05-16 04:19:15 2013-05-16 04:29:15 1 2013-05-16 04:29:15 2013-05-16 04:39:15 1 BEGIN_TIME END_TIME UNDOBLKS ------------------- ------------------- ---------- 2013-05-16 04:39:15 2013-05-16 04:49:15 0 2013-05-16 04:49:15 2013-05-16 04:59:15 0 2013-05-16 04:59:15 2013-05-16 05:09:15 60 2013-05-16 05:09:15 2013-05-16 05:19:15 0 2013-05-16 05:19:15 2013-05-16 05:29:15 1 2013-05-16 05:29:15 2013-05-16 05:39:15 0 2013-05-16 05:39:15 2013-05-16 05:49:15 0 2013-05-16 05:49:15 2013-05-16 05:59:15 2 2013-05-16 05:59:15 2013-05-16 06:09:15 57 2013-05-16 06:09:15 2013-05-16 06:19:15 0 2013-05-16 06:19:15 2013-05-16 06:29:15 0 BEGIN_TIME END_TIME UNDOBLKS ------------------- ------------------- ---------- 2013-05-16 06:29:15 2013-05-16 06:39:15 1 2013-05-16 06:39:15 2013-05-16 06:49:15 0 2013-05-16 06:49:15 2013-05-16 06:59:15 1 2013-05-16 06:59:15 2013-05-16 07:09:15 68 2013-05-16 07:09:15 2013-05-16 07:19:15 0 2013-05-16 07:19:15 2013-05-16 07:29:15 1 2013-05-16 07:29:15 2013-05-16 07:39:15 1 2013-05-16 07:39:15 2013-05-16 07:49:15 0 2013-05-16 07:49:15 2013-05-16 07:59:15 1 2013-05-16 07:59:15 2013-05-16 08:09:15 155 2013-05-16 08:09:15 2013-05-16 08:19:15 1626 BEGIN_TIME END_TIME UNDOBLKS ------------------- ------------------- ---------- 2013-05-16 08:19:15 2013-05-16 08:29:15 2 2013-05-16 08:29:15 2013-05-16 08:39:15 0 2013-05-16 08:39:15 2013-05-16 08:49:15 0 2013-05-16 08:49:15 2013-05-16 08:59:15 0 2013-05-16 08:59:15 2013-05-16 09:09:15 68 2013-05-16 09:09:15 2013-05-16 09:19:15 0 2013-05-16 09:19:15 2013-05-16 09:29:15 1 2013-05-16 09:29:15 2013-05-16 09:39:15 0 2013-05-16 09:39:15 2013-05-16 09:49:15 1 2013-05-16 09:49:15 2013-05-16 09:59:15 1 2013-05-16 09:59:15 2013-05-16 10:09:15 82 BEGIN_TIME END_TIME UNDOBLKS ------------------- ------------------- ---------- 2013-05-16 10:09:15 2013-05-16 10:19:15 0 2013-05-16 10:19:15 2013-05-16 10:29:15 1 2013-05-16 10:29:15 2013-05-16 10:39:15 1 2013-05-16 10:39:15 2013-05-16 10:49:15 1 2013-05-16 10:49:15 2013-05-16 10:59:15 0 2013-05-16 10:59:15 2013-05-16 11:09:15 52 2013-05-16 11:09:15 2013-05-16 11:19:15 5 2013-05-16 11:19:15 2013-05-16 11:29:15 2 2013-05-16 11:29:15 2013-05-16 11:39:15 3 2013-05-16 11:39:15 2013-05-16 11:49:15 1 2013-05-16 11:49:15 2013-05-16 11:59:15 0 BEGIN_TIME END_TIME UNDOBLKS ------------------- ------------------- ---------- 2013-05-16 11:59:15 2013-05-16 12:04:28 57 78 rows selected.