三、Oracle数据库表空间管理

曾经终败给现在 2022-06-05 01:25 379阅读 0赞

#                                    Oracle数据库表空间管理 #

![这里写图片描述][8d2c2fa7ab3c818b1a94ad1c636ab72b.png]

关于Oracle数据库体系结构请参照笔者整理的文章：[Oracle数据库体系结构概论][Oracle]

> Tablespace（表空间） ： 表空间是一组数据文件（.dbf/.ora）  
> Segment(段)： 段是分配给某个逻辑结构的一组区。  
> Extent（区）：是两个或者多个相邻的ORACLE数据块，他是空间分配的单元。  
> Data Block(块) ：是oracle数据块的存储基础，有若干字节组成。又是若干，到 底是多少个字节呢？一般常用的是8KB,4KB的了，在创建数据库的时候可以指定。

## 1、表空间概述与默认表空间 ##

**重点内容表空间有三种：小文件表空间（默认）、大文件表空间、临时表空间。**

### 1.1、表空间概念 ###

表空间是一个逻辑概念，一个表空间由一个或者多个数据文件（如上图中.dbf/.ora结尾的文件）构成。每一个数据文件只属于一个表空间，一个表空间可以存放多个数据文件，创建表空间时必须创建数据文件，反之，创建数据文件必须指定对应的表空间。

不同的用户可以在同一个表空间中创建同名的表。 一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。用户和表空间没有隶属关系，表空间是一个用来管理数据存储的逻辑概念，表空间只是和数据文件发生关系，数据文件是物理的。

我们使用\*\*用户名点表名（gcki\_test.user）\*\*的方式调用一个数据库表，表示调用的是当前用户所在的表空间的这张表。

### 1.2、默认表空间 ###

默认表空间表示，当建表，建索引等的时候，如果不指定表放在哪里，那么自动放在该用户的默认表空间（创建一个用户的时候需要指定他的默认表空间）。这就印证了：一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用。

Oracle11g数据库存在6个默认表空间：EXAMPLE、SYSAUX、SYSTEM、TEMP、UNDOTBS1、USERS。其中：

>       EXAMPLE表空间：用于安装Oracle 11g数据库使用示例数据库。  
>       SYSAUX表空间：作为EXAMPLE表空间的辅助表空间。  
>       SYSTEM表空间：用来存储SYS用户的表、视图、、存储过程等数据库对象。  
>       TEMP表空间：临时表空间，用户SQL语句处理的表和索引信息。  
>       UNDOTBS1表空间：用于存储撤销信息。  
>       USERS表空间：存储数据库用户创建的数据库对象。

### 1.3、查看默认表空间 ###

#### 1.3.1、查看所有默认表空间 ####

通过数据库字典DBA\_TABLESPACES查看所有默认表空间。

select * from dba_tablespaces

![这里写图片描述][3e3c8773ab4702d6124a3d201a13e0b7.png]

#### 1.3.1、查看指定用户默认表空间 ####

通过数据库字典DBA\_USERS查看指定用户的默认表空间。

select * from dba_users

![这里写图片描述][c13c35859e33eb0db7b45ad8f42f234c.png]

具体的数据库字典存放的相关信息请参照笔者整理的文章：[Oracle数据库数据字典概论][Oracle 1]

## 2、表空间管理 ##

表空间管理无非三种情况：对表空间的创建、修改、删除、扩容。

### 2.1、创建表空间 ###

创建表空间的一般语法：  
      （语法符号解析：中括号\[\]表示可有可无，竖线 | 表示或者, 大括号\{ \}表示必须要有的）

CREATE TABLESPACE tablespace_name
    	DATAFILE 'filename'  SIZE size 
    	[AUTOEXTEND  [OFF|ON NEXT size] ]  
    	[MAXSIZE size]
    	[PERMANENT|TEMPORARY]
    	[EXTENT MANAGEMENT 
    		[DICTIONARY|LOCAL
    			[AUTOALLLOCATE|UNIFORM.[SIZE integer[K|M]]]
            ]
    	 ]

> 【语法说明】  
>           TABLESPACE ：指定要创建表空间的名称  
>           DATAFILE：指定表空间的数据文件名称，还要指定文件的路径。  
>          AUTOEXTEND ：指定表空间数据文件的自动扩展方式。ON表示自动扩展，OFF表示非自动扩展。如果使用自动扩展应该在NEXT关键字后指定具体大小。  
>           MAXSIZE : 指定数据文件自动扩展方式时的最大值。  
>         PERMANENT|TEMPORARY ：指定表空间的类型，PERMANENT表示永久表空间；TEMPORARY表示临时表空间。  
>           EXTENT MANAGEMENT ：指定表空间的管理方式，DICTIONARY表示字典管理方式，LOCAL表示本地管理方式。（默认使用本地管理方式，也是推荐使用的）

