sys使用者不支援flashback table特性！

講課時隨手記錄的flashback table大致過程[@more@]

SQL> connect / as sysdba
Connected.
SQL> desc tt
Name Null? Type
----------------------------------------- -------- ----------------------------

ID NUMBER(38)

SQL> select * from tt;

no rows selected

SQL> set time on
14:14:36 SQL> insert into tt values(1);

1 row created.

14:14:42 SQL> insert into tt values(2);

1 row created.

14:14:44 SQL> insert into tt values(3);

1 row created.

14:14:46 SQL> commit;

Commit complete.
14:15:10 SQL> update tt set id=100;

3 rows updated.

14:15:14 SQL> commit;

Commit complete.
14:16:14 SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

Session altered.

14:16:29 SQL> select * from tt as of timestamp to_date('2009/05/31 14:14:47');

no rows selected

14:16:31 SQL> select sysdate from dual;

SYSDATE
-------------------
2009/05/31 14:17:10

14:17:10 SQL> select *from tt;

ID
----------
100
100
100

14:17:17 SQL> select * from tt as of timestamp to_date('2009/05/31 14:15:47');

ID
----------
100
100
100

14:17:30 SQL> select * from tt as of timestamp to_date('2009/05/31 14:14:47');

no rows selected
14:17:51 SQL> select * from tt as of timestamp to_date('2009/05/31 14:14:53');

ID
----------
1
2
3
14:19:05 SQL> select id ,ora_rowscn from tt;

ID ORA_ROWSCN
---------- ----------
100 1738940
100 1738940
100 1738940

14:20:07 SQL> select id ,ora_rowscn,rowid from tt;

ID ORA_ROWSCN ROWID
---------- ---------- ------------------
100 1738940 AAACiwAAEAAAAAMAAA
100 1738940 AAACiwAAEAAAAAMAAB
100 1738940 AAACiwAAEAAAAAMAAC

14:20:28 SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_b
lock_number(rowid) bno from tt;

FNO BNO
---------- ----------
4 12
4 12
4 12
14:21:20 SQL> alter system dump datafile 4 block 12;

System altered.
--=================================
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.0000007b 0x00800dbe.002e.1c C--- 0 scn 0x0000.001a88b1
0x02 0x0006.02a.00000079 0x00801396.0029.14 --U- 3 fsc 0x0000.001a88bc
--=================================
14:22:12 SQL> select to_number('001a88bc','xxxxxxxx') from dual;

TO_NUMBER('001A88BC','XXXXXXXX')
--------------------------------
1738940
14:25:17 SQL> create table tt1(id int) ROWDEPENDENCIES ;

Table created.

14:25:52 SQL> insert into tt1 values(1);

1 row created.

14:26:00 SQL> insert into tt1 values(2);

1 row created.

14:26:02 SQL> insert into tt1 values(3);

1 row created.

14:26:04 SQL> commit;

Commit complete.

14:26:05 SQL> select * from tt1;

ID
----------
1
2
3
14:26:14 SQL> select ora_rowscn,id from tt1;

ORA_ROWSCN ID
---------- ----------
1739181 1
1739181 2
1739181 3

14:26:19 SQL> insert into tt1 values(4);

1 row created.

14:26:26 SQL> commit;

Commit complete.

14:26:28 SQL> select ora_rowscn,id from tt1;

ORA_ROWSCN ID
---------- ----------
1739181 1
1739181 2
1739181 3
1739190 4

14:26:30 SQL> select * from tt;

ID
----------
100
100
100

14:27:14 SQL> select id,ora_rowscn from tt;

ID ORA_ROWSCN
---------- ----------
100 1738940
100 1738940
100 1738940

14:27:21 SQL> insert into tt values(1);

1 row created.

14:27:33 SQL> commit;

Commit complete.

14:27:34 SQL> select id,ora_rowscn from tt;

ID ORA_ROWSCN
---------- ----------
100 1739213
100 1739213
100 1739213
1 1739213

14:27:36 SQL> insert into tt1 values(5);

1 row created.

14:28:02 SQL> commit;

Commit complete.

14:28:03 SQL> select ora_rowscn,id from tt1;

ORA_ROWSCN ID
---------- ----------
1739181 1
1739181 2
1739181 3
1739190 4
1739224 5

14:28:07 SQL> flashback table tt to timestamp to_date('2009/05/31 14:14:53');
flashback table tt to timestamp to_date('2009/05/31 14:14:53')
*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS

14:30:45 SQL> show user
USER is "SYS"
14:30:56 SQL>