- Release tablespace datafile
-
点击(此处)折叠或打开
-
col name for a40
-
col resizecmd for a80
-
SELECT a.file#,
a.name,
a.bytes / 1024 / 1024 CurrentMB,
CEIL (hwm * a.block_size) / 1024 / 1024 ResizeTo,
(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
'alter database datafile '
|| a.name
|| ' resize '
|| ROUND ( (CEIL (HWM * a.block_size) / 1024 / 1024 + 100), -2) -
|| 'M;'
ResizeCmd
FROM v$datafile a,
( SELECT file_id, MAX (block_id + blocks - 1) HWM
FROM DBA_EXTENTS
GROUP BY file_id) b
WHERE a.file# = b.file_id(+) AND (a.bytes - HWM * a.block_size) > 0
ORDER BY 5;
-
col name for a40
- check tablespace usage
- 点击(此处)折叠或打开
-
-
SELECT B.TABLESPACE_NAME TBSNAME,B.TOTAL_MB-NVL(A.FREE_MB,0) USED_MB,
-
ROUND(((B.TOTAL_MB-NVL(A.FREE_MB,0))/(B.TOTAL_ALLOCATE_MB+NVL(C.NONAUTO,0)))*100,2) USED_PERCENT,
-
B.TOTAL_MB TOTAL_MB,B.TOTAL_ALLOCATE_MB+NVL(C.NONAUTO,0) MAX_ALLOCATE_MB,
-
(B.TOTAL_ALLOCATE_MB+NVL(C.NONAUTO,0))-(B.TOTAL_MB-NVL(A.FREE_MB,0)) Real_Free FROM
-
(SELECT TABLESPACE_NAME,SUM(BYTES)/1048576 FREE_MB FROM DBA_FREE_SPACE GROUP BY
-
TABLESPACE_NAME) A,
-
(SELECT TABLESPACE_NAME,SUM(BYTES)/1048576 TOTAL_MB, SUM(MAXBYTES)/1048576 TOTAL_ALLOCATE_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B,
-
(SELECT SUM(BYTES)/1048576 NONAUTO, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE MAXBYTES =\'0\' GROUP BY TABLESPACE_NAME) C
-
WHERE A.TABLESPACE_NAME(+)=B.TABLESPACE_NAME
-
AND C.TABLESPACE_NAME(+)=B.TABLESPACE_NAME
-
AND B.TABLESPACE_NAME NOT LIKE \'UNDO%\'
-
AND B.TABLESPACE_NAME NOT IN (\'SYSTEM\', \'DBVISION\')
- ORDER BY USED_PERCENT DESC ;
-
SELECT B.TABLESPACE_NAME TBSNAME,B.TOTAL_MB-NVL(A.FREE_MB,0) USED_MB,
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27634305/viewspace-1425662/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
没有了~
请登录后发表评论
登录
全部评论
