Oracle表空间详细介绍

表空间概述

Oracle的表空间属于Oracle中的存储结构，是一种用于存储数据库对象(如：数据文件)的逻辑空间，是Oracle中信息存储的最大逻辑单元，其下还包含有段、区、数据块等逻辑数据类型。表空间是在数据库中开辟的一个空间，用于存放数据库的对象，一个数据库可以由多个表空间组成。可以通过表空间来实现对Oracle的调优。（Oracle数据库独特的高级应用）。

表空间的分类

**永久表空间：**存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引。

**临时表空间：**存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有temp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。

**UNDO表空间：**保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。

创建表空间参数详解

CREATE [UNDO] TABLESPACE tablespace_name
[DATAFILE datefile_spec1 [,datefile_spec2] ......
[{MININUM EXTENT integer [k|m] 
|BLOCKSIZE integer [k]
|logging clause
|FORCE LOGGING
|DEFAULT {data_segment_compression} storage_clause
|[online|offline]
|[PERMANENT|TEMPORARY] 
|extent_manager_clause
|segment_manager_clause}]
1
2
3
4
5
6
7
8
9
10
11

1、undo

说明系统将创建一个回滚表空间。

数据库管理员可以不必管理回滚段，只有建立了undo表空间，系统就会自动管理回滚段的分配，回收的工作。当然,也可以创建一般的表空间,在上面创建回滚段.不过对于用户来说,系统管理比自己理要好很多.如果需要自己管理,当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。

2、tablespace

指出表空间的名称

3、datafile datefile_spec1

指出表空间包含什么空间文件。datefile_spec1 是形如 [‘filename’] [SIZE integer [ K | M ]] [REUSE] [autoextend_clause]，

[autoextend_clause]是形如： AUTOEXTEND { OFF | ON [ NEXT integer [ K | M ] ] [maxsize_clause] }，

其中filename是数据文件的全路径名，size是文件的大小,REUSE表示文件是否被重用，

AUTOEXTEND表明是否自动扩展. OFF | ON 表示自动扩展是否被关闭.NEXT 表示数据文件满了以后,扩展的大小，

maxsize_clause表示数据文件的最大大小.形如MAXSIZE { UNLIMITED | integer [ K | M ] }.UNLIMITED 表示无限的表空间.integer是数据文件的最大大小，

DATAFILE ‘D:"oracle"oradata"IMAGEDATA01.dbf’ SIZE 2000M，

‘D:"oracle"oradata"IMAGEDATA02.dbf’ SIZE 2000M

4、MININUM EXTENT integer [k|m]

指出在表空间中范围的最小值。这个参数可以减小空间碎片，保证在表空间的范围是这个数值的整数倍。

5、BLOCKSIZE integer [k]

这个参数可以设定一个不标准的块的大小。如果要设置这个参数，必须设置db_block_size,至少一个db_nk_block_size,并且声明的integer的值必须等于db_nk_block_size。

注意：在临时表空间不能设置这个参数。

6、logging clause

这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），包括表，索引，分区，物化视图，物化视图上的索引，分区。

7、FORCE LOGGING

使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。这个参数高于对象的nologging选项。

注意：设置这个参数数据库不行open并且出于读写模式。而且，在临时表空间和回滚表空间中不能使用这个选项。

8、DEFAULT storage_clause

声明缺省的存储子句。

9、online|offline

改变表空间的状态。online使表空间创建后立即有效.这是缺省值.offline使表空间创建后无效.这个值，可以从dba_tablespace中得到。

10、PERMANENT|TEMPORARY

指出表空间的属性，是永久表空间还是临时表空间。永久表空间存放的是永久对象 ，临时表空间存放的是session生命期中存在的临时对象。这个参数 生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。如果要创建本地管理表空间，必须使用create temporary tablespace。

注意，声明了这个参数后，不能声明block size

11、extent_management_clause

说明了表空间如何管理范围。一旦声明了这个子句，只能通过移植的方式改变这些参数。

如果希望表空间本地管理的话，声明local选项。本地管理表空间是通过位图管理的。autoallocate说明表空间自动分配范围，用户不能指定范围的大小。只有9.0以上的版本具有这个功能。uniform说明表空间的范围的固定大小，缺省是1m。不能将本地管理的数据库的system表空间设置成字典管理。

如果没有设置extent_management_clause，oracle会给他设置一个默认值。如果初始化参数compatible小于9.0.0,那么系统创建字典管理表空间。如果大于9.0.0,那么按照如下设置：

如果没有指定default storage_clause,oracle创建一个自动分配的本地管理表空间。

否则，如果指定了mininum extent,那么oracle判断mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果满足以上的条件，oracle创建一个本地管理表空间，extent size是initial.如果不满足以上条件，那么oracle将创建一个自动分配的本地管理表空间。

如果没有指定mininum extent。initial、那么oracle判断next是否相等,以及pctincrease是否=0。如果满足oracle创建一个本地管理表空间并指定uniform。否则oracle将创建一个自动分配的本地管理表空间。

注意：本地管理表空间只能存储永久对象。如果你声明了local,将不能声明default storage_clause,mininum extent、temporary。

EXTENT MANAGEMENT LOCAL

12、segment_management_clause

SEGMENT SPACE MANAGEMENT AUTO

实例：

CREATE TABLESPACE YNCCIP --表空间名
DATAFILE '/home/u02/oradata/orcl/YNCCIP01.dbf' --表空间对应的数据文件
SIZE 100M --数据文件大小
AUTOEXTEND ON NEXT 10M --数据文件不够用自动扩展，每次扩展大小
MAXSIZE 1000M --数据文件最大文件大小
LOGGING --启动重做日志
PERMANENT --指定表空间为永久性的表空间
EXTENT MANAGEMENT LOCAL AUTOALLOCATE --指定新建表空间为本地管理方式的表空间
BLOCKSIZE 16K --块大小
SEGMENT SPACE MANAGEMENT auto --指定本地管理表空间中段的存储管理方式，AUTO自动，MANUAL手工。
1
2
3
4
5
6
7
8
9
10

create tablespace NNC_INDEX01 datafile 'D:\oracle\NNC_INDEX01.DBF' 
size 500M  autoextend on next 50M extent management local uniform size 256k;
1
2

修改表空间名称

alter tablespace TEST rename to TEST1;
1

查询表空间剩余字节大小

select tablespace_name, sum(bytes)/1024/1024 as "free space(m)"
from dba_free_space
where tablespace_name = '&tablespace_name'
group by tablespace_name;
1
2
3
4

注：如果是临时表空间，请查询DBA_TEMP_FREE_SPACE

select tablespace_name, free_space/1024/1024 as "free space(m)"
from dba_temp_free_space
where tablespace_name = '&tablespace_name';
1
2
3

dba_free_space表介绍

SQL> select * from dba_free_space;

TABLESPACE_NAME                                                 FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------------------------------------ ---------- ---------- ---------- ---------- ------------
SYSTEM                                                                1     111096      65536          8            1
SYSTEM                                                                1     112896    8388608       1024            1
SYSAUX                                                                3      66728   30081024       3672            3
UNDOTBS1                                                              4        472     262144         32            4
UNDOTBS1                                                              4       1024   78643200       9600            4
UNDOTBS1                                                              4      10752  121634816      14848            4
UNDOTBS1                                                              4      25608   13631488       1664            4
UNDOTBS1                                                              4      27280   54394880       6640            4
UNDOTBS1                                                              4      34944    5242880        640            4
UNDOTBS1                                                              4      35712   53477376       6528            4
UNDOTBS1                                                              4      42368    2097152        256            4
UNDOTBS1                                                              4      42752     917504        112            4
UNDOTBS1                                                              4      43912     786432         96            4
UNDOTBS1                                                              4      44016    1179648        144            4
USERS                                                                 7        344    2424832        296            7
ZHANGTEST                                                            13     199456   96206848      11744           13

已选择 16 行。

SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

dba_date_files表介绍

查看表空间大小以及使用率

SELECT
B.FILE_NAME 物理文件名,
B.TABLESPACE_NAME 表空间名称,
B.BYTES/1024/1024 大小M,
(B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 已使用M,
SUBSTR((B.BYTES-SUM(NVL(A.BYTES,0)))/(B.BYTES)*100,1,5) 使用率
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES
ORDER BY B.TABLESPACE_NAME;
1
2
3
4
5
6
7
8
9
10

查询表空间所有数据文件路径

select tablespace_name, file_id, file_name, bytes/1024/1024 as "bytes(m)"
from dba_data_files
where tablespace_name = '&tablespace_name';
1
2
3

注：如果是临时表空间，请查询DBA_TEMP_FILES

select tablespace_name, file_id, file_name, bytes/1024/1024 as "space(m)"
from dba_temp_files
where tablespace_name = '&tablespace_name';
1
2
3

为空间不足的表空间增加数据文件

alter tablespace &tablespace_name add datafile '&datafile_name' size 2G;
1

注：如果要为临时表空间扩容，使用下面的语句

alter tablespace &tablespace_name add tempfile '&datafile_name' size 2G;
1

查看临时表空间的大小 和 数据文件路径

select tablespace_name, file_id, file_name, bytes/1024/1024 as "space(m)"
from dba_temp_files
where tablespace_name = 'temp';
1
2
3

或者

select name, bytes/1024/1024 as "大小(M)" from v$tempfile order by bytes;
1

重建并修改默认临时表空间办法

查询当前数据库默认临时表空间名

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
1

创建新的临时表空间

create temporary tablespace temp02   tempfile 'E:\oracle\oradata\lims\TEMP02.DBF' size 1024M autoextend on;
1

修改默认表空间为刚刚建立的临时表空间

alter database default temporary tablespace temp02;
1

查看用户所用临时表空间的情况

select USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;
1

删除原来的临时表空间

drop tablespace temp including contents and datafiles;
1

查看所有表空间名确认临时表空间是否已删除

select tablespace_name from dba_tablespaces;
1

查表空间使用率情况（含临时表空间）

select d.tablespace_name "name", d.status "status",
       to_char (nvl (a.bytes / 1024 / 1024, 0), '99,999,990.90') "size (m)",
       to_char (nvl (a.bytes - nvl (f.bytes, 0), 0) / 1024 / 1024,'99999999.99') use,
       to_char (nvl ((a.bytes - nvl (f.bytes, 0)) / a.bytes * 100, 0),'990.00') "used %"
  from sys.dba_tablespaces d,
       (select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name) a,
       (select tablespace_name, sum (bytes) bytes from dba_free_space group by tablespace_name) f
 where d.tablespace_name = a.tablespace_name(+)
   and d.tablespace_name = f.tablespace_name(+)
   and not (d.extent_management like 'local' and d.contents like 'temporary')
union all
select d.tablespace_name "name", d.status "status",
       to_char (nvl (a.bytes / 1024 / 1024, 0), '99,999,990.90') "size (m)",
       to_char (nvl (t.bytes, 0) / 1024 / 1024, '99999999.99') use,
       to_char (nvl (t.bytes / a.bytes * 100, 0), '990.00') "used %"
  from sys.dba_tablespaces d,
       (select tablespace_name, sum (bytes) bytes from dba_temp_files group by tablespace_name) a,
       (select tablespace_name, sum (bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
 where d.tablespace_name = a.tablespace_name(+)
   and d.tablespace_name = t.tablespace_name(+)
   and d.extent_management like 'local'
   and d.contents like 'temporary';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

查看表空间大小以及使用率

select
b.file_name 物理文件名,
b.tablespace_name 表空间名称,
b.bytes/1024/1024 大小m,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用m,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 使用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
1
2
3
4
5
6
7
8
9
10

查看用户使用的表空间

select username,default_tablespace from dba_users;
1

修改数据文件大小

alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF' resize 10240M;
1

查看用户所在的表空间

select username,default_tablespace from dba_users order by username;
1

创建用户时指定表空间

create user username identified by password
default tablespace user_data
temporary tablespace user_temp;
1
2
3

修改用户对应的表空间

alter user c##zhang default tablespace ZHANGTEST01;
1

拓展表空间

首先查看表空间的名字和所属文件

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
1
2
3
4

方法一：增加数据文件

ALTER TABLESPACE game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;
1

方法二：手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;
1

方法三：设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;
1