查看: 771|回复: 0

[Mysql数据库] Oracle12C闪回技术

发表于 2018-1-24 08:00:01

1.Oracle Flashback Query --闪回查询
ORACLE根据undo信息,利用undo数据,类似一致性读取方法,可以把表置于一个删除前的时间点(或SCN),从而将数据找回。
Flashback query(闪回查询)前提:
sys@CLONEPDB_PLUGPDB> show parameter undo

NAME TYPE VALUE


temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
其中undo_management = auto,设置自动管理
Undo_retention = n(秒),设置决定undo最多的保存时间,其值越大,就需要越多的undo表空间的支持。修改undo_retention的命令如下:
sys@CLONEPDB_PLUGPDB> alter system set undo_retention = 3600;

System altered.

Elapsed: 00:00:00.06
sys@CLONEPDB_PLUGPDB> show parameter undo

NAME TYPE VALUE


temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
获取数据删除前的一个时间点或scn,如下:
sys@CLONEPDB_PLUGPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

TIME SCN


2018-01-17 14:16:00 5409876

Elapsed: 00:00:00.11
sys@CLONEPDB_PLUGPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@CLONEPDB_PLUGPDB> select count(*) from emp;

COUNT(*)
  1. 12
复制代码

Elapsed: 00:00:00.03
scott@CLONEPDB_PLUGPDB> delete from emp;

12 rows deleted.

Elapsed: 00:00:00.04
scott@CLONEPDB_PLUGPDB> commit;

Commit complete.

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual
*
ERROR at line 1:
ORA-00904: DBMS_FLASHBACK: invalid identifier

Elapsed: 00:00:00.02
查询该时间点(或scn)的数据,如下:
scott@CLONEPDB_PLUGPDB> select count(*) from emp;

COUNT(*)
  1. 0
复制代码

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select from emp as of timestamp to_timestamp('2018-01-17 14:16:00', 'yyyy-mm-dd hh24:mi:ss');--或select from emp as of scn 5409876;

  1. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
复制代码

  1. 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
  2. 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
  3. 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
  4. 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
  5. 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
  6. 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
  7. 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
  8. 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
  9. 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
  10. 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
  11. 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
  12. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
复制代码

  1. 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
复制代码

12 rows selected.

Elapsed: 00:00:00.15
恢复
scott@CLONEPDB_PLUGPDB> insert into emp (select * from emp as of scn 5409876);--也可以用时间

12 rows created.

Elapsed: 00:00:00.04
scott@CLONEPDB_PLUGPDB> commit;

Commit complete.

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select count(*) from emp;

COUNT(*)
  1. 12
复制代码

局限:

| 不能Falshback到5天以前的数据。

| 闪回查询无法恢复到表结构改变之前,因为闪回查询使用的是当前的数据字典。

| 受到undo_retention参数的影响,对于undo_retention之前的数据,Flashback不保证能Flashback成功。

| 对drop,truncate等不记录回滚的操作,不能恢复。

| 普通用户使用dbms_flashback包,必须通过管理员授权。

2.Oracle Flashback Drop Table 闪回Drop掉表
scott@clonepdb_plugPDB> show recyclebin
scott@clonepdb_plugPDB> select * from test;

no rows selected

Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> drop table test;

Table dropped.

Elapsed: 00:00:00.06
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME


TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> create table test as select * from emp where rownum<2;

Table created.

Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)
  1. 1
复制代码

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> drop table test;

Table dropped.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME


TEST BIN$Kqeo3hZ/RA+w3PdIGKlt6Q==$0 TABLE 2018-01-18:15:27:11
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table test to before drop;

Flashback complete.

Elapsed: 00:00:00.06
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)
  1. 1
复制代码

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME


TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> drop table test;

Table dropped.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME


TEST BIN$WZphkGyLQjqqgTNlaFN6jA==$0 TABLE 2018-01-18:15:29:52
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table "BIN$WZphkGyLQjqqgTNlaFN6jA==$0" to before drop;

Flashback complete.

Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)
  1. 1
复制代码

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> drop table test;

Table dropped.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME


TEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18:15:30:43
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table "BIN$lHb2N8coS86p8/1o8xr29A==$0" to before drop;

Flashback complete.

Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME


TEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18:15:30:43
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)
  1. 0
复制代码

scott@clonepdb_plugPDB> flashback table "BIN$Avlh8rB/Q22J0WciRhx58g==$0" to before drop rename to test_new;

Flashback complete.

Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> show recyclebinscott@clonepdb_plug[PDB](10.8.5.204-12.2)>
br/>scott@clonepdb_plug[PDB](10.8.5.204-12.2)>
按RECYCLEBIN NAME可以任意恢复
可以重命名
sys表不能闪回
闪回后和drop回收站约束在但乱码(外键约束除外),索引不见了
没用undo,其他的都用了
Oracle12C闪回技术
3.Oracle Flashback Table 闪回表
scott@clonepdb_plugPDB> drop table test purge;

Table dropped.

Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> create table test as select empno,ename,sal from emp;

Table created.

Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> alter table test enable row movement;

Table altered.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

TIME SCN


2018-01-18 16:08:56 5535328

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> drop table test purge
2 ;

Table dropped.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> flashback table test to scn 5535328;
flashback table test to scn 5535328
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> create table test as select empno,ename,sal from emp;

Table created.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test enable row movement;

Table altered.

Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> select * from test;

  1. EMPNO ENAME SAL
复制代码

  1. 7369 SMITH 800
  2. 7499 ALLEN 1600
  3. 7521 WARD 1250
  4. 7566 JONES 2975
  5. 7654 MARTIN 1250
  6. 7698 BLAKE 2850
  7. 7782 CLARK 2450
  8. 7839 KING 5000
  9. 7844 TURNER 1500
  10. 7900 JAMES 950
  11. 7902 FORD 3000
  12. EMPNO ENAME SAL
复制代码

  1. 7934 MILLER 1300
复制代码

12 rows selected.

Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

TIME SCN


2018-01-18 16:13:45 5536324

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum<3;

2 rows deleted.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit
2 ;

Commit complete.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

TIME SCN


2018-01-18 16:14:57 5536483

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum<3;

2 rows deleted.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

TIME SCN


2018-01-18 16:15:14 5536518

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test;

8 rows deleted.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536518;

Flashback complete.

Elapsed: 00:00:00.55
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)
  1. 8
复制代码

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536324;

Flashback complete.

Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)
  1. 12
复制代码

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536483;

Flashback complete.

Elapsed: 00:00:00.10
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)
  1. 10
复制代码

Elapsed: 00:00:00.01

purge表不能闪回
闪回不分先后顺序
需要启动行移动alter table test enable row movement;
system表不能闪回
4.FLASHBACK ARCHIVE 闪回归档
sys@clonepdb_plugPDB> create tablespace fda datafile 'C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\fda01.dbf' size 5m;

Tablespace created.

Elapsed: 00:00:00.56
sys@clonepdb_plugPDB> select name from v$datafile;

NAME

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\USERS01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\FDA01.DBF

Elapsed: 00:00:00.04
sys@clonepdb_plugPDB> create flashback archive fla1 tablespace fda retention 3 year;

Flashback archive created.

Elapsed: 00:00:00.19

sys@clonepdb_plugPDB> grant flashback archive on fla1 to scott;

Grant succeeded.
scott@clonepdb_plugPDB> select * from test;

  1. EMPNO ENAME SAL
复制代码

  1. 7521 WARD 1250
  2. 7566 JONES 2975
  3. 7654 MARTIN 1250
  4. 7698 BLAKE 2850
  5. 7782 CLARK 2450
  6. 7839 KING 5000
  7. 7844 TURNER 1500
  8. 7900 JAMES 950
  9. 7902 FORD 3000
  10. 7934 MILLER 1300
复制代码

10 rows selected.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test flashback archive fla1;

Table altered.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test drop column SAL;

Table altered.

Elapsed: 00:00:06.97
scott@clonepdb_plugPDB> select * from test;

  1. EMPNO ENAME
复制代码

  1. 7521 WARD
  2. 7566 JONES
  3. 7654 MARTIN
  4. 7698 BLAKE
  5. 7782 CLARK
  6. 7839 KING
  7. 7844 TURNER
  8. 7900 JAMES
  9. 7902 FORD
  10. 7934 MILLER
复制代码

10 rows selected.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> desc test
Name Null? Type


