oracle 表空间猛增,查看表空间的增长情况，表空间增长

查看表空间的增长情况，表空间增长

--查询前一周表空间增长情况

SELECT C.TABLESPACE_NAME,

D."Total(MB)",

D."Used(MB)" - C."Used(MB)" AS "Increment(MB)",

TO_CHAR(NEXT_DAY(TRUNC(SYSDATE), 2) - 7, 'yyyy/mm/dd') || '--' ||

TO_CHAR(NEXT_DAY(TRUNC(SYSDATE), 2) - 7, 'yyyy/mm/dd') "TIME"

FROM (SELECT B.NAME TABLESPACE_NAME,

CASE

WHEN B.NAME NOT LIKE 'UNDO%' THEN

ROUND(A.TABLESPACE_SIZE * 8 / 1024)

WHEN B.NAME LIKE 'UNDO%' THEN

ROUND(A.TABLESPACE_SIZE * 8 / 1024 / 2)

END AS "Total(MB)",

ROUND(A.TABLESPACE_USEDSIZE * 8 / 1024) "Used(MB)",

A.RTIME

FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B

WHERE A.TABLESPACE_ID = B.TS#

AND TO_CHAR(TO_DATE(REPLACE(RTIME, '/', NULL),

'mmddyyyy hh24:mi:ss'),

'yyyymmdd hh24:mi') =

TO_CHAR(NEXT_DAY(TRUNC(SYSDATE), 2) - 14, 'yyyymmdd hh24:mi')) C,

(SELECT B.NAME TABLESPACE_NAME,

CASE

WHEN B.NAME NOT LIKE 'UNDO%' THEN

ROUND(A.TABLESPACE_SIZE * 8 / 1024)

WHEN B.NAME LIKE 'UNDO%' THEN

ROUND(A.TABLESPACE_SIZE * 8 / 1024 / 2)

END AS "Total(MB)",

ROUND(A.TABLESPACE_USEDSIZE * 8 / 1024) "Used(MB)",

A.RTIME

FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B

WHERE A.TABLESPACE_ID = B.TS#

AND TO_CHAR(TO_DATE(REPLACE(RTIME, '/', NULL),

'mmddyyyy hh24:mi:ss'),

'yyyymmdd hh24:mi') =

TO_CHAR(NEXT_DAY(TRUNC(SYSDATE), 2) - 7, 'yyyymmdd hh24:mi')) D

WHERE C.TABLESPACE_NAME = D.TABLESPACE_NAME;

--查询前一天表空间增长情况

SELECT C.TABLESPACE_NAME,

D."Total(MB)",

D."Used(MB)" - C."Used(MB)" AS "Increment(MB)",

TO_CHAR(TRUNC(SYSDATE - 1), 'yyyy/mm/dd') "TIME"

FROM (SELECT B.NAME TABLESPACE_NAME,

CASE

WHEN B.NAME NOT LIKE 'UNDO%' THEN

ROUND(A.TABLESPACE_SIZE * 8 / 1024)

WHEN B.NAME LIKE 'UNDO%' THEN

ROUND(A.TABLESPACE_SIZE * 8 / 1024 / 2)

END AS "Total(MB)",

ROUND(A.TABLESPACE_USEDSIZE * 8 / 1024) "Used(MB)",

A.RTIME

FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B

WHERE A.TABLESPACE_ID = B.TS#

AND TO_CHAR(TO_DATE(REPLACE(RTIME, '/', NULL),

'mmddyyyy hh24:mi:ss'),

'yyyymmdd hh24:mi') =

TO_CHAR(TRUNC(SYSDATE - 1), 'yyyymmdd hh24:mi')) C,

(SELECT B.NAME TABLESPACE_NAME,

CASE

WHEN B.NAME NOT LIKE 'UNDO%' THEN

ROUND(A.TABLESPACE_SIZE * 8 / 1024)

WHEN B.NAME LIKE 'UNDO%' THEN

ROUND(A.TABLESPACE_SIZE * 8 / 1024 / 2)

END AS "Total(MB)",

ROUND(A.TABLESPACE_USEDSIZE * 8 / 1024) "Used(MB)",

A.RTIME

FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B

WHERE A.TABLESPACE_ID = B.TS#

AND TO_CHAR(TO_DATE(REPLACE(RTIME, '/', NULL),

'mmddyyyy hh24:mi:ss'),

'yyyymmdd hh24:mi') =

TO_CHAR(TRUNC(SYSDATE), 'yyyymmdd hh24:mi')) D

WHERE C.TABLESPACE_NAME = D.TABLESPACE_NAME;

--在得知哪个表空间增长明显的情况下，再去查询该表空间的对象大小情况，TABLESPACE_NAME为某表空间名称

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES / 1024 / 1024 / 1024 GB

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME ='&TABLESPACE_NAME'

ORDER BY BYTES DESC;

SELECT *

FROM (SELECT OWNER,

SEGMENT_NAME,

SEGMENT_TYPE,

BYTES / 1024 / 1024 / 1024 GB

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME ='&TABLESPACE_NAME'

ORDER BY BYTES DESC)

WHERE ROWNUM < 30;

--如何确定某些对象容量增涨快，需要一定时间的跟踪，周期性查询该对象的容量。然后根据对象做相应后续处理。

http://www.dengb.com/oracle/1352327.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/1352327.htmlTechArticle查看表空间的增长情况，表空间增长 --查询前一周表空间增长情况 SELECT C.TABLESPACE_NAME, D."Total(MB)", D."Used(MB)" - C."Used(MB)" AS "Increment(MB)", TO_...