- SQL> create user testuser identified by test;
- User created.
- SQL> grant connect to testuser;
- Grant succeeded.
- SQL> grant flashback archive administer to testuser;
- Grant succeeded.
- SQL> alter user testuser quota 100m on users;
- User altered.
- SQL> conn testuser/test
- Connected.
- SQL> create flashback archive test_archive1
- 2 tablespace users
- 3 quota 10m
- 4 retention 1 day;
- Flashback archive created.
- SQL> drop flashback archive test_archive1;
- Flashback archive dropped.
- SQL> create flashback archive test_archive1
- 2 tablespace users
- 3 quota 110m
- 4 retention 1 day;
- tablespace users
- *
- ERROR at line 2:
- ORA-55621: User quota on tablespace "USERS" is not enough for Flashback Archive
- SQL> create flashback archive test_archive1
- 2 tablespace users
- 3 quota 10m
- 4 retention 1 day;
- Flashback archive created.
- SQL> conn / as sysdba
- Connected.
- SQL> grant create table to testuser;
- Grant succeeded.
- SQL> grant alter any table to testuser;
- Grant succeeded.
- SQL> conn testuser/test
- Connected.
- SQL> create table test(id number);
- Table created.
- SQL> alter table test flashback archive test_archive1;
- Table altered.
- SQL> insert into test values(1);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
- TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
- --------------------------------------
- 2010-09-16 13:06:25
- SQL> insert into test values(2);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
- TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
- --------------------------------------
- 2010-09-16 13:06:48
- SQL> insert into test values(3);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
- TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
- --------------------------------------
- 2010-09-16 13:07:00
- SQL> select * from test
- 2 as of timestamp
- 3 to_timestamp('2010-09-16 13:06:48','yyyy-mm-dd hh24:mi:ss');
- ID
- ----------
- 1
- 2
- SQL> select * from test
- 2 as of timestamp
- 3 to_timestamp('2010-09-16 13:06:25','yyyy-mm-dd hh24:mi:ss');
- ID
- ----------
- 1
- SQL> update test set id=5;
- 758090 rows updated.
- SQL> commit;
- SQL> select sid,seq#,event ,p1text,p1 from v$session_wait where wait_class<>'Idle';
- SID SEQ# EVENT P1TEXT P1
- ---------- ---------- ------------------------------ ------------------------------ ----------
- 121 6340 direct path write temp file number 201
- SQL> drop table test;
- drop table test
- *
- ERROR at line 1:
- ORA-55610: Invalid DDL statement on history-tracked table
-
- 五.清除闪回归档区的数据
- 1.清除所有数据
- NING@11g>alter flashback archive test_archive1 purge all;
- Operation 219 succeeded.
- 2.清除某个时间点,比如一天前的数据
- NING@11g>alter flashback archive test_archive1
- 2 purge before timestamp (systimestamp – interval ’1′ day);
- Operation 219 succeeded.
- 3.清除某个SCN之前的历史数据
- NING@11g>alter flashback archive test_archive1
- 2 purge before scn 8570685767554;
- Operation 219 succeeded.
- 六.置于Flashback data archive中的table的一些限制
- 追踪表(Tracked table),也就是指定将历史数据保存到某个flashback data archive中的table,不能执行DDL操作(add column除外)。
- NING@11g>drop table test;
- drop table test
- *
- ERROR at line 1:
- ORA-55610: Invalid DDL statement on history-tracked table
- NING@11g>truncate table test;
- truncate table test
- *
- ERROR at line 1:
- ORA-55610: Invalid DDL statement on history-tracked table
- NING@11g>alter table test drop column object_id;
- alter table test drop column object_id
- *
- ERROR at line 1:
- ORA-55610: Invalid DDL statement on history-tracked table
- NING@11g>alter table test add col_test int;
- Table altered.
- 但是可以rename table,这一点和文档上说的不一致
- NING@11g>rename test to test1;
- Table renamed.
- NING@11g>select table_name,flashback_archive_name from dba_flashback_archive_tables;
- TABLE_NAME FLASHBACK_ARCHIVE_NAME
- —————————— ——————————
- TEST1 TEST_ARCHIVE1
- SQL> drop user testuser cascade;
- drop user testuser cascade
- *
- ERROR at line 1:
- ORA-00604: error occurred at recursive SQL level 1
- ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on
- table "TESTUSER"."SYS_FBA_TCRV_70625"
- SQL> conn testuser/test
- Connected.
- SQL> alter table test no flashback archive;
- Table altered.
- SQL> conn / as sysdba
- Connected.
- SQL> drop user testuser cascade;
- User dropped.
转自: http://space.itpub.net/22034023/viewspace-674138
阅读(734) | 评论(0) | 转发(0) |