Oracle表空间管理（一）

Oracle数据库表可分为系统默认表空间和用户自定义的表空间，本篇主要介绍系统默认表空间的管理。
默认的表空间一般有六个：

本篇一次性介绍完SYSTEM、SYSAUX,TEMP 3个表空间的所有管理和操作。

系统表空间

SYSTEM 表空间是 Oracle 数据库最重要的一个表空间,存放了一些 DDL 语言产生的信息以及 PL/SQL 包、视图、函数、过程等,称之为数据字典,因此该表空间也具有其特殊性。

SYSTEM 表空间的管理

1. 建议不存放用户数据
   易引起错误：因用户错误导致系统表空间不可用
   解决措施：应当为系统设定缺省的默认表空间来避免用户创建时使用系统表空间；

//为数据库设定默认表空间
SQL>ALTER DATABASE DEFAULT TABLESPACE tablespace_name;
//查询默认表空间
SQL> col property_value format a30 --修改 property_value 参数格式长度为 30
SQL> select property_name,property_value from database_properties
2 where property_name like 'DEFAULT%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------ DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS --应为非 SYSTEM 的表空间
DEFAULT_TBS_TYPE SMALLFILE

2.SYSTEM 表空间特性
• 不能脱机 offline
• 不能置为只读 read only
• 不能重命名
• 不能删除
（1）不能脱机：

SQL> alter tablespace system offline;
alter tablespace system offline
*ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

（2）不能置为只读状态

SQL> alter tablespace system read only;
alter tablespace system read only
*ERROR at line 1:
ORA-01643: system tablespace can not be made read only

（3） 不能重命名

SQL> alter tablespace system rename to system2;
alter tablespace system rename to system2
*ERROR at line 1:
ORA-00712: cannot rename system tablespace

（4）不能删除

SQL> drop tablespace system;
drop tablespace system
*ERROR at line 1:
ORA-01550: cannot drop system tablespace
SQL> drop tablespace system including contents and datafiles;
drop tablespace system including contents and datafiles
*ERROR at line 1:
ORA-01550: cannot drop system tablespace

总结：普通表空间所具有的更名、删除、只读、脱机不适用 system 表空间。

3.空间管理
–保证空间可用，一般存放单个数据文件，设置为自动扩展；
– 如果 SYSTEM 表空间数据文件很大，可以考虑使用 bigfile；

丢失系统表空间的处理：

//查看归档模式
SQL>select log_mode from v$database;
//关闭数据库备份 system 表空间的数据文件
[oracle@oracle ~]cp .. //在用户表空间创建一个表
SQL>create table table_name tablespace tablespace_name as ...;
//切换日志直至清除日志
SQL> alter system switch logfile;
//删除 system 表空间的数据文件
[oracle@oracle ~]rm .. //重新启动数据库，并根据错误信息恢复数据库
SQL> startup force;
[oracle@oracle ~]cp .. //恢复数据库
SQL> recover database until cancel;
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
//以最旧的 SCN 号打开数据库
SQL> alter system set "_allow_resetlogs_corruption" = true scope = spfile;
SQL> startup force;
SQL> alter database open resetlogs;
SQL> exit
[oracle@oracle dbs]$ sqlplus / as sysdba
SQL> startup;
//将参数改回来
SQL> alter system reset "_allow_resetlogs_corruption" scope =spfile sid ='*';
SQL> desc test;

辅助表空间

SYSAUX 表空间在 Oracle Database 10g 中引入,作为 SYSTEM 表空间的辅助表空间. 以前一些使用独立表空间或系统表空间的数据库组件现在在 SYSAUX 表空间中创建. 通过分离这些组件和功能，SYSTEM 表空间的负荷得以减轻.反复创建一些相关对象及组件引起 SYSTEM 表空间的碎片问题得以避免。如果 SYSAUX 表空间不可用,数据库核心功能将保持有效;但DB实例最终会 失败或功能受限.
SYSAUX 表空间的特点：
• 不能删除
• 不能重命名
• 不能置为 read only
辅助表空间是一个管理及规划上的改进,进一步独立 SYSTEM 表空间,保证其存储及性能. 我们在做数据库规划时大可借鉴 Oracle 这个改进,分离重要数据及次要数据，分离稳定结构及频繁变化结构，尽量减少对重要数据及结构的影响。

临时表空间

1. 临时表空间作用

Oracle 临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题 sql 语句的执行，temp 表空间会一直增长，直到耗尽硬盘空间。有人猜测在磁盘空间的分配上，oracle 使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是 1GB。
也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。
临时表空间的主要作用：
– 索引 create 或 rebuild；
–Order by 或 group by；
– Distinct 操作；
– Union 或 intersect 或 minus；
– Sort-merge joins；
– analyze。

2.为什么要使用临时表空间？

