查询所有表空间：SELECT tablespace_name, status, contents FROM dba_tablespaces;

查询表空间大小：SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024,2) AS total_size_gb FROM dba_data_files GROUP BY tablespace_name;

查询表空间使用情况：SELECT tablespace_name, ROUND(used_space/1024/1024,2) AS used_mb, ROUND(free_space/1024/1024,2) AS free_mb FROM (SELECT tablespace_name, SUM(bytes) used_space FROM dba_extents GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) free_space FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;

表空间查询语句汇总

1. 查看所有表空间：select tablespace_name,file_name,bytes/1024/1024 size from dba_data_files;

2. 查看表空间剩余空间：select tablespace_name,file_id,file_name,bytes/1024/1024 size,autoextensible from dba_data_files;

3. 查看表空间使用率：select t.tablespace_name "表空间名",round(s.bytes/1024/1024,2) "总大小(M)",round(nvl(fs.bytes,0)/1024/1024,2) "已使用(M)",round((s.bytes-nvl(fs.bytes,0))/s.bytes*100,2) "使用率%" from dba_tablespaces t, (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) s, (select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name) fs where t.tablespace_name = s.tablespace_name(+) and t.tablespace_name = fs.tablespace_name(+);

Oracle表空间常用查询语句

表空间总大小：SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 AS GIGABYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ORDER BY 1;

表空间使用情况：select df.tablespace_name "表空间",totalusedspace "总使用量(M)",totalspace "总大小(M)",(round((totalusedspace/totalspace)*100,0)) "使用率%" from (select tablespace_name, sum(bytes) / 1024 / 1024 totalspace from dba_data_files group by tablespace_name) total,(select tablespace_name,sum(bytes)/1024/1024 totalusedspace from dba_extents group by tablespace_name) used,df.tablespace_name where df.tablespace_name = total.tablespace_name(+) and df.tablespace_name = used.tablespace_name(+ ) order by 4 desc;

详细表空间监控SQL

-- 当前表空间使用情况 SELECT a.tablespace_name, ROUND((a.bytes_free+a.bytes_used)/1024/1024,2) total_size, ROUND(a.bytes_free/1024/1024,2) free_size, ROUND(a.bytes_used/1024/1024,2) used_size, ROUND((a.bytes_used/(a.bytes_free+a.bytes_used))*100,2) used_percent FROM (SELECT tablespace_name, SUM(bytes) bytes_free, 0 bytes_used FROM dba_free_space GROUP BY tablespace_name UNION ALL SELECT tablespace_name, 0 bytes_free, SUM(bytes) bytes_used FROM dba_extents GROUP BY tablespace_name) a GROUP BY a.tablespace_name,a.bytes_free,a.bytes_used;

表空间文件信息查询

SELECT file_name,tablespace_name,bytes/1024/1024 AS size_mb,status,autoextensible,maxbytes/1024/1024 AS max_size_mb FROM dba_data_files ORDER BY tablespace_name;

FAQ

Q: 如何查看表空间是否自动扩展？
A: SELECT tablespace_name, autoextensible FROM dba_data_files;

Q: 表空间满了怎么办？
A: 可以添加数据文件或修改现有文件自动扩展参数：ALTER DATABASE DATAFILE 'file_path' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

Q: 如何查询用户默认表空间？
A: SELECT username, default_tablespace FROM dba_users;

Q: 临时表空间查询语句？
A: SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) AS total_size_mb FROM dba_temp_files GROUP BY tablespace_name;