oracle表空间扩容与查询剩余表空间

表空间扩容

 su - oracle
 sqlplus / as sysdba
 alter tablespace TEST add datafile '+DATA/RACDB/DATAFILE/test001.dbf' size 32760M;

查看ASM共享磁盘使用空间

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

查看所有数据文件总大小(不包含日志文件、临时文件)

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

查看数据库总大小

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
    );

查询表空间使用情况

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;