课程第21次

botang · 发表于 2019-6-29 08:58:42

SYS：

select * from dba_tablespaces;
select * from dba_data_files;
create tablespace tbs1 datafile size 20M autoextend off ;
create flashback archive fda1 tablespace tbs1 quota 20M retention 30 day;
select * from dba_flashback_archive;
select * from dba_flashback_archive_tables;
select * from dba_flashback_archive_ts;
grant flashback archive on fda1 to hr;
select * from dba_tab_privs tp where tp.grantee='HR';

复制代码

HR:

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 29 08:54:48 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn hr/hr
Connected.
SQL> create table t05311_a ( a number ) ;
Table created.
SQL> insert into t05311_a values ( 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05311_a;
A
----------
1
SQL> alter table t05311_a flashback archive fda1;
Table altered.
SQL>

复制代码

select o.object_name , o.object_id
from dba_objects o
where o.owner='HR' and o.object_name='T05311_A';
--87432
select * from dba_flashback_archive_tables t where
t.table_name='T05311_A' and t.owner_name='HR';

复制代码

	TABLE_NAME	OWNER_NAME	FLASHBACK_ARCHIVE_NAME	ARCHIVE_TABLE_NAME	STATUS
1	T05311_A	HR	FDA1	SYS_FBA_HIST_87432	ENABLED

查看闪回数据归档，要做改动，要耐心等：
HR：

SQL> select * from hr.sys_fba_hist_87432;
select * from hr.sys_fba_hist_87432
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from t05311_a;
A
----------
1
SQL> update t05311_a set a=2 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from hr.sys_fba_hist_87432;
select * from hr.sys_fba_hist_87432
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from hr.sys_fba_hist_87432;
RID
--------------------------------------------------------------------------------
STARTSCN ENDSCN XID O A
---------- ---------- ---------------- - ----------
AAAVWIAAEAAAAInAAA
1286803 1

复制代码

关于ENDSCN的解释：
HR：

SQL> select * from t05311_a;
A
----------
2
SQL> select * from t05311_a as of scn 1286803 ;
A
----------
2
SQL> select * from t05311_a as of scn 1286802;
A
----------
1

复制代码

试一试删除形式的DDL:
HR:

SQL> create table t05311_b ( a number , b varchar2(20)) ;
Table created.
SQL> insert into t05311_b values (1 , 'A');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> alter table t05311_b flashback archive;
Table altered.
SQL> update t05311_b set a=2 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05311_b drop (b) ;
Table altered.
SQL> update t05311_b set a=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t05311_b as of scn 1287466;
A
----------
2
SQL> select versions_xid, versions_startscn , versions_operation, a
2 from t05311_b
3 versions between scn minvalue and maxvalue;
VERSIONS_XID VERSIONS_STARTSCN V A
---------------- ----------------- - ----------
1200120055000000 1287350 U 2
1
1287467 U 2
0C001C0050000000 1287538 U 3
SQL> ed
Wrote file afiedt.buf
1 select versions_xid, versions_startscn , versions_operation, a
2 from t05311_b
3 versions between scn minvalue and maxvalue
4* order by versions_startscn
SQL> /
VERSIONS_XID VERSIONS_STARTSCN V A
---------------- ----------------- - ----------
1200120055000000 1287350 U 2
1287467 U 2
0C001C0050000000 1287538 U 3
1
SQL>

复制代码

SYS:

select * from hr.SYS_FBA_HIST_87439;

复制代码

	RID	STARTSCN	ENDSCN	XID	OPERATION	A	D_1287355_B
1	AAAVWPAAEAAAAIvAAA	1287350	1287467	1200120055000000	U	2	A
2	AAAVWPAAEAAAAIvAAA		1287350			1	A

-----------------------------------------------------------------------------------------------------------------------------
试一试FDA满空间出错：
（死锁是不会出现在首页告警的，快照太旧和不可扩展表空间的空间满是会出现在首页告警上的）：

SQL> begin
2 for i in 5398..10000
3 loop
4 update t05311_c set a='A'||to_char(i);
5 commit;
6 end loop;
7 end;
8 /
begin
*
ERROR at line 1:
ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on
"T05311_C" is suspended
ORA-06512: at line 4

复制代码

alter flashback archive fda1 modify tablespace tbs1 quota 1G ;
select * from hr.SYS_FBA_HIST_87447;

复制代码

有结果了。

做复杂DDL操作：

select * from dba_tab_partitions tp
where tp.table_owner='HR' and tp.table_name='T05311_E';
begin
dbms_flashback_archive.disassociate_fba('HR','T05311_E');
end;
select * from dba_flashback_archive_tables;
select * from hr.SYS_FBA_HIST_88721;
insert into hr.SYS_FBA_HIST_88721（A) values ( 6) ;
alter table hr.t05311_e split partition p1 at ( 5) into ( partition p11, partition p12 ) ;
begin
dbms_flashback_archive.reassociate_fba('HR','T05311_E');
end;
select * from dba_tab_partitions tp
where tp.table_owner='HR' and tp.table_name='T05311_E';
select versions_xid , a from hr.t05311_e
versions between scn minvalue and maxvalue ;

复制代码