查询表空间使用率:
select upper(f.tablespace_name) "表空间名",
d.tot_grootte_mb "表空间大小(M)",
d.tot_grootte_mb - f.total_bytes "已使用空间(M)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
2),
'990.99') "使用比",
f.total_bytes "空闲空间(M)",
f.max_bytes "最大块(M)"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 4 desc;
查询表文件存储位置:
select t.* from sys.dba_data_files t where t.tablespace_name ='tablespace_name'
查询出路径如下:/oracle/data/DS.DBF
扩容的三种方式:
1、对ds表空间增加存储文件操作:
ALTER TABLESPACE DS ADD DATAFILE '/oracle/data/DS2.DBF' SIZE 1024M REUSE;
2、还可以增加 DS.DBF 文件大小进行扩容:
alter database datafile '/oracle/data/DS.DBF' resize 5000M;
3、设置表空间自动扩容
alter database datafile '/oracle/data/DS.DBF' autoextend on next 500m maxsize 10000m;