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