1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
模拟测试环境:
SQL> select current_scn
from
v$database;
CURRENT_SCN
-----------
1264179
07
:
16
:
18
SQL> select *
from
test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7521
WARD SALESMAN
7698
22
-FEB
-81
1250
500
30
7566
JONES MANAGER
7839
02
-APR
-81
2975
20
7654
MARTIN SALESMAN
7698
28
-SEP
-81
1250
1400
30
7698
BLAKE MANAGER
7839
01
-MAY
-81
2850
30
7782
CLARK MANAGER
7839
09
-JUN
-81
2450
10
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
20
7839
KING PRESIDENT
17
-NOV
-81
5000
10
7844
TURNER SALESMAN
7698
08
-SEP
-81
1500
0
30
7876
ADAMS CLERK
7788
23
-MAY
-87
1100
20
7900
JAMES CLERK
7698
03
-DEC
-81
950
30
7902
FORD ANALYST
7566
03
-DEC
-81
3000
20
7934
MILLER CLERK
7782
23
-JAN
-82
1300
10
16
rows selected.
07
:
16
:
23
SQL>
delete
from
test;
16
rows deleted.
07
:
16
:
50
SQL> commit;
Commit complete.
07
:
16
:
52
SQL> select *
from
test;
no rows selected
07
:
16
:
57
SQL>
insert
into
test select *
from
emp
where
rownum=
1
;
1
row created.
07
:
17
:
17
SQL> commit;
Commit complete.
07
:
17
:
19
SQL> select *
from
test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
通过flashback table回退:
07
:
17
:
21
SQL> flashback table test to scn
1264179
;
flashback table test to scn
1264179
*
ERROR
at
line
1
:
ORA
-08189
: cannot flashback the table because row movement is
not
enabled
回退table必须支持row movement:
07
:
17
:
41
SQL> alter table test enable row movement;
Table altered.
07
:
18
:
01
SQL> flashback table test to scn
1264179
;
Flashback complete.
07
:
18
:
05
SQL> select *
from
test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7369
SMITH CLERK
7902
17
-DEC
-80
800
20
7499
ALLEN SALESMAN
7698
20
-FEB
-81
1600
300
30
7521
WARD SALESMAN
7698
22
-FEB
-81
1250
500
30
7566
JONES MANAGER
7839
02
-APR
-81
2975
20
7654
MARTIN SALESMAN
7698
28
-SEP
-81
1250
1400
30
7698
BLAKE MANAGER
7839
01
-MAY
-81
2850
30
7782
CLARK MANAGER
7839
09
-JUN
-81
2450
10
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
20
7839
KING PRESIDENT
17
-NOV
-81
5000
10
7844
TURNER SALESMAN
7698
08
-SEP
-81
1500
0
30
7876
ADAMS CLERK
7788
23
-MAY
-87
1100
20
7900
JAMES CLERK
7698
03
-DEC
-81
950
30
7902
FORD ANALYST
7566
03
-DEC
-81
3000
20
7934
MILLER CLERK
7782
23
-JAN
-82
1300
10
16
rows selected.
---回退成功 !
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
05
:
43
:
31
SQL>
delete
from
scott.emp1;
14
rows deleted.
05
:
44
:
25
SQL> flashback table scott.emp1 to timestamp to_timestamp(
'2011-03-18 04:50:00'
,
'yyyy-mm-dd hh24:mi:ss'
);
Flashback complete.
05
:
44
:
32
SQL> select *
from
scott.emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369
SMITH CLERK
7902
1980
-12
-17
00
:
00
:
00
800
20
7499
ALLEN SALESMAN
7698
1981
-02
-20
00
:
00
:
00
1600
300
30
7521
WARD SALESMAN
7698
1981
-02
-22
00
:
00
:
00
1250
500
30
7566
JONES MANAGER
7839
1981
-04
-02
00
:
00
:
00
2975
20
7654
MARTIN SALESMAN
7698
1981
-09
-28
00
:
00
:
00
1250
1400
30
7698
BLAKE MANAGER
7839
1981
-05
-01
00
:
00
:
00
2850
30
7782
CLARK MANAGER
7839
1981
-06
-09
00
:
00
:
00
2450
10
7788
SCOTT ANALYST
7566
1987
-04
-19
00
:
00
:
00
3000
20
7839
KING PRESIDENT
1981
-11
-17
00
:
00
:
00
5000
10
7844
TURNER SALESMAN
7698
1981
-09
-08
00
:
00
:
00
1500
0
30
7876
ADAMS CLERK
7788
1987
-05
-23
00
:
00
:
00
1100
20
7900
JAMES CLERK
7698
1981
-12
-03
00
:
00
:
00
950
30
7902
FORD ANALYST
7566
1981
-12
-03
00
:
00
:
00
3000
20
7934
MILLER CLERK
7782
1982
-01
-23
00
:
00
:
00
1300
10
14
rows selected.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
1
)
06
:
52
:
29
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
T01 TABLE
T02 TABLE
7
rows selected.
06
:
52
:
31
SQL> drop table t01;
Table dropped.
查看回收站:
06
:
52
:
38
SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T01 BIN$qrJLbL74ZgvgQKjA8Agb/A==$
0
TABLE
2011
-
08
-
17
:
06
:
52
:
38
--------除了system 表空间,其余表空间都有一个类似windows 回收站,在drop table,实际上把table 改名后放入recyclebin。
06
:
52
:
44
SQL> flashback table t01 to before drop;
Flashback complete.
06
:
54
:
05
SQL> show recycle;
06
:
54
:
07
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
T01 TABLE
T02 TABLE
7
rows selected.
06
:
54
:
11
SQL> drop table t02 purge;
//purge 会彻底的删除table
Table dropped.
06
:
54
:
40
SQL> show recycle;
-----------清空recyclebin
06
:
54
:
43
SQL> drop table t01;
Table dropped.
06
:
55
:
49
SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T01 BIN$qrJLbL75ZgvgQKjA8Agb/A==$
0
TABLE
2011
-
08
-
17
:
06
:
55
:
49
06
:
55
:
51
SQL> purge recyclebin;
Recyclebin purged.
06
:
55
:
57
SQL> show recycle;
06
:
55
:
59
SQL>
--------------如何恢复同一个schema 下同名的table
06
:
56
:
32
SQL> drop table test;
Table dropped.
06
:
56
:
42
SQL> create table test as select * from emp;
Table created.
06
:
56
:
46
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$qrJLbL76ZgvgQKjA8Agb/A==$
0
TABLE
TEST TABLE
6
rows selected.
06
:
56
:
50
SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$qrJLbL76ZgvgQKjA8Agb/A==$
0
TABLE
2011
-
08
-
17
:
06
:
56
:
36
06
:
56
:
58
SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line
1
:
ORA-
38312
: original name is used by an existing object
06
:
57
:
09
SQL> flashback table test to before drop rename to test_old;
Flashback complete.
06
:
57
:
32
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST_OLD TABLE
TEST TABLE
6
rows selected.
flashback Drop不支持sys用户:
----system 表空间不存在recyclebin ,表直接被删除
06
:
57
:
36
SQL> conn /as sysdba
Connected.
06
:
58
:
33
SQL>
06
:
58
:
33
SQL> create table test as select * from user_tables;
Table created.
06
:
58
:
42
SQL> drop table test;
Table dropped.
06
:
58
:
46
SQL> show recycle;
闪回表回收站——
3
个视图
使用方面,闪回特性还要关注两个回收站视图。user_recyclebin、all_recyclebin、dba_recyclebin。
|