oracle 扩展表空间

ALTER TABLESPACE ZYMES ADD DATAFILE /u02/CDB/mesdb/ZYMES08.dbf SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;1.查询表空间大小SELECT df.tablespace_name, ROUND(df.total_mb, 2) AS total_mb, ROUND(df.total_mb - NVL(fs.free_mb, 0), 2) AS used_mb, ROUND(NVL(fs.free_mb, 0), 2) AS free_mb, ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 2) AS pct_used FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb FROM dba_data_files GROUP BY tablespace_name) df LEFT JOIN (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb FROM dba_free_space GROUP BY tablespace_name) fs ON df.tablespace_name fs.tablespace_name ORDER BY pct_used DESC;2查询数据文件容量情况SELECT df.file_id, df.file_name, df.tablespace_name, ROUND(df.bytes / 1024 / 1024, 2) AS total_mb, ROUND(NVL(fs.free_bytes, 0) / 1024 / 1024, 2) AS free_mb, ROUND((df.bytes - NVL(fs.free_bytes, 0)) / 1024 / 1024, 2) AS used_mb, df.autoextensible, ROUND(df.maxbytes / 1024 / 1024, 2) AS max_mb FROM dba_data_files df, (SELECT file_id, SUM(bytes) AS free_bytes FROM dba_free_space GROUP BY file_id) fs WHERE df.file_id fs.file_id() AND df.tablespace_name ZYMES ORDER BY df.file_id;3立即添加新的数据文件最直接有效立即添加新的数据文件最直接有效因为前 5 个大文件已经无法扩展必须通过增加新文件来提升表空间总容量。建议至少添加 1~2 个 32GB 的文件sql-- 添加第一个新文件 ALTER TABLESPACE ZYMES ADD DATAFILE /u02/CDB/mesdb/ZYMES08.dbf SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G; -- 如果空间仍紧张再添加第二个 ALTER TABLESPACE ZYMES ADD DATAFILE /u02/CDB/mesdb/ZYMES09.dbf SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;4验证磁盘空间大小 df -h /u02