flashback data archive初体验

最新推荐文章于 2021-04-13 12:40:36 发布

转载最新推荐文章于 2021-04-13 12:40:36 发布 · 80 阅读

本文介绍了在Oracle数据库中创建用户、授予权限、建立闪回归档及对其进行管理的过程。包括闪回归档的创建、使用限制及数据清除方法等关键步骤。

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-674138/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-674138/