一、查询表空间使用情况
SELECT
a.tablespace_name AS "表空间名",
a.bytes / 1024 / 1024 AS "表空间大小(M)",
( a.bytes - b.bytes ) / 1024 / 1024 AS "已使用空间(M)",
b.bytes / 1024 / 1024 "空闲空间(M)",
round((( a.bytes - b.bytes ) / a.bytes ) * 100, 2 ) "使用比"
FROM
( SELECT tablespace_name, sum( bytes ) bytes FROM dba_data_files GROUP BY tablespace_name ) 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;
二、扩展表空间
1、查看表空间的名字及文件所在位置
SELECT
tablespace_name,
file_id,
file_name,
round( bytes / ( 1024 * 1024 ), 0 ) total_space
FROM
dba_data_files
ORDER BY
tablespace_name
2、增加该表空间数据文件
alter tablespace SYSAUX add datafile '/oracle/oradata/orcl11g/new_sysaux01.dbf' size 500m AUTOEXTEND ON NEXT 200M >
文章来源：https://blog.csdn.net/codepeter/article/details/108123977