EMPNO NUMBER(4)
ENAME VARCHAR2(10)

scott@clonepdb_plugPDB> truncate table test;

Table truncated.

Elapsed: 00:00:08.37
scott@clonepdb_plugPDB> drop table test;
drop table test
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> insert into test select empno,ename from emp;

12 rows created.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> commit
2 ;

Commit complete.

Elapsed: 00:00:00.23
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)
  1. 12
复制代码

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> select count(*) from test as of timestamp to_timestamp('2018-01-19 07:00:00','YYYY-MM-DD HH24:MI:SS');

COUNT(*)
  1. 0
复制代码

scott@clonepdb_plugPDB> delete from test;

12 rows deleted.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.10
scott@clonepdb_plugPDB> select count(*) from test as of timestamp to_timestamp('2018-01-19 09:40:00','YYYY-MM-DD HH24:MI:SS');

COUNT(*)
  1. 12
复制代码

scott@clonepdb_plugPDB> insert into test select empno,ename from test as of timestamp to_timestamp('2018-01-19 09:40:00','YYYY-MM-DD HH24:MI:SS');

12 rows created.

Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> update test set empno=7969 where empno=7369;
update test set empno=7969 where empno=7369
*
ERROR at line 1:
ORA-55617: Flashback Archive "FLA1" runs out of space and tracking on "TEST" is suspended
SYS扩空间
SQL> alter database datafile 'C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\fda01.dbf' autoextend on next 32M maxsize 2048M;

Database altered.
scott@clonepdb_plugPDB> update test set ename=7969 where empno=7369;

1 row updated.

Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.14
scott@clonepdb_plugPDB> update test set ENAME =(select ename from test as of timestamp to_timestamp('2018-01-19 10:40:00','YYYY-MM-DD HH24:MI:SS') where empno=7369) where empno=7369;

1 row updated.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> select * from test where empno=7369;

  1. EMPNO ENAME
复制代码

  1. 7369 SMITH
复制代码

Elapsed: 00:00:00.01

sys@newtestCDB> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID


CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 98.54 0 5 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0

8 rows selected.
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME TYPE VALUE


db_recovery_file_dest_size big integer 300M
sys@newtestCDB> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2G;

System altered.

Elapsed: 00:00:00.06
SQL> ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR; --更改保留时间

Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE
2 TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' day);
从闪回数据归档FLA1中清除了一天前的所有历史记录数据。通常会在保留时间到期后的第一天执行自动清除。也可以覆盖此设置以进行临时清除。
Flashback archive altered.
scott@clonepdb_plugPDB> col table_name format A10
scott@clonepdb_plugPDB> col owner_name format A10
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME STATUS


TEST SCOTT ENABLED

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> alter table test no flashback archive;
alter table test no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL> grant flashback archive administer to scott;

Grant succeeded.
scott@clonepdb_plugPDB> alter table test no flashback archive;

Table altered.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME STATUS


TEST SCOTT DISABLED
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

TIME SCN


2018-01-19 14:16:51 5757544

Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> truncate table test;

Table truncated.

Elapsed: 00:00:08.32
scott@clonepdb_plugPDB> select * from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh24:mi:ss');

  1. EMPNO ENAME
复制代码

  1. 7369 SMITH
  2. 7499 ALLEN
  3. 7521 WARD
  4. 7566 JONES
  5. 7654 MARTIN
  6. 7698 BLAKE
  7. 7782 CLARK
  8. 7839 KING
  9. 7844 TURNER
  10. 7900 JAMES
  11. 7902 FORD
  12. EMPNO ENAME
复制代码

  1. 7934 MILLER
复制代码

12 rows selected.

