课程第22/23次

botang

1. 闪回删除表第一个要注意的事项：
   表空间空间压力（已经开始自动扩展了），回收站就已经被自动清除了。
2. 依附对象的名字的问题：

1. select * from dba_segments s where s.owner='HR'
   
2.   and s.segment_name  like '%T05311_H%';
   
3.   
   
4. select * from dba_segments s where s.owner='HR'
   
5.   ;
   
6. 
   
7. 
   
8. select  * from dba_recyclebin;
   
9. 
   
10. alter index hr."BIN$jJ57htVbtPHgU1AAqMC0vg==$0" rename to pk_t05311_H;

复制代码

3. 重名的问题：

1. SQL> create table t05311_i ( a  number ) ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t05311_i values (1) ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> drop table t05311_i ;
    
14. 
    
15. Table dropped.
    
16. 
    
17. SQL> show recyclebin
    
18. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    
19. ---------------- ------------------------------ ------------ -------------------
    
20. T05311_G         BIN$jG7hZcslbUjgU1AAqMBmVQ==$0 TABLE        2019-06-29:11:25:23
    
21. T05311_G         BIN$jG7hZcskbUjgU1AAqMBmVQ==$0 TABLE        2019-06-29:11:23:27
    
22. T05311_I         BIN$jJ57htVdtPHgU1AAqMC0vg==$0 TABLE        2019-07-01:20:21:06
    
23. SQL> create table t05311_i ( a date )  ;
    
24. 
    
25. Table created.
    
26. 
    
27. SQL> insert into t05311_i values ( sysdate ) ;
    
28. 
    
29. 1 row created.
    
30. 
    
31. SQL> commit;
    
32. 
    
33. Commit complete.
    
34. 
    
35. SQL> drop table t05311_i;
    
36. 
    
37. Table dropped.
    
38. 
    
39. SQL> show recyclebin
    
40. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    
41. ---------------- ------------------------------ ------------ -------------------
    
42. T05311_G         BIN$jG7hZcslbUjgU1AAqMBmVQ==$0 TABLE        2019-06-29:11:25:23
    
43. T05311_G         BIN$jG7hZcskbUjgU1AAqMBmVQ==$0 TABLE        2019-06-29:11:23:27
    
44. T05311_I         BIN$jJ57htVetPHgU1AAqMC0vg==$0 TABLE        2019-07-01:20:22:29
    
45. T05311_I         BIN$jJ57htVdtPHgU1AAqMC0vg==$0 TABLE        2019-07-01:20:21:06
    
46. SQL> select * from "BIN$jJ57htVdtPHgU1AAqMC0vg==$0";
    
47. 
    
48.          A
    
49. ----------
    
50.          1
    
51. 
    
52. SQL> select  * from "BIN$jJ57htVetPHgU1AAqMC0vg==$0";
    
53. 
    
54. A
    
55. ------------------
    
56. 01-JUL-19
    
57. 
    
58. SQL> create table t05311_i ( a  varchar2(20))  ;
    
59. 
    
60. Table created.
    
61. 
    
62. SQL> insert into t05311_i values ('A') ;
    
63. 
    
64. 1 row created.
    
65. 
    
66. SQL> commit;
    
67. 
    
68. Commit complete.
    
69. 
    
70. SQL> drop table t05311_i;
    
71. 
    
72. Table dropped.
    
73. 
    
74. SQL> show recyclebin
    
75. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    
76. ---------------- ------------------------------ ------------ -------------------
    
77. T05311_G         BIN$jG7hZcslbUjgU1AAqMBmVQ==$0 TABLE        2019-06-29:11:25:23
    
78. T05311_G         BIN$jG7hZcskbUjgU1AAqMBmVQ==$0 TABLE        2019-06-29:11:23:27
    
79. T05311_I         BIN$jJ57htVftPHgU1AAqMC0vg==$0 TABLE        2019-07-01:20:24:12
    
80. T05311_I         BIN$jJ57htVetPHgU1AAqMC0vg==$0 TABLE        2019-07-01:20:22:29
    
81. T05311_I         BIN$jJ57htVdtPHgU1AAqMC0vg==$0 TABLE        2019-07-01:20:21:06
    
82. SQL> flashback table T05311_I to before drop ;
    
83. 
    
84. Flashback complete.
    
85. 
    
86. SQL> select  * from T05311_I;
    
87. 
    
88. A
    
89. --------------------
    
90. A
    
91. 
    
92. SQL> flashback table T05311_I  to  before drop ;
    
93. flashback table T05311_I  to  before drop
    
94. *
    
95. ERROR at line 1:
    
96. ORA-38312: original name is used by an existing object
    
