【实验】截断表就不能闪回查询了吗？flashback archive新特性依然可以闪回

最新推荐文章于 2023-01-29 15:25:12 发布

weixin_44298389

最新推荐文章于 2023-01-29 15:25:12 发布

阅读量205

点赞数

分类专栏： flashback 文章标签： flashback

本文链接：https://blog.csdn.net/weixin_44298389/article/details/86593138

版权

flashback 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1、首先找到一个表，能通过回滚段（undo）做闪回查询：

1* select * from departments_new as of timestamp sysdate-1/24
HR@PROD1> /
DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          290 Poduct Sales                          114        1800
          300 Busses Sales                          203        1700
          280 Mac Sales                             205        1700
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700
          120 Treasury                                         1700
          130 Corporate Tax                                    1700
          140 Control And Credit                               1700
          150 Shareholder Services                             1700
          160 Benefits                                         1700
          170 Manufacturing                                    1700
          180 Construction                                     1700
          190 Contracting                                      1700
DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          200 Operations                                       1700
          210 IT Support                                       1700
          220 NOC                                              1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700
          250 Retail Sales                                     1700
          260 Recruiting                                       1700
          270 Payroll                                          1700
30 rows selected.

2、截断表，这里是改变表行的名称

HR@PROD1> alter table departments_new rename column department_id to departmentid;
Table altered.

也可以truncate table table_name;
3、验证，真的不能闪回查询了

HR@PROD1> select * from departments_new as of timestamp sysdate-1/24；
select * from departments_new as of timestamp sysdate-1/24
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

4、创建闪回归档表空间

SYS@PROD1> create tablespace fratbs2 
  2  datafile '/u01/app/oracle/oradata/PROD1/fratbs02.dbf' size 50m
  3  autoextend on maxsize 1g
  4  extent management local uniform size 1m;
Tablespace created.

5、创建闪回管理用户，并赋予闪回归档权限

SYS@PROD1> create user fradmin identified by fradmin;
User created.
SYS@PROD1> grant connect,resource to fradmin;
Grant succeeded.
SYS@PROD1> select privilege from dba_sys_privs where privilege like '%FLASHBACK%';
PRIVILEGE
----------------------------------------
FLASHBACK ANY TABLE
FLASHBACK ANY TABLE
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ANY TABLE
6 rows selected.
SYS@PROD1> grant FLASHBACK ARCHIVE ADMINISTER to fradmin;
Grant succeeded.

6、创建闪回归档并验证

SYS@PROD1> conn fradmin/fradmin
Connected.
FRADMIN@PROD1> create flashback archive fr1 tablespace fratbs2 retention 1 year;
Flashback archive created.
FRADMIN@PROD1> desc dba_flashback_archive
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER_NAME                                         VARCHAR2(30)
 FLASHBACK_ARCHIVE_NAME                    NOT NULL VARCHAR2(255)
 FLASHBACK_ARCHIVE#                        NOT NULL NUMBER
 RETENTION_IN_DAYS                         NOT NULL NUMBER
 CREATE_TIME                                        TIMESTAMP(9)
 LAST_PURGE_TIME                                    TIMESTAMP(9)
 STATUS                                             VARCHAR2(7)
FRADMIN@PROD1> col FLASHBACK_ARCHIVE_NAME format a20
FRADMIN@PROD1> select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS from dba_flashback_archive;
           FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYS
------------------------------ -------------------- -----------------
FRADMIN                        FR1                                365
FRADMIN@PROD1> grant flashback archive on fr1 to hr;
Grant succeeded.

7、更改hr.departments表的行名，截断表了，但是依然能够闪回查询

FRADMIN@PROD1> conn hr/hr
Connected.
HR@PROD1> alter table departments flashback archive fr1;
Table altered.
HR@PROD1> select count(*) from departments as of timestamp sysdate-1/24;
  COUNT(*)
----------
        30
HR@PROD1> alter table departments rename column department_id to depaertmentid;
Table altered.
HR@PROD1> select count(*) from departments as of timestamp sysdate-1/24;
  COUNT(*)
----------
        30