查看表空间的增长情况

--查询前一周表空间增长情况
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;
--如何确定某些对象容量增涨快，需要一定时间的跟踪，周期性查询该对象的容量。然后根据对象做相应后续处理。

转载于:https://www.cnblogs.com/ritchy/p/10937902.html