97. 
    
98. 
    
99. SQL>  flashback table T05311_I  to  before drop rename to t05311_j;
    
100. 
     
101. Flashback complete.
     
102. 
     
103. SQL> select * from t05311_j;
     
104. 
     
105. A
     
106. ------------------
     
107. 01-JUL-19
     
108. 
     
109. SQL> drop table T05311_I ;
     
110. 
     
111. Table dropped.
     
112. 
     
113. SQL> show recyclebin
     
114. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
     
115. ---------------- ------------------------------ ------------ -------------------
     
116. T05311_G         BIN$jG7hZcslbUjgU1AAqMBmVQ==$0 TABLE        2019-06-29:11:25:23
     
117. T05311_G         BIN$jG7hZcskbUjgU1AAqMBmVQ==$0 TABLE        2019-06-29:11:23:27
     
118. T05311_I         BIN$jJ57htVgtPHgU1AAqMC0vg==$0 TABLE        2019-07-01:20:26:29
     
119. T05311_I         BIN$jJ57htVdtPHgU1AAqMC0vg==$0 TABLE        2019-07-01:20:21:06
     
120. SQL> select  * from "BIN$jJ57htVgtPHgU1AAqMC0vg==$0";
     
121. 
     
122. A
     
123. --------------------
     
124. A
     
125. 
     
126. SQL> flashback table "BIN$jJ57htVdtPHgU1AAqMC0vg==$0" to before drop ;
     
127. 
     
128. Flashback complete.
     
129. 
     
130. SQL> select  * from  t05311_i;
     
131. 
     
132.          A
     
133. ----------
     
134.          1
     
135. 
     
136. SQL>
     

复制代码

1. select  * from v$session_longops;

复制代码

	SID	SERIAL#	OPNAME	TARGET	TARGET_DESC	SOFAR	TOTALWORK	UNITS	START_TIME	LAST_UPDATE_TIME	TIMESTAMP	TIME_REMAINING	ELAPSED_SECONDS	CONTEXT	MESSAGE	USERNAME	SQL_ADDRESS	SQL_HASH_VALUE	SQL_ID	SQL_PLAN_HASH_VALUE	SQL_EXEC_START	SQL_EXEC_ID	SQL_PLAN_LINE_ID	SQL_PLAN_OPERATION	SQL_PLAN_OPTIONS	QCSID
1	125	13	RMAN: aggregate input	33	backup	2880	2880	Blocks	2019/7/3 22:26:57	2019/7/3 22:28:14		0	77	3	RMAN: aggregate input: backup 33: 2880 out of 2880 Blocks done	SYS	00000000BA10EFC8	1385816205	1k3u5np99ms4d	0	2019/7/3 22:26:57	16777216				0
2	125	13	RMAN: aggregate output	33	backup	655	655	Blocks	2019/7/3 22:28:14	2019/7/3 22:28:14		0	0	3	RMAN: aggregate output: backup 33: 655 out of 655 Blocks done	SYS	00000000BA10EFC8	1385816205	1k3u5np99ms4d	0	2019/7/3 22:28:14	16777222				0
3	125	13	RMAN: aggregate input	33	backup	1752952	1752952	Blocks	2019/7/3 22:29:36	2019/7/3 22:32:56		0	200	5	RMAN: aggregate input: backup 33: 1752952 out of 1752952 Blocks done	SYS	00000000BA10EFC8	1385816205	1k3u5np99ms4d	0	2019/7/3 22:29:36	16777223				0
4	125	13	RMAN: aggregate output	33	backup	571511	571511	Blocks	2019/7/3 22:32:56	2019/7/3 22:32:56		0	0	5	RMAN: aggregate output: backup 33: 571511 out of 571511 Blocks done	SYS	00000000BA10EFC8	1385816205	1k3u5np99ms4d	0	2019/7/3 22:32:56	16777243				0
5	24	1	RMAN: full datafile backup	8	Set Count	2880	2880	Blocks	2019/7/3 22:26:57	2019/7/3 22:28:12		0	75	1	RMAN: full datafile backup: Set Count 8: 2880 out of 2880 Blocks done	SYS	00	0		0	2019/7/3 22:32:56	16777243				0
6	24	1	RMAN: full datafile backup	8	Set Count	655	655	Blocks	2019/7/3 22:26:57	2019/7/3 22:28:12		0	75	2	RMAN: full datafile backup: Set Count 8: 655 out of 655 Blocks done	SYS	00	0		0	2019/7/3 22:32:56	16777243				0
7	142	31	RMAN: aggregate output	33	backup	655	655	Blocks	2019/7/3 22:28:12	2019/7/3 22:28:12		0	0	3	RMAN: aggregate output: backup 33: 655 out of 655 Blocks done	SYS	00000000BA10EFC8	1385816205	1k3u5np99ms4d	0	2019/7/3 22:28:12	16777218				0
8	24	1	RMAN: archived log backup	9	Set Count	422558	422558	Blocks	2019/7/3 22:29:36	2019/7/3 22:29:55		0	19	1	RMAN: archived log backup: Set Count 9: 422558 out of 422558 Blocks done	SYS	00	0		0	2019/7/3 22:28:12	16777218				0
9	24	1	RMAN: archived log backup	9	Set Count	422562	422562	Blocks	2019/7/3 22:29:36	2019/7/3 22:29:55		0	19	2	RMAN: archived log backup: Set Count 9: 422562 out of 422562 Blocks done	SYS	00	0		0	2019/7/3 22:28:12	16777218				0
10	142	31	RMAN: aggregate output	33	backup	422562	422562	Blocks	2019/7/3 22:30:02	2019/7/3 22:30:02		0	0	5	RMAN: aggregate output: backup 33: 422562 out of 422562 Blocks done	SYS	00000000BA10EFC8	1385816205	1k3u5np99ms4d	0	2019/7/3 22:30:02	16777225				0
11	24	1	RMAN: incremental datafile backup	10	Set Count	805280	805280	Blocks	2019/7/3 22:30:03	2019/7/3 22:31:17		0	74	1	RMAN: incremental datafile backup: Set Count 10: 805280 out of 805280 Blocks done	SYS	00	0		0	2019/7/3 22:30:02	16777225				0
12	24	1	RMAN: incremental datafile backup	10	Set Count	145816	145816	Blocks	2019/7/3 22:30:03	2019/7/3 22:31:17		0	74	2	RMAN: incremental datafile backup: Set Count 10: 145816 out of 145816 Blocks done	SYS	00	0		0	2019/7/3 22:30:02	16777225				0
13	142	31	RMAN: aggregate output	33	backup	571374	571374	Blocks	2019/7/3 22:31:19	2019/7/3 22:32:30		0	71	5	RMAN: aggregate output: backup 33: 571374 out of 571374 Blocks done	SYS	00000000BA10EFC8	1385816205	1k3u5np99ms4d	0	2019/7/3 22:31:19	16777230				0
14	24	1	RMAN: incremental datafile backup	11	Set Count	524288	524288	Blocks	2019/7/3 22:31:19	2019/7/3 22:31:56		0	37	1	RMAN: incremental datafile backup: Set Count 11: 524288 out of 524288 Blocks done	SYS	00	0		0	2019/7/3 22:31:19	16777230				0
15	24	1	RMAN: incremental datafile backup	11	Set Count	2368	2368	Blocks	2019/7/3 22:31:19	2019/7/3 22:31:56		0	37	2	RMAN: incremental datafile backup: Set Count 11: 2368 out of 2368 Blocks done	SYS	00	0		0	2019/7/3 22:31:19	16777230				0
16	24	1	RMAN: incremental datafile backup	12	Set Count	626	626	Blocks	2019/7/3 22:32:04	2019/7/3 22:32:20		0	16	1	RMAN: incremental datafile backup: Set Count 12: 626 out of 626 Blocks done	SYS	00	0		0	2019/7/3 22:31:19	16777230				0
17	24	1	RMAN: incremental datafile backup	12	Set Count	628	628	Blocks	2019/7/3 22:32:04	2019/7/3 22:32:20		0	16	2	RMAN: incremental datafile backup: Set Count 12: 628 out of 628 Blocks done	SYS	00	0		0	2019/7/3 22:31:19	16777230				0
18	24	1	RMAN: archived log backup	13	Set Count	135	135	Blocks	2019/7/3 22:32:31	2019/7/3 22:32:46		0	15	1	RMAN: archived log backup: Set Count 13: 135 out of 135 Blocks done	SYS	00	0		0	2019/7/3 22:31:19	16777230				0
19	24	1	RMAN: archived log backup	13	Set Count	137	137	Blocks	2019/7/3 22:32:31	2019/7/3 22:32:46		0	15	2	RMAN: archived log backup: Set Count 13: 137 out of 137 Blocks done	SYS	00	0		0	2019/7/3 22:31:19	16777230				0
20	142	31	RMAN: aggregate output	33	backup	571511	571511	Blocks	2019/7/3 22:32:56	2019/7/3 22:32:56		0	0	5	RMAN: aggregate output: backup 33: 571511 out of 571511 Blocks done	SYS	00000000BA10EFC8	1385816205	1k3u5np99ms4d	0	2019/7/3 22:32:56	16777239				0