课程第26次（2016-05-25星期三）：闪回2

botang · 发表于 2016-5-26 11:21:14

【上完1Z0-053的第11章】：闪回2（闪回数据归档，Oracle Total Recall，全局方案演化，闪回删除表）
【1Z0-051】：共10章（0 1 2 3 4 5 6 7 8 9）
【1Z0-052】：共10章（0 1 2 3 4 5 6 9 10 14）
【1Z0-053】：共4章（0 1 10 11）

课堂命令行：

SQL> create table t05311_a ( a number , b varchar2(20)) ;
Table created.
SQL> insert into t05311_a values ( 1 , 'A' ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t05311_a flashback archive ;
Table altered.
SQL> update t05311_a set a=2 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05311_a set a=3 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05311_a drop ( b) ;
Table altered.
SQL> update t05311_a set a=4 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> select versions_startscn , a , b
2 from t05311_a
3 versions between scn minvalue and maxvalue ;
VERSIONS_STARTSCN A B
----------------- ---------- --------------------
1251787 1 A
1251810 2 A
1251824 3 A
1252094 4
1252027 3
SQL> alter table t05311_a enable row movement;
Table altered.
SQL> flashback table t05311_a to scn 1252027;
flashback table t05311_a to scn 1252027
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> flashback table t05311_a to scn 1252093;
Flashback complete.
SQL> select * from t05311_a;
A
----------
3
SQL> create table part1 ( a number )
2 partition by range (a )
3 (partition p1 values less than ( 10 ) ,
4 partition p2 values less than ( maxvalue ) );
Table created.
SQL> insert into part1 values ( 10 ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from part1 partition (p2) ;
A
----------
10
SQL> insert into part1 values ( -10 ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from part1 partition (p2) ;
A
----------
10
SQL> select * from part1 partition (p1) ;
A
----------
-10
SQL> select * from part1 ;
A
----------
-10
10
SQL> alter table part1 flashback archive ;
Table altered.
SQL> update part1 set a=-20 where a=-10 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table part1 drop partition part2 ;
alter table part1 drop partition part2
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> alter table part1 drop partition part1;
alter table part1 drop partition part1
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> alter table part1 truncate partition part2;
alter table part1 truncate partition part2
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> truncate table t05311_a ;
Table truncated.
SQL> select * from t05311_a
2 version between scn minvalue and maxvalue
3 ;
version between scn minvalue and maxvalue
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
SQL> select * from t05311_a
2 versions between scn minvalue and maxvalue ;
A B
---------- --------------------
1 A
2 A
3
4
3 A
3
6 rows selected.
SQL> rename t05311_a to t05311_b ;
Table renamed.
SQL> select * from t05311_b
2 versions between scn minvalue and maxvalue ;
A B
---------- --------------------
1 A
2 A
3
4
3 A
3
6 rows selected.
SQL> select * from part1
2 versions between scn minvalue and maxvalue ;
A
----------
-10
-20
10
SQL> alter table part1 drop partition part2 ;
alter table part1 drop partition part2
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL> alter table part1 drop partition p2 ;
Table altered.
SQL> select * from part1
2 versions between scn minvalue and maxvalue ;
A
----------
-20
SQL> select * from t05311_a ;
select * from t05311_a
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from t05311_b ;
no rows selected
SQL> select * from t05311_b ;
no rows selected
SQL> select * from t05311_b
2 as of timestamp sysdate-1/48 ;
no rows selected
SQL> select * from t05311_b
2 as of timestamp sysdate-1/24 ;
no rows selected
SQL> select versions_startscn , a
2 from t05311_b
3 versions between scn minvalue and maxvalue ;
VERSIONS_STARTSCN A
----------------- ----------
1251787 1
1251810 2
1252027 3
1252094 4
1251824 3
1252548 3
6 rows selected.
SQL> select * from t05311_b ;
no rows selected
SQL> select * from t05311_b
2 as of scn 1252094 ;
A
----------
4
SQL> select * from SYS_FBA_HIST_74721;
RID
--------------------------------------------------------------------------------
STARTSCN ENDSCN XID O A D_1251994_B
---------- ---------- ---------------- - ---------- --------------------
AAASPhAAEAAAAJnAAA
1251787 1251810 0A00210005030000 I 1 A
AAASPhAAEAAAAJnAAA
1251810 1251824 08001600C8030000 U 2 A
AAASPhAAEAAAAJnAAA
1252027 1252094 U 3
RID
--------------------------------------------------------------------------------
STARTSCN ENDSCN XID O A D_1251994_B
---------- ---------- ---------------- - ---------- --------------------
AAASPhAAEAAAAJnAAA
1252094 1252548 06000600C8030000 U 4
AAASPhAAEAAAAJnAAA
1251824 1252027 09001800DB030000 U 3 A
AAASPhAAEAAAAJnAAB
1252548 1253252 08001C00C9030000 I 3
6 rows selected.
SQL> update SYS_FBA_HIST_74721 set a=40 where a=4 ;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t05311_b
2 as of scn 1252094 ;
A
----------
40
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T_CASCADE BIN$M4OVsQZ26zrgUKjAWgBU/w==$0 TABLE 2016-05-23:21:40:33
SQL> create table t05311_c ( a number ) ;
Table created.
SQL> insert into t05311_c values
2 (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t05311_c add constraint pk_t05311_c primary key ( a ) ;
Table altered.
SQL> drop table t05311_c ;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311_C BIN$M6ujzM+srbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:27:49
T_CASCADE BIN$M4OVsQZ26zrgUKjAWgBU/w==$0 TABLE 2016-05-23:21:40:33
SQL> select * from t05311_c;
select * from t05311_c
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from BIN$M6ujzM+srbXgUKjAWgBzWQ==$0;
select * from BIN$M6ujzM+srbXgUKjAWgBzWQ==$0
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from "BIN$M6ujzM+srbXgUKjAWgBzWQ==$0";
A
----------
1
SQL> flashback table t05311_c to before drop ;
Flashback complete.
SQL> select * from t05311_c;
A
----------
1
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T_CASCADE BIN$M4OVsQZ26zrgUKjAWgBU/w==$0 TABLE 2016-05-23:21:40:33
SQL> drop table t05311_c ;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311_C BIN$M6ujzM+vrbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:35:09
T_CASCADE BIN$M4OVsQZ26zrgUKjAWgBU/w==$0 TABLE 2016-05-23:21:40:33
SQL> select * from t04209_uname ;
select * from t04209_uname
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> !ls
3_2014-05-21-1.sql create_t04209_uname.sql rac_tns t05310.sql update2_t04209_uname.sql 模板文档桌面
afiedt.buf mail revertape-root.sh tnsnames.ora update_t04209_uname.sql 视频下载
copytape-root.sh oradiag_oracle select_t04209_uname.sql ttio.rcv 公共的图片音乐
SQL> set echo off
SQL> set feedback off
SQL> @create_t04209_uname.sql
SQL> show recyclebin
SQL> set echo on
SQL> set feedback on
SQL> show recyclebin
SQL> create table t05311_d ( a number ) ;
Table created.
SQL> insert into t05311_d values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311_d ;
Table dropped.
SQL> create table t05311_d ( a date ) ;
Table created.
SQL> insert into t05311_d vaues ( sysdate ) .;
insert into t05311_d vaues ( sysdate ) .
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL>
SQL> insert into t05311_d values ( sysdate ) .;
insert into t05311_d values ( sysdate ) .
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> insert into t05311_d values ( sysdate ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311_d ;
Table dropped.
SQL> create table t05311_d ( a varchar2(20) ) ;
Table created.
SQL> insert into t05311_d values ('A') ;
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311_d ;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311_D BIN$M6ujzM+yrbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:41:18
T05311_D BIN$M6ujzM+xrbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:40:52
T05311_D BIN$M6ujzM+wrbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:40:19
SQL> flashback table T05311_D to before drop ;
Flashback complete.
SQL> select * from T05311_D ;
A
--------------------
A
1 row selected.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311_D BIN$M6ujzM+xrbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:40:52
T05311_D BIN$M6ujzM+wrbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:40:19
SQL> flashback table T05311_D to before drop ;
flashback table T05311_D to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL> flashback table "BIN$M6ujzM+wrbXgUKjAWgBzWQ==$0" to before drop rename to T05311_D2;
Flashback complete.
SQL> select * from T05311_D2;
A
----------
1
1 row selected.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311_D BIN$M6ujzM+xrbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:40:52
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 25 21:45:34 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 5 days
Connected.
SQL> drop table T05311_D2 purge ;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311_D BIN$M6ujzM+xrbXgUKjAWgBzWQ==$0 TABLE 2016-05-25:21:40:52
SQL> purge user_recyclebin ;
Recyclebin purged.
SQL> drop table T05311_D ;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311_D BIN$M6vnCl9aVAHgUKjAWgAmzA==$0 TABLE 2016-05-25:21:46:37
SQL> purge T05311_D ;
purge T05311_D
*
ERROR at line 1:
ORA-38302: invalid PURGE option
SQL> purge table T05311_D ;
Table purged.
SQL> drop table t04209_uname ;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T04209_UNAME BIN$M6vnCl9bVAHgUKjAWgAmzA==$0 TABLE 2016-05-25:21:47:44
SQL> show recyclebin
SQL>

复制代码

PL/SQL窗口：

select * from dba_indexes i
where i.owner='HR' and i.table_name='T05311_C';
select * from dba_tables t
where t.owner='HR' and t.table_name='T05311_C';
select bytes/1024/1024 from dba_data_files where tablespace_name='USERS';
select * from dba_recyclebin;
alter index hr."BIN$M6ujzM+rrbXgUKjAWgBzWQ==$0" rename to pk_t05311_c;

复制代码