lead to changes in flashback table rowid

2011-10-09  来源:本站原创  分类:Database  人气:61 

First look at the test

SQL> create table test_move as select * from dba_users;

Table created.

SQL> create user zhoul identified by zhoul;

User created.

SQL> grant dba to zhoul;

Grant succeeded.

SQL> conn zhoul / zhoul
Connected.
SQL> create table test_move as select * from dba_users;

Table created.

SQL> select count (*) from test_move;

COUNT (*)
----------
28

SQL> select username, rowid from test_move;

USERNAME ROWID
------------------------------ ------------------
SYSTEM AAAOkdAAEAAAAR8AAA
SYS AAAOkdAAEAAAAR8AAB
TEST AAAOkdAAEAAAAR8AAC
OEM AAAOkdAAEAAAAR8AAD
ZHOUL AAAOkdAAEAAAAR8AAE
ZZ AAAOkdAAEAAAAR8AAF
SCOTT AAAOkdAAEAAAAR8AAG
STRADMIN AAAOkdAAEAAAAR8AAH
ASSET AAAOkdAAEAAAAR8AAI
MGMT_VIEW AAAOkdAAEAAAAR8AAJ
OUTLN AAAOkdAAEAAAAR8AAK

USERNAME ROWID
------------------------------ ------------------
DBSNMP AAAOkdAAEAAAAR8AAL
OLAPSYS AAAOkdAAEAAAAR8AAM
SI_INFORMTN_SCHEMA AAAOkdAAEAAAAR8AAN
ORDPLUGINS AAAOkdAAEAAAAR8AAO
XDB AAAOkdAAEAAAAR8AAP
ANONYMOUS AAAOkdAAEAAAAR8AAQ
CTXSYS AAAOkdAAEAAAAR8AAR
WMSYS AAAOkdAAEAAAAR8AAS
DMSYS AAAOkdAAEAAAAR8AAT
EXFSYS AAAOkdAAEAAAAR8AAU
ORDSYS AAAOkdAAEAAAAR8AAV

USERNAME ROWID
------------------------------ ------------------
MDSYS AAAOkdAAEAAAAR8AAW
DIP AAAOkdAAEAAAAR8AAX
MDDATA AAAOkdAAEAAAAR8AAY
TSMSYS AAAOkdAAEAAAAR8AAZ
ORACLE_OCM AAAOkdAAEAAAAR8AAa
SYSMAN AAAOkdAAEAAAAR8AAb

28 rows selected.

SQL> select current_scn from v $ database;

CURRENT_SCN
-----------
1.1000E +13

SQL> select to_char (current_scn) from v $ database;

TO_CHAR (CURRENT_SCN)
----------------------------------------
10,999,711,206,848

SQL> delete from test_move where username = 'ZHOUL';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select username, rowid from test_move;

USERNAME ROWID
------------------------------ ------------------
SYSTEM AAAOkdAAEAAAAR8AAA
SYS AAAOkdAAEAAAAR8AAB
TEST AAAOkdAAEAAAAR8AAC
OEM AAAOkdAAEAAAAR8AAD
ZZ AAAOkdAAEAAAAR8AAF
SCOTT AAAOkdAAEAAAAR8AAG
STRADMIN AAAOkdAAEAAAAR8AAH
ASSET AAAOkdAAEAAAAR8AAI
MGMT_VIEW AAAOkdAAEAAAAR8AAJ
OUTLN AAAOkdAAEAAAAR8AAK
DBSNMP AAAOkdAAEAAAAR8AAL

USERNAME ROWID
------------------------------ ------------------
OLAPSYS AAAOkdAAEAAAAR8AAM
SI_INFORMTN_SCHEMA AAAOkdAAEAAAAR8AAN
ORDPLUGINS AAAOkdAAEAAAAR8AAO
XDB AAAOkdAAEAAAAR8AAP
ANONYMOUS AAAOkdAAEAAAAR8AAQ
CTXSYS AAAOkdAAEAAAAR8AAR
WMSYS AAAOkdAAEAAAAR8AAS
DMSYS AAAOkdAAEAAAAR8AAT
EXFSYS AAAOkdAAEAAAAR8AAU
ORDSYS AAAOkdAAEAAAAR8AAV
MDSYS AAAOkdAAEAAAAR8AAW