示例：

CREATE TABLESPACE testone
    	DATAFILE 'testone.dbf'  SIZE 10M 
    	AUTOEXTEND  ON NEXT 128k
    	MAXSIZE 2048M

### 2.2、重命名表空间 ###

重命名表空间语法：

ALTER TABLESPACE old_name to new_name

> 【说明】  
> \*不是所有的表空间的都可以重命名，YSYTEM、SYSAUX就不能，处于**OFFLINE状态**的表空间也不能重命名。  
> \*要重命名表空间的前提是，表空间存在，可以在数据字典中查看表空间的名字在进行操作。

示例：

ALTER TABLESPACE testone to testonenew

### 2.3、设置表空间读写状态 ###

设置表空间读写状态的语法：

ALTER TABLESPACE tablespace_name READ {ONLY|WRITE}

> 【语法说明】  
>       READ ONLY：只读  
>       READ WRITE：读写  
>       \*只有表空间在**ONLINE状态**才能操作。

示例：

ALTER TABLESPACE testonenew READ WRITE

### 2.4、设置表空间的可用状态 ###

设置表空间可用状态的语法：

ALTER TABLESPACE tablespace_name  {ONLINE|OFFLINE               [NORMAL|TEMPORARY|IMMEDIATE]}

> 【语法说明】  
>       ONLINE：联机，可用状态  
>       OFFLINE：脱机，不可用。  
>       NORMAL|TEMPORARY|IMMEDIATE：在脱机状态下还有这三种方式。NORMAL，普通；TEMPORARY，临时；IMMEDIATE，立即。

示例：

ALTER TABLESPACE testonenew ONLINE

### 2.5、设置大文件表空间 ###

设置大文件表空间是在正常表空间不足够使用的时候才创建。  
      设置大文件表空间的语法：

CREATE BIGFILE TABLESPACE tablespace_name  DATAFILE filename SIZE size

> 【语法说明】  
>       tablespace :表空间  
>       filename ：设置大文件名  
>       SIZE：设置大文件的大小

示例：

CREATE BIGFILE TABLESPACE testbigfile DATAFILE 'bigfiletest.dbf' SIZE 2G

### 2.6、删除表空间 ###

删除表空间的时候可以选择将**表空间的文件**和**完整性**一起删除。  
      删除表空间的语法：

DROP TABLESPACE tablespace_name [INCLUDING CONTENTS]  [CASCADE CONSTRAINTS]

> 【语法说明】  
>       \[INCLUDING CONTENTS\]: 包含数据文件  
>       \[CASCADE CONSTRAINTS\]：包含表空间完整性

示例：

DROP TABLESPACE testone INCLUDING CONTENTS

### 2.7 扩容表空间 ###

##### Meathod1：给表空间增加数据文件 #####

ALTER TABLESPACE app_data ADD DATAFILE  
    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;

##### Meathod2：新增数据文件，并且允许数据文件自动增长 #####

ALTER TABLESPACE app_data ADD DATAFILE
    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
    AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

##### Meathod3：允许已存在的数据文件自动增长 #####

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'  
    AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

##### Meathod4：手工改变已存在数据文件的大小 #####

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'  
    RESIZE 100M;

## 3、临时表空间管理 ##

Oracle临时表空间主要用来做查询和存放一些**缓冲区数据**。临时表空间消耗的主要原因是需要对查询的中间结果进行**排序**。  
      重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。  
      存放临时数据（一般是排序结果），数据库存储数据，当内存不够时存入临时表空间，当执行完数据库操作后，自动清空。

> 以下操作将会消耗临时表空间：  
> 索引create或rebuild  
> Order by 或 group by  
> Distinct 操作  
> Union 或 intersect 或 minus  
> Sort-merge joins  
> analyze

查看临 时表空间：
    SELECT *  FROM V$TEMPFILE;

![这里写图片描述][395465f75b1493ccfa154ad4e53affbe.png]

### 3.1、创建临时表空间 ###

创建临时表空间的语法：

CREATE TEMPORARY TABLESPACE tablespace_name 
           TEMPFILE 'filename.dbf' SIZE size

> 【语法说明】  
>       TABLESPACE ：表空间名  
>       TEMPFILE ：表空间文件名  
>       SIZE：表空间大小

示例：

CREATE TEMPORARY TABLESPACE temptable
           TEMPFILE 'temptable.dbf' SIZE 100M

