查看表空间的增长情况

冷不防 2021-12-16 20:11 288阅读 0赞

\--查询前一周表空间增长情况  
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