Oracle 数据库表空间管理基础

在工作与学习中，Oracle 数据库是很常用的关系型数据库，一般很多系统在对数据库进行数据操作的过程中，常常会有一些错误是表空间不足引起的，因此一些简单的对 Oracle 表空间操作的 sql 语句还是要熟知的，便于定位报错原因。

注意：对表空间进行操作的 sql 需要在 dba 用户下执行！！！

一、查询表空间

select a.tablespace_name, a.file_name, status, 
       a.bytes / 1024 / 1024 "sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used %"
  from (select tablespace_name, file_id, file_name, status, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name, file_id, file_name, status) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 --order by ((a.bytes - b.bytes) / a.bytes) desc;
 order by a.file_id;

执行结果：

二、创建表空间

CREATE TABLESPACE TEST DATAFILE 'D:\OracleData\test\test.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
或者
CREATE TABLESPACE TEST DATAFILE 'D:\OracleData\test\test.dbf' SIZE 10M;

引用百度知道中牛人的解答：

​extent是“区间”的意思。在oracle数据库中：extent management 有两种方式：extent management local（本地管理）; extent management dictionary（数据字典管理），默认的是local。
每种管理也有两种大小增长方式：
uniform：默认为1M大小，在temp表空间里为默认的，但是不能被应用在undo表空间。
本地管理表空间与字典管理表空间相比大大提高了管理效率和数据库性能，其优点如下：
　　1.减少了递归空间管理
　　本地管理表空间是自己管理分配，而不是象字典管理表空间需要系统来管理空间分配，本地表空间是通过在表空间的每个数据文件中维持一个位图来跟踪在此文件中块的剩余空间及使用情况。并及时做更新。这种更新只对表空间的额度情况做修改而不对其他数据字典表做任何update操作，所以不会产生任何回退信息，从而大大减少了空间管理，提高了管理效率。同时由于本地管理表空间可以采用统一大小分配方式(UNIFORM),因此也大大减小了空间管理，提高了数据库性能。
　　2.系统自动管理extents大小或采用统一extents大小
　　本地管理表空间有自动分配(AUTOALLOCATE)和统一大小分配(UNIFORM)两种空间分配方式，自动分配方式(AUTOALLOCATE)是由系统来自动决定extents大小，而统一大小分配(UNIFORM)则是由用户指定extents大小。这两种分配方式都提高了空间管理效率。
　　3.减少了数据字典之间的竞争
　　因为本地管理表空间通过维持每个数据文件的一个位图来跟踪在此文件中块的空间情况并做更新，这种更新只修改表空间的额度情况，而不涉及到其他数据字典表，从而大大减少了数据字典表之间的竞争，提高了数据库性能。
　　4.不产生回退信息
　　因为本地管理表空间的空间管理除对表空间的额度情况做更新之外不修改其它任何数据字典表，因此不产生回退信息，从而大大提高了数据库的运行速度。
　　5.不需合并相邻的剩余空间
　　因为本地管理表空间的extents空间管理会自动跟踪相邻的剩余空间并由系统自动管理，因而不需要去合并相邻的剩余空间。同时，本地管理表空间的所有extents还可以具有相同的大小，从而也减少了空间碎片。
　　6.减少了空间碎片
　　7.对临时表空间提供了更好的管理
autoallocate:
You can convert a tablespace from dictionary extent management to local extent management
and back with the Oracle-supplied PL/SQL package DBMS_SPACE_ADMIN. The SYSTEM
tablespace and any temporary tablespaces, however, cannot be converted from local to the
older style dictionary managem
两种extent管理方式是可以相互转换的，利用PL/SQL DBMS_SPACE_ADMIN
但是系统表空间和临时表空间不能从local管理转化到dictionary管理。

执行成功后查询数据库结果：

执行成功后查询指定路径下文件：

三、修改表空间

3.1、修改大小

alter database datafile 'D:\OracleData\test\test.dbf' resize 5M;

修改成功后查询数据库结果：

备注：一般修改表空间大小，都是由于空间太小而扩展，一般不会从大修改为小，这里只是测试而已。

3.2、自动扩展

alter database datafile 'D:\OracleData\test\test.dbf' autoextend on next 1M maxsize 100M;

当存储空间不够用时，数据库会自动每次扩展 1M 大小。

修改前：

修改后：

3.3、增加文件

alter tablespace TEST add datafile 'D:\OracleData\test\test2.dbf' size 2M;

增加成功后查询数据库结果：

增加成功后查询指定路径下文件：

四、删除表空间

drop tablespace TEST including contents and datafiles; 

删除成功后查询数据库结果：

删除成功后查询指定路径下文件：

注意：如果直接删除路径下的文件，则是删除不了的！！！