USERNAME ROWID
------------------------------ ------------------
DIP AAAOkdAAEAAAAR8AAX
MDDATA AAAOkdAAEAAAAR8AAY
TSMSYS AAAOkdAAEAAAAR8AAZ
ORACLE_OCM AAAOkdAAEAAAAR8AAa
SYSMAN AAAOkdAAEAAAAR8AAb

27 rows selected.

SQL> flashback table test_move to scn 10999711206848;
flashback table test_move to scn 10999711206848
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table test_move enable row movement;

Table altered.

SQL> alter session set sql_trace = true;

Session altered.

SQL> flashback table test_move to scn 10999711206848;

Flashback complete.

SQL> select username, rowid from test_move;

USERNAME ROWID
------------------------------ ------------------
SYSTEM AAAOkdAAEAAAAR8AAE
SYS AAAOkdAAEAAAAR8AAc
TEST AAAOkdAAEAAAAR8AAd
OEM AAAOkdAAEAAAAR8AAe
ZHOUL AAAOkdAAEAAAAR8AAf
ZZ AAAOkdAAEAAAAR8AAg
SCOTT AAAOkdAAEAAAAR8AAh
STRADMIN AAAOkdAAEAAAAR8AAi
ASSET AAAOkdAAEAAAAR8AAj
MGMT_VIEW AAAOkdAAEAAAAR8AAk
OUTLN AAAOkdAAEAAAAR8AAl

USERNAME ROWID
------------------------------ ------------------
DBSNMP AAAOkdAAEAAAAR8AAm
OLAPSYS AAAOkdAAEAAAAR8AAn
SI_INFORMTN_SCHEMA AAAOkdAAEAAAAR8AAo
ORDPLUGINS AAAOkdAAEAAAAR8AAp
XDB AAAOkdAAEAAAAR8AAq
ANONYMOUS AAAOkdAAEAAAAR8AAr
CTXSYS AAAOkdAAEAAAAR8AAs
WMSYS AAAOkdAAEAAAAR8AAt
DMSYS AAAOkdAAEAAAAR8AAu
EXFSYS AAAOkdAAEAAAAR8AAv
ORDSYS AAAOkdAAEAAAAR8AAw

USERNAME ROWID
------------------------------ ------------------
MDSYS AAAOkdAAEAAAAR8AAx
DIP AAAOkdAAEAAAAR8AAy
MDDATA AAAOkdAAEAAAAR8AAz
TSMSYS AAAOkdAAEAAAAR8AA0
ORACLE_OCM AAAOkdAAEAAAAR8AA1
SYSMAN AAAOkdAAEAAAAR8AA2

28 rows selected.

SQL> alter session set sql_trace = false;

Session altered.
You can see the rowid has changed, and further check the background trace file and found that flashback table is to do a delete and insert operations.

************************************************** ******************************

