flashback_transaction_query的資料來源！

flashback_transaction_query的資料完全來自undo datafile![@more@]

flashback_transaction_query是一個檢視，定義如下

create or replace view sys.flashback_transaction_query as
select xid, start_scn, start_timestamp,
decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
commit_scn, commit_timestamp,
logon_user, undo_change#, operation, table_name, table_owner,
row_id, undo_sql
from sys.x$ktuqqry

sys.x$ktuqqry是一個動態效能表

--=======================================
SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup nomount
ORACLE 例程已經啟動。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 79693156 bytes
Database Buffers 83886080 bytes
Redo Buffers 2945024 bytes

SQL> select count(*) from x$ktuqqry;

COUNT(*)
----------
0
SQL> alter database mount;

資料庫已更改。

SQL> select count(*) from x$ktuqqry;

COUNT(*)
----------
0

SQL> alter database open;

資料庫已更改。

SQL> select count(*) from x$ktuqqry;

COUNT(*)
----------
294182

--db open之後x$ktuqqry有了資料，說明x$ktuqqry的資料肯定和datafile檔案有關，這就不能斷定x$ktuqqry肯定和undo的datafile有關了。

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create undo tablespace undotbs2 datafile 'E:ORACLEPRODUCT10.2.0ORADATA
TESTundotbs02.dbf' size 1m;

表空間已建立。

SQL> alter system set undo_tablespace=undotbs2;

系統已更改。

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> select count(*) from x$ktuqqry;

COUNT(*)
----------
294236

SQL> drop tablespace undotbs1 including contents and datafiles;

表空間已刪除。

SQL> select count(*) from x$ktuqqry;

COUNT(*)
----------
15

SQL> select count(*) from flashback_transaction_query;

COUNT(*)
----------
15

SQL>