oracle表空间操作

On By 去哪儿

表空间查询

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;

更改数据表大小

alter database datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/APEX_2601085220352001.dbf' resize 10240m;

查看是否增

SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

更改数据表大小

alter database datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/APEX_2601085220352001.dbf' resize 10240m;

表空间自增

alter tablespace APEX_2601085220352001 add datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/APEX_26010852203520011.dbf' size 200M autoextend on maxsize 30G;
commit
语句说明：给表空间APEX_2601085220352001  增加APEX_26010852203520011.dbf 的空间，如果APEX_2601085220352001  使用满了就会使用APEX_26010852203520011的空间