DELETE / * + BYPASS_UJVC * / FROM (SELECT / * + ORDERED USE_NL (S) PARALLEL (S,
DEFAULT) PARALLEL (T, DEFAULT) * / S.rowid FROM SYS_TEMP_FBT T,
"ZHOUL". "TEST_MOVE" S
WHERE
T.rid = S.rowid and T.action = 'D' and T.object # =: 1) V

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- - ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 5 34 28
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- --------- - ---------- ----------
total 2 0.00 0.00 0 5 34 28

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (recursive depth: 1)

Rows Row Source Operation
----- ------------------------------------------- --------
0 DELETE TEST_MOVE (cr = 5 pr = 0 pw = 0 time = 9221 us)
28 PX COORDINATOR (cr = 5 pr = 0 pw = 0 time = 8462 us)
0 PX SEND QC (RANDOM): TQ10000 (cr = 0 pr = 0 pw = 0 time = 0 us)
0 NESTED LOOPS (cr = 0 pr = 0 pw = 0 time = 0 us)
0 PX BLOCK ITERATOR (cr = 0 pr = 0 pw = 0 time = 0 us)
0 TABLE ACCESS FULL SYS_TEMP_FBT (cr = 0 pr = 0 pw = 0 time = 0 us)
0 TABLE ACCESS BY USER ROWID TEST_MOVE (cr = 0 pr = 0 pw = 0 time = 0 us)

************************************************** ******************************

INSERT / * + PARALLEL (S, DEFAULT) PARALLEL (T, DEFAULT) * / INTO
"ZHOUL". "TEST_MOVE" SELECT / * + USE_NL (S) ORDERED PARALLEL (S, DEFAULT)
PARALLEL (T, DEFAULT) * / S. * FROM SYS_TEMP_FBT T, "ZHOUL". "TEST_MOVE" as of
SCN: 1 S WHERE T.rid = S.rowid and T.action = 'I' and T.object # =: 2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- - ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 1.03 0 5 5 28
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- --------- - ---------- ----------
total 2 0.00 1.03 0 5 5 28

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67 (recursive depth: 1)

Rows Row Source Operation
----- ------------------------------------------- --------
28 PX COORDINATOR (cr = 3 pr = 0 pw = 0 time = 9972 us)
0 PX SEND QC (RANDOM): TQ10000 (cr = 0 pr = 0 pw = 0 time = 0 us)
0 NESTED LOOPS (cr = 0 pr = 0 pw = 0 time = 0 us)
0 PX BLOCK ITERATOR (cr = 0 pr = 0 pw = 0 time = 0 us)
0 TABLE ACCESS FULL SYS_TEMP_FBT (cr = 0 pr = 0 pw = 0 time = 0 us)
0 TABLE ACCESS BY USER ROWID TEST_MOVE (cr = 0 pr = 0 pw = 0 time = 0 us)

************************************************** ******************************

相关文章
  • lead to changes in flashback table rowid 2011-10-09

    First look at the test SQL> create table test_move as select * from dba_users; Table created. SQL> create user zhoul identified by zhoul; User created. SQL> grant dba to zhoul; Grant succeeded. SQL> conn zhoul / zhoul Connected. SQL> create

  • FlashBack Summary of Flashback Query and Flashback Table (original) 2011-03-23

    Foreword This paper describes the use of the UNDO tablespace flashback technology, including: Flashback Table, Flashback Versions Query, Flashback Transaction Query, Flashback Query. The Flashback technology to read the table from the rollback operat

  • Oracle 10g flashback table index name changes brought 2011-04-29

    When doing the test today, accidentally discovered unusual name index: Quote SQL> set autot traceonly exp stat SQL> select * from zhoultest where obj # = 1023; 56 rows selected. Execution Plan -------------------------------------------------- -----

  • oracle 10g in the Flashback Table feature 2010-10-26

    Contact oracle10g the beginning, some time on the table to delete the class action when there are many names found in the database hash table BIN $*******=$ 0, that is their due to operator error, and then I This is a deleted table in the recycle bin

  • Oracle PL/SQL之Flashback Table与外键约束 2012-03-09

    我们知道 Flashback Table可以把drop掉的表从回收站里恢复回来,但是并不是关于该表的所有东西都能被Flashback回来,比如外键约束. [email protected]>create table d(deptno number primary key, deptname varchar2(20)); Table created. Elapsed: 00:00:00.28 [email protected]>create table e(empno number primary ke

  • Oracle 闪回特性(Flashback Query.Flashback Table) 2012-03-09

    --================================================== -- Oracle 闪回特性(Flashback Query.Flashback Table) --================================================== Oracle 闪回查询是指针对特定的表来查询特定的时间段内的数据变化情况来确定是否将表闪回到某一个特定的时刻以保证数据无讹误存在. 这个特性大大的减少了采用时点恢复所需的工作量以及数据库脱机的

  • oralce flashback to use summary 2010-11-08

    oralce flashback to use Summary: A key knowledge points In oracle 10g, if the object is deleted using the drop to the table, and put this object in view on the Recycle Bin Recycle Bin: select * from recyclebin; Then restore the object: flashback tabl

  • flashback conditions required 2011-04-13

    flashback table requires the following conditions must be met 1 need to have flashback any table system privilege or the flashback of the table object permissions; (2) the need for this form of basic dml, alter operating authority; 3. Must ensure tha

  • oracle flashback drop off the table 2011-06-16

    SELECT * FROM user_recyclebin WHERE original_name = 'upper-case table name'; FLASHBACK TABLE uppercase table name TO BEFORE DROP;

  • Oracle Flashback Technology Summary 2010-08-08

    Flashback technology is Undo segment of the content-based, so limited UNDO_RETENTON parameters. To use the flashback feature, you must enable the automatic revocation of management of the table space. In Oracle 10g in, Flash back into the following f

  • oracle rowid of the relevant 2011-10-08

    The views expressed are from Thomas Kyte's "Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions" a book 1 ALTER TABLE EMP ENABLE ROW MOVEMENT allow oracle to change the allocation to each line of the rowid. Or fla

  • flashback data recovery 2010-05-29

    flashback table subscriberecord TO TIMESTAMP to_timestamp ('2010-05-07 16:00:00 ',' yyyy-mm-dd hh24: mi: ss'); / * Pop ORA-08189 error, need to execute the following command first: * / alter table subscriberecord enable row movement; / * The command

  • Oracle pl sql restore accidentally deleted using drop off the table 2010-07-07

    View Recycle Bin Table select object_name, original_name, partition_name, type, ts_name, createtime, droptime from recyclebin; Restore table SQL> flashback table test_drop to before drop; or SQL> flashback table "BIN $ b + XkkO1RS5K10uKo9BfmuA

  • oracle 10g flashback 2010-08-01

    flashback: the flash back is made from oracle9i to start an operation to restore function, carried out in oracle10g enhanced and modified, through flashbacks, the user can accomplish many can not be recovered, the current oracle10g of flashbacks, inc

  • plsql how to retrieve accidentally drop table. 2010-11-15

    Oracle pl sql restore accidentally deleted using drop off the table View Recycle Bin Table select object_name, original_name, partition_name, type, ts_name, createtime, droptime from recyclebin; Restore table SQL> flashback table test_drop to before

  • oracle 10G flashback learning 2010-12-23

    Project development, the front desk personnel are not accidentally delete the configuration table data, I use this method to delete data recovery too, follow these steps - First start the line moving alter table table_name enable row movement; - Poin

  • Oracle multi-version read excerpt 2 --- Data lock and Flashback 2010-09-15

    1 The following is a summary of Oracle locking strategies: Oracle only when the data plus the modified row-level locking. Under normal circumstances will not upgrade to a block-level locks or table-level lock (to mention but two Pay for a short perio

  • How to recover accidentally drop table plsql. 2010-11-15

    Oracle pl sql restore accidentally deleted using drop off the table View table Recycle Bin select object_name, original_name, partition_name, type, ts_name, createtime, droptime from recyclebin; Restore table SQL> flashback table test_drop to before

  • FlashBack summary of the Flashback Database and Flashback Drop (Original) 2011-03-24

    Flashback Database feature Flashback Database feature is very similar to the incomplete recovery with RMAN, it can roll back the entire database to a point past the state, this function depends on the Flashback log logs, and RMAN is more than fast an

  • To restore the deleted table structure [change] 2010-01-04

    1. Delete data in the table are two ways to a. delete * from My_Table; b. truncate table My_Table; 2. Delete the entire table drop table My_Table; How to recover accidentally Drop it off the table, in fact, Oracle also has a similar "Recycle Bin"