首页 > 代码库 > 【练习】闪回归档
【练习】闪回归档
1.开启归档模式：
16:02:21 SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
2.创建表空间：
16:06:22 SYS@ORA11GR2>create tablespace fb_gd datafile ‘/u01/app/oracle/oradata/ORA11GR2/fb_gd.dbf‘ size 2G autoextend on;
Tablespace created.
3.创建默认闪回归档表，配额512M 保留时间为1年：
16:06:59 SYS@ORA11GR2>create flashback archive default fb_gd tablespace fb_gd quota 512M retention 1 year;
Flashback archive created.
4.为scott用户赋予启用闪回归档数据权限ps： 查看权限名称
16:36:41 SYS@ORA11GR2>select name from system_privilege_map where name like ‘%FLASHBACK%‘;
NAME
----------------------------------------
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ANY TABLE
16:17:49 SYS@ORA11GR2>grant flashback archive on fb_gd to scott;
Grant succeeded.
使用scott用户
16:37:22 SYS@ORA11GR2>conn scott/tiger
Connected.
5.创建表启用闪回归档数据：
16:38:36 SCOTT@ORA11GR2>create table t1 (x int) flashback archive fb_gd;
Table created.
6.对已存在的表启用闪回归档数据：
16:39:52 SCOTT@ORA11GR2>create table t2(x int);
Table created.
16:42:02 SCOTT@ORA11GR2>alter table t2 flashback archive fb_gd;
Table altered.
7.查看启用闪回归档数据的表：
16:42:20 SCOTT@ORA11GR2>select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- -------------
T1 SCOTT
FB_GD
SYS_FBA_HIST_88701 ENABLED
T2 SCOTT
FB_GD
SYS_FBA_HIST_88702 ENABLED
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- -------------
8.禁用闪回归档数据（必须有flashback archive administer权限）：
16:43:06 SCOTT@ORA11GR2>alter table t2 no flashback archive;
alter table t2 no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
16:46:08 SCOTT@ORA11GR2>conn / as sysdba
Connected.
16:48:30 SYS@ORA11GR2>grant flashback archive administer to scott;
Grant succeeded.
16:49:52 SCOTT@ORA11GR2>alter table t2 no flashback archive;
Table altered.
9.插入数据：
16:51:49 SCOTT@ORA11GR2>insert into t1 select deptno from dept;
4 rows created.
16:52:18 SCOTT@ORA11GR2>select * from t1;
X
----------
10
20
30
40
查看scn‘
16:55:46 SYS@ORA11GR2>select current_scn from v$database;
CURRENT_SCN
-----------
1268243
10.删除数据：
16:56:37 SCOTT@ORA11GR2>delete from t1;
4 rows deleted.
16:56:51 SCOTT@ORA11GR2>select count(*) from t1;
COUNT(*)
----------
0
16:56:59 SCOTT@ORA11GR2>commit;
Commit complete.
11.闪回查询：
16:57:35 SCOTT@ORA11GR2>select * from t1 as of scn 1268243;
X
----------
10
20
30
40
查看scott用户下的表
17:23:07 SCOTT@ORA11GR2>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DEPT1 TABLE
EMP TABLE
EMP_ORA11GR2 TABLE
SALGRADE TABLE
SYS_FBA_DDL_COLMAP_88701 TABLE
SYS_FBA_HIST_88701 TABLE
SYS_FBA_TCRV_88701 TABLE
T1 TABLE
T2 TABLE
11 rows selected.
12.开启跟踪：
17:17:06 SCOTT@ORA11GR2>set autotrace on;
闪回查询从闪回归档表查询
17:17:10 SCOTT@ORA11GR2>select * from t1 as of scn 1268243;
X
----------
10
20
30
40
Execution Plan
----------------------------------------------------------
Plan hash value: 1359406288
--------------------------------------------------------------------------------
-------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 25
(8)| 00:00:01 | | |
| 1 | VIEW | | 5 | 65 | 25
(8)| 00:00:01 | | |
| 2 | UNION-ALL | | | |
| | | |
| 3 | PARTITION RANGE SINGLE| | 4 | 164 | 14
(0)| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_88701 | 4 | 164 | 14
(0)| 00:00:01 | 1 | 1 |
|* 5 | FILTER | | | |
| | | |
| 6 | MERGE JOIN OUTER | | 1 | 2053 | 11 (
19)| 00:00:01 | | |
| 7 | SORT JOIN | | 1 | 25 | 7 (
15)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | T1 | 1 | 25 | 6
(0)| 00:00:01 | | |
|* 9 | SORT JOIN | | 1 | 2028 | 4 (
25)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_88701 | 1 | 2028 | 3
(0)| 00:00:01 | | |
--------------------------------------------------------------------------------
13.删除当前undo表空间：
17:34:47 SYS@ORA11GR2>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
17:34:52 SYS@ORA11GR2>create undo tablespace UNDOTBS2 datafile ‘/u01/app/oracle/oradata/ORA11GR2/undotbs——01.dbf‘ size 100M autoextend on next 10M;
Tablespace created.
17:37:52 SYS@ORA11GR2>alter system set undo_tablespace=UNDOTBS2;
System altered.
17:38:33 SYS@ORA11GR2>drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
删除undo表空间也可以闪回查询
17:43:52 SCOTT@ORA11GR2>select * from t1 as of scn 1268243;
X
----------
10
20
30
40
14.开启闪回归档的表不能删除：
19:16:55 SCOTT@ORA11GR2>drop table t1 purge;
drop table t1 purge
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
19:17:43 SCOTT@ORA11GR2>alter table t1 no flashback archive;
Table altered.
19:18:19 SCOTT@ORA11GR2>drop table t1 purge;
Table dropped.
【练习】闪回归档
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。