oracle 回滚段 原理,Oracle 回滚段

--查询数据文件

select t.TABLESPACE_NAME, --表空间名

t.FILE_NAME, --文件名

t.AUTOEXTENSIBLE, --是否自动扩展

t.BYTES / 1024 / 1024 as tsize, --表空间初始大小

t.MAXBYTES / 1024 / 1024 msize, --表空间最大扩展到多少

b.CONTENTS, --表空间类型

b.EXTENT_MANAGEMENT --表空间管理模式

from dba_temp_files t, dba_tablespaces b

where t.TABLESPACE_NAME = b.TABLESPACE_NAME

--查询曾经的最大使用率

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,

USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",

NVL(FREE_SPACE,0) "FREE_SPACE(M)"

FROM

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

--查询当前的使用率

SELECT D.tablespace_name,

SPACE "SUM_SPACE(M)",

blocks "SUM_BLOCKS",

used_space "USED_SPACE(M)",

Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",

SPACE - used_space "FREE_SPACE(M)"

FROM (SELECT tablespace_name,

Round(SUM(bytes) / (1024 * 1024), 2) SPACE,

SUM(blocks) BLOCKS

FROM dba_temp_files

GROUP BY tablespace_name) D,

(SELECT tablespace,

Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE

FROM v$sort_usage

GROUP BY tablespace) F

WHERE D.tablespace_name = F.tablespace(+)

AND D.tablespace_name in ("TEMP")

--增加表空间

ALTER TABLESPACE temp add tempFILE "+SSDDG/SEA/TEMPFILE/temp061" SIZE 32000M;