oracle 临时表空间维护,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’;