查看临时表空间大小
select tablespace_name,file_name,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_name,file_name;(临时表空间)
临时表空间添加数据文件
alter tablespace tmp1 add tempfile ‘/oracle/app/oradata/wlh1/tmp02.dbf’ size 55m autoextend on;
查看用户默认temp表空间
select username,TEMPORARY_TABLESPACE from dba_users;
建新的temp表空间
CREATE temporary TABLESPACE ntemp TEMPFILE ‘+NEWDATA/rac/ntemp01.dbf’ SIZE 100m autoextend off;
如果用户的默认表空间只有一个,修改全部的默认temp表空间
alter database default temporary tablespace ntemp;
如果用户的默认表空间有多个,分别重新设置这些用户的默认temp表空间
select ‘alter user ‘||username||’ TEMPORARY TABLESPACE temp03;’ from dba_users where TEMPORARY_TABLESPACE=‘TEMP’;
删除temp表空间(如果用的到的话)
drop tablespace ntemp including contents and datafiles;
–查询temp表空间使用率:
select df.tablespace_name “Tablespace”,
df.totalspace “Total(MB)”,
nvl(FS.UsedSpace, 0) “Used(MB)”,
(df.totalspace - nvl(FS.UsedSpace, 0)) “Free(MB)”,
round(100 * (1 - (nvl(fs.UsedSpace, 0) / df.totalspace)), 2) “Pct. Free(%)”
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name,
ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+);
1、使用如下语句查看一下认谁在用临时段
SELECT
se.USERNAME,
se.sid,
se.SERIAL#,
se.sql_address,
se.machine,
se.program,
su.tablespace,
su.segtype,
su.contents
FROM
v
s
e
s
s
i
o
n
s
e
,
v
session se, v
sessionse,vsort_usage su
WHERE se.saddr=su.session_addr;
2、杀死正在使用临时段的会话
SQL>Altersystem kill session ‘sid,serial#’;
3、把TEMP表空间回缩一下
SQL>alter tablespace TEMP coalesce;