[DB] Oracle 表空间达到 32GB 后扩容的方法

背景

Oracle 表空间数据文件容量与 DB_BLOCK_SIZE 参数有关，物理文件最大允许 4194304 个数据块（由操作系统决定）。
如果初始建库时 DB_BLOCK_SIZE = 8K，数据文件最大体积为 32GB（32768MB）。
4194304 × 8 / 1024 = 32768 (MB)
当表空间容量达到最大值时，数据库就不能继续增加信息了。

查看表空间

/* 查看表空间物理文件的名称及大小 */
SELECT tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) "file size (MB)",
       round(bytes / (1024 * 1024 * 1024), 0) "file size (GB)"
  FROM dba_data_files
 ORDER BY tablespace_name;

/*查看表空间的使用情况*/
SELECT DF.TABLESPACE_NAME "Tablespace",
       DF.TOTALSPACE "Total GB",
       TOTALUSEDSPACE "Used GB",
       ROUND(100 * (TU.TOTALUSEDSPACE / DF.TOTALSPACE)) || '%' "Pct. Used",
       (DF.TOTALSPACE - TU.TOTALUSEDSPACE) "Free GB",
       ROUND(100 * ((DF.TOTALSPACE - TU.TOTALUSEDSPACE) / DF.TOTALSPACE)) || '%' "Pct. Free"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 4) TOTALSPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME
        UNION
        SELECT TABLESPACE_NAME,
               round(SUM(BYTES) / (1024 * 1024 * 1024), 4) TBS_SIZE
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) DF,
       (SELECT ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 4) TOTALUSEDSPACE,
               TABLESPACE_NAME
          FROM DBA_SEGMENTS
         GROUP BY TABLESPACE_NAME) TU
 WHERE DF.TABLESPACE_NAME = TU.TABLESPACE_NAME(+)
   and df.tablespace_name <> 'TEMP'
 order by df.tablespace_name;

 扩展表空间

/*增加表空间*/
alter tablespace USERS add datafile 'D:\app\oradata\ORCL\DATAFILE\USERS_EXTEND_20230223.DBF' size 200M autoextend on next 50M maxsize unlimited;

参考资料

https://cloud.tencent.com/developer/article/1691934
http://www.oraask.com/2021/04/check-tablespace-usage-in-oracle.html
https://www.cnblogs.com/zjfjava/p/9556311.html