- 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博客 ” ,链接:http://blog.itpub.net/27634305/viewspace-1425662/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27634305/viewspace-1425662/