Oracle 临时表空间问题小结__tablespace_TABLESPACE_NAME_alter_TOT_GROOTTE_MB_SQL_

当前位置: 数据库>oracle

Oracle 临时表空间问题小结

来源: 互联网发布时间：2017-03-28

本文导语: 1>用户临时缺省表空间的切换 --查询用户缺省临时表空间SQL> select username,temporary_tablespace from dba_users;USERNAME TEMPORARY_TABLESPACE------------------------------ ------------------------------MGMT_VIEW TEMPSYS TEMPSYSTEM TEMPDBSNMP TEMPSYSMAN TEMPSCOTT TEMPHR TEMPOU...

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 rownumAlter 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 (F.TABLESPACE_NAME) "tablesacpe name",
D.TOT_GROOTTE_MB "tablespace size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "have used(M)",
(((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,
((BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
((BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
((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