ALTER DATABASE TEMPFILE RESIZE

tempfile有20G，磁盘空间不太多了。

1、先创建一个新的temp2，修改默认temp ts
SQL> create temporary tablespace temp2 tempfile '/opt/oracle/oradata/conner/temp2.dbf' size 256M autoextend off;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp including contents and datafiles cascade constraints;
（彻底删除包括操作系统中的临时表空间的数据文件）
也可以再创建一个temp，修改回来
改为原来的temp
SQL> create temporary tablespace temp tempfile '/opt/oracle/oradata/conner/temp.dbf' size 256M autoextend off;
SQL> alter database default temporary tablespace temp;
SQL> drop tablespace temp2;
SQL> alter tablespace temp1 add tempfile '/u01/app/oracle/orcl/temp1.dbf' size 512m autoextend on next 256m；
2、查询默认temp表空间
查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
select username,temporary_tablespace,default_tablespace from dba_users;
3、alter database tempfile resize
SQL> alter database tempfile '/data1/oracle11g/oracle/oradata/test/temp01.dbf' resize 512M;
Database altered.
4、查询创建语句
select 'alter database tempfile '''||a.name ||''' resize '||b.siz||'M;' from v$tempfile a,
(select tmsize.maxblk*bk.value/1024/1024 siz from
(select nvl(max(segblk#),128) maxblk from v$sort_usage) tmsize,
(select value From v$parameter where name = 'db_block_size') bk) b
/

相关日志