常用空间查询和处理的运维SQL

windjack

--查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

　　D.TOT_GROOTTE_MB "表空间大小(M)",

　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",

　　F.TOTAL_BYTES "空闲空间(M)",

　　F.MAX_BYTES "最大块(M)"

　　FROM (SELECT TABLESPACE_NAME,

　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

　　FROM SYS.DBA_FREE_SPACE

　　GROUP BY TABLESPACE_NAME) F,

　　(SELECT DD.TABLESPACE_NAME,

　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 1;

--查询数据文件空间使用情况

select

　　b.file_name 物理文件名,

　　b.tablespace_name 表空间,

　　b.bytes/1024/1024 大小M,

　　(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,

　　substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率

　　from dba_free_space a,dba_data_files b

　　where a.file_id=b.file_id

　　group by b.tablespace_name,b.file_name,b.bytes

　　order by b.tablespace_name;

--查询表空间的free space

　　select tablespace_name,

　　count(*) as extends,

　　round(sum(bytes) / 1024 / 1024, 2) as MB,

　　sum(blocks) as blocks

　　from dba_free_space

　　group by tablespace_name;

--查询表空间的总容量

　　select tablespace_name, sum(bytes) / 1024 / 1024 as MB

　　from dba_data_files

　　group by tablespace_name;

--查询表空间使用率

　　select total.tablespace_name,

　　round(total.MB, 2) as Total_MB,考试大论坛

　　round(total.MB - free.MB, 2) as Used_MB,

　　round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct

　　from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

　　from dba_free_space

　　group by tablespace_name) free,

　　(select tablespace_name, sum(bytes) / 1024 / 1024 as MB

　　from dba_data_files

　　group by tablespace_name) total

　　where free.tablespace_name = total.tablespace_name;

--查看datafile 的HWM,估算resize小size脚本，方便数据迁移

SELECT a.tablespace_name,

               file_name,

               c.VALUE / 1024 "Blk. size(Kb)",

               CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",

               CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",

               CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"

FROM dba_data_files a,

            (SELECT file_id, MAX (block_id + blocks - 1) hwm

                FROM dba_extents

             GROUP BY file_id) b,

            (SELECT VALUE

                FROM v$parameter

              WHERE NAME = 'db_block_size') c

WHERE a.file_id = b.file_id(+)

and  a.status !='INVALID';

--smallest(Mb) - HWM：为最小值

--修改：Alter  database   datafile  'xxx'  resize   最小值；

--查询表空间使用率

select total.ts tablespace,

       total.mb total_mb,

       NVL(total.mb - free.mb, total.mb) used_mb,

       NVL(free.mb, 0) free_mb,

       DECODE(total.mb,

              NULL,

              0,

              NVL(ROUND((total.mb - free.mb) / (total.mb) * 100, 2), 100)) pct_used

  from (select tablespace_name ts, sum(bytes) / 1024 / 1024 mb

          from dba_data_files

         group by tablespace_name) total,

       (select tablespace_name ts, sum(bytes) / 1024 / 1024 mb

          from dba_free_space

         group by tablespace_name) free

where total.ts = free.ts(+)

order by 5 desc;

--查询表空间使用率超过90%的数据文件明细

col file_name for a60 ;

col tablespace_name for a20;

set line 200

  select file_id,

         file_name,

         tablespace_name,

         bytes / 1024 / 1024 as "size(M)",

         status,

         autoextensible

    from dba_data_files

   where tablespace_name in

         (select tablespace

            from (select total.ts tablespace,

                         total.mb total_mb,

                         NVL(total.mb - free.mb, total.mb) used_mb,

                         NVL(free.mb, 0) free_mb,

                         DECODE(total.mb,

                                NULL,

                                0,

                                NVL(ROUND((total.mb - free.mb) / (total.mb) * 100,

                                          2),

                                    100)) pct_used

                    from (select tablespace_name ts,

                                 sum(bytes) / 1024 / 1024 mb

                            from dba_data_files

                           group by tablespace_name) total,

                         (select tablespace_name ts,

                                 sum(bytes) / 1024 / 1024 mb

                            from dba_free_space

                           group by tablespace_name) free

                   where total.ts = free.ts(+))

           where pct_used > 90)

   order by 3;

   

   

--开启小于25G的数据文件的自动扩展

set pagesize 1000 set head off

  select 'alter database datafile ' || file_id || ' autoextend on;'

    from dba_data_Files

   where autoextensible = 'NO'

     and (bytes / 1024 / 1024 / 1024) < 25;

         

         

         

--关闭大于30G的数据文件的自动扩展

set pagesize 1000 ;

set head off;

  select 'alter database datafile ' || file_id || ' autoextend off;'

    from dba_data_Files

   where autoextensible = 'YES'

     and (bytes / 1024 / 1024 / 1024) > 30;

--查看每天表空间增长率

SELECT A.NAME, B.TABLESPACE_ID,B.DATETIME,B.USED_SIZE_MB,B.INC_MB,  

       CASE WHEN SUBSTR(INC_RATE,1,1)='.' THEN '0'||INC_RATE  

            WHEN SUBSTR(INC_RATE,1,2)='-.' THEN '-0'||SUBSTR(INC_RATE,2,LENGTH(INC_RATE))   

            ELSE INC_RATE  

       END AS INC_RATEX  

  FROM V$TABLESPACE A,   

       (  

           SELECT TABLESPACE_ID,DATETIME,  

                  USED_SIZE_MB,   

                  (DECODE(PREV_USE_MB,0,0,USED_SIZE_MB)-PREV_USE_MB) AS  INC_MB,   

                  TO_CHAR(ROUND((DECODE(PREV_USE_MB,0,0,USED_SIZE_MB)-PREV_USE_MB)/DECODE(PREV_USE_MB,0,1,PREV_USE_MB)*100,2))||'%' AS INC_RATE  

         FROM  

         (  

           SELECT TABLESPACE_ID,   

                 TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) DATETIME,   

                  MAX(TABLESPACE_USEDSIZE * 8 / 1024) USED_SIZE_MB,  

                  LAG(MAX(TABLESPACE_USEDSIZE * 8 / 1024),1,0) OVER(PARTITION BY TABLESPACE_ID ORDER BY TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) ) AS PREV_USE_MB  

             FROM DBA_HIST_TBSPC_SPACE_USAGE  

            WHERE TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) > TRUNC(SYSDATE - 30)  

            GROUP BY TABLESPACE_ID, TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss'))  )   

       ) B  

WHERE A.TS# = B.TABLESPACE_ID  

ORDER BY B.TABLESPACE_ID,DATETIME;