目录

1.锁机制

2.闪回

（1）查询闪回

（2）表闪回

（3）删除闪回

 （4）回收站管理

（5）数据库闪回

（6）归档闪回

3.Undo表空间

（1）undo表空间管理的优点

（2）undo表空间管理参数

（3）创建undo表空间

（4）修改undo表空间

（5）删除undo表空间

---

Oracle学习的相关知识点（汇总）

Oracle数据库相关事务（一）

Oracle数据库之备份与恢复（二）

1.锁机制

使用锁机制在事务之间提供并行性和完整性，主要用于事务控制。

• 共享锁（Share Lock）。共享锁通过 数据存取的高并行性来实现。如果获得了一 个共享锁，那么用户就可以共享相同的资源。 许多事务可以获得相同资源上的共享锁。例 如，多个用户可以在相同的时间读取相同的 数据。
• 独占锁（Exclusive Lock）。独占锁防止 共同改变相同的资源。假如一个事务获得了 某一资源上的一个独占锁，那么直到该锁被 解除，其他事务才能修改该资源，但允许对 资源进行共享。例如，假如一个表被锁定在 独占模式下，它并不阻止其他用户从同一个 表得到数据。

死锁：

当两个或者多个用户等待其中一个被锁住的资源时，就有可能 发生死锁现象。对于死锁，Oracle自动进行定期搜索，通过回 滚死锁中包含的其中一个语句来解决死锁问题，也就是释放其 中一个冲突锁，同时返回一个消息给对应的事务。用户在设计 应用程序时，要遵循一定的锁规则，尽力避免死锁现象的发生。

锁的类型：

• DML锁：DML锁的目标是保证并行访问的数据完整性，防止同步冲 突的DML和DDL操作的破坏性交互。能够获取独占DML锁的语句有：INSERT、UPDATE、 DELETE和带有FOR UPDATE子句的SELECT语句。
• DDL锁：保护方案对象的定义，调用一个DDL语句将会隐式提交事务。
  • 独占DDL锁。当CREATE、ALTER和DROP等语句用于一个对象时使用该锁。
  • 共享DDL锁。当GRANT与CREATE PACKAGE等语句用于一个对象时使用此锁。
  • 可破的分析DDL锁。库高速缓存区中语句或PL/SQL对象有一个用于它所引 用的每一个对象的锁。
• 内部锁包含内部数据库和内存结构。对用户来说，它们是不可访问的，因为用户不需要控制它们的发生。

为了使事务能够保护表中的DML存取以及防止表中产生冲突的DDL操作，Oracle获得表锁（TM）。

表锁

语句	类型	模式
INSERT	TM	独占（3）（RX）
UPDATE	TM	独占（3）（RX）
DELETE	TM	独占（3）（RX）
SELECT FOR UPDATE	TM	共享（2）（RS）
LOCK TABLE	TM	独占（6）（X）

提示：3表示一行独占锁（RX）；2表示一个行共享锁（RS）；6表示一个独占锁（X）

获得事务锁（TX）。事务锁总是在行级上获得，它独占地锁住 该行，并阻止其他事务修改该行，直到持有该锁的事务回滚或提交数据为止。

事务锁

语句	类型	模式
INSERT	TX	独占（6）（X）
UPDATE	TX	独占（6）（X）
DELETE	TX	独占（6）（X）
SELECT FOR UPDATE	TX	独占（6）（X）

2.闪回

这一点和我们之前所讲的回退有点类似。

闪回数据库是进行时间点恢复的新方法。它能够快速将Oracle数据库恢复到以前的时间，以正确更正由于逻 辑数据损坏或用户错误而引起的任何问题。当需要恢复时，可以将数据库恢复到错误前的时间点，并且只恢 复改变的数据块。

• 查询闪回（Flashback Query）：查询过 去某个指定时间、指定实体的数据，恢 复错误的数据库更新、删除等。
• 表闪回（Flashback Table）：使表返 回到过去某一时间的状态，可以恢复 表、取消对表进行的修改。
• 删除闪回（Flashback Drop）：可以将 删除的表重新恢复。
• 数据库闪回（Flashback Database）：可以将整个数据库回 退到过去某个时间点。
• 归档闪回（Flashback Data Archive）： 可以闪回到指定时间之前的旧数据而不 影响重做日志的策略。