Elapsed: 00:00:00.12
scott@clonepdb_plugPDB> insert into table test (select from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh24:mi:ss'));
insert into table test (select
from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh24:mi:ss'))
*
ERROR at line 1:
ORA-00903: invalid table name

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> insert into test (select * from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh24:mi:ss'));

12 rows created.

Elapsed: 00:00:00.06
scott@clonepdb_plugPDB> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

TIME SCN


2018-01-19 14:23:58 5759385

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> alter table test rename to test_01;

Table altered.

Elapsed: 00:00:08.36
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME STATUS


TEST_01 SCOTT ENABLED

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> drop flashback archive fla1;

Flashback archive dropped.

Elapsed: 00:00:00.04
truncate 表 表能恢复
flashback archive administer //授予用户创建,修改或删除闪回回档 flashback archive //授予用户对表进行归档。
可以删除列,truncate 表,rename 表名 与11g R1不同
不能drop表
插入不能闪回,删除,更新可以

ORA-55617 直接resize不行 建议用autoextend on next 32M maxsize 2048M;

5.Oracle Flashback Version Query 闪回版本查询
Flashback Version Query引入了一些数据表“伪列”,可以提供对数据版本的操作和检索。
scott@CLONEPDB_PLUGPDB> create table test as select empno, ename, sal from emp where rownum<3;

Table created.

Elapsed: 00:00:00.17
scott@CLONEPDB_PLUGPDB> select * from test;

  1. EMPNO ENAME SAL
复制代码

  1. 7369 SMITH 800
  2. 7499 ALLEN 1600
复制代码

Elapsed: 00:00:00.07
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO


  1. 7369
  2. 7499
复制代码

Elapsed: 00:00:00.08
scott@CLONEPDB_PLUGPDB> update test set sal=200 where empno=7369;

1 row updated.

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO


  1. 7369
  2. 7499
复制代码

Elapsed: 00:00:00.03
scott@CLONEPDB_PLUGPDB> commit;

Commit complete.

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO


07001E000D070000 5415641 U 7369
5415641 7369
7499

Elapsed: 00:00:00.02
U表示数据修改后的版本数据。如果删除数据,如下操作:
scott@CLONEPDB_PLUGPDB> delete test where empno=7499;

1 row deleted.

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> commit;

Commit complete.

Elapsed: 00:00:00.00
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO


09000800EE060000 5415674 D 7499
07001E000D070000 5415641 U 7369
5415641 7369
5415674 7499
scott@clonepdb_plugPDB> delete test;

1 rows deleted.

scott@clonepdb_plugPDB> commit;

scott@clonepdb_plugPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO


01001C005D070000 5415969 D 7369
09000800EE060000 5415674 D 7499
07001E000D070000 5415641 5415969 U 7369
5415641 7369
5415674 7499
scott@clonepdb_plugPDB> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('07001E000D070000')
4 /
FROM flashback_transaction_query
*
ERROR at line 2:
ORA-01031: insufficient privileges
sys@clonepdb_plugPDB> grant select on flashback_transaction_query to scott;

Grant succeeded.

Elapsed: 00:00:00.04
sys@clonepdb_plugPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@clonepdb_plugPDB> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('07001E000D070000')
4 /
FROM flashback_transaction_query
*
ERROR at line 2:
ORA-01031: insufficient privileges
sys@clonepdb_plugPDB> grant execute on dbms_flashback to scott;

Grant succeeded.

Elapsed: 00:00:00.05
sys@clonepdb_plugPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@clonepdb_plugPDB> exec dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('01001C005D070000'));
BEGIN dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('01001C005D070000')); END;


ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 1
也失败
在cdb建C##scott用户
c##scott@newtestCDB> select count(
) from emp;

COUNT(*)
  1. 12
复制代码

Elapsed: 00:00:00.02
c##scott@newtestCDB> create table test as select empno, ename, sal from emp where rownum<3;

Table created.

Elapsed: 00:00:00.25
c##scott@newtestCDB> select * from test;

  1. EMPNO ENAME SAL
复制代码

  1. 7369 SMITH 800
  2. 7499 ALLEN 1600
复制代码

Elapsed: 00:00:00.05
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO


  1. 7369
  2. 7499
复制代码

Elapsed: 00:00:00.01
c##scott@newtestCDB> update test set sal=200 where empno=7369;

1 row updated.

Elapsed: 00:00:00.02
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO


  1. 7369
  2. 7499
复制代码

Elapsed: 00:00:00.01
c##scott@newtestCDB> commit;

Commit complete.

Elapsed: 00:00:00.01
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO


080010009B0E0000 5502537 U 7369
5502537 7369
7499
c##scott@newtestCDB> select * from test;

  1. EMPNO ENAME SAL
复制代码

  1. 7369 SMITH 200
  2. 7499 ALLEN 1600
复制代码

3.cccc



回复

使用道具 举报