1.查看临时表空间的大小 和 数据文件路径
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE(M)"
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = ‘TEMP‘;
或者
select name, bytes/1024/1024 as "大小(M)" from v$tempfile order by bytes;
2.在删除默认临时表空间必须先重新指定默认临时表空间
SQL> drop tablespace temp including contents and datafiles;
ORA-12906: cannot drop default temporary tablespace
3.查询当前数据库默认临时表空间名
select * from database_properties where property_name=‘DEFAULT_TEMP_TABLESPACE‘;
4.创建新的临时表空间
create temporary tablespace temp02 tempfile ‘E:\oracle\oradata\lims\TEMP02.DBF‘ size 1024M autoextend on;
5.修改默认表空间为刚刚建立的临时表空间
alter database default temporary tablespace temp02;
6.查看用户所用临时表空间的情况
SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;
或者
SQL> select property_name,property_value from database_properties where property_name=‘DEFAULT_TEMP_TABLESPACE‘;
PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP02
7.删除原来的临时表空间
drop tablespace temp including contents and datafiles;
8.查看所有表空间名确认临时表空间是否已删除
select tablespace_name from dba_tablespaces;
9.为表空间添加数据文件
ALTER TABLESPACE TEMP02 ADD TEMPFILE ‘D:\ORACLE11G\ORACLE_11G\ORADATA\ORCL\TEMP02.DBF‘ SIZE 20480M;