Oracle临时表空间日常维护SQL

临时表空间日常维护SQL：

1.确定数据库schema的默认表空间
select username,temporary_tablespace from dba_users where account_status =‘OPEN’;

2.确定数据库的默认临时表空间
select * from database_properties where property_name =‘DEFAULT_TEMP_TABLESPACE’;

1.创建中转临时表空间 
create temporary tablespace temp2 tempfile ‘/data/app/oracle/oradata/prod/temp02.dbf’ size 512M autoextend on next 1M maxsize unlimited;

2.改变缺省临时表空间为中转临时表空间TEMP2 
alter database default temporary tablespace TEMP2;
验证用户的临时表空间为TEMP2
select username,temporary_tablespace from dba_users where account_status =‘OPEN’;

3.删除原临时表空间 
drop tablespace temp including contens and datafiles;

4.重建临时表空间 
create temporary tablespace temp tempfile ‘/data/app/oracle/oradata/prod/temp01.dbf’ size 512M autoextend on next 1M maxsize unlimited;

5.重置缺省临时表空间为新建的TEMP表空间 
alter database default temporary tablespace TEMP1;
验证用户的临时表空间为TEMP
select username,temporary_tablespace from dba_users where account_status =‘OPEN’;