ORACLE数据库存储使用情况查询命令

ORACLE数据库存储使用情况查询命令

 

一、查看表空间脚本与命令

方法一：查询表空间使用情况（系统级BASH脚本）

echo "######################## Oracle ${ORACLE_SID} tablespace Check ########################"
sqlplus -S '/ as sysdba' <<EOF
col tablespace_name for a16
set FEEDBACK off
set linesize 200
set pagesize 200
set echo off
SELECT upper(f.tablespace_name) "Tablespace_name",
       round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) " Used (%) ",
       round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "Free (%)",
       d.Tot_grootte_Mb "Total (MB)",
       d.Tot_grootte_Mb - f.total_bytes " Used (MB)",
       f.total_bytes " Free_space (MB) "
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 2 DESC;
exit
EOF

 

方法二：查询表空间使用情况（数据库SQL查询命令）

语句一：

SELECT upper(f.tablespace_name) "Tablespace_name",
       round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) " Used (%) ",
       round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "Free (%)",
       d.Tot_grootte_Mb "Total (MB)",
       d.Tot_grootte_Mb - f.total_bytes " Used (MB)",
       f.total_bytes " Free_space (MB) "
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 2 DESC;

语句二：

set linesize 2000;
col TABLESPACE_NAME for a30
SELECT a.tablespace_name,
        total/1024/1024 "TOTAL(MB)",
        round((total-free)/1024/1024,2) "USED(MB)",
        round(free/1024/1024,2) "FREE(MB)",
        round((total-free)/total,4)*100 "USED(%)"
FROM
    (SELECT tablespace_name,
            SUM(bytes) free 
      FROM DBA_FREE_SPACE
      GROUP BY tablespace_name ) a,
      (SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
      GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name; 

 

二、查看ASM磁盘使用情况

方法一：进入asmcmd中查看ASM共享磁盘使用空间

su - grid
asmcmd
lsdg

 

方法二：在数据库中通过SQL命令查看ASM共享磁盘使用空间

col total_size for a20;
col free_size for a20;
select name,round(total_mb/1024) || 'G' as total_size,round(free_mb/1024) || 'G' as free_size from v$asm_diskgroup;

 

三、查询ORACLE数据库全库大小（实例）

1、数据库环境

系统版本：RHEL 7.6

数据库版本：19.3.0.0.0

2、数据库编码格式

col parameter for a30
col value for a30
select * from v$NLS_PARAMETERS;

3、所有的数据文件大小

col tablespace_name for a10
col file_name for a60
select tablespace_name,file_name,round(bytes/1024/1024/1024,2) G from dba_data_files;

4、所有数据文件总大小

select round(sum(bytes)/1024/1024/1024,2) "TOTAL(G)" from dba_data_files;

5、临时文件总大小

select sum(bytes)/1024/1024/1024 "TOTAL(G)" from dba_temp_files;

6、日志文件总大小

select sum(bytes)/1024/1024/1024 "TOTAL(G)" from v$log;

7、数据库总大小

select sum(GB) as "TOTAL(G)"
    from (
        select sum(bytes)/1024/1024/1024 as GB
            from dba_data_files
        union all
        select sum(bytes)/1024/1024/1024
            from dba_temp_files
        union all
        select sum(bytes)/1024/1024/1024
            from v$log
    );