表的ROWDEPENDENCIES以及flashback db

时间：2010-11-08 来源：wmlm

在创建表时，可以使用ROWDEPENDENCIES 属性，例如
SQL> create table t(
2 mycol varchar2(20)
3 )
4 rowdependencies;

加这个属性的作用如下：
select ora_rowscn,mycol from t;
oracle9i 中虽然有这个属性，但没有ora_rowscn函数，仅仅在数据复制环境中提到了这点.

NOROWDEPENDENCIES, the default, specifies that the SCN is tracked at the data block level.
ROWDEPENDENCIES specifies that the SCN is tracked for each row in the table.

下面是参考psoug的演示流程，做一个闪回数据库的示例:

conn / as sysdba
SELECT flashback_on, log_mode
FROM v;
set linesize 121
col name format a30
col value format a30
SELECT name, value
FROM gv
WHERE name LIKE '%flashback%';
shutdown immediate;
startup mount exclusive;
alter database archivelog;
alter database flashback on;
alter database open;
SELECT flashback_on, log_mode
FROM v;
SELECT name, value
FROM gv
WHERE name LIKE '%flashback%';
-- 2 days
alter system set DB_FLASHBACK_RETENTION_TARGET=2880;
SELECT name, value
FROM gv
WHERE name LIKE '%flashback%';
SELECT estimated_flashback_size
FROM gv;

-- as sys

SQL> select
2 oldest_flashback_scn,
3 oldest_flashback_time
4 from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_
-------------------- -----------------
2042427 20101108 12:18:22

SQL> grant flashback any table to hr;

Grant succeeded.

-- as hr
SQL> create table t(
2 mycol varchar2(20)
3 )
4 rowdependencies;

Table created.

SQL> insert into t values ('abc');

1 row created.

SQL> insert into t values ('def');

1 row created.

SQL> commit;

Commit complete.

SQL> create restore point bef_damage;

Restore point created.

SQL> insert into t values ('ghi');

1 row created.

SQL> commit;

Commit complete.
SQL> select ora_rowscn,mycol from t;

ORA_ROWSCN MYCOL
---------- --------------------
2045697 abc
2045697 def
2045715 ghi

SQL>
SQL>

-- as sys
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1219136 bytes
Variable Size 142607808 bytes
Database Buffers 163577856 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> flashback database to scn 2045697;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL>

-- as hr
[oracle@ww1 ~]$ sqlplus hr/hr

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 8 14:18:23 2010

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select ora_rowscn,mycol from t;

ORA_ROWSCN MYCOL
---------- --------------------
2045697 abc
2045697 def

SQL>

数据已恢复到SCN 2045697.