SELECT --B.file_name "文件名", A.TABLESPACE_NAME "表空间名", TOTAL "表空间大小", FREE "表空间剩余大小", (TOTAL - FREE) "表空间使用大小", TOTAL / (1024 * 1024 * 1024) "表空间大小(G)", FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)", (TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)", ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %" FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, -- file_name, SUM(BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME /,file_name/ ) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;
=============表空间查询====================================== SELECT a.tablespace_name "表空间名", total "表空间大小", free "表空间剩余大小", (total - free) "表空间使用大小", total / (1024 * 1024 * 1024) "表空间大小(G)", free / (1024 * 1024 * 1024) "表空间剩余大小(G)", (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %" FROM(SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;
==============查看Oracle数据库表空间是否自动扩展====================== 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
