Oracle-重建临时表空间 rebuild temporary tablespace

查询用户的默认表空间/临时表空间 TEMP

select userid,
       username,
       account_status,
       to_char(created, 'yyyymmdd'),
       default_tablespace,
       temporary_tablespace
  from dba_users
 where username = 'TESTUSER';

修改用户的临时表空间为TEMP

alter user TESTUSER temporary tablespace temp;

删除用户临时表空间​​TEMPTEST1​及数据文件

drop tablespace TEMPTEST1 including contents and datafiles;

查看用户的临时表空间数据文件

select tablespace_name
      ,file_name
      ,file_id
      ,status
      ,bytes / 1024 / 1024
  from dba_temp_files;100

创建用户的临时表空间​​TEMPTEST1​​

create temporary tablespace TEMPTEST1 tempfile '+DATA' size 100M autoextend on;

​​​​修改用户的临时表空间为​​TEMPTEST1​​

alter user HSLIQPOWER temporary tablespace TEMPTEST1;

查看用户的临时表空间数据文件

select tablespace_name
      ,file_name
      ,file_id
      ,status
      ,bytes / 1024 / 1024
  from dba_temp_files;

其他的一些操作：

select s.sid,
       u.inst_id,
       u.username,
       u.tablespace,
       segfile#,
       u.sql_id,
       s.program,
       s.machine,
       s.event,
       s.status,
       s.last_call_et
  from gv$sort_usage u, gv$session s
 where u.session_addr = s.saddr
   and u.inst_id = s.inst_id
   and u.username = s.username
   and u.segfile# = 202
 order by 7;

select s.sid, s.serial#, u.username, u.tablespace
  from gv$sort_usage u, gv$session s
 where u.session_addr = s.saddr
   and u.inst_id = s.inst_id
   and u.username = s.username
   and u.segfile# = 202
 order by 1;

select 'alter system kill session ''' || sid || ',' || serial# || ''';'
  from v$session
 where program = 'function.exe'
   and username = 'TESTUSER';

select username, program, count(*)
  from gv$session
 where username = 'TESTUSER'
 group by username, program
 order by 3;