重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 33|回复: 0

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

[复制链接]
发表于 2020-11-20 18:22:22 | 显示全部楼层 |阅读模式
--查询表空间使用情况

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;























回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2020-11-28 16:01 , Processed in 0.134172 second(s), 21 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表