-- 登录
sqlplus / as sysdba

-- 设定显示格式
SET LINESIZE 1200
COL TABLESPACE_NAME FOR A30
COL FILE_NAME FOR A60;

-- 查看所有非临时表空间
select
tablespace_name,
file_name,
STATUS,
AUTOEXTENSIBLE,
INCREMENT_BY,
BYTES/1024/1024 AS "FILE_SIZE(M)",
MAXBYTES/1024/1024 AS "MAX_SIZE(M)",
USER_BYTES/1024/1024 AS "USEFUL_SIZE"
from dba_data_files;

-- 更改非临时表空间大小
alter database datafile '/opt/oracle/oradata/isdb/system01.dbf' resize 4000M;

-- 设定非临时表空间自动扩张
ALTER DATABASE datafile '/opt/oracle/oradata/isdb/system01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED;

-- 查看临时表空间
SELECT
tablespace_name,
USER_BYTES/1024/1024 AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;

-- 更改临时表空间大小
ALTER DATABASE TEMPFILE '/opt/oracle/oradata/isdb/temp01.dbf' RESIZE 4G;

-- 设定临时表空间自动扩张 ALTER DATABASE TEMPFILE '/opt/oracle/oradata/isdb/temp01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;