Oracle-临时表空间操作总结 TEMP

##创建临时表空间

create temporary tablespace TEMP tempfile '/oracle/app/oracle/oradata/baoyw/temp_01.dbf' size 10g;
create temporary tablespace TEMP tempfile '+DATA' size 20g;

##新增临时表空间数据文件

alter tablespace TEMP add tempfile '/oracle/app/oracle/oradata/baoyw/temp_02.dbf' size 30g;
alter tablespace TEMP add tempfile '+DATA' size 30g;

##修改默认临时表空间为 TEMP01

alter database default temporary tablespace TEMP01;

##查看用户的默认临时表空间

SELECT user_id,
        username,
        account_status,
        to_char(created,
         'yyyymmdd'),default_tablespace,temporary_tablespace
FROM dba_users;

##查看正在使用临时表空间的会话

select username,sql_id,tablespace,segtype,segfile# from v$tempseg_usage;

##或者
set linesize 200;
col username for a18;
col process for a16;
col program for a18;
col tablespace for a10;
SELECT S.SID,
        S.SERIAL#,
        S.USERNAME,
        S.PROCESS,
        S.PROGRAM,
        S.LOCKWAIT,
        S.STATUS,
        S.BLOCKING_SESSION_STATUS,
        T.SQL_ID,
        T.TABLESPACE,
        T.SEGTYPE,
        T.SEGFILE#,
        P.PID,
        P.SPID
FROM V$TEMPSEG_USAGE T, V$SESSION S, V$PROCESS P
WHERE T.SESSION_ADDR = S.SADDR
        AND S.PADDR = P.ADDR
ORDER BY  S.SID;

##v$TEMPSEG_USAGE 描述临时段的使用情况

SQL_ID: SQL identifier of SQL statement
TABLESPACE: Tablespace in which space is allocated
SEGTYPE: Type of sort segment:SORT\HASH\DATA\INDEX\LOB_DATA\LOB_INDEX
SEGFILE#: File number of initial extent

##拼接字段 是为了将临时表空间的会话做KILL处理

SELECT 'alter system kill session'||' '||''''||s.sid||','||s.serial#||''''||';'
FROM V$TEMPSEG_USAGE T, V$SESSION S, V$PROCESS P
WHERE T.SESSION_ADDR = S.SADDR
        AND S.PADDR = P.ADDR;

##查看TEMP表空间数据文件状态

col file_name for a60;
SELECT file_name,
        file_id,
        tablespace_name,
        bytes,
        status,
        autoextensible
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';

##离线TEMP表空间

alter database tempfile 4 offline;

##在线临时表空间

alter database tempfile 1 online;

##扩容TEMP表空间

alter database tempfile 1 resize 30g;

##缩容TEMP表空间

alter database tempfile 1 resize 1g;

##删除临时表空间中的数据文件

alter database tempfile 1 drop;

##删除临时表空间及数据文件

drop tablespace TEMP including contents and datafiles;

##数据文件中含有数据的处理方法

#1
select owner,segment_name,tablespace_name from dba_extents where file_id = '6'
#2
alter table table_name move tablespace tablespace_name;
#3
index_rebbulid
#4
alter tablespace tablespace_name drop datafile 6;