- 根据表空间名称,查看表空间大小等情况
select tablespace_name, file_id, file_name, round(bytes/(1024*1024), 0) total_space from dba_data_files where tablespace_name = 'SYSAUX';
SELECT t.tablespace_name, round(sum(bytes/(1024*1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name and t.tablespace_name = 'SYSAUX' group by t.tablespace_name;
查看剩余空间
select sum(bytes) / (1024*1024) as free_space,tablespace_name from dba_free_space where tablespace_name ='SYSAUX' group by tablespace_name;
查看使用 和 剩余量
select a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, round((b.bytes*100)/a.bytes, 2) "% used",
round((c.bytes * 100) / a.bytes, 2) "% free"
from sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name and a.tablespace_name = 'SYSAUX';
- 为SYSAUX表空间增加数据文件
alter tablespace SYSAUX add datafile 'D:\APP\ZSDORACLE\ORADATA\ORCL\SYSAUX_20180502.DBF' SIZE 1G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
其中:表空间名称、路径地址、初始文件大小等参数根据自身情况去定义。
检查结果
select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'SYSAUX';
参考:https://blog.csdn.net/qq_25391785/article/details/66970349
https://www.cnblogs.com/iceless/p/7124509.html