- 查看
col name for a44
select file#,name,bytes/1024/1024 MB from v$tempfile;
select * from dba_temp_free_space;
- 使用临时表空间的SHRINK方法缩小临时表空间的大小
alter tablespace temp shrink space;
select file#,name,bytes/1024/1024 MB from v$tempfile;
- 收缩表空间中具体的临时文件
模拟临时文件大小为100M
alter database tempfile '/oracle/ora19c/oradata/test/temp01.dbf' resize 1024m;
针对具体的临时文件进行收缩
alter tablespace temp shrink tempfile '/oracle/ora19c/oradata/test/temp01.dbf' keep 10m;
验证收缩结果
select file#,name,bytes/1024/1024 MB from v$tempfile;
- 查看当前用户默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
- 查看所有用户的默认表空间和默认临时表空间
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;