Oracle 查询库文件信息

- 日理万妓 2023-06-01 05:42 19阅读 0赞

--1、查看Oracle数据库中数据文件信息的命令方法
    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
    
    --2、
    select * from dba_data_files;
    
    --3、
    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 ROUND(SUM(BYTES)/1024/1024/1024,2)||'GB' 
    FROM DBA_DATA_FILES;
    
    SELECT SUM(BYTES)/1024/1024/1024 AS GB 
    FROM DBA_TEMP_FILES;
    
    SELECT SUM(BYTES)/1024/1024/1024 AS GB 
    FROM V$LOG;
    
    SELECT SUM(GB) AS GB
    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
         );

转载于:https://www.cnblogs.com/Jeely/p/11386966.html