重建oracle临时表空间

1、首先查询用户的缺省临时表空间
sqlplus "/ as sysdba"
select username,temporary_tablespace from dba_users;
select name from v$tempfile;

 

2、重建新的临时表空间并进行切换:
create temporary tablespace temp tempfile 'd:oracleoradatawebora9temp01.dbf' size 100M extent management local uniform size 1M
alter database default temporary tablespace temp;

 

3、删除其他临时表空间和数据文件：
drop tablespace temp1 including contents and datafiles;

 

4、查询表空间的使用情况：
Select Tablespace_Name, Sum(bytes) / 1024 / 1024
  From Dba_Segments
 Group By Tablespace_Name
 order by Tablespace_Name;

select a.a1 表空间名称,c.c2 类型,c.c3 区管理,b.b2 / 1024 / 1024 表空间大小M,(b.b2 - a.a2) / 1024 / 1024 已使用M,substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
  from (select tablespace_name a1, sum(nvl(bytes, 0)) a2 from dba_free_space group by tablespace_name) a,
       (select tablespace_name b1, sum(bytes) b2 from dba_data_files  group by tablespace_name) b,
       (select tablespace_name c1, contents c2, extent_management c3 from dba_tablespaces) c
 where a.a1 = b.b1 and c.c1 = b.b1
 order by a.a1;

select b.file_name 数据文件名,b.tablespace_name 表空间,b.bytes/1024/1024 大小M,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

select a.tablespace_name 表空间名称,b.bytes 大小bytes,b.file_name 数据文件名
from dba_tablespaces a, dba_data_files b
Where a.tablespace_name=b.tablespace_name and a.contents='TEMPORARY';

Select * from V$TEMPFILE;

select * from v$temp_space_header;

 

 

Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text 　　from v$sort_usage su,
       v$parameter p,
       v$session se,
       v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr 　　
 order by se.username, se.sid