- 浏览: 959452 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
孤星119:
好熟悉的数据库字段啊, 上家公司做的项目每天都跟这些字段打招呼 ...
Oracle exp compress参数引起的空间浪费 -
itspace:
quxiaoyong 写道遇到个问题,网上一搜,全他妈这篇文章 ...
数据库连接错误ORA-28547 -
quxiaoyong:
遇到个问题,网上一搜,全他妈这篇文章。你转来转去的有意思吗?
数据库连接错误ORA-28547 -
hctech:
关于version count过高的问题,不知博主是否看过ey ...
某客户数据库性能诊断报告 -
itspace:
invalid 写道写的不错,我根据这个来安装,有点理解错误了 ...
AIX 配置vncserver
flashback table导致rowid变化
- 博客分类:
- ORACLE管理
首先看测试
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)
----------------------------------------
10999711206848
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.
可以看到rowid已经发生变化,进一步查看后台跟踪文件,发现flashback table其实是做了delete和insert操作。
********************************************************************************
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)
********************************************************************************
发表评论
-
buffer cache 的内部结构
2020-03-18 14:21 517BUFFER CACHE作为数据块的 ... -
Oracle OMC介绍
2020-03-18 13:19 439Oracle管理云服务(OMC)的大数据平台,自动收集的企业 ... -
参加Oracle勒索病毒防范专题培训会议
2019-09-27 17:15 4212019年7月22日,受邀参加Oracle勒索病毒防范专题培训 ... -
记一次内存换IO的Oracle优化
2019-09-27 16:50 784某客户数据库从P595物理 ... -
如何定位Oracle SQL执行计划变化的原因
2019-07-03 14:49 1353性能优化最难的是能够 ... -
如何定位Oracle SQL执行计划变化的原因
2018-10-30 09:24 1185性能优化最难的是能够 ... -
数据库性能优化目标
2018-10-08 10:59 465从数据库性能优化的场 ... -
数据库无法打开的原因及解决办法
2018-10-05 20:45 1906数据库的启动是一个相当复杂的过程。比如,Oracle在启动之前 ... -
怎么样彻底删除数据库?
2018-09-18 11:10 550Oracle提供了drop database命令用来删除数据库 ... -
Oracle减少日志量的方法
2018-09-10 10:17 807LGWR进程将LOG BUFFER中的 ... -
如何快速关闭数据库
2018-09-09 13:14 1195“一朝被蛇咬,十年怕井绳”。在没被“蛇”咬之前,很多DBA喜欢 ... -
关于《如何落地智能化运维》PPT
2018-05-17 10:19 1086在DTCC 2018发表《如何落地智能化运维》演讲,主要内容如 ... -
记录在redhat5.8平台安装oracle11.2容易忽视的几个问题
2018-05-11 19:58 529问题一:ping不通问题 在虚拟机上安装好linux系统后, ... -
《Oracle DBA实战攻略》第一章
2018-05-11 10:42 889即日起,不定期更新《OracleDBA实战攻略》一书电子版,请 ... -
Oracle 12c新特性
2018-05-11 10:33 850查询所有pdb [oracle@gj4 ~]$ sqlplu ... -
关于修改memory_target的值后数据库无法启动的问题
2017-02-28 12:24 3922操作系统:RHEL6.5 数据库版本:11.2.0.4 ... -
10g rac安装error while loading shared libraries libpthread.so.0 问题
2017-02-28 12:22 63211g rac安装在二节点跑脚本一般会报此错误: 解决这个问 ... -
记一次Oracle会话共享模式故障处理过程
2017-02-27 19:16 753故障简述 XXX第八人民医院HIS数据库7月13日11点左右从 ... -
RESMGR:cpu quantum等待事件处理过程
2017-02-27 18:23 2473由于数据库上线过程中出现大量的RESMGR:cpu quant ... -
谈谈log file sync
2014-03-19 14:18 1679数据库中的log file sync等待事件指的是,当user ...
相关推荐
全面总结和解释了oracle flashback技术的使用,优势以及限制
Flashback Query本身不会恢复任何操作或修改,也不能告诉你做过什么操作或修改,实际上Flashback Query特性实际应用时,是基于标准SELECT的扩展,借助该特性能够让用户查询到指定时间点的表中的记录,相当于拥有了...
FLASHBACK TABLE CF_IMPORT_RULE TO BEFORE DROP; FLASHBACK COMPLETE 您可能感兴趣的文章:Oracle误删除表数据后的数据恢复详解Oracle7.X 回滚表空间数据文件误删除处理方法Oracle7.X 回滚表空间数据文件误删除...
mysqlbinlog的flashback
Oracle 10g High Availability-Flashback技術介紹
ORACLE flashback database测试,主要进行ORACLE flashbackup 闪回整个数据库的实现。
BB FlashBack Pro2.7.6.zip
BB FlashBack Pro 3.2.2 注册机
flashback全记录,所有可能遇到的各种情况的flashback操作
录屏软件BB flashback.rar 录屏软件BB flashback.rar 录屏软件BB flashback.rar 录屏软件BB flashback.rar
FlashBack Pro 5 Recorder 是一种屏幕记录器,能快速容易地创建视频。有详尽的软件阐述、屏幕演示、介绍、指南以及练习。,欢迎您下载。
Flashback 技术是以Undo segment中的内容为基础的, 因此受限于UNDO_RETENTON参数。要使用flashback 的特性,必须启用自动撤销管理表空间。
BB.FlashBack.Pro 五星屏录演示制作(BB.FlashBack.Pro)v4.0.0.2375 注册名 Any 注册码 随意挑一个吧 2SX7I-M6ERP-9P6C3-64TLN-JEM1 2SX7I-MRH4A-3N6VA-34TLF-12NP 2SX7I-MV21L-7PMHK-84TLJ-722K 2SX7I-MEARI-QN6J3-...
BB FlashBack Pro 3.2.2截图工具 亲测可用
oracle数据库FLASHBACK系列功能介绍
FLASHBACK DATABASE flashback data1base闪回到过去的某一时刻 闪回点之后的所有工作都将丢失 必须使用resetlogs创建新的场景并打开数据库(一旦resetlogs之后,将不能再闪回至resetlogs之前的时间点) .......
Flashback_code-master.rar
flashback 截屏工具,可以对屏幕进行截图、录像 小巧好用
BB FlashBack Pro win8 屏幕 录像 包括破解补丁,仅供学习使用。
很好用的录屏软件BB FlashBack 4 汉化版,亲测可用,操作简单,录制文件大小和屏幕变化频率有关系,如果屏幕不是经常改变,一分钟大约1-2M的文件。很适合录制培训视频等。(内附注册码) BB FlashBack Professional ...