在 Oracle 数据库中进行排序、分组汇总、索引等到作时，会产生很多的临时数据。通常情况下，Oracle 数据库会先将这些临时数据存放到内存的 PGA(程序全局区)内。在这个程序全局区中有一个叫做排序区的地方，专门用来存放这些因为排序操作而产生的临时数据。但是这个分区的容量是有限的。当这个分区的大小不足以容纳排序后所产生的记录时，数据库系统就会将临时数据存放到临时表空间中。用户进行数据库操作时，排序、分组汇总、索引这些作业是少不了，其会产生大量的临时数据。为此基本上每个数据库都需要用到临时表空间。而如果这个临时表空间设置不当的话，则会给数据库性能带来很大的负面影响。为此管理员在维护这个临时表空间的时候，不能够掉以轻心。
要避免因为临时表空间设置不当影响数据库的性能。
（1）为用户创建临时表空间
强烈建议在创建用户时为用户指定临时表空间。如可以利用语句 default temporary tablespace 语句来为数据库设置默认的临时表空间。
（2）合理设置 PGA，减少临时表空间使用的几率。
当排序操作产生临时数据时，数据库并不是马上将其存储在临时表空间中。通常情况下，会先将这些临时数据存储在内存的 PGA 程序全局区内。只有当这个程序全局区无法容纳全部数据时，数据库系统才会启用临时表空间中的临时段来保存这些数据。在实际工作中，可以根据需要来设置初始化参数 SORT_AREA_SIZE参数。这个参数主要控制这个 PGA 程序全局区内排序区的大小。通常情况下，如果这个数据库系统主要用来查询并且需要大量的排序、分组汇总、索引等操作时，那么可以适当调整这个参数，来扩大 PGA 分区的大小。相反，如果这个系统主要用于更新操作，或者在这个数据库服务器上还部署由其他的应用程序，那么这个 PGA 分区就不能够占用太多的内存，以防止对其他应用程序产生不利的影响。
（3）要为临时表空间保留足够的硬盘空间。
其他表空间对应的数据文件，在其创建时就会被完全分配和初始化，即在其创建时就会被分配存储空间。但是临时表空间对应的临时文件则不同。如在 Linux 操作系统中，临时表空间创建时系统是不会分配和初始化临时文件的。也就是说，不会为临时文件分配存储空间。只有临时数据出现需要用到临时文件的时候，系统才会在硬盘上分配一块地方用来保存临时文件。此时就可能会产生一个问题，即当需要用到临时文件系统为其分配空间的时候。
如果系统分区中没有足够的存储空间，就会产生一些难以预料的后果。为此对于这些临时文件，数据库管理员最好能够预先为其保留足够的空间。如在 Linux操作系统中，可以将其防止在一个独立的分区内，不允许其他应用程序使用。如此的话，就不用担心临时文件没有地方存储了。另外由于临时表空间主要用来存放一些排序用的临时文件。为此如果能够将这个临时表空间存放在性能比较好的分区中，还可以提高数据库系统读取临时表空间中数据的速度。另外由于系统需要频繁分配临时表空间中的数据，为此临时表空间所在的分区会出现比较多的碎片。此时如果将临时表空间存放在一个独立的分区内，那么数据库管理员就可以单独对这个分区进行碎片整理，从而提高这个分区的性能。
所以无论出于什么原因，将临时表空间防止在一个独立的分区内，是一个不错的想法。不仅可以保证临时文件有存储的空间，而且还可以提高数据库的性能。
默认情况下这个临时表空间对各个用户都是共享的。也就是说每个连接到数据库的用户都可以使用默认的临时表空间。可以为其指定其他的临时表空间。一般来说，只需要一个临时表空间即可。

3.临时表空间组

（1）概述
从 oracle 10g 开始,可以创建多个临时表空间,并把它们组成一个临时表空间组,这样应用数据用于排序时可以使用组里的多个临时表空间,一个临时表空间组至少有一个临时表空间,其最大个数没有限制,组的名字不能和其中某个表空间的名字相同。临时表空间组是在创建临时表空间时通过指定 group 字句创建的,如果删除组中的全部临时表空间,那么这个组也将消失。我们将可以将一个表空间从一个组移动另一个组,或是从一个组中删除临时表空间,或是往组里添加新的表空间。
（2）使用临时表空间组的优点:
• 数据库层面可以同时指定多个临时表空间，避免当临时表空间不足时所引起的磁盘排序问题;
• 当一个用户同时有多个会话时,可以使得它们使用不同的临时表空间;
• 并行操作中，不同的从属进程可以使用不同的临时表空间；
创建临时表空间组：

SQL> create temporary tablespace tempts1 tempfile2 '/home/oracle/temp1_02.dbf' size 2M tablespace group group1;
Tablespace created
SQL> create temporary tablespace tempts2 tempfile2 '/home/oracle/temp2_02.dbf' size 2M tablespace group group2;
Tablespace created

查询临时表空间组

SQL> select * from dba_tablespace_groups;
GROUP_NAME            TABLESPACE_NAME
------------------------------ ------------------------------ 
GROUP1                       TEMPTS1
GROUP2                        TEMPTS2

将表空间从一个临时表空间组移动到另外一个临时表空间组：

SQL> alter tablespace tempts1 tablespace group GROUP2 ;
Tablespace altered
SQL> select * from dba_tablespace_groups;
GROUP_NAME            TABLESPACE_NAME
------------------------------ ------------------------------ 
GROUP2                       TEMPTS1
GROUP2                       TEMPTS2

把临时表空间组指定给用户

SQL> alter user scott temporary tablespace GROUP2;

在数据库级设置临时表空间

SQL> alter database <db_name> default temporary tablespace GROUP2;

删除临时表空间组 (删除组成临时表空间组的所有临时表空间)

SQL> drop tablespace tempts1 including contents and datafiles;
Tablespace dropped
SQL> select * from dba_tablespace_groups;
GROUP_NAME             TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2                       TEMPTS2
SQL> drop tablespace tempts2 including contents and datafiles;
Tablespace dropped
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME

本篇完结。
码字不易，宝贵经验分享不易，请各位支持原创，转载注明出处，多多关注作者，后续不定期分享DB核心知识和排障案例及经验、性能调优等。