表空间查询语句:
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;
这句可能不够详细
或者
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 UPPER(F.TABLESPACE_NAME) "name",
D.TOT_GROOTTE_MB "size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "used",
F.TOTAL_BYTES "availve(M)",
F.MAX_BYTES "max(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;
发现使用比例已经高达99%
这时候需要扩充下表空间文件
ALTER tablespace "表空间名称" add datafile'表空间文件名,最好是跟之前查询出来的文件名后缀啥的类似,然后这个文件名称用序号迭代上去即可' size 扩充的大小 AUTOEXTEND ON NEXT 自动延伸大小 maxsize 最大限制大小;
eg:
ALTER tablespace "EAS_D_HLT20220815_STANDARD" add datafile'/easdata/inUseData/EAS_D_HLT20220815_STANDARD3.ORA' size 30720M AUTOEXTEND ON NEXT 30720M maxsize UNLIMITED;
参考博客:
oracle数据库报错:ORA-01654: 索引 XXX 无法通过 128 (在表空间 xxx 中) 扩展_He_k的博客-CSDN博客