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