oracle表空间调整

--查询表空间使用情况
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 tablespace_name, sum(bytes) / 1024 / 1024  from dba_data_files group by tablespace_name;

--查看是否开启自增
SELECT file_name,
       tablespace_name,
       autoextensible,
       maxbytes / (1024 * 1024) "Max Size in MB"
FROM dba_data_files
WHERE tablespace_name = 'APEX_2180168206453338';

--查看表空间件路径
SELECT file_name, tablespace_name, bytes / (1024 * 1024) "Size in MB"
FROM dba_data_files
ORDER BY file_id;

--调整表空间自增最大值
ALTER DATABASE DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/APEX_2180168206453338.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 2048M;
ALTER DATABASE DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/APEX_2180168206453338.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;;

--调增表空间
ALTER DATABASE DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/APEX_2180168206453338.dbf' RESIZE 2048M;