（1）查询闪回

管理员和用户都能够查询过去某些时间点的任何数据，还能够重建和查看由于不小心删除或者更改的数据。

函数：TO_TIMESTAMP和TIMESTAMP

格式：TO_TIMESTAMP(&＃39;timepoint&＃39;,&＃39;format&＃39;)；

提示：timepoint表示某个时间点，format表示把timepoint时间点的格式转化成何种格式。

例子：

第一步：设置时间点

SET TIME ON;

第二步：查询数据

SELECT * FROM DXSB；

第三步：删除表中的数据 

DELETE FROM DXSB;

COMMIT；

  

第四步：查询闪回

select * from dxsb as of timestamp

        to_timestamp(&＃39;2022-10-07 12:28:38&＃39;,&＃39;YYYY-MM-DD HH24:MI:SS&＃39;);

第五步：将闪回的数据重新插回DXSB表中

insert into dxsb

        select * from dxsb as of timestamp

                to_timestamp(&＃39;2022-10-07 12:28:38&＃39;,&＃39;YYYY-MM-DD HH24:MI:SS&＃39;);

（2）表闪回

 利用表闪回可以恢复表，取消对表所进行的修改。表闪回要求用户具有以下权限。

• FLASHBACK ANY TABLE 权限或者该表的Flashback对象 权限。
• 有该表的SELECT、INSERT、 DELETE和ALTER权限。
• 必须保证该表ROW MOVEMENT。

表示闪回特性：

• 在线操作。
• 恢复到指定时间点 或者SCN的任何数据。
• 自动恢复相关属性， 如索引、触发器等。
• 满足分布式的一致 性。
• 满足数据一致性， 所有相关对象的一致性。

提示：要实现表闪回，必须确保与撤销表空间有关的参数设置合理。撤销表空间相关参数为： UNDO_MANAGEMENT、UNDO_TABLESPACE和UNDO_RETENTION。以SYSTEM用户连接数据库，查看相关的撤销表空间的参数。

show parameter undo;

提示： UNDO_RETENTION表示当前所做的增加，删除和修改操作提交后，记录在撤销表空间的数据保留时间。

注意：在创建撤销表空间时，要考虑数据保存的时间长短、每秒产生的块数据量及块大小等。假如表空间大小用 undo表示，那么undo＝UR×UPS×DB_BLOCK_SIZE+冗余量。

其中：

• UR：在undo中保持的最长时间数 （秒），由数据库参数 UNDO_RETENTION值决定。
• UPS：在undo中每秒产生的数据块数量。

FLASHBACK TABLE[用户方案名.]<表名>

        TO {[BEFORE DROP [RENAME TO<新表名>]]

        | [SCN | TIMESTAMP]<表达式>[ENABLE | DISABLE | TRIGGERS]}

• BEFORE DROP： 表示恢复到删除之前。
• RENAME TO：表示 恢复时更换表名。
• SCN：SCN是系统改 变号，可以从 flashback_transaction_quer y数据字典中查到。
• TIMESTAMP：表示 系统邮戳，包含年月日以 及时分秒。
• ENABLE TRIGGERS： 表示触发器恢复之后的状 态为ENABLE。默认为 DISABLE。

例子：利用flashback table恢复表DXSB中删除的数据。

第一步：指定时间点  SET TIME ON

第二步：删除学生信息 DELETE FROM DXSB where sid=&＃39;151306&＃39;;

第三步：授予表DXSB权限ROW MOVEMENT：ALTER TABLE DXSB ENABLE ROW MOVEMENT;

第四步：恢复删除的数据：

FLASHBACK TABLE DXSB TO TIMESTAMP

        TO_TIMESTAMP(&＃39;2022-10-07 13:01:36&＃39;,&＃39;YYYY-MM-DD HH24:MI:SS&＃39;)；

提示：使用TIMESTAMP_TO_SCN函数实现时间戳转换为SCN。

（3）删除闪回

当用户对表进行DDL操作时，它是自动提交的。如果误删了某个表，在Oracle 10g版本之前只能使用日 常的备份恢复数据。但是Oracle提供了更加安全的机制：删除闪回。

• 由于我们删除的东西是放在回收站的，就像我们使用windows的回收站一样，只要你不删除回收站的文件，那么这个文件就一直存在，直到你永久的删除。
• 当用户删除一个表时，Oracle系统会将该表放到回收站中，直到用户 决定永久删除它，使用PURGE命令对回收站空间进行清除，或在出现表空间的空间不足时，它才会被删除。
• 回收站是一个虚拟容器，用于存储所有被删除的对象。为了避免被删除的表与同类对象名称重复，被 删除表（或者其他对象）放到回收站时，Oracle系统对被删除表（或对象名）进行了转换，如：BIN$globalUID$Sversion

第一步：启动回收站管理

alter session set recyclebin=on;

提示：ON表示启用，OFF表示关闭。

第二步：可以查看表是否被删除

select table_name,dropped from user_tables;

第三步：删除闪回的实现

现在用户Scott下面有一个DXSB表，首先将其删除之后，再将其删除闪回

drop table dxsb;

查询回收站字典信息：

select object_name,original_name,type,droptime from recyclebin;

删除闪回

flashback table dxsb to before drop;

或者flashback table BIN$u42pQBRARqOHBxRtzNVy7w==$0 to before drop;

 （4）回收站管理

回收站可以提供误操作后进行恢复的必要信息，但是如果不经常对回收站的信息进行管理，磁盘 空间会被长期占用，因此要经常清除回收站中无用的东西。要清除回收站，可以使用PURGE命令。PURGE命 令可以删除回收站中的表、表空间和索引，并释放表、表空间和索引所占用的空间。

PURGE {

        TABLESPACE<表空间名> USER<用户名> |

        [ TABLE<表名> | INDEX <索引名>] 

        | [ RECYCLEBIN | DBA_RECYCLEBIN ]

}

提示：

• TABLESPACE：指示清 除回收站中的表空间。
• USER：指示清除回 收站中的用户。
• TABLE：指示清除回收站 中的表。
• INDEX：指示清除回收 站中的索引。
• RECYCLEBIN：指的是当 前用户需要清除的回收站。
• DBA_RECYCLEBIN：仅 SYSDBA系统权限才能使用，此参 数可使用户从Oracle系统回收站清 除所有对象。

查看一下Scott用户下的回收站情况

 select object_name,original_name,type,droptime 
    from recyclebin;

 清楚表：DEMO_TABLE

PURGE table demo_table;

（5）数据库闪回

 数据库闪回可以使数据库回到过去某一时间点上或SCN的状态，用户可以不利用备份就能快速地实现时间 点的恢复。

• DB_RECOVERY_FILE_DEST： 确定FLASHBACK LOGS的存 放路径。
• DB_RECOVERY_FILE_DES T_SIZE：指定恢复区的大 小，默认值为空。
• DB_FLASHBACK_RETENTION_ TARGET：设定闪回数据库的 保存时间，单位是分钟，默 认是一天。

注意：如果需要闪回数据库功能，DBA必须正确配置该日志区的大小，最好根据每天数据库块发生改变的数量来 确定其大小。

当用户发布FLASHBACK DATABASE语句后，Oracle系统首先检查所需的归档文件和联机重做日志，如果正常， 则恢复数据库中所有数据文件到指定的SCN或时间点上。

