关于闪回事务查询的限制疑惑,请高人指教

今天看到书上说如果利用flashback_transaction_query来完成集群表或者具有行链接的表闪回操作需要启用补充日志(alter database add supplemental log data),但做实验并不需要启用(只做了集群表实验).不知实验是否哪儿做错了,请高人指点？

SQL> show user
USER is "SCOTT"
SQL> create cluster emp_dept(dept no number(3));
create cluster emp_dept(dept no number(3))
*
ERROR at line 1:
ORA-02335: invalid datatype for cluster column

SQL> desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> create cluster emp_dept(deptno number(2));

Cluster created.

SQL> create table dept1(deptno number(2) primary key,dname varchar2(20))
2 cluster emp_dept(deptno);

Table created.

SQL> create table emp1(empno number(3) primary key,ename varchar2(20),deptno number(2) references dept) cluster emp_dept(deptno);

Table created.

SQL> insert into dept1 values(1,'sales');
insert into dept1 values(1,'sales')
*
ERROR at line 1:
ORA-02032: clustered tables cannot be used before the cluster index is built

SQL> create index idx_emp_dept on cluster emp_dept;

Index created.

SQL> insert into dept1 values(1,'sales');

1 row created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
533345

SQL> commit;

Commit complete.

SQL> insert into dept1 values(2,'sales2');

1 row created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
533364

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> select xid,table_name,undo_sql from flashback_transaction_query where table_name='DEPT1';

XID
----------------
TABLE_NAME
--------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
0200000003010000
DEPT1
delete from "SCOTT"."DEPT1" where ROWID = 'AAAM0SAAEAAAAGOAAA';

05002E0019010000
DEPT1
delete from "SCOTT"."DEPT1" where ROWID = 'AAAM0SAAEAAAAGNAAA';

XID
----------------
TABLE_NAME
--------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------

08002B00FF000000
DEPT1
delete from "SCOTT"."DEPT1" where ROWID = 'AAAM0SAAEAAAAGNAAA';

0A000900CE000000
DEPT1

XID
----------------
TABLE_NAME
--------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
delete from "SCOTT"."DEPT1" where ROWID = 'AAAM0SAAEAAAAGOAAA';

SQL> select * from dept1 where rowid in('AAAM0SAAEAAAAGOAAA','AAAM0SAAEAAAAGNAAA','AAAM0SAAEAAAAGNAAA','AAAM0SAAEAAAAGOAAA');
select * from dept1 where rowid in('AAAM0SAAEAAAAGOAAA','AAAM0SAAEAAAAGNAAA','AAAM0SAAEAAAAGNAAA','AAAM0SAAEAAAAGOAAA')
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> l
1* select * from dept1 where rowid in('AAAM0SAAEAAAAGOAAA','AAAM0SAAEAAAAGNAAA','AAAM0SAAEAAAAGNAAA','AAAM0SAAEAAAAGOAAA')
SQL> c/dept1/scott.dept1
1* select * from scott.dept1 where rowid in('AAAM0SAAEAAAAGOAAA','AAAM0SAAEAAAAGNAAA','AAAM0SAAEAAAAGNAAA','AAAM0SAAEAAAAGOAAA')
SQL> /

DEPTNO DNAME
---------- --------------------
1 sales
2 sales2

SQL> show user
USER is "SYS"
SQL> desc v$database
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(10)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(21)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> select xid,table_name,undo_sql from flashback_transaction_query where table_name='DEPT1';

XID
----------------
TABLE_NAME
--------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
0200000003010000
DEPT1
delete from "SCOTT"."DEPT1" where ROWID = 'AAAM0SAAEAAAAGOAAA';

05002E0019010000
DEPT1
delete from "SCOTT"."DEPT1" where ROWID = 'AAAM0SAAEAAAAGNAAA';

XID
----------------
TABLE_NAME
--------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------

08002B00FF000000
DEPT1
delete from "SCOTT"."DEPT1" where ROWID = 'AAAM0SAAEAAAAGNAAA';

0A000900CE000000
DEPT1

XID
----------------
TABLE_NAME
--------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
delete from "SCOTT"."DEPT1" where ROWID = 'AAAM0SAAEAAAAGOAAA';

SQL> l
1* select xid,table_name,undo_sql from flashback_transaction_query where table_name='DEPT1'
SQL> c/xid/xid,operation
1* select xid,operation,table_name,undo_sql from flashback_transaction_query where table_name='DEPT1'
SQL> c/,undo_sql/
1* select xid,operation,table_name from flashback_transaction_query where table_name='DEPT1'
SQL> /

XID OPERATION
---------------- --------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
0200000003010000 INSERT
DEPT1

05002E0019010000 INSERT
DEPT1

08002B00FF000000 INSERT
DEPT1

XID OPERATION
---------------- --------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
0A000900CE000000 INSERT
DEPT1

SQL> select * FROM EMP1;
select * FROM EMP1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from scott.emp1;

no rows selected

SQL> desc scott.emp1
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(3)
ENAME VARCHAR2(20)
DEPTNO NUMBER(2)

SQL> desc scott.dept1
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(20)

SQL> desc dba_tables
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
DROPPED VARCHAR2(3)

SQL> select table_name,cluster_name,cluster_owner from dba_tables where table_name in ('DEPT1','EMP1');

TABLE_NAME CLUSTER_NAME
------------------------------ ------------------------------
CLUSTER_OWNER
------------------------------
DEPT1 EMP_DEPT
SCOTT

EMP1 EMP_DEPT
SCOTT

SQL> spool off;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16298743/viewspace-1048262/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16298743/viewspace-1048262/