一,切换临时表空间
1.查看默认表空间:
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
2.查看表空间使用情况:
select file_id,file_name,tablespace_name,bytes,maxbytes,autoextensible,increment_by from dba_temp_files;
3.创建新的临时表空间:
create temporary tablespace TEMP1 tempfile '/u01/oracle/app/oradata/orcl/temp1.dbf' size 50m autoextend on next 5M maxsize unlimited;
4.修改新建的临时表空间为默认表空间:
alter database default temporary tablespace TEMP1;
5.再次查看:
修改成功
二,undo表空间
1.查看undo表空间信息
show parameter undo_tablespace
select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1';
2.创建新的undo表空间
create undo tablespace UNDOTBS2 datafile '/u01/oracle/app/oradata/orcl/undotbs02.dbf' size 50M;
3.切换新建的表空间为undo表空间
alter system set undo_tablespace = undotbs2 scope=both;
4.此时查看原来的表空间为offline状态
select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
5.查看原来的表空间中没有online的segment
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
6.删除旧的UNDOTBS1
Drop tablespace UNDOTBS1 including contents and datafiles;
7.查看此时的表空间
show parameter undo_tablespace;
修改完成!