芜湖建设厅官方网站,京东自营商城官网,阿里云 拦截网站,wordpress更换网站文章目录 1 表空间介绍1.1 简介1.2 表空间分类1.2.1 SYSTEM 表空间1.2.2 SYSAUX 表空间1.2.3 UNDO 表空间1.2.4 USERS 表空间 1.3 表空间字典与本地管理1.3.1 字典管理表空间#xff08;Dictionary Management Tablespace#xff0c;DMT#xff09;1.3.2 本地管理方式的表空… 文章目录 1 表空间介绍1.1 简介1.2 表空间分类1.2.1 SYSTEM 表空间1.2.2 SYSAUX 表空间1.2.3 UNDO 表空间1.2.4 USERS 表空间 1.3 表空间字典与本地管理1.3.1 字典管理表空间Dictionary Management TablespaceDMT1.3.2 本地管理方式的表空间Local Management TablespaceLMT 1.4 表空间的创建与管理1.4.1 表空间的创建1.4.2 Oracle大文件表空间1.4.3 修改表空间1.4.3.1 修改 AUTOEXTEND1.4.3.2 修改表空间数据文件大小 1.4.4 为表空间添加数据文件1.4.5 重命名表空间数据文件1.4.5.1 alter tablespace1.4.5.2 alter database 1.4.6 表空间属性操作1.4.6.1 修改表空间的读写属性1.4.6.2 表空间的脱机与联机1.4.6.3 数据文件的脱机与联机 1.4.7 删除表空间1.4.7.1 删除数据文件1.4.7.2 删除表空间 1.5 设置用户的默认表空间1.5.1 查看数据库的默认表空间1.5.2 修改数据库的默认表空间1.5.3 查看用户的默认表空间1.5.4 创建新用户指定或者不指定默认表空间 1.6 临时表空间管理1.6.1 简介1.6.2 查看临时表空间信息1.6.3 创建临时表空间1.6.4 设置默认临时表空间1.6.5 使用临时表空间组 1.7 undo 表空间管理1.7.1 相关参数1.7.2 管理方式1.7.2.1 AUTO自动管理1.7.2.2 MANUAL手工管理 1.7.3 与 undo 表空间有关的数据字典与动态视图1.7.4 管理 undo 表空间1.7.4.1 新建 undo 表空间1.7.4.2 修改 undo 表空间1.7.4.3 回退信息保留时间相关设置1.7.4.5 切换undo表空间1.7.4.6 删除undo表空间 1.8 undo 表空间的应用闪回技术1.8.1 简介1.8.2 闪回版本查询1.8.3 闪回事务查询Flashback Transaction Query1.8.4 闪回删除1.8.4.1 禁用和启用回收站1.8.4.2 查看回收站中的信息1.8.4.3 使用闪回删除恢复被删除Drop的表1.8.4.4 清除回收站中的对象 1.8.5 闪回表1.8.6 闪回数据归档Flashback Data Archive 1.8.6 闪回数据库1.8.6.1 简介1.8.6.2 操作 1 表空间介绍
1.1 简介
Oracle数据库开创性地提出了 表空间tablespaces 的设计理念Oracle中很多优化都是基于表空间实现的。表空间是ORACLE数据库恢复的最小单位容纳着许多数据库对象如表、视图、索引、聚簇、回退段和临时段等。 Oarcle 数据库真正存放数据的是数据文件表空间tablespaces实际上是一个逻辑的概念在物理上并不存在。
Oracle 使用表空间将相关的逻辑结构如段、数据区等组合在一起表空间是数据库的最大逻辑划分区域通常用来存放数据表、索引、回滚段等数据对象任何数据对象在创建时都必须被指定存储在某个表空间中。
表空间属逻辑存储结构与数据文件属物理存储结构相对应一个Oracle数据库可以有一个或多个表空间一个表空间由一个或多个数据文件组成一个数据文件只属于一个表空间 Oracle 数据的存储空间在逻辑上表现为表空间而在物理上表现为数据文件。 举个例子来说表空间相当于操作系统中的文件夹而数据文件就相当于文件夹中的文件。每个数据库至少有一个表空间即 SYSTEM 表空间 表空间的大小等于所有从属于它的数据文件大小的总和。
由于表空间在物理上即磁盘上包含操作系统中的一个或多个数据文件因此在表空间中创建的数据对象就存在以下两种情况
若表空间只包含一个数据文件则该表空间中的所有对象都存储在这个数据文件中。若表空间包含多个数据文件则 Oracle 即可将数据对象存储在该表空间的任意一个数据文件中也可以将同一个数据对象中的数据分布在表空间的多个数据文件中。
在创建数据库时 Oracle 系统会自动创建多个默认的表空间这些表空间除了用于管理用户数据的表空间之外还包括用于管理Oracle系统内部数据(如数据字典)的表空间。
1.2 表空间分类
表空间分为系统表空间system 表空间、sysaux 表空间和非系统表空间UNDO表空间、临时表空间、用户表空间、自定义表空间。对应数据文件在$ORACLE_HOME\oradata\orcl下分别为SYSTEM01.DBFSYSAUX01.DBFUNDOTBS01.DBF TEMP01.DBFUSERS01.DBF。
表空间相关的视图包括DBA_TABLESPACESUSER_TABLESPACES、BA_DATA_FILES 与数据文件相关视图V$DATAFILE 与临时文件相关视图V$TEMPFILEDBA_TEMP_FILES
查看表空间
SQL select name from v$tablespace;
SQL select tablespace_name, file_name from dba_data_files;1.2.1 SYSTEM 表空间
SYSTEM 表——系统表空间用于存放 Oracle 系统内部表和数据字典的数据如表名、列名、用户名等系统表空间必须在任何时候都处于可用状态也是数据库运行的必要条件。该表空间一旦损坏数据库将无法打开。
Oracle 本身不赞成将用户创建的表、索引等存放在系统表空间中。表空间中的数据文件个数不是固定不变的可以根据需要向表空间中追加新的数据文件
1.2.2 SYSAUX 表空间
SYSAUX 表空间是 Oracle 11g 新增加的表空间是随着数据库的创建而创建的它充当 SYSTEM的辅助表空间降低了 SYSTEM 表空间的负荷主要存储除数据字典以外的其他数据对象。 Oracle 将工具放到 sysaux 表空间以减轻 system 表空间的压力。 SYSAUX表空间一般不存储用户的数据由 Oracle 系统内部自动维护。
1.2.3 UNDO 表空间
UNDO 表空间——撤销表空间用于存储撤销信息的表空间。当用户对数据表进行修改操作包括插入、更新、删除等操作时 Oracle 系统自动使用撤销表空间来临时存放修改前的旧数据。当所做的修改操作完成并执行提交命令后 Oracle 根据系统设置的保留时间长度来决定何时释放掉撤销表空间的部分空间。undo 表空间用于事务的回滚实例恢复回滚一致性读时需要构造 CR 块。
一般在创建 Oracle 实例后 Oracle 系统自动创建一个名字为“UNDOTBS1”的撤销表空间该撤销表空间对应的数据文件是“UNDOTBS01.DBF”。
回退段的作用如下
为事务提供回退 当事务执行失败或用户执行回滚操作rollback时Oracle 会利用保存在回退段中的信息将数据恢复到原来的值实例恢复 当数据库实例运行失败在数据库重新启动时Oracle 先利用重做日志文件的信息对数据库进行恢复再利用回退段中的信息回滚未提交的事务提供读一致性 当用户对数据进行修改时会预先将其原始值保存到回退段中。此时如果有其它用户访问该数据则访问回退段中的信息使当前用户未提交的修改其他用户无法看到保证数据的一致性提供对 DML 操作的闪回处理 通过保留在回退段中的信息用户可以查询某个数据在过去某个时刻的状态。
回退段的工作方式
当事务开始时系统分配给该事务一个回退段。在事务的整个生命周期中当数据发生改变时数据的原始值被复制到回退段中。回退段采用循环写的方式进行工作当事务写满回退段的一个区之后会接着写入回退段的下一个区当所有的区都写满后事务开始循环写入到第一个区或者分配新的区extent
1.2.4 USERS 表空间
USERS 表空间——用户表空间是 Oracle 建议用户使用的表空间可以在这个表空间上创建各种数据对象比如创建表、索引、用户等数据对象。
Oracle 系统的样例用户 SCOTT 对象就存放在 USERS表空间中。除了 Oracle 系统默认创建的表空间外用户可根据应用系统的实际情况及其所要存放的对象类型创建多个自定义的表空间以区分用户数据与系统数据。此外不同应用系统的数据应存放在不同的表空间上而不同表空间的文件应存放在不同的盘上从而减少 I/O 冲突提高应用系统的操作性能。
1.3 表空间字典与本地管理
在表空间中区是最小的空间分配单位对表空间的管理是以区为单位进行的。由于区extent是 Oracle 创建对象时的最小分配单元所以表空间的管理实际上就是针对区的管理。 根据管理方式不同表空间分为本地管理表空间Oracle11g 默认采用本地管理方式的表空间和字典管理表空间
1.3.1 字典管理表空间Dictionary Management TablespaceDMT
由数据字典管理区。数据字典的信息存储在 system 表空间中Oracle 服务器将在分配或回收区时更新数据字典中对应的表。使用数据字典管理存储空间的分配当表空间分配新的区或者回收已分配的区时Oracle 会对数据字典对应的表进行查询、更新并且会产生回退和重做信息。字典管理方式较为灵活但效率要低得多。
用于管理表空间的数据字典表分别为
UET UsedExtents已使用的空间 FETFreeExtents空闲空间。
查看表空间管理方式 select tablespace_name,EXTENT_MANAGEMENT from dba_tablespaces;EXTENT_MANAGEMENT 如果显示为 DICTIONARY 就是字典管理表空间。
字典管理表空间的工作方式 当建立一个新的段或者段在表空间中请求新的空间时Oracle 通过执行一系列的 SQL 语句来完成这个工作这些工作包括从 FET$ 中找到可用的自由空间移动或增加相应的行到 UET$ 中并在 FET$ 中删除相应的记录当删除一个段的时候Oracle 则移动 UET$ 中相应的行到 FET$。这个过程的发生是连续的、串行的在繁忙的数据库中这类操作极可能导致竞争和等待产生数据字典的争用 另一方面当数据字典的表的信息被修改时系统同样要记录 undo 和 redo 信息频繁的修改又不可避免地对整个数据库的性能产生影响。
字典管理表空间面临的另外一个严重问题是空间碎片。
1.3.2 本地管理方式的表空间Local Management TablespaceLMT
从Oracle 8i 开始Oracle 引入了一种全新的表空间管理方式本地管理表空间。 所谓本地化管理就是指 Oracle 不再利用数据字典表来记录 Oracle 表空间里面的区间的使用状况而是 在每个表空间的数据文件的头部加入了一个位图区域在其中记录每个 Extent 的使用状况。每当一个 Extent 被使用或者被释放以供重新使用时Oracle 都会更新数据文件头部的这个记录反映这个变化。
在创建本地管理表空间时还可以选择更具体的空间分配方式自动分配autoallocate和统一尺寸uniform。若为自动分配则让 Oracle 来决定区块的使用办法缺省地 Oracle 会按照递增算法来分配空间如果选择统一尺寸则还可以详细指定每个区间Extent的大小。
在本地管理表空间的空间管理上Oracle 将存储信息保存在表空间头部的位图中而不是保存在数据字典中。通过这样的方式在分配或者回收空间的时候表空间就可以独立地在数据文件头部完成操作而无需与其他对象打交道。因为仅仅操作数据文件头部的几个数据块不用操作数据字典所以 Oracle 在本地管理的表空间中添加、删除段时效率要比字典管理的表空间快特别是在并发性很强的空间请求中。
本地管理表空间技术的主要优点
通过位图代替字典管理。 本地管理的表空间用数据文件头部的位图块来记录和管理空间的分配和回收避免了递归的空间管理操作从而避免了字典操作以及因为字典操作而带来的性能问题。避免了碎片问题及空间浪费。 本地化管理可以通过 UNIFORM 或 AUTOALLOCATE 的方式进行区间管理通过对Extent 进行 UNIFORM 约定传统的空间碎片问题得以解决进而空间的分配效率也大大提高。
查看表空间管理方式
SQL select tablespace_name,EXTENT_MANAGEMENT,allocation_type from dba_tablespaces;1.4 表空间的创建与管理
1.4.1 表空间的创建
Oracle 使用 create tablespace 命令创建表空间该命令的语法格式如下
create [TEMPORARY] tablespace tablespace_name ----- 表空间的名称
TEMPFILE | datafile .../*.dbf ----- 数据文件的路径和名称
size x M|G ----- 数据文件的初始大小
autoextend on next x M|G ----- 数据文件每次自动扩展多少每次自动扩展多少
maxsize unlimited | x M|G; ----- 数据文件的最大尺寸说明TEMPORARY 选项用于创建临时表空间同时使用 TEMPFILE 指定文件名。 举例
CREATE TABLESPACE tbs01
Datafile D:\oracle\product\10.2.0\oradata\orcl\tbs01.dbf SIZE 100M;可加入Autoextend On Next 100M Maxsize Unlimited否则会由于空间不足报ORA-01659错误表示无法分配超出的MINEXTENTS。
表空间默认区大小为64KB可通过UNIFORM SIZE 128k来指定区大小为128k或使用AUTOALLOCATE区的大小由数据库根据情况指定。此时需要加入EXTENT MANAGEMENT LOCAL表示表空间的区管理方式为本地管理否则为字典管理
1.4.2 Oracle大文件表空间
Oracle大文件表空间只包含一个数据文件这个数据文件可包含4GB个数据块一个数据文件大小可达32TB(8KB*4GB)。区Extent管理方式为本地管理LOCAL段SEGMENT管理方式为自动管理AUTO。
建立大表空间语句如下
Create Bigfile Tablespace btbs1 Datafile
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\BTBS01. DBF Size 200M;1.4.3 修改表空间
1.4.3.1 修改 AUTOEXTEND
表空间的大小由数据文件的个数和数据文件的大小决定可通过以下方法调整
使用重置数据文件的大小为表空间添加新的数据文件。
可以指定数据文件的 AUTOEXTEND 子句启用或禁用数据文件的自动扩展。文件将按指定的增量增加直到达到指定的最大值。使用AUTOEXTEND 子句的优点如下
当表空间的空间用尽时无需过多的直接干预确保应用程序不会由于未能分配区而暂停。
关闭数据文件的自动扩展属性
ALTER DATABASE DATAFILE DATA/orcl/datafile/ts001.dbf AUTOEXTEND OFF;查看数据文件的自动扩展属性
select tablespace_name, file_name,autoextensible from dba_data_files;重新开启数据文件的自动扩展属性
ALTER DATABASE DATAFILE DATA/orcl/datafile/ts001.dbf AUTOEXTEND on next 100M;1.4.3.2 修改表空间数据文件大小
尽管指定自动扩展选项可以使得数据文件在数据写满的情况下自动扩展但自动扩展导致递归空间操作从而降低系统性能。可以使用 ALTER DATABASE 命令手动增加或减少数据文件的大小。命令格式如下
ALTER DATABASE DATAFILE file_name RESIZE x K|M;示例
Alter Database Datafile
D:\oracle\product\10.2.0\oradata\orcl\tbs01.dbf Resize 200M;alter database datafile DATA/orcl/datafile/ts001_2.dbf resize 200M;1.4.4 为表空间添加数据文件
可以通过 ALTER TABLESPACE ADD DATAFILE 命令向表空间添加数据文件以增加表空间的容量。命令格式如下
ALTER TABLESPACE tablespace_name ADD DATAFILE filespec [autoextend_clause];示例
SQL alter tablespace ts001 add datafile DATA/orcl/datafile/ts001_2.dbf size 100M autoextend on next 50m;
Tablespace altered.表空间不足时增加数据文件可增加1个或多个
ALTER TABLESPACE tbs01 add Datafile
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS01-1.DBF Size 50M,
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS01-2.DBF Size 50M;1.4.5 重命名表空间数据文件
1.4.5.1 alter tablespace
使用 alter tablespace 命令——不需要停库 命令格式如下
ALTER TABLESPACE tablespace_name RENAME DATAFILE file_name1 TO file_name2;说明
该方法不能移动 SYSTEM 表空间中的数据文件。源文件名必须与存储在控制文件内的文件名称匹配。表空间必须脱机并且 TO 子句后的目标数据文件必须存在。
使用该命令重命名数据文件的步骤如下
使表空间脱机。使用操作系统命令移动或复制文件。执行 ALTER TABLESPACE RENAME DATAFILE 命令。使表空间联机。
示例如下
-- 数据库在 open 状态
--1、设置表空间为 offline 状态
SQL alter tablespace ts001 offline;
Tablespace altered.--2、在操作系统下修改文件名
ASMCMD cp DATA/orcl/datafile/ts001.dbf DATA/orcl/datafile/ts001_1.dbf
copying DATA/orcl/datafile/ts001.dbf - DATA/orcl/datafile/ts001_1.dbf
ASMCMD rm DATA/orcl/datafile/ts001.dbf--3、更新控制文件中的数据文件信息
SQL alter tablespace ts001 rename DATAFILE DATA/orcl/datafile/ts001.dbf to DATA/orcl/datafile/ts001_1.dbf;
Tablespace altered.--4、设置表空间为 online 状态
SQL alter tablespace ts001 online;
Tablespace altered.1.4.5.2 alter database
使用 alter database 命令——需要停库 命令格式如下
ALTER DATABASE RENAME FILE file_name1 TO file_name2;说明该命令可用来移动任意类型的数据文件数据库必须处于 mount 状态且目标数据文件必须存在。操作步骤如下
关闭数据库。使用操作系统命令移动文件。启动数据库到 mount 状态。执行 ALTER DATABASE RENAME FILE 命令。打开数据库。
示例如下
--1、关闭数据库
SQL shutdown immediate--2、移动 ts001 表空间对应的数据文件
ASMCMD cp DATA/orcl/datafile/ts001_1.dbf DATA/orcl/datafile/ts1_001.dbf
copying DATA/orcl/datafile/ts001_1.dbf - DATA/orcl/datafile/ts1_001.dbf
ASMCMD rm DATA/orcl/datafile/ts001_1.dbf
ASMCMD cp DATA/orcl/datafile/ts001_2.dbf DATA/orcl/datafile/ts1_002.dbf
copying DATA/orcl/datafile/ts001_2.dbf - DATA/orcl/datafile/ts1_002.dbf
ASMCMD rm DATA/orcl/datafile/ts001_2.dbf--3、启动到数据库到 mount 状态
SQL startup mount--4、使用 alter database 命令更改控制文件中数据文件的位置信息
SQL alter database rename file DATA/orcl/datafile/ts001_1.dbf to DATA/orcl/datafile/ts1_001.dbf;
SQL alter database rename file DATA/orcl/datafile/ts001_2.dbf to DATA/orcl/datafile/ts1_002.dbf;--5、启动数据库到 open 状态
SQL alter database open;1.4.6 表空间属性操作
1.4.6.1 修改表空间的读写属性
ALTER TABLESPACE tablespace_name READ ONLY| READ WRITE; 说明执行该命令时与该表空间相关的所有事务会自动回滚不允许再对该表空间进行任何写入操作。 此时用户只能在该表空间的对象上执行查询操作而不能执行 DML 或 DDL 操作。但可以执行 DROP TABLE 或 DROP INDEX 删除该表空间上的表或索引。 因为 DROP 命令只更新数据字典数据字典位于 system 表空间而不更新只读表空间上的物理文件。
1.4.6.2 表空间的脱机与联机
通过将表空间设置为联机或脱机状态可以控制表空间的可用性。当表空间处于联机状态时用户可以访问其中的数据。当表空间处于脱机状态时用户无法访问它的数据。表空间脱机一般用于以下几种情况
使数据库的一部分表空间不可用但允许正常访问数据库的其余表空间备份表空间时在数据库打开时恢复表空间或数据文件在数据库打开时移动数据文件
使表空间脱机和联机的命令如下
ALTER TABLESPACE tablespace_name ONLINE | OFFLINE [NORMAL|TEMPORARY|IMMEDIATE|FOR RECOVER]}说明
NORMAL默认设置将表空间中所有数据文件内的所有块从 SGA 中写入数据文件并将数据文件关闭。在使该表空间重新联机之前无须对其执行介质恢复。TEMPORARY对表空间内的所有联机数据文件执行检查点操作但是在执行检查点时并不检查数据文件的状态即使某些文件无法写入检查点Oracle 也会忽略这些错误。在重新联机之前所有脱机文件可能都需要进行介质恢复。IMMEDIATE不保证表空间文件可用而且不执行检查点操作。在使表空间重新联机前必须对其执行介质恢复操作。FOR RECOVER使表空间脱机以进行表空间时间点恢复。SYSTEM表空间无法设置为脱机状态。 当一个表空间脱机后Oracle 服务器将使与之相关联的所有数据文件脱机。
1.4.6.3 数据文件的脱机与联机
与表空间类似可以把数据文件设置为联机或脱机状态。脱机的数据文件对于数据库来说是不可用的直到它们被恢复为联机状态为止。
如果数据文件发生损坏时Oracle 会自动将这个数据文件设置为脱机状态并且记录在警告文件中。如果损坏的文件恢复后需要以手工方式重新将数据文件恢复为联机状态。将数据文件设置为脱机状态不会影响表空间的状态但是反过来将表空间设置为脱机状态后属于该表空间的数据文件同时会进入脱机状态。可以使用 ALTER ATABASE 命令改变数据文件的状态
--把表空间 ts001 对应的数据文件设置为脱机状态
SQL ALTER DATABASE DATAFILE DATA/orcl/datafile/ts001.dbf OFFLINE;1.4.7 删除表空间
1.4.7.1 删除数据文件
从表空间中删除数据文件命令如下
alter tablespace tablespace_name drop datafile file_name;1.4.7.2 删除表空间
可以通过 DROP TABLESPACE 命令从数据库中删除表空间。删除表空间时需要注意以下问题
如果表空间包含数据段必须使用 INCLUDING CONTENTS 选项。删除表空间后数据将不再包含在数据库内。删除表空间时只删除关联数据库控制文件内的文件指针。操作系统文件仍然存在可以使用 AND DATAFILES 子句同时删除操作系统文件。表空间切换到只读状态仍可以删除该表空间以及其中的段。删除表空间之前建议将表空间脱机以确保没有事务处理访问该表空间内的任何段。SYSTEM 表空间不能被删除。
删除表空间的命令如下
DROP TABLESPACE tablespace_name [INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]说明
INCLUDING CONTENTS删除表空间内的所有段。AND DATAFILES删除关联的操作系统文件。CASCADE CONSTRAINTS如果表空间之外的表引用了该表空间内表的主键和唯一键则删除引用完整性约束。
示例
DROP TABLESPACE tbs01 INCLUDING CONTENTS AND DATAFILES;1.5 设置用户的默认表空间
在Oracle11g 以前如果创建用户时未使用 DEFAULT TABLESPACE 子句指定默认表空间则将 SYSTEM 表空间作为它们的默认表空间。 在Oracle 10g 中定义了数据库级别的默认表空间 USERS在创建用户时如果没有定义默认表空间就会把数据库级别的默认表空间当作用户的默认表空间。
1.5.1 查看数据库的默认表空间
SELECT PROPERTY_VALUEFROM database_propertiesWHERE PROPERTY_NAME DEFAULT_PERMANENT_TABLESPACE;说明
如果在创建用户时没有指定用户表空间则默认会使用数据库的默认表空间如果我们修改了数据库的默认表空间用户的表空间也会发生改变。如果在创建用户时指定用户的表空间是其他的表空间修改数据库的默认表空间不会影响用户的表空间。数据库的默认表空间不能删除除非将默认表空间指向其他表空间之后才可以删除。如果用户的默认表空间指向其他的表空间当这个表空间被删除之后用户的默认表空间会自动指向数据库的默认表空间。
1.5.2 修改数据库的默认表空间
SQL ALTER DATABASE DEFAULT TABLESPACE ts001;
Database altered.1.5.3 查看用户的默认表空间
当前用户
SQL select default_tablespace from user_users;所有用户
SQL select user_id, username, default_tablespace from dba_users;1.5.4 创建新用户指定或者不指定默认表空间
不指定默认表空间
SQL create user john identified by john;指定默认表空间
SQL create user tom identified by tom default tablespace ts001;1.6 临时表空间管理
1.6.1 简介
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象。如果 Oracle 需要执行排序操作时并且当 PGA 中 sort_area_size 大小不够时就会把数据放入临时表空间里进行排序。当操作完成后系统会自动清理临时表空间中的临时对象自动释放临时段。和永久表空间不同的地方在于它是由临时数据文件temporary files组成的而不是永久数据文件datafiles。临时表空间不会存储永久类型的对象因此不需要备份。
此外对临时数据文件的操作不产生 redo 日志但会生成 undo 日志。
临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果。临时表空间不会存储永久类型的对象所以它不会也不需要备份。
1.6.2 查看临时表空间信息
查看临时空间
SQL select tablespace_name,contents from dba_tablespaces;临时文件
select file#, ts#, name from v$tempfile;查看数据库默认的临时表空间
SELECT PROPERTY_VALUE FROM database_properties
WHERE PROPERTY_NAME DEFAULT_TEMP_TABLESPACE;1.6.3 创建临时表空间
创建临时表空间的语法和永久表空间相似命令如下
SQL create temporary tablespace temp02 tempfile DATA/orcl/tempfile/temp02.dbf size 50M autoextend on next 50M;查看永久表空间及其对应的临时文件
SQL select file_id, file_name, tablespace_name from dba_temp_files;此外为临时表空间添加数据文件、更改数据文件的大小、删除表空间对应的数据文件等操作与永久表空间的对应操作也是基本相同的。
1.6.4 设置默认临时表空间
默认情况下用户的默认临时表空间自动继承数据库的默认临时表空间。
--查看数据库的默认临时表空间
SQL select * from database_properties where property_name like %TABLESPACE%;--查看某个用户的默认临时表空间
SQL select user_id,username,default_tablespace,temporary_tablespace from dba_users where usernameJOHN;修改数据库的默认临时表空间
SQL alter database default temporary tablespace temp02;1.6.5 使用临时表空间组
临进表空间组是 Oracle10g 开始引入的一个新特性它是一个逻辑概念不需要显示的创建和删除。只要把一个临时表空间分配到一个组中临时表空间组就自动创建所有的临时表空间从临时表空间组中移除就自动删除。如果删除一个临时表空间组的所有成员该组也自动被删除。
临时表空间的名字不能与临时表空间组的名字相同可以在创建临时表空间是指定表空间组。
临时表空间的作用 可以把用户的默认临时表空间指定为一个临时表空间组当此用户建立多个连接时如果用到临时表空间不同的连接将会使用临时表空间组中的不同临时表空间。
创建临时表空间同时加入临时表空间组
SQL CREATE TEMPORARY TABLESPACE TEMP03 TEMPFILE DATA/orcl/tempfile/temp03.dbf SIZE 10M TABLESPACE GROUP temp_group;查看临时表空间组
SQL select * from dba_tablespace_groups;把表空间加入临时表空间组
SQL ALTER TABLESPACE TEMP02 TABLESPACE GROUP TEMP_GROUP;
Tablespace altered.把临时表空间从临时表空间组中移除
ALTER TABLESPACE TEMP03 TABLESPACE GROUP ;指定用户的默认临时表空间为临时表空间组
SQL alter user john temporary tablespace TEMP_GROUP;1.7 undo 表空间管理
1.7.1 相关参数
与 undo 表空间有关的初始化参数
SQL show parameter undo_
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1说明
undo_managementUNDO 表空间回退段的管理方式AUTO自动管理MANUAL手动管理。undo_retention事务提交后相应的 UNDO 数据保留的时间单位秒。undo_tablespace指定使用的 undo 表空间
1.7.2 管理方式
SQL show parameter undo_managementrollback segment 管理方式有两种AUTO自动管理默认方式MANUAL手动管理。
1.7.2.1 AUTO自动管理
自动管理方式下由初始化参数 undo_tablespace 指定一个 undo 表空间作为默认使用的 undo 表空间。
自动管理模式下创建 undo 表空间 undotbs11
SQL create undo tablespace undotbs11datafile DATA/orcl/datafile/undotbs11 size 50mautoextend on next 50m;把 undo 表空间切换为 undotbs11
SQL alter system set undo_tablespace undotbs11;
SQL show parameter undo_tablespace--查看回退段
SQL select segment_name,tablespace_name,status from1.7.2.2 MANUAL手工管理
把回退段的管理方式修改为手工管理只需要把初始化参数 undo_management 修改为 manual 即可。 当回退段的管理方式为自动管理并且由于回退段出现问题而导致数据库无法启动时可以把回退段管理方式设置为手工管理数据库启动后再修改为自动管理。 注集群环境下修改回退段管理方式会影响到所有节点
初始化参数 undo_management 为静态参数修改之后需要重启数据库才能生效。
SQL alter system set undo_management manual scope spfile;注意回退段管理方式修改为手工管理之后发现原有的回退段全部变成了 OFFLINE 状态system 除外。此时只有系统表空间的对象能够进行数据修改操作用户表空间的对应无法进行数据修改操作。
update e01 set salsal1;
update e01 set salsal1*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace USERS出现错误由于 emp 表不在系统表空间因此没有回退段可用导致数据修改无法进行。
把表 e01 移动到 system 表空间
SQL alter table e01 move tablespace system;
Table altered.在回退段管理方式修改为手工管理之后需要手工创建回退段并设置为 online。
步骤1创建回退段
SQL create rollback segment undo_segs001 tablespace UNDOTBS1;
Rollback segment created.SQL create rollback segment undo_segs002 tablespace UNDOTBS1;
Rollback segment created.步骤2查看新建的回退段的状态OFFLINE
SQL select segment_name, tablespace_name,status from dba_rollback_segs;
UNDO_SEGS002 UNDOTBS1 OFFLINE
UNDO_SEGS001 UNDOTBS1 OFFLINE步骤3修改回退段的状态为 ONLINE
SQL alter rollback segment UNDO_SEGS001 online;
SQL alter rollback segment UNDO_SEGS002 online;步骤4把回退段信息添加到参数文件否则重启后会自动变成 offline
SQL alter system set rollback_segmentsUNDO_SEGS001,UNDO_SEGS002,UNDO_SEGS003,UNDO_SEGS004,UNDO_SEGS005 scopespfile;1.7.3 与 undo 表空间有关的数据字典与动态视图
查看所有表空间信息
select * from v$tablespace;查看表空间的类型
select tablespace_name,contents from dba_tablespaces;查看表空间对应的数据文件
select tablespace_name,file_name from dba_data_files;查看 undo 表空间所包含的回退段及回退段的大小等特征。
select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs ;查看所有表空间所包含的段及段的大小等特征。
select owner,segment_name,bytes/1024/1024 mb
from dba_segments where tablespace_name like %UNDO%;查询 undo 表空间中区的大小与状态信息。
select tablespace_name,segment_name,extent_id,status from dba_undo_extents;undo表空间中区的状态status 一共有3种EXPIRED、UNEXPIRED、ACTIVE。
EXPIRED表示该回退信息对应的事务已经提交保存时间超过保留区
UNEXPIRED表示该回退信息对应的事务已经提交保存时间没有超过保留区
ACTIVE表示回退信息对应的事务还没有提交该区还在使用查询回退段的名称
select * from v$rollname;查询 undo 表空间回退段的性能统计信息。
select v$rollname.*,v$rollstat.extents Extents,v$rollstat.rssize Size_in_Bytes,v$rollstat.xacts XActs,v$rollstat.gets Gets,v$rollstat.waits Waits,v$rollstat.writes Writesfrom v$rollstat, v$rollnamewhere v$rollstat.usn() v$rollname.usn;查询 undo 表空间中回退段的性能统计信息。
SELECT TO_CHAR(BEGIN_TIME, MM/DD/YYYY HH24:MI:SS) BEGIN_TIME, TO_CHAR(END_TIME, MM/DD/YYYY HH24:MI:SS) END_TIME, UNDOBLKS, MAXQUERYLEN, TUNED_UNDORETENTION FROM v$UNDOSTATWHERE ROWNUM 10; 查询事务所使用的回退段信息。查询哪些用户使用了回退段
select a.username,b.name,c.used_ublk
from v$session a,v$rollname b,v$transaction cwhere a.saddrc.ses_addr and b.usnc.xidusn;1.7.4 管理 undo 表空间
1.7.4.1 新建 undo 表空间
CREATE UNDO TABLESPACE tablespace_name
DATAFILE path/filename SIZE x [K | M | G]
[AUTOEXTEND] [OFF | ON] NEXT x [K | M | G] MAXSIZE [UNLIMITED | x [K | M | G] ]
[EXTENT MANAGEMENT LOCAL] [AUTOALLOCATE]
[RETENTION GUARANTEE | NOGUARANTEE]1.7.4.2 修改 undo 表空间
可以使用 alter tablespace 修改 undo 表空间允许对 undo 表空间进行如下操作
为 undo 表空间添加数据文件重命名 undo 表空间的数据文件将 undo 表空间的数据文件联机或脱机启用或禁用保护回退信息在回退段中的保留时间。
其中为 undo 表空间添加数据文件、重命名 undo 表空间的数据文件、将 undo 表空间的数据文件联机或脱机等三个操作和永久表空间的操作完全相同。
1.7.4.3 回退信息保留时间相关设置
回退信息保留时间Oracle 根据 undo 表空间的大小以及事务量的多少自动调整回退信息的保留时间可通过调整初始化参数 undo_retention 设置回退信息在回退段中的保留时间
ALTER SYSTEM SET UNDO_RETENTION 1800;当用户将 UNDO_RETENTION 参数设置为 1800s 以后Oracle 会尽量的将回退信息保存 1800s。但在操作过程中如果回退表空间不够用了新的回退信息依然会将未达到 1800s 的回退信息覆盖。
我们也可以指定回退信息必须保留到 undo_retention 规定的时间通过启用 undo 表空间的 retention guarantee 特性保证只有过期已提交且达到 undo_retention 设定的值的数据才会被覆盖即使 undo 表空间容量已经不足也不会覆盖未过期的回退信息。
查看undo 表空间的 retention guarantee 属性
select tablespace_name, retention from dba_tablespaceswhere tablespace_name like %UNDO%;修改undo 表空间的 retention guarantee 属性
ALTER TABLESPACE UNDOTBS101 RETENTION GUARANTEE;重新查看 undo 表空间的 retention guarantee 属性
select tablespace_name, retention from dba_tablespaceswhere tablespace_name like %UNDO%;1.7.4.5 切换undo表空间
在数据库运行过程中可以从一个 undo 表空间切换到另一个 undo 表空间由于初始化参数 undo_tablespace 是一个动态参数直接修改即可无需重启实例。
在以下情况下undo 表空间切换会发生错误
指定的 undo 表空间不存在指定的表空间不是 undo 表空间指定的 undo 表空间正在被其它实例使用。
在完成 undo 表空间的切换后任何新的事务的回退信息都会进入新的 undo 表空间中如果旧的 undo 表空间还存在未提交的事务则旧的 undo 表空间进入挂起脱机状态挂起脱机状态的 undo 表空间不能被新的事务使用也不能删除。当前未提交的事务将继续使用该表空间。当所有事务都提交完成后旧的 undo 表空间进入脱机状态。
切换方式如下
SQL ALTER SYSTEM SET UNDO_TABLESPACE UNDOTBS101;查看回退段的状态
select owner,segment_name,tablespace_name,status from dba_rollback_segs;1.7.4.6 删除undo表空间
与普通表空间一样可以使用 drop tablespace 来删除 undo 表空间但是不能删除当前正在使用的 undo 表空间。如果在 undo 表空间中含有任何未提交的事务的回退信息则不能删除表空间。
此外即使已经删除了 undo 表空间在该表空间中也可能存在未过期的回退信息这样导致某些查询所需的回退信息丢失。删除 undo 表空间命令如下
drop tablespace UNDOTBS101 including contents and datafiles;
drop tablespace UNDOTBS101 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace UNDOTBS101 is currently in use无法删除要删除的表空间时当前正常使用的表空间重新把表空间切换为 UNDOTBS1
ALTER SYSTEM SET UNDO_TABLESPACE UNDOTBS1;重建删除 undo 表空间 UNDOTBS101
drop tablespace UNDOTBS101 including contents and datafiles;1.8 undo 表空间的应用闪回技术
1.8.1 简介
为了使Oracle 数据库能够从任何逻辑操作中迅速恢复Oracle 提供了闪回技术。在数据库发生逻辑错误的时候闪回技术能提供快速且最小损失的恢复。大部分闪回技术都需要依赖回退段rollback segment中的原始数据。事务启动时Oracle 会为其分配一个回退段。回退数据是反转 DML语句结果所需的信息只要某个事务修改了数据那么更新前的原有数据就会被写入一个回退段。 闪回技术包括以下几种
闪回数据库Flashback Database使数据库迅速地回滚到以前的某个时间点或某个 SCN 值的状态闪回删除Flashback Drop类似于操作系统的回收站功能可以恢复被 drop 的表或索引。该功能基于 undo 数据闪回查询Flashback Query查询过去某个时间点或某个 SCN 值时表中的数据闪回版本查询Flashback Version Query查询过去某个时间段或某个 SCN 段内表中数据的变化情况闪回事务查询Flashback Transaction Query查询某个事务或所有事务在过去一段时间对数据进行的修改闪回表Flashback Table将表恢复到过去的某个时间点或某个 SCN 值时的状态。
闪回技术的最大特点是实现自动备份与恢复当 Oracle 数据库发生认为故障时不需要实现备份数据库就可以利用闪回技术快速进行恢复。为了使用数据库的闪回技术必须启用 undo 表空间自动回退信息。如果要使用闪回删除技术和闪回数据库技术还需要启用回收站和闪回恢复区。
1.8.2 闪回版本查询
闪回版本查询用于查询某段时间内对表的操作记录。闪回版本查询的语法格式如下
select colunm_name[,...]
from table_name
versions {between scn | timestamp exp | minvalue AND exp | timestamp
};查询过去某个时间点或某个 SCN 值时表中的数据。闪回查询的语法格式如下
select column_name[,...] from table_name
as of scn | timestamp ext;闪回查询是利用 UNDO 表空间的撤销数据所以能把表闪回到多久之前受 undo_retentionUNDO 表空间的数据文件是否启动自动增长功能是否设置 guarantee 等因素的影响
undo_retention 参数设置回退数据保存的时间
SQL show parameter undo_retention;1.8.3 闪回事务查询Flashback Transaction Query
闪回事务查询会查询 FLASHBACK_TRANSACTION_QUERY 视图。FLASHBACK_TRANSACTION_QUERY 视图中的 UNDO_SQL 列显示与事务中执行的 DML 语句在逻辑上相反的 SQL 语句。可以使用这些 SQL 语句来回退在事务中执行的逻辑步骤。
Flashback_transaction_query 视图的结构如下
desc Flashback_transaction_query;使用闪回事务查询之前必须先启用重做日志流的其他日志记录。否则在使用视图 flashback_transaction_query 执行闪回事务查询时operation 列的值为 unknown并且 undo_sql 为空。
命令如下
--追加日志数据模式
SQL alter database add supplemental log data;只有sys 用户才能执行闪回事务查询闪回事务查询的步骤如下
执行闪回版本查询确定要恢复的数据以及事务号xid
SQL select versions_startscn, versions_endscn, versions_operation, versions_xid,empno, ename, salfrom emp_bakversions between scn minvalue and maxvalue;查询某个事务对应的 SQL 语句
select table_owner, operation, undo_sql from flashback_transaction_querywhere xid0E001C00CC000000;--查询事务号 10001500CD000000 对应的 SQL 语句
SQL select table_owner, operation, undo_sql from flashback_transaction_querywhere xid10001500CD000000;1.8.4 闪回删除
闪回删除技术用于恢复已经被用户删除Drop的数据库对象需要使用到 Oracle 中的回收站机制。如果在执行 drop table 命令时加上 purge则永久删除表无法恢复。
每个用户都有一个回收站回收站是一个逻辑结构它不是一块独立的存储空间存在于当前表空间内。所以如果有别的操作需要空间比如需要创建一张表没有足够空间可用回收站中的数据就会被清理这也是导致闪回删除失败的原因。
1.8.4.1 禁用和启用回收站
Oracle 中的回收站默认是启用的。如果回收站处于禁用off状态则被删除的数据库对象无法保存到回收站中只能时彻底删除。可以通过设置初始化参数 recyclebin 的值修改回收站的状态语法如下
alter system set recyclebin on | off;查看回收站的状态
SQL show parameter recyclebin1.8.4.2 查看回收站中的信息
普通用户
--使用 show recyclebin 命令
SQL show recyclebin;--使用数据字典user_recyclebin
SQL select object_name, ORIGINAL_NAME,type from user_recyclebin;--直接查询当前用户所拥有的表也能看到被删除的表
SQL select * from tab;管理员
select owner,object_name,original_name from dba_recyclebin;1.8.4.3 使用闪回删除恢复被删除Drop的表
闪回删除的语法如下
Flashback table table_name to before drop [rename to new_table_name];
说明
table_name可以使用表的原名也可以使用在再回收站中的名称。如果表的原名相同则在使用原名进行闪回删除操作时默认还原最近一次删除的表。表被还原后默认情况下使用原名如果该名称已经存在则需要在还原该表时对其重命名此时需要使用 rename to 子句。--闪回删除
SQL flashback table t0001 to before drop rename to ts_001;1.8.4.4 清除回收站中的对象
使用purge 命令语法如下
purge {
[table table_name | index index_name] |
[recyclebin | dba_recyclebin]
};
说明
purge table table_name彻底删除某个表
purge recyclebin清空回收站举例
查看回收站信息
SQL show recyclebin;彻底删除表 t1
SQL purge table t1;清空回收站
SQL purge recyclebin;1.8.5 闪回表
实质上是将表中的数据恢复的指定的时间点timestamp或 scn并将自动恢复索引、触发器和约束等属性同时数据库保持联机。闪回表操作使用 Flashback table 语句语法如下
Flashback table [schema.]table_name to { {scn | timestamp} exp[{ enable | disable} triggers]};
说明
schema模式名
table_name表名
scn exp指定要恢复的 scn 号
timestamp exp指定要恢复的时间
enable trigger与表相关的触发器恢复后默认为启用状态
disable trigger默认选项与表相关的触发器恢复后默认为禁用状态执行闪回表操作之前需要启用表的行移动功能语法如下
alter table 表名 enable row movement;闪回表可能会失败原因有可能有以下几种情况
违反了数据库约束比如用户不小心删除了子表中的数据现在想利用闪回表技术进行回退但父表中与该数据对应的记录也被删除了由于违反了外键约束导致闪回表操作失败撤销数据失效比如用于闪回操作的撤销数据被覆盖了这种情况闪回表操作会失败闪回不能跨越DDL即在闪回点和当前点之间表结构有过变更这种情况闪回操作也会失败。
注意闪回表功能都是基于撤销数据的而撤销数据是会被重写的。因此在使用闪回功能去恢复数据的时候最短时间发现错误第一时间执行闪回操作才能最大程度地保证闪回功能的成功。
1.8.6 闪回数据归档Flashback Data Archive
闪回查询对撤销数据及参数 undo_retention 的依赖注定了它们在大事务量的情况下闪回时间窗口会很小想要查询数月之前的数据绝对不可能。闪回数据归档可使数据具有回退到过去任何时间点的能力。
闪回数据归档的工作原理是将原本只能保存在 UNDO 表空间的撤销数据保存在指定的普通表空间permanent 类型的表空间中。闪回数据归档可以只为特定的表服务这样就可以长时间地保存一些重要的数据。
闪回数据归档可以和日志归档类比日志归档记录的是 Redo 的历史状态用于保证恢复的连续性。而闪回归档记录的是 UNDO 的历史状态可用于对数据进行闪回追溯查询。后台进程 LGWR 用于将 Redo 信息写出到日志文件ARCH 进程负责进行日志归档在 Oracle11g新增的后台进程 FBDAFlashback Data Archiver Process则用于对闪回数据进行归档写出。
创建闪回数据归档的步骤如下
创建保存闪回数据归档的表空间也可以使用已经存在的表空间
create tablespace undo_archive datafile DATA/orcl/datafile/undo_archive.dbf size 50m autoextend on next 50m;创建一个保留时间为 2 年的闪回归档
SQL create flashback archive undo_data_archive tablespace undo_archive retention 2 year;为某个用户比如scott用户下的 emp 表启用闪回归档
赋予用户归档flashback archive的权限
grant flashback archive on undo_data_archive to scott;alter table emp flashback archive undo_data_archive;
Table altered.1.8.6 闪回数据库
1.8.6.1 简介
闪回数据库 能够 使数据库迅速回滚到以前的某个时间点或者某个 SCN 上。闪回数据库之后闪回点之后的所有操作会丢失相当于数据库的不完整恢复。因此只能以 resetlogs 模式打开数据库。
闪回数据库的工作原理 闪回数据库不使用撤销数据使用另外一种机制来保留回退所需要的恢复数据。当启用闪回数据库发生变化的数据块会不断从数据库缓冲区缓存中复制到闪回缓冲区。然后恢复写入器Recovery Writer后台进程会将这些数据刷新到磁盘中的闪回日志Flashback_logs文件中。 闪回数据库的结构由 RVWR 后台进程和闪回数据库日志Flashback_logs组成。如果要启动闪回数据库功能RVWR 进程要先启动。闪回数据库日志是一种新的日志文件类型闪回恢复区是闪回数据库的先决条件因为 RVWR 进程要将闪回日志写入闪回恢复区。因此在使用闪回数据库功能时必须先配置闪回恢复区。
首先在 SGA 中分配一些内存并在磁盘上分配一些空间来存储闪回数据同时启动 RVWR 后台进程来记录闪回日志Flashback_logs。此进程会将闪回缓冲区内容刷新到磁盘和闪回日志LGWR 进程会将日志缓冲区刷新到磁盘。闪回日志记录是此操作的附属物。 与重做日志不同的是闪回日志不是记录变化的日志而是记录完整块映像的日志。闪回日志不同于重做日志它不能多路复用和归档闪回日志它们是自动创建和管理的。当执行闪回时Oracle 会使用闪回日志将数据库及时回退到期望的时间之前的某个时间点然后按照不完整恢复中的常规方式应用重做日志最大限度的使用归档重做日志文件和联机重做日志文件以便将数据文件前滚到希望的确切时间。
闪回的过程则是一个提取闪回日志 -- 将块映像复制回数据文件的过程。 闪回数据库Flashback database architecture架构如下 开启闪回数据库功能之后会在 SGA中 开辟内存 Flashback buffer记录 buffer cache 中的部分改变然后后台恢复写入进程 RVWR 将记录写入闪回日志 Flashback logs 中。FBDAFlashback Data Archive进程则会将 Flashback logs 进行归档。这个过程和重做日志类似。
1.8.6.2 操作
配置闪回数据库闪回数据库要求数据库为归档模式的步骤如下
查看数据库是否为归档模式
SQL select log_mode from v$database;-- 打开归档模式
alter database archivelog;-- 关闭归档模式
alter database noarchivelog;指定闪回恢复区也就是存放闪回日志的位置
查看闪回恢复区的位置、大小
SQL show parameter recover;修改闪回恢复区的位置使用如下命令
alter system set db_recovery_file_dest ...;修改闪回恢复区的大小使用如下命令
alter system set db_recovery_file_dest_size 8G; 设置闪回保留目标时间
该时间通过 DB_FLASHBACK_RETENTION_TARGET 初始化参数来控制单位是分钟其默认值是 1 天。闪回日志空间以循环方式重用更新的数据将覆盖旧数据。该参数指示 Oracle 在重用之前保存闪回数据的分钟数。
SQL show parameter flashback指定闪回日志保留时间为 2 小时
SQL alter system set db_flashback_retention_target120; 正常停库然后重新启动数据库到 mount 状态
--正常停库
SQL shutdown immediate--重新启动数据库到 mount 状态
SQL startup mount启用闪回数据库
SQL alter database flashback on;打开数据库查看数据库状态
SQL alter database open;闪回数据库
正常停库
SQL shutdown immediate启动数据库到 mount
SQL startup mount闪回数据库到 scn2772117
SQL Flashback database to scn 2772117;使用 resetlogs 参数打开数据库
SQL alter database open resetlogs;
