Oracle 临时表空间问题小结
1>用户临时缺省表空间的切换
--查询用户缺省临时表空间 SQL> select username,temporary_tablespace from dba_users; USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ MGMT_VIEW TEMP SYS TEMP SYSTEM TEMP DBSNMP TEMP SYSMAN TEMP SCOTT TEMP HR TEMP OUTLN TEMP MDSYS TEMP ORDSYS TEMP EXFSYS TEMP DMSYS TEMP WMSYS TEMP CTXSYS TEMP ANONYMOUS TEMP XDB TEMP ORDPLUGINS TEMP SI_INFORMTN_SCHEMA TEMP OLAPSYS TEMP TSMSYS TEMP BI TEMP PM TEMP MDDATA TEMP IX TEMP SH TEMP DIP TEMP OE TEMP 27 rows selected.
|
--创建新的临时表空间
SQL> create temporary tablespace temp2 tempfile '/u02/temp1.dbf' size 10M; Tablespace created.
--需要的话
--alter tablespace temp add tempfile '/u02/temp2.dbf' 10M; |
--进行切换
SQL> alter database default temporary tablespace temp2 ; Database altered.
--查询是否成功切换 SQL> select username,temporary_tablespace from dba_users; USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ MGMT_VIEW TEMP2 SYS TEMP2 SYSTEM TEMP2 DBSNMP TEMP2 SYSMAN TEMP2 SCOTT TEMP2 HR TEMP2 OUTLN TEMP2 MDSYS TEMP2 ORDSYS TEMP2 EXFSYS TEMP2 DMSYS TEMP2 WMSYS TEMP2 CTXSYS TEMP2 ANONYMOUS TEMP2 XDB TEMP2 ORDPLUGINS TEMP2 SI_INFORMTN_SCHEMA TEMP2 OLAPSYS TEMP2 TSMSYS TEMP2 BI TEMP2 PM TEMP2 MDDATA TEMP2 IX TEMP2 SH TEMP2 DIP TEMP2 OE TEMP2 27 rows selected.
|
2>表空间满方式1:重启数据库
方式2:
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
|
方式3:
1,查看谁在用临时段
SELECT username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr
或
--显示当前top5临时段的session
select * from
(select sess.username,sess.SID,sess.serial#,segtype,blocks*8/1024 "MB",sql_text
from v$sort_usage sort, v$session sess ,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sort.SQLADDR (+)
order by blocks desc
) a
where rownum<6;
2,正在使用临时段的进程
SQL>Alter system kill session 'sid,serial#';
3,把临时表空间回缩下
SQL>Alter tablespace TEMP coalesce;
--迅速收缩临时段(适用于临时段表空间收缩很慢的情况)
alter tablespace temp default storage(pctincrease 1);
alter tablespace temp default storage(pctincrease 0);
方法四:
1确定temp表空间ts#
SQL> select ts#, name from sys.ts$ ; TS# NAME ---------- -------------------- 0 SYSTEM 1 UNDOTBS1 2 SYSAUX 3 TEMP 4 USERS 5 UNDOTBS2 6 EXAMPLE 7 TSS 8 TEMP2
|
2执行清理
SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 4' ; Session altered.
|
temp表空间的TS# 为 3*, So TS#+ 1= 4
SELECT UPPER(F.TABLESPACE_NAME) "tablesacpe name", D.TOT_GROOTTE_MB "tablespace size(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "have used(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "using rate", F.TOTAL_BYTES "free space(M)", F.MAX_BYTES "max block(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC
|
评论暂时关闭