ORACLE表空间查询常用SQL

MySQLDBA修炼之路 2020-08-26
670

undo的情况
SELECT tablespace_name, status, SUM (bytes)/1024/1024 "Bytes(M)" FROM dba_undo_extents GROUP BY tablespace_name, status;

查看表空间的使用情况
SELECT SUM(bytes)  (1024 * 1024) AS free_space, tablespace_name  FROM dba_free_space  GROUP BY tablespace_name;

查看表空间的使用情况
set lines 300
SELECT a.tablespace_name, a.bytes  (1024 * 1024 * 1024 ) "total(g)", b.bytes  (1024 * 1024 ) "used(m)", c.bytes  (1024 * 1024 * 1024) "free(g)", (b.bytes * 100)  a.bytes "% USED ", (c.bytes * 100)  a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;


查看某个表空间的使用情况
set lines 300
COLUMN TABLESPACE_NAME             FORMAT a16   
COLUMN  SUM_SPACE(M)               FORMAT a16   
COLUMN SUM_BLOCKS                  FORMAT a16   
COLUMN USED_SPACE(M)               FORMAT a16   
COLUMN USED_RATE(%)                FORMAT a16   
COLUMN FREE_SPACE(M)               FORMAT a16   
SELECT * FROM (
SELECT D.TABLESPACE_NAME,
        SPACE || 'M' "SUM_SPACE(M)",
        BLOCKS "SUM_BLOCKS",
        SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
        ROUND ( (1 - NVL (FREE_SPACE, 0)  SPACE) * 100, 2) || '%'
           "USED_RATE(%)",
        FREE_SPACE || 'M' "FREE_SPACE(M)"
   FROM (  SELECT TABLESPACE_NAME,
                  ROUND (SUM (BYTES)  (1024 * 1024), 2) SPACE,
                  SUM (BLOCKS) BLOCKS
             FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
        (  SELECT TABLESPACE_NAME,
                  ROUND (SUM (BYTES)  (1024 * 1024), 2) FREE_SPACE
             FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 UNION ALL                                                           
 SELECT D.TABLESPACE_NAME,
        SPACE || 'M' "SUM_SPACE(M)",
        BLOCKS SUM_BLOCKS,
        USED_SPACE || 'M' "USED_SPACE(M)",
        ROUND (NVL (USED_SPACE, 0)  SPACE * 100, 2) || '%' "USED_RATE(%)",
        NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
   FROM (  SELECT TABLESPACE_NAME,
                  ROUND (SUM (BYTES)  (1024 * 1024), 2) SPACE,
                  SUM (BLOCKS) BLOCKS
             FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
        (  SELECT TABLESPACE_NAME,
                  ROUND (SUM (BYTES_USED)  (1024 * 1024), 2) USED_SPACE,
                  ROUND (SUM (BYTES_FREE)  (1024 * 1024), 2) FREE_SPACE
             FROM V$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 ORDER BY 1)  
 WHERE TABLESPACE_NAME IN ('UNDOTBS1');



----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    


查询表空间的使用情况
  set lines 1000
    SELECT * FROM (
    SELECT D.TABLESPACE_NAME,
            SPACE || 'M' "SUM_SPACE(M)",
            BLOCKS "SUM_BLOCKS",
            SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
            ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
               "USED_RATE(%)",
            FREE_SPACE || 'M' "FREE_SPACE(M)"
       FROM (  SELECT TABLESPACE_NAME,
                      ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
                      SUM (BLOCKS) BLOCKS
                 FROM DBA_DATA_FILES
             GROUP BY TABLESPACE_NAME) D,
            (  SELECT TABLESPACE_NAME,
                      ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
                 FROM DBA_FREE_SPACE
             GROUP BY TABLESPACE_NAME) F
      WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
     UNION ALL                                                           
     SELECT D.TABLESPACE_NAME,
            SPACE || 'M' "SUM_SPACE(M)",
            BLOCKS SUM_BLOCKS,
            USED_SPACE || 'M' "USED_SPACE(M)",
            ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
            NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
       FROM (  SELECT TABLESPACE_NAME,
                      ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
                      SUM (BLOCKS) BLOCKS
                 FROM DBA_TEMP_FILES
             GROUP BY TABLESPACE_NAME) D,
            (  SELECT TABLESPACE_NAME,
                      ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
                      ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
                 FROM V$TEMP_SPACE_HEADER
             GROUP BY TABLESPACE_NAME) F
      WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
     ORDER BY 1)  
     WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');
    
G显示
set lines 300
set pages 200
col ts-per for a10
col tablespace-name for a20


select b.status,upper(f.tablespace_name) "tablespace-name",b.CONTENTS,b.extent_management,b.SEGMENT_SPACE_MANAGEMENT,b.MAX_EXTENTS,       
        d.tot_grootte_mb/1024 "ts-bytes(g)",
        d.tot_grootte_mb - f.total_bytes "ts-used (m)",
        f.total_bytes/1024 "ts-free(g)",
        to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2),'990.99') "ts-per"              
        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,
          (select tb.status,tb.TABLESPACE_NAME,tb.CONTENTS,tb.EXTENT_MANAGEMENT,
          tb.SEGMENT_SPACE_MANAGEMENT,tb.MAX_EXTENTS from dba_tablespaces tb order by tb.tablespace_name) b
 where d.tablespace_name = f.tablespace_name and f.tablespace_name=b.tablespace_name;
    
M显示
set lines 300
set pages 200
col ts-per for a10
col tablespace-name for a20


select b.status,upper(f.tablespace_name) "tablespace-name",b.CONTENTS,b.extent_management,b.SEGMENT_SPACE_MANAGEMENT,b.MAX_EXTENTS,  
            d.tot_grootte_mb "ts-bytes(m)",      
            d.tot_grootte_mb - f.total_bytes "ts-used (m)",      
            f.total_bytes "ts-free(m)",      
            to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2), '990.99') "ts-per"                       
            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,      
                    (select tb.status,tb.TABLESPACE_NAME,tb.CONTENTS,tb.EXTENT_MANAGEMENT,     
                    tb.SEGMENT_SPACE_MANAGEMENT,tb.MAX_EXTENTS from dba_tablespaces tb order by tb.tablespace_name) b
                    where d.tablespace_name = f.tablespace_name and f.tablespace_name=b.tablespace_name;
                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    或者            