**设置临时表空间为默认的临时表空间：**

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace

### 3.2、查询临时表空间 ###

通过字典DBA\_TEMP\_FILES查看临时表空间：

SELECT * FROM DBA_TEMP_FILES

![这里写图片描述][c4e4fb16de915c857b2f5982b1720b5a.png]

### 3.3、创建临时表空间组 ###

临时表空间组是由一个或者多个临时表空间组成的，临时表空间组不能和其他表空间重名。临时表空间组本质上就是为表空间设置了一个组，所以创建表空间组的语法和创建表空间类似，在创建临时表空间组有两种方式：一是创建临时表空间组的同时创建临时表空间，一是创建临时表空间组时将存在的临时表空间移动到该组

#### 3.3.1、创建临时表空间组的同时创建临时表空间的语法： ####

CREATE TEMPORARY TABLESPACE tablespace_name TEMFILE filename SIZE size  TABLESPACE GROUP group_name

示例：

CREATE TEMPORARY TABLESPACE temtable1 TEMFILE 'tep1.dbf' SIZE 100M  TABLESPACE GROUP temgroup

#### 3.3.2、将存在的临时表空间存入临时表空间组的语法： ####

ALTER TABLESPACE tablespace_name  TABLESPACE GROUP group_name

示例：

ALTER TABLESPACE temtable1 TABLESPACE GROUP temgroup

> 临时表空间组 :  
> 1、多个临时表空间的集合，无数量限制  
> 2、删除临时表空间组的所有成员，该组也自动被删除  
> 3、临时表空间组的名称不能与临时表空间名称相同  
> 4、给用户分配临时表空间时，可以使用临时表空间组的名字代替临时表空间名;在给 数据库分配默认临时表空间时，也可以使用临时表空间组的名字.

> 临时表空间组好处 :  
> 1、sql查询可以并发使用几个临时表空间进行排序操作，减少查询出现排序空间不 够，避免临时表空间不够引起磁盘排序问题  
> 2、可以在数据库级指定多个默认临时表空间  
> 3、一个并行操作的并行服务器能有效的利用多个临时表空间  
> 4、一个用户在不同会话中可以同时使用多个临时表空间

### 3.4、查询临时表空间组 ###

通过字典DBA\_TABLESPACE\_GROUPS查看临时表空间组：

SELECT * FROM DBA_TABLESPACE_GROUPS

### 3.5、删除临时表空间组 ###

删除临时表空间组时必须同时将表空间组下的临时表空间也一并删除：

DROP TABLESPACE  tablespace_name INCLUDING CONTENTS AND DATAFILES

## 4、数据文件管理 ##

### 4.1、移动数据文件 ###

在创建临时表空间时数据文件已经创建好了，如果想把当前表空间的数据移动到其他表空间，可以这么做：

1、把存放数据文件的表空间设置成脱机状态：
    ALTER TABLESPACE tablespace_name OFFLINE

2、手动将数据文件移动到其他表空间

3、更改数据文件名称：
    ALTER TABLESPACE tablespace_name RENAME DATAFILE oldfilename TO newfilename

4、把存放数据文件的表空间设置成联机状态：
    ALTER TABLESPACE tablespace_name ONLINE

### 4.2、删除数据文件 ###

在使用数据文件时经常去除一些没有用的数据文件，删除文件也是有前提的。当数据文件处于以下三种情况的时候将不能被删除：

1、数据文件中存在数据；
    2、数据文件是表空间中唯一一个或者第一个数据文件；
    3、数据文件或者数据文件所在的表空间处于只读状态；

语法：

ALTER TABLESPACE tablespace_name DROP DATAFILE 'filename'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[8d2c2fa7ab3c818b1a94ad1c636ab72b.png]: /images/20220605/da67f857f3ee4327838a93745645380d.png
[Oracle]: http://blog.csdn.net/newbie_907486852/article/details/78513673
[3e3c8773ab4702d6124a3d201a13e0b7.png]: /images/20220605/36caf00362bd4c6293f137d1b436eae8.png
[c13c35859e33eb0db7b45ad8f42f234c.png]: /images/20220605/dce5a63063bd4e5e98ba000943503f3a.png
[Oracle 1]: http://blog.csdn.net/newbie_907486852/article/details/78518425
[395465f75b1493ccfa154ad4e53affbe.png]: /images/20220605/857657678c794da4a2c4bb0d2df4cb32.png
[c4e4fb16de915c857b2f5982b1720b5a.png]: /images/20220605/3d1cb5653c2146e8bff7dbcd3d7d2154.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220605/0423c5f31d3c497c80b6dd48b9d1f120.png