设为首页收藏本站

Botang唐波's Oracle Station

查看: 843|回复: 0

课程第26次(2016-05-25星期三):闪回2

[复制链接]

744

主题

1117

帖子

8081

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8081
发表于 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)

课堂命令行:
  1. SQL> create table t05311_a ( a   number   , b varchar2(20))  ;

  2. Table created.

  3. SQL> insert into t05311_a values ( 1   , 'A' )  ;

  4. 1 row created.

  5. SQL> commit;

  6. Commit complete.

  7. SQL> alter table t05311_a  flashback archive ;

  8. Table altered.

  9. SQL> update t05311_a set a=2 ;

  10. 1 row updated.

  11. SQL> commit;

  12. Commit complete.

  13. SQL> update t05311_a set a=3 ;

  14. 1 row updated.

  15. SQL> commit;

  16. Commit complete.

  17. SQL> alter table t05311_a drop ( b)  ;

  18. Table altered.

  19. SQL> update t05311_a set a=4 ;

  20. 1 row updated.

  21. SQL> commit;

  22. Commit complete.

  23. SQL> select  versions_startscn  , a  , b  
  24.   2   from t05311_a  
  25.   3   versions between scn minvalue and maxvalue ;

  26. VERSIONS_STARTSCN           A B
  27. ----------------- ---------- --------------------
  28.           1251787           1 A
  29.           1251810           2 A
  30.           1251824           3 A
  31.           1252094           4
  32.           1252027           3

  33. SQL> alter table t05311_a enable row movement;

  34. Table altered.

  35. SQL> flashback table t05311_a to scn 1252027;
  36. flashback table t05311_a to scn 1252027
  37.                 *
  38. ERROR at line 1:
  39. ORA-01466: unable to read data - table definition has changed


  40. SQL> flashback table t05311_a to scn 1252093;

  41. Flashback complete.

  42. SQL> select  * from t05311_a;

  43.          A
  44. ----------
  45.          3

  46. SQL> create table part1 ( a   number )
  47.   2  partition by range (a )
  48.   3  (partition p1 values less than ( 10 ) ,
  49.   4   partition p2 values less than ( maxvalue ) );

  50. Table created.

  51. SQL> insert into part1  values ( 10 ) ;

  52. 1 row created.

  53. SQL> commit;

  54. Commit complete.

  55. SQL> select  * from part1  partition (p2)  ;

  56.          A
  57. ----------
  58.         10

  59. SQL> insert into part1 values ( -10 )  ;

  60. 1 row created.

  61. SQL> commit;

  62. Commit complete.

  63. SQL> select  * from part1  partition (p2)  ;

  64.          A
  65. ----------
  66.         10

  67. SQL> select  * from part1  partition (p1)  ;

  68.          A
  69. ----------
  70.        -10

  71. SQL> select  * from part1   ;

  72.          A
  73. ----------
  74.        -10
  75.         10

  76. SQL> alter table part1  flashback archive ;

  77. Table altered.

  78. SQL> update part1 set a=-20 where a=-10  ;

  79. 1 row updated.

  80. SQL> commit;

  81. Commit complete.

  82. SQL> alter table part1  drop partition part2 ;
  83. alter table part1  drop partition part2
  84. *
  85. ERROR at line 1:
  86. ORA-55610: Invalid DDL statement on history-tracked table


  87. SQL> alter table part1  drop partition part1;
  88. alter table part1  drop partition part1
  89. *
  90. ERROR at line 1:
  91. ORA-55610: Invalid DDL statement on history-tracked table


  92. SQL> alter table part1   truncate  partition part2;
  93. alter table part1   truncate  partition part2
  94. *
  95. ERROR at line 1:
  96. ORA-55610: Invalid DDL statement on history-tracked table


  97. SQL> truncate table t05311_a ;

  98. Table truncated.

  99. SQL> select  * from t05311_a
  100.   2  version between scn minvalue and maxvalue
  101.   3  ;
  102. version between scn minvalue and maxvalue
  103.         *
  104. ERROR at line 2:
  105. ORA-00933: SQL command not properly ended


  106. SQL> select  * from t05311_a
  107.   2   versions between scn minvalue and maxvalue ;

  108.          A B
  109. ---------- --------------------
  110.          1 A
  111.          2 A
  112.          3
  113.          4
  114.          3 A
  115.          3

  116. 6 rows selected.

  117. SQL> rename t05311_a to t05311_b ;

  118. Table renamed.

  119. SQL> select  * from t05311_b
  120.   2   versions between scn minvalue and maxvalue ;

  121.          A B
  122. ---------- --------------------
  123.          1 A
  124.          2 A
  125.          3
  126.          4
  127.          3 A
  128.          3

  129. 6 rows selected.

  130. SQL> select  * from  part1
  131.   2    versions between scn minvalue and maxvalue ;

  132.          A
  133. ----------
  134.        -10
  135.        -20
  136.         10

  137. SQL> alter table part1 drop partition part2  ;
  138. alter table part1 drop partition part2
  139.                                  *
  140. ERROR at line 1:
  141. ORA-02149: Specified partition does not exist


  142. SQL> alter table part1 drop partition p2  ;

  143. Table altered.

  144. SQL>  select  * from  part1
  145.   2   versions between scn minvalue and maxvalue ;

  146.          A
  147. ----------
  148.        -20

  149. SQL> select  * from t05311_a ;
  150. select        * from t05311_a
  151.                *
  152. ERROR at line 1:
  153. ORA-00942: table or view does not exist


  154. SQL> select  * from t05311_b ;

  155. no rows selected

  156. SQL> select  * from t05311_b ;

  157. no rows selected

  158. SQL> select  * from t05311_b
  159.   2   as of timestamp sysdate-1/48  ;

  160. no rows selected

  161. SQL> select  * from t05311_b
  162.   2   as of timestamp sysdate-1/24  ;

  163. no rows selected

  164. SQL>  select  versions_startscn , a  
  165.   2    from t05311_b
  166.   3   versions between scn minvalue and maxvalue ;

  167. VERSIONS_STARTSCN           A
  168. ----------------- ----------
  169.           1251787           1
  170.           1251810           2
  171.           1252027           3
  172.           1252094           4
  173.           1251824           3
  174.           1252548           3

  175. 6 rows selected.

  176. SQL> select  * from t05311_b ;

  177. no rows selected

  178. SQL> select  * from t05311_b
  179.   2   as of scn 1252094 ;

  180.          A
  181. ----------
  182.          4

  183. SQL> select  * from SYS_FBA_HIST_74721;

  184. RID
  185. --------------------------------------------------------------------------------
  186.   STARTSCN     ENDSCN XID               O          A D_1251994_B
  187. ---------- ---------- ---------------- - ---------- --------------------
  188. AAASPhAAEAAAAJnAAA
  189.    1251787    1251810 0A00210005030000 I          1 A

  190. AAASPhAAEAAAAJnAAA
  191.    1251810    1251824 08001600C8030000 U          2 A

  192. AAASPhAAEAAAAJnAAA
  193.    1252027    1252094                       U          3


  194. RID
  195. --------------------------------------------------------------------------------
  196.   STARTSCN     ENDSCN XID               O          A D_1251994_B
  197. ---------- ---------- ---------------- - ---------- --------------------
  198. AAASPhAAEAAAAJnAAA
  199.    1252094    1252548 06000600C8030000 U          4

  200. AAASPhAAEAAAAJnAAA
  201.    1251824    1252027 09001800DB030000 U          3 A

  202. AAASPhAAEAAAAJnAAB
  203.    1252548    1253252 08001C00C9030000 I          3


  204. 6 rows selected.

  205. SQL> update SYS_FBA_HIST_74721 set a=40 where a=4 ;

  206. 1 row updated.

  207. SQL> commit;

  208. Commit complete.

  209. SQL> select  * from t05311_b
  210.   2   as of scn 1252094 ;

  211.          A
  212. ----------
  213.         40

  214. SQL> show recyclebin
  215. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  216. ---------------- ------------------------------ ------------ -------------------
  217. T_CASCADE         BIN$M4OVsQZ26zrgUKjAWgBU/w==$0 TABLE             2016-05-23:21:40:33
  218. SQL> create table t05311_c ( a  number )  ;

  219. Table created.

  220. SQL> insert into t05311_c values
  221.   2  (1)  ;

  222. 1 row created.

  223. SQL> commit;

  224. Commit complete.

  225. SQL> alter table t05311_c  add constraint pk_t05311_c  primary key ( a )   ;

  226. Table altered.

  227. SQL> drop table t05311_c  ;

  228. Table dropped.

  229. SQL> show recyclebin
  230. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  231. ---------------- ------------------------------ ------------ -------------------
  232. T05311_C         BIN$M6ujzM+srbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:27:49
  233. T_CASCADE         BIN$M4OVsQZ26zrgUKjAWgBU/w==$0 TABLE             2016-05-23:21:40:33
  234. SQL> select  * from t05311_c;
  235. select        * from t05311_c
  236.                *
  237. ERROR at line 1:
  238. ORA-00942: table or view does not exist


  239. SQL> select  * from   BIN$M6ujzM+srbXgUKjAWgBzWQ==$0;
  240. select        * from         BIN$M6ujzM+srbXgUKjAWgBzWQ==$0
  241.                            *
  242. ERROR at line 1:
  243. ORA-00933: SQL command not properly ended


  244. SQL> select  * from   "BIN$M6ujzM+srbXgUKjAWgBzWQ==$0";

  245.          A
  246. ----------
  247.          1

  248. SQL> flashback table t05311_c to before drop ;

  249. Flashback complete.

  250. SQL> select  * from t05311_c;

  251.          A
  252. ----------
  253.          1

  254. SQL> show recyclebin
  255. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  256. ---------------- ------------------------------ ------------ -------------------
  257. T_CASCADE         BIN$M4OVsQZ26zrgUKjAWgBU/w==$0 TABLE             2016-05-23:21:40:33
  258. SQL> drop table t05311_c  ;

  259. Table dropped.

  260. SQL> show recyclebin
  261. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  262. ---------------- ------------------------------ ------------ -------------------
  263. T05311_C         BIN$M6ujzM+vrbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:35:09
  264. T_CASCADE         BIN$M4OVsQZ26zrgUKjAWgBU/w==$0 TABLE             2016-05-23:21:40:33
  265. SQL> select  * from t04209_uname ;
  266. select        * from t04209_uname
  267.                *
  268. ERROR at line 1:
  269. ORA-00942: table or view does not exist


  270. SQL> !ls
  271. 3_2014-05-21-1.sql  create_t04209_uname.sql  rac_tns                      t05310.sql    update2_t04209_uname.sql  模板  文档  桌面
  272. afiedt.buf            mail                     revertape-root.sh              tnsnames.ora  update_t04209_uname.sql   视频  下载
  273. copytape-root.sh    oradiag_oracle             select_t04209_uname.sql  ttio.rcv            公共的                      图片  音乐

  274. SQL> set echo off
  275. SQL> set feedback off
  276. SQL> @create_t04209_uname.sql
  277. SQL> show recyclebin
  278. SQL> set echo on
  279. SQL> set feedback on
  280. SQL> show recyclebin
  281. SQL> create table t05311_d ( a   number )  ;

  282. Table created.

  283. SQL> insert into t05311_d values (1) ;

  284. 1 row created.

  285. SQL> commit;

  286. Commit complete.

  287. SQL> drop table t05311_d ;

  288. Table dropped.

  289. SQL> create table t05311_d ( a   date  )  ;

  290. Table created.

  291. SQL> insert into t05311_d vaues ( sysdate ) .;
  292. insert into t05311_d vaues ( sysdate ) .
  293.                              *
  294. ERROR at line 1:
  295. ORA-00928: missing SELECT keyword


  296. SQL>
  297. SQL> insert into t05311_d values ( sysdate ) .;
  298. insert into t05311_d values ( sysdate ) .
  299.                                         *
  300. ERROR at line 1:
  301. ORA-00933: SQL command not properly ended


  302. SQL> insert into t05311_d values ( sysdate ) ;

  303. 1 row created.

  304. SQL> commit;

  305. Commit complete.

  306. SQL> drop table t05311_d  ;

  307. Table dropped.

  308. SQL> create table t05311_d ( a    varchar2(20)  )  ;

  309. Table created.

  310. SQL> insert into t05311_d values ('A')  ;

  311. 1 row created.

  312. SQL> commit;

  313. Commit complete.

  314. SQL> drop table t05311_d  ;

  315. Table dropped.

  316. SQL> show recyclebin
  317. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  318. ---------------- ------------------------------ ------------ -------------------
  319. T05311_D         BIN$M6ujzM+yrbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:41:18
  320. T05311_D         BIN$M6ujzM+xrbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:40:52
  321. T05311_D         BIN$M6ujzM+wrbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:40:19
  322. SQL> flashback table T05311_D to before drop ;

  323. Flashback complete.

  324. SQL> select  * from T05311_D  ;

  325. A
  326. --------------------
  327. A

  328. 1 row selected.

  329. SQL> show recyclebin
  330. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  331. ---------------- ------------------------------ ------------ -------------------
  332. T05311_D         BIN$M6ujzM+xrbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:40:52
  333. T05311_D         BIN$M6ujzM+wrbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:40:19
  334. SQL> flashback table T05311_D to before drop ;
  335. flashback table T05311_D to before drop
  336. *
  337. ERROR at line 1:
  338. ORA-38312: original name is used by an existing object


  339. SQL> flashback table "BIN$M6ujzM+wrbXgUKjAWgBzWQ==$0" to before drop rename to T05311_D2;

  340. Flashback complete.

  341. SQL> select  * from T05311_D2;

  342.          A
  343. ----------
  344.          1

  345. 1 row selected.

  346. SQL> show recyclebin
  347. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  348. ---------------- ------------------------------ ------------ -------------------
  349. T05311_D         BIN$M6ujzM+xrbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:40:52
  350. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  351. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  352. and Real Application Testing options
  353. [oracle@station90 ~]$ sqlplus /nolog

  354. SQL*Plus: Release 11.2.0.1.0 Production on Wed May 25 21:45:34 2016

  355. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  356. SQL> conn hr/oracle_4U
  357. ERROR:
  358. ORA-28002: the password will expire within 5 days


  359. Connected.
  360. SQL> drop table T05311_D2  purge ;

  361. Table dropped.

  362. SQL> show recyclebin
  363. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  364. ---------------- ------------------------------ ------------ -------------------
  365. T05311_D         BIN$M6ujzM+xrbXgUKjAWgBzWQ==$0 TABLE             2016-05-25:21:40:52
  366. SQL> purge user_recyclebin ;

  367. Recyclebin purged.

  368. SQL> drop table T05311_D  ;

  369. Table dropped.

  370. SQL> show recyclebin
  371. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  372. ---------------- ------------------------------ ------------ -------------------
  373. T05311_D         BIN$M6vnCl9aVAHgUKjAWgAmzA==$0 TABLE             2016-05-25:21:46:37
  374. SQL> purge T05311_D  ;
  375. purge T05311_D
  376.       *
  377. ERROR at line 1:
  378. ORA-38302: invalid PURGE option


  379. SQL> purge table  T05311_D  ;

  380. Table purged.

  381. SQL> drop table t04209_uname ;  

  382. Table dropped.

  383. SQL> show recyclebin
  384. ORIGINAL NAME         RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
  385. ---------------- ------------------------------ ------------ -------------------
  386. T04209_UNAME         BIN$M6vnCl9bVAHgUKjAWgAmzA==$0 TABLE             2016-05-25:21:47:44
  387. SQL> show recyclebin
  388. SQL>




复制代码

PL/SQL窗口:

  1. select  * from dba_indexes i
  2. where  i.owner='HR' and i.table_name='T05311_C';

  3. select  * from dba_tables t
  4. where t.owner='HR' and t.table_name='T05311_C';

  5. select  bytes/1024/1024  from dba_data_files where tablespace_name='USERS';

  6. select * from dba_recyclebin;

  7. alter index hr."BIN$M6ujzM+rrbXgUKjAWgBzWQ==$0" rename to pk_t05311_c;
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-11-19 14:47 , Processed in 0.116624 second(s), 24 queries .

快速回复 返回顶部 返回列表