Oracle 数据库如何查看表空间及数据文件状态

---

---

1] 什么是 Oracle 的表空间

Oracle 将一个数据库划分为一个或多个逻辑存储单元，这些逻辑存储单元即为表空间。每个表空间包含一个或多个文件，这些文件被称为数据文件。数据文件是保存数据库对象，例如表、索引等到磁盘的物理实体。换句话说，表空间是 Oracle 数据库保存数据的逻辑实体，而数据文件是保存对应表空间中数据的物理实体。

2] Oracle 数据库的默认表空间

Oracle 数据库会默认创建以下表空间：SYSTEM、SYSAUX、USERS、UNDOTBS1 以及 TEMP。其中 SYSTEM 和 SYSAUX 存储系统创建时生成的数据对象，例如数据字典，用户是不能在这两个表空间中存储数据的；USERS 表空间对于临时用户来说是非常有用的；UNDOTBS1 存储撤消数据；TEMP 表空间用于存放排序、哈希计算以及大对象处理的中间数据。

3] 查询所有表空间容量以及空闲的查询语句

set lines 200 pages 100;
select df.tablespace_name "Tablespace",
nvl(totalusedspace,0) "Used MB",
(df.totalspace - nvl(tu.totalusedspace,0)) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2)
"Pct. Free",
round(100 * ( (df.totalspace - (df.totalspace - nvl(tu.totalusedspace,0)))/ df.totalspace),2)
"Pct. Used",
nvl(fs.free_space,0) extendable_free_space
, round(maxspace,2) maxspace
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
, sum(maxbytes)/1024/1024 maxspace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
,
(
select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name
) fs
where df.tablespace_name = tu.tablespace_name(+)
AND df.tablespace_name = fs.tablespace_name(+)
ORDER BY "Pct. Free";
select df.tablespace_name "Tablespace",
nvl(totalusedspace,0) "Used MB",
(df.totalspace - nvl(tu.totalusedspace,0)) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2)
"Pct. Free",
round(100 * ( (df.totalspace - (df.totalspace - nvl(tu.totalusedspace,0)))/ df.totalspace),2)
"Pct. Used",
nvl(fs.free_space,0) extendable_free_space
, round(maxspace,2) maxspace
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
, sum(maxbytes)/1024/1024 maxspace
from dba_temp_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
,
(
select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name
) fs
where df.tablespace_name = tu.tablespace_name(+)
AND df.tablespace_name = fs.tablespace_name(+)
ORDER BY "Pct. Free";

典型输出结果如下所示：

图.1 查询 Oracle 数据库表空间语句典型输出

4] 查询数据文件以及归属表空间语句

set lines 200 pages 100;
col FILE_NAME for a60;
select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "Size In MB", STATUS, AUTOEXTENSIBLE, ONLINE_STATUS from DBA_DATA_FILES;

典型输出如下所示：

图.2 查询 Oracle 数据文件语句输出

5] 查询临时表空间及临时文件的语句

set lines 200 pages 100;
col FILE_NAME for a60;
select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "Size In MB", STATUS, AUTOEXTENSIBLE from DBA_TEMP_FILES;

典型输出结果如下：

图.3 查询 Oracle 数据库临时表空间及临时文件语句输出

希望有文对于初步了解 Oracle 数据保存机制有所帮助。