常用脚本整理

1. Release tablespace datafile

2. 点击(此处)折叠或打开

   1. col name for a40
      
   2. col resizecmd for a80
      
   3.   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)
   4.          || '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;
   
   
3.  check tablespace usage
4. 点击(此处)折叠或打开
5. 1. SELECT B.TABLESPACE_NAME TBSNAME,B.TOTAL_MB-NVL(A.FREE_MB,0) USED_MB,
      
   2. ROUND(((B.TOTAL_MB-NVL(A.FREE_MB,0))/(B.TOTAL_ALLOCATE_MB+NVL(C.NONAUTO,0)))*100,2) USED_PERCENT,
      
   3. B.TOTAL_MB TOTAL_MB,B.TOTAL_ALLOCATE_MB+NVL(C.NONAUTO,0) MAX_ALLOCATE_MB,
      
   4. (B.TOTAL_ALLOCATE_MB+NVL(C.NONAUTO,0))-(B.TOTAL_MB-NVL(A.FREE_MB,0)) Real_Free FROM
      
   5. (SELECT TABLESPACE_NAME,SUM(BYTES)/1048576 FREE_MB FROM DBA_FREE_SPACE GROUP BY
      
   6. TABLESPACE_NAME) A,
      
   7. (SELECT TABLESPACE_NAME,SUM(BYTES)/1048576 TOTAL_MB, SUM(MAXBYTES)/1048576 TOTAL_ALLOCATE_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B,
      
   8. (SELECT SUM(BYTES)/1048576 NONAUTO, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE MAXBYTES =\'0\' GROUP BY TABLESPACE_NAME) C
      
   9. WHERE A.TABLESPACE_NAME(+)=B.TABLESPACE_NAME
      
   10. AND C.TABLESPACE_NAME(+)=B.TABLESPACE_NAME
       
   11. AND B.TABLESPACE_NAME NOT LIKE \'UNDO%\'
       
   12. AND B.TABLESPACE_NAME NOT IN (\'SYSTEM\', \'DBVISION\')
       
   13. ORDER BY USED_PERCENT DESC ;
   
   
   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27634305/viewspace-1425662/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27634305/viewspace-1425662/