Oracle数据库之表空间
1、表空间简述
ORACLE数据库被划分成称作为表空间的逻辑区域——形成ORACLE数据库的逻辑结构。
一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件,但一个数据库文件只能与一个表空间相联系。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。
每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的,用于存储系统的数据字典表、程序单元、过程、函数、包和触发器等。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。
注意:
oracle很多优化通过表空间实现,表与数据库对象存放在数据文件中
2、表空间分类及其作用
2.1、表空间分类
2.1.1、永久表空间:
存放永久化存储对象,如:表、视图、存储过程
2.1.2、临时表空间:
数据库操作过程当中的中间执行过程(执行结束后,自动释放)
2.1.3、UNDO表空间:
保存事务被修改的旧址(撤销操作),用来暂时存储DML操作的数据,其主要作用有:
1、事务回滚:在进行DML操作时,insert、update、delete操作时,undo段记录事务的反向操作并且redo日志也记录undo段的操作,既redo保护undo段的信息。当实例关闭或意外崩溃后,再次open时实例需要对没有commit的事务进行回滚,完成事务的恢复。
2、实例恢复:用户进行DML操作后没有进行commit,需要修改前的数据。只要该操作在undo段保护的时间内,此时执行rollback操作可以回滚到最近记录点或上一次commit操作后的状态,恢复到数据修改前的状态。
3、读一致性:当进行DML操作时,undo段会记录数据变更前的状态(通过构造原数据的一致性数据块)。如果用户还没有进行commit操作,其他人查询此条数据会看到数据变更前的状态。因为其他用户读到的数据是undo段中原数据块中的数据,保证没有commit的数据读取的一致性。
4、闪回
2.2、表空间作用
表空间的作用能帮助DBA用户完成以下工作:
1. 决定数据库实体的空间分配
2. 设置数据库用户的空间份额
3. 控制数据库部分数据的可用性
4. 分布数据于不同的设备之间以改善性能
5. 备份和恢复数据。
用户创建其数据库实体时,必须给予表空间中具有相应的权力,所以对一个用户来说,其要操纵一个ORACLE数据库中的数据,应该:
1. 被授予关于一个或多个表空间中的RESOURCE特权
2. 被指定缺省表空间
3. 被分配指定表空间的存储空间使用份额
4. 被指定缺省临时段表空间,建立不同的表空间,设置最大的存储容量。
3、查看表空间
3.1、查看表空间信息
具有dba权限的用户(例如:sys、system):
select tablespace_name from dba_tablespaces;(查看所有表空间)
select tablespace_name from user_tablespaces;(查看当前用户的表空间
若用户未自己创建表空间,则查询结果基本如下:
–SYSTEM(系统表空间):存放sys表视图、存储过程以及数据库对象
–SYSAUX:作为example辅助表空间
–UNDOTBS1:存储撤销信息(UNDO类型)
–TEMP(临时):存储sql语句处理的表和索引信息
–USERS(永久):存储用户创建的数据库对象
–EXAMPLE:安装oracle 11g数据库示例
3.2、查看用户下的默认表空间/临时表空间
select default_tablespace,temporary_tablespace from dba_users
;(查看所有用户)
select default_tablespace,temporary_tablespace from user_users;(查看当前用户)
注意:
默认情况下,每一个用户只有一个临时表空间,普通用户不可更改默认表空间(除非为其设置权限)
修改语句如下:
alter user username default|temporary tablespace tablespace_name;
3.3、查看数据文件
desc dba_data_files
select file_name from dba_data_files where tablespace_name=‘xxx’;
SELECT * FROM V$DATAFILE;
若file_name是以”+“开头,例如”/DATA/orcl/datafile/system.256.969659767“,则说明数据文件使用的是ASM卷管理器。否则,使用的是本地指定路径
3.3.1、ASM(自动存储管理)
- ASM可以自动管理磁盘组并提供有效的数据冗余功能。使用ASM(自动存储管理)后,数据库管理员不再需要对ORACLE中成千上万的数据文件进行管理和分类,从而简化了DBA的工作量,可以使得工作效率大大提高。
- select name,state from v$asm_diskgroup;
- show parameter asm_diskgroups;
3.4、查看表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) AS "TABLESPACE_NAME",
ROUND(D.MAX_BYTES,2) AS "TBS_TOTAL_SIZE" ,
ROUND(D.AVAILB_BYTES ,2) AS "TABLESPACE_SIZE",
ROUND(D.MAX_BYTES - D.AVAILB_BYTES +USED_BYTES,2) AS "TBS_AVABLE_SIZE",
ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "TBS_USED_SIZE",
TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
2),
'999.99') AS "USED_RATE(%)",
ROUND(F.USED_BYTES, 6) AS "FREE_SIZE(G)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 5 DESC;
4、创建表空间
4.1、创建临时表空间
create temporary tablespace 临时表空间名
tempfile 'xx.dbf'
size 大小
[AUTOEXTEND ON]
[NEXT 大小]
[MAXSIZE 大小];
说明:
[ ]里面内容可选项,数据文件路径中若包含目录需先创建
SIZE 为初始表空间大小,单位为K或者M
AUTOEXTEND 是否自动扩展,值为ON或OFF
NEXT 为文件满了后扩展大小
MAXSIZE 为文件最大大小,值为数值或UNLIMITED(表示不限大小)
4.2、创建数据表空间
create tablespace 数据表空间名
datafile 'xx.dbf'
size 大小
[AUTOEXTEND ON]
[NEXT 大小]
[MAXSIZE 大小];
5、维护表空间
5.1、修改表空间状态(默认联机状态)
--联机、脱机状态
alter tablespace tablespace_name online|offline;
--只读、可读写状态
alter tablespace tablespace_name read only|read write;
--查看状态
select status from dba_tablespaces where tablespace_name='xxx';
5.2、修改表空间的数据文件
--增加数据文件
alter tablespace tablespace_name add datafile 'xx.dbf' size xx;
--删除数据文件(表空间的第一个数据文件不可删,除非删除表空间)
alter tablespace tablespace_name drop datafile 'xx.dbf';
5.3、表空间/数据文件重命名
5.3.1、表空间重命名
ALTER TABLESPACE XXX RENAME TO XXX;
注意:
1、Oracle 11g以前的版本中,表空间无法重命名。
2、数据库管理员只能对普通的表空间进行更名,不能对SYSTEM和SYSAUX表空间进行重命名,也不能对已经处于OFFLINE状态的表空间进行重命名。
5.3.2、数据文件重命名
--方式一:Alter tablespace data file rename
--步骤如下:
alter tablespace userdata offline;
mv /u01/oradata/orcl/userdata1.dbf /u02/oradata/orcl/userdata01.dbf
alter tablespace userdata rename datafile '/u01/oradata/orcl/userdata1.dbf' to '/u02/oradata/orcl/userdata01.dbf';
alter tablespace userdata online;
--方式二:Alter Database data file rename
--步骤如下:
shutdown immediate;
mv /u01/oradata/orcl/userdata1.dbf /u02/oradata/orcl/userdata01.dbf
startup mount;
ALTER DATABASE RENAME file '/u01/app/oracle/oldname.dbf' TO '/u01/app/oracle/newname.dbf'
alter database open
6、删除表空间
--删除表空间不删除数据文件
drop tablespace tablespace_name;
--全部删除
drop tablespace tablespace_name including contents;
7、undo表空间重建
--1、查看undo表空间的数据文件
select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like '%UNDO%';
--2、查看undo segment的状态
select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
--3、创建undo表空间
create undo tablespace undotbs3 datafile '/u01/app/oradata/TJ/undotbs301.dbf' size 150M reuse AUTOEXTEND ON;
--切换UNDO表空间为新的UNDO表空间
alter system set undo_tablespace=undotbs3;
--等待原UNDO表空间所有UNDO SEGMENT OFFLINE
select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
--删除原UNDO表空间
alter tablespace undotbs1 offline
drop tablespace undotbs1 including contents and datafiles;
--增加数据文件方法
ALTER TABLESPACE undotbs3 ADD DATAFILE '/u01/app/oradata/TJ/undotbs303.dbf' size 150M reuse AUTOEXTEND ON;
注意:
1、旧的表空间上还在执行的事务,则旧的表空间状态变成pending offline(等待离线)。
2、用户运行的事务正常运行,切换操作结束,不会等待旧的UNDO表空间的事务结束。
3、切换操作完成后,所有新的事务所产生的undo数据都会存放在新指定的undo表空间中。
4、成为pending offline状态的undo表空间不能被删除
5、旧的undo表空间上的所有事务都提交后,旧的undo表空间状态会由pending offline状态变为offline状态,此时这个undo表空间可以被删除。
6、执行drop tablespace 表空间,相当于 drop tablespace 表空间 including contents (删除所有extent区段,HWM会立即降下)
7、如果undo表空间内包含inactive(不活跃)状态undo数据块,不影响被删除,但此时由于还没有超过undo_retention参数设定的数据保留时间(自动状态下最小值为900秒),此时删除如需要使用当前回滚信息时就会找不到数据而报错。最好等待超过undo_retention参数的保留时间再删除表空间。
undo表空间爆满的分析:
https://www.cnblogs.com/kerrycode/p/5693629.html