FLASHBACK [ STANDBY | DATABASE<数据库名> {

         TO [ SCN | TIMESTAMP ]<表达式> |

         TO BEFORE [ SCN | TIMESTAMP ]<表达式>

}

• TO SCN：指定 SCN。
• TO IMESTAMP：指 定一个需要恢复的时间点。
• TO BEFORE SCN： 恢复到之前的SCN。
• TO BEFORE TIMESTAMP：恢复数据库 到之前的时间点。

使用FLASHBACK DATABASE，必须以MOUNT启动数据库实例，然后执行ALTER DATABASE FLASHBACK ON 或者ALTER DATABASE TSNAME FLASHBACK ON命令打开数据库闪回功能。ALTER DATABASE FLASHBACK OFF命令是关闭数据库闪回功能。

第一步：设置闪回数据库的路径

查看闪回信息：

show parameter db_recovery_file_dest;

show parameter flashback；

 确定实例是否在归档模式下

select dbid,name,log_mode from v$database;

shutdown immediate;

设置FLASHBACK DATABASE命令启用

STARTUP MOUNT;

ALTER DATABASE FLASHBACK ON;

ALTER DATABASE OPEN;

提示：通过上述过程，对数据库闪回功能的设置， Oracle的数据库闪回功能就会自动收集数据，用 户只要确保数据库是在归档模式下即可。

怎么进入ARCHIVELOG模式

第二步：数据库闪回

SELECT DBID,NAME,LOG_MODE,FROM V$DATABASE;

ARCHIVELOG LIST;

SHOW PARAMETER DB_RECOVERY_FILE_DEST;

第三步：查询当前时间和旧的闪回号。 

SHOW USER;

SELECT SYSDATE FROM DUAL;

ALTER SESSION SET NLS_DATE_FORMAT=&＃39;YYYY-MM-DD HH24:MI:SS&＃39;; SELECT SYSDATE FROM DUAL;

SELECT OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME

        FROM V$FLASHBACK_DATABASE_LOG;

SET TIME ON；

第四步：创建一个临时的表

CREATE TABLE KCB1

        AS SELECT *

                FROM SCOTT.KCB;

第五步：确定时间点并并删除表

SELECT SYSDATE FROM DUAL;

DROP TABLE KCB1;

DESC KCB1 

第六步：以MOUNT打开数据库并进行数据库闪回。

SHUTDOWN IMMEDIATE;

STARTUP MOUNT EXCLUSIVE;

FLASHBACK DATABASE

        TO TIMESTAMP(TO_DATE(&＃39;2015-7-8 15:23:27&＃39;, &＃39;YYYY-MM-DD HH24:MI:SS&＃39;));

--打开数据

ALTER DATABASE OPEN RESETLOGS;

第七步：关闭数据库闪回

ALTER DATABASE FLASHBACK

（6）归档闪回

FLASHBACK DATA ARCHIVE通过将变化数据另外存储到创建的归档闪回中，这样可以通过为归档闪回单独设置存活策略，使数据库可以闪回到指定时间之前的旧数据而不影响重做日志的策略，并且可以根据需要执行那些数据库对象需要保存历史变化的数据，而不是将所有对象的变化数据都保存下来，从而可以极大地减少空间的需求。

CREATE FLASHBACK ARCHIVE [DEFAULT]<闪回归档区名称>

        TABLESPACE<表空间名>

        [QUOTA<数字值> {M | G | T | P} ]

        [RETENTION<数字值> {YEAR | MONTH | D}];

• DEFAULT：指定默 认的闪回数据归档区。
• TABLESPACE： 指定闪回数据归档区存放的表空间。
• QUOTA：指定 闪回数据归档区的最大大小。
• RETENTION：指定归档区可以保留的时间，YEAR、MONTH 和DAY分别表示年、月、日。

例子：创建一个闪回数据归档区，并作为默认的归档区。

第一步：创建闪回数据归档

首先查看自己的表空间：

select tablespace_name

        from dba_tablespaces;

create FLASHBACK ARCHIVE DEFAULT DemoArch
    TABLESPACE USERS
    QUOTA 10M
    RETENTION 1 DAY; 

第二步：归档闪存

使用Scott用户连接数据库，并创建表temp

create table temp as select * from DXSB;

select * from temp;

 对表temp进行归档设置

alter table scott.temp

        flashback archive demoArch;

提示：取消归档设置

alter table scott.temp no

        flashback archive demoArch;

记录SCN

select dbms_flashback.get_system_change_number 
    from dual;

删除temp表中的数据

delete 
    from temp
    where sid=&＃39;151306&＃39;; 

commit；

执行归档闪存查询

select * from scott.temp as of scn 1898692;

3.Undo表空间

为什么要提出undo表空间？

在RBS表空间创建大的回 滚段的方法处理大的事务。但是由于一个事务只可以 使用一个回滚段，当一个回滚段动态扩展超过数据库 文件允许的扩展范围时，将产生回滚段不足的错误， 系统就终止事务。使用自动Undo管理后，一个事务可 以使用多个回滚段。当一个回滚段不足时，Oracle系统 会自动使用其他回滚段，不终止事务的运行。

提示：

允许创建多个Undo表空间，但是同一时间 只能激活一个Undo表空间。使用参数文件中的 Undo_TABLESPACE参数指定要激活的Undo表空间名， Undo表空间的组织和管理由Oracle系统内部机制自动完 成。在自动Undo管理设置完成后，在数据字典 DBA_ROLLBACK_SEGS中可以显示回滚段信息，但是回滚 段的管理由数据库实例自动进行。

（1）undo表空间管理的优点

只要事务没 有提交，与事务有关的数据就一直保存在回滚段中，一旦事务提交，系统立即清除回滚段中的数据。

•  存储非提交或提 交的事务改变块备份。
• 存储数据库改变 的数据行备份（可能 是块级）。
• 存储自从上次提交 以来的事务的快照。
• 在内存中存放逻 辑信息或文件中的非物 理信息。
• 存储一个事务的前映 像（Before Image）。
• 系统撤销数据允 许非提交事务。

（2）undo表空间管理参数

•  Undo_MANAGEMENT：确定 Undo表空间的管理方式，如果该参 数设置为“AUTO”，表示系统使用自 动Undo管理；如果设置为 “MANUAL”，表示使用手动Undo管 理，以回滚段方式启动数据库。
• Undo_TABLESPACE：表示 使用自动Undo管理时，系统默 认Undo表空间名，默认名为 UNDOTBS1。
• Undo_RETENTION：决 定Undo数据的维持时间，即 用户事务结束后，Undo的数 据保留时间，默认值为900s。

设置语句：ALTER SYSTEM SET UNDO_MANAGERMENT=AUTO;

查看undo参数设置：SHOW PARAMETER UNDO;

  

（3）创建undo表空间

CREATE UNDO TABLESPACE<表空间名>

         DATAFILE &＃39;<文件路径>/<文件名>&＃39; [SIZE [ K∣M ]] [ REUSE ]

         [ AUTOEXTEND [ OFF∣ON [ NEXT<磁盘空间大小> [ K∣M ]]

         [ MAXSIZE [ UMLIMITED∣<最大磁盘空间大小> [ K∣M ] ] ] ]

         [ ONLINE∣OFFLINE ]

         [ LOGGING∣NOLOGGING ]

         [EXTENT MANAGEMENT LOCAL AUTOALLOCATE]

说明

• DATAFILE子句：用于为表空间创建数据文件，格式与CREATE DATABASE语句中的DATAFILE子句相同。
• AUTOEXTEND：用于指定是否禁止或允许自动扩展数据文件。若选择OFF，则禁止自动扩展数据文件。 若选择ON，则允许自动扩展数据文件。NEXT指定当需要更多盘区时分配给数据文件的磁盘空间。
• MAXSIZE：指定允许分配给数据文件的最大磁盘空间。其中，UMLIMITED表示对分配给数据文件的 磁盘空间没有设置限制。
• ONLINE和OFFLINE：ONLINE表示在创建表空间之后，使授权访问该表空间的用户立即可用该表空间； OFFLINE表示在创建表空间之后使该表空间不可用，默认为ONLINE。
• LOGGING/NOLOGGING：指定日志属性，它表示将来的表、索引等是否需要进行日志处理。默认值 为LOGGING。
• EXTENT MANAGEMENT：指定如何管理表空间的盘区。
• LOCAL：指定本地管理表空间。
• AUTOALLOCATE：指定表空间由系统管理，用户不能指定盘区尺寸。

例子，创建一个undo表空间demoUndo

CREATE UNDO TABLESPACE DemoUndo
    DATAFILE &＃39;E:\Oracle\UndoSpace\demo01.dbf&＃39; SIZE 100M
    AUTOEXTEND OFF 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

查看表空间情况

SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,CONTENTS
    FROM DBA_TABLESPACES;

undo表空间只能有一个被激活，如果要将新创建的undo表空间进行使用的话，需要进行激活：

ALTER SYSTEM SET UNDO_TABLESPACE=‘<新的undo表空间名>’ 

（4）修改undo表空间

参考表空间的修改

（5）删除undo表空间

未被激活的undo表空间可以被删除：

--再执行删除操作

Drop tablespace DemoUndo;