1、查看数据库所有表空间
select tablespace_name, sum(bytes)/1024/1024 proportion from dba_data_files group by tablespace_name;
2、查看具体表单所占空间
Select Segment_Name,Sum(bytes)/1024/1024 proportion From User_Extents Group By Segment_Name;
3、查看所有表空间占用率
SELECT
c.ts#, c.name,
d.contents, d.extent_management,
e.file_bytes, c.used,
SUBSTR (c.used / e.file_bytes * 100, 1, 5) proportion --占比
FROM (SELECT name, ts#, SUM(used) used
FROM (SELECT a.allocated_space * (SELECT value -- 查询db_block_size当前值
FROM v$parameter
WHERE name = 'db_block_size') / 1024/ 1024 used,
b.ts#, b.name
FROM v$filespace_usage a, v$tablespace b
WHERE a.tablespace_id = b.ts#)
GROUP BY name, ts#
) c,dba_tablespaces d,
(SELECT ts#, SUM(bytes) / 1024/ 1024 file_bytes FROM v$datafile GROUP BY ts#) e
WHERE c.name = d.tablespace_name
AND e.ts# = c.ts#
ORDER BY ts#
4、查看具体dbf表空间占用率
在这里插入代码片
select b.file_id,/文件ID/
b.tablespace_name,/表空间/
b.file_name,/物理文件名/
b.bytes,/总字节数/
(b.bytes-sum(nvl(a.bytes,0))) Used,/已使用/
sum(nvl(a.bytes,0)) surplus,/剩余/
sum(nvl(a.bytes,0))/(b.bytes)*100 proportion/剩余百分比/
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.file_id,b.bytes
order by b.tablespace_name
5、检查数据文件路径
Select * From dba_data_files t where t.TABLESPACE_NAME = 'HIP_DATA';
6、追加数据文件
Alter Tablespace HIP_DATA Add Datafile '/data/oradata/DB_20240914.dbf' SIZE 20G;
7、扩容原数据文件
alter database datafile '/data/oradata/DB_20240914.dbf' RESIZE 20G;