set lines 300
set pages 200
SELECT a.tablespace_name,TRUNC (tablespace_size *b.block_size / 1024 / 1024) size_m,TRUNC (used_space * b.block_size / 1024 / 1024) used_size_m,TRUNC ( (TABLESPACE_SIZE - used_space) * b.block_size / 1024 /1024)free_size_m,USED_PERCENT FROM DBA_TABLESPACE_USAGE_METRICS a, dba_tablespaces  b WHERE a.tablespace_name =b.tablespace_name ORDER BY USED_PERCENT ;






--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
   
                    
        
SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;


MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
     257          265


SQL> select dbid from v$database;


      DBID
----------
1558556473
          
                  
                  
                  begin
                            dbms_workload_repository.drop_snapshot_range(
                              low_snap_id =>257,
                             high_snap_id =>259,
                             dbid =>1558556473);
                  end;
                  /
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
   
                                      
查看剩余磁盘大小


select name, total_mb, free_mb from v$asm_diskgroup;


select name,total_mb,free_mb from v$asm_disk;
 
SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    200
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN group_name             FORMAT a25           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
column free_mb               format 999,999,999 heading 'Free Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
      name                                     group_name
    , sector_size                              sector_size
    , block_size                               block_size
    , allocation_unit_size                     allocation_unit_size
    , state                                    state
    , type                                     type
    , total_mb                                 total_mb
    , USABLE_FILE_MB  , free_mb                                 free_mb
    , (total_mb - free_mb)                     used_mb
    , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
  FROM
      v$asm_diskgroup
  WHERE
      total_mb != 0
  ORDER BY
      name
  /
oracle
文章转载自MySQLDBA修炼之路，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
ORACLE表空间查询常用SQL

评论
