Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1446289
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19
文章分类

全部博文(239)

文章存档

2014年(4)

2013年(22)

2012年(140)

2011年(14)

2010年(59)

我的朋友

分类: Oracle

2012-06-21 16:33:39


点击(此处)折叠或打开

  1. SQL> create user testuser identified by test;

  2. User created.


  3. SQL> grant connect to testuser;

  4. Grant succeeded.


  5. SQL> grant flashback archive administer to testuser;

  6. Grant succeeded.

  7. SQL> alter user testuser quota 100m on users;

  8. User altered.

  9. SQL> conn testuser/test
  10. Connected.
  11. SQL> create flashback archive test_archive1
  12.   2 tablespace users
  13.   3 quota 10m
  14.   4 retention 1 day;

  15. Flashback archive created.

  16. SQL> drop flashback archive test_archive1;

  17. Flashback archive dropped.

  18. SQL> create flashback archive test_archive1
  19.   2 tablespace users
  20.   3 quota 110m
  21.   4 retention 1 day;
  22. tablespace users
  23.            *
  24. ERROR at line 2:
  25. ORA-55621: User quota on tablespace "USERS" is not enough for Flashback Archive


  26. SQL> create flashback archive test_archive1
  27.   2 tablespace users
  28.   3 quota 10m
  29.   4 retention 1 day;

  30. Flashback archive created.

  31. SQL> conn / as sysdba
  32. Connected.
  33. SQL> grant create table to testuser;

  34. Grant succeeded.


  35. SQL> grant alter any table to testuser;

  36. Grant succeeded.

  37. SQL> conn testuser/test
  38. Connected.
  39. SQL> create table test(id number);

  40. Table created.

  41. SQL> alter table test flashback archive test_archive1;

  42. Table altered.

  43. SQL> insert into test values(1);

  44. 1 row created.

  45. SQL> commit;

  46. Commit complete.

  47. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

  48. TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
  49. --------------------------------------
  50. 2010-09-16 13:06:25

  51. SQL> insert into test values(2);

  52. 1 row created.

  53. SQL> commit;

  54. Commit complete.

  55. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

  56. TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
  57. --------------------------------------
  58. 2010-09-16 13:06:48

  59. SQL> insert into test values(3);

  60. 1 row created.

  61. SQL> commit;

  62. Commit complete.

  63. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

  64. TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
  65. --------------------------------------
  66. 2010-09-16 13:07:00

  67. SQL> select * from test
  68.   2 as of timestamp
  69.   3 to_timestamp('2010-09-16 13:06:48','yyyy-mm-dd hh24:mi:ss');

  70.         ID
  71. ----------
  72.          1
  73.          2

  74. SQL> select * from test
  75.   2 as of timestamp
  76.   3 to_timestamp('2010-09-16 13:06:25','yyyy-mm-dd hh24:mi:ss');

  77.         ID
  78. ----------
  79.          1

  80. SQL> update test set id=5;

  81. 758090 rows updated.
  82. SQL> commit;
  83. SQL> select sid,seq#,event ,p1text,p1 from v$session_wait where wait_class<>'Idle';

  84.        SID SEQ# EVENT P1TEXT P1
  85. ---------- ---------- ------------------------------ ------------------------------ ----------
  86.        121 6340 direct path write temp file number 201
  87. SQL> drop table test;
  88. drop table test
  89.            *
  90. ERROR at line 1:
  91. ORA-55610: Invalid DDL statement on history-tracked table

  92.  

  93. .清除闪回归档区的数据

  94. 1.清除所有数据


  95. NING@11g>alter flashback archive test_archive1 purge all;

  96. Operation 219 succeeded.

  97. 2.清除某个时间点,比如一天前的数据

  98. NING@11g>alter flashback archive test_archive1
  99. 2 purge before timestamp (systimestamp – interval ’1′ day);

  100. Operation 219 succeeded.

  101. 3.清除某个SCN之前的历史数据

  102. NING@11g>alter flashback archive test_archive1
  103. 2 purge before scn 8570685767554;

  104. Operation 219 succeeded.

  105. .置于Flashback data archive中的table的一些限制

  106. 追踪表(Tracked table),也就是指定将历史数据保存到某个flashback data archive中的table,不能执行DDL操作(add column除外)

  107. NING@11g>drop table test;
  108. drop table test
  109. *
  110. ERROR at line 1:
  111. ORA-55610: Invalid DDL statement on history-tracked table

  112. NING@11g>truncate table test;
  113. truncate table test
  114. *
  115. ERROR at line 1:
  116. ORA-55610: Invalid DDL statement on history-tracked table

  117. NING@11g>alter table test drop column object_id;
  118. alter table test drop column object_id
  119. *
  120. ERROR at line 1:
  121. ORA-55610: Invalid DDL statement on history-tracked table


  122. NING@11g>alter table test add col_test int;

  123. Table altered.

  124. 但是可以rename table,这一点和文档上说的不一致

  125. NING@11g>rename test to test1;

  126. Table renamed.

  127. NING@11g>select table_name,flashback_archive_name from dba_flashback_archive_tables;

  128. TABLE_NAME FLASHBACK_ARCHIVE_NAME
  129. —————————— ——————————
  130. TEST1 TEST_ARCHIVE1

  131. SQL> drop user testuser cascade;
  132. drop user testuser cascade
  133. *
  134. ERROR at line 1:
  135. ORA-00604: error occurred at recursive SQL level 1
  136. ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on
  137. table "TESTUSER"."SYS_FBA_TCRV_70625"

  138. SQL> conn testuser/test
  139. Connected.
  140. SQL> alter table test no flashback archive;

  141. Table altered.

  142. SQL> conn / as sysdba
  143. Connected.
  144. SQL> drop user testuser cascade;

  145. User dropped.
转自: http://space.itpub.net/22034023/viewspace-674138
阅读(734) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~