Oracle12c数据库查看表空间使用情况

通过以下sql语句可以查看Oracle12c数据库中所有表空间的使用情况：

可以通过以下语句查看Oracle12c数据库中所有表空间的使用情况：

1. SELECT F.TABLESPACE_NAME,
   
2.        (T.TOTAL_SPACE - F.FREE_SPACE) / 1024 "USED (GB)",
   
3.        F.FREE_SPACE / 1024 "FREE (GB)",
   
4.        T.TOTAL_SPACE / 1024 "TOTAL(GB)",
   
5.        (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '% ' PER_FREE
   
6.   FROM (SELECT TABLESPACE_NAME,
   
7.                ROUND(SUM(BLOCKS *
   
8.                          (SELECT VALUE / 1024
   
9.                             FROM V$PARAMETER
   
10.                            WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE
    
11.           FROM CDB_FREE_SPACE
    
12.          GROUP BY TABLESPACE_NAME) F,
    
13.        (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
    
14.           FROM CDB_DATA_FILES
    
15.          GROUP BY TABLESPACE_NAME) T
    
16.  WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

可以通过添加筛选条件查看指定表空间的使用情况，代码如下：

1. SELECT F.TABLESPACE_NAME,
   
2.        (T.TOTAL_SPACE - F.FREE_SPACE) / 1024 "USED (GB)",
   
3.        F.FREE_SPACE / 1024 "FREE (GB)",
   
4.        T.TOTAL_SPACE / 1024 "TOTAL(GB)",
   
5.        (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '% ' PER_FREE
   
6.   FROM (SELECT TABLESPACE_NAME,
   
7.                ROUND(SUM(BLOCKS *
   
8.                          (SELECT VALUE / 1024
   
9.                             FROM V$PARAMETER
   
10.                            WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE
    
11.           FROM CDB_FREE_SPACE
    
12.          GROUP BY TABLESPACE_NAME) F,
    
13.        (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
    
14.           FROM CDB_DATA_FILES
    
15.          GROUP BY TABLESPACE_NAME) T
    
16.  WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND T.TABLESPACE_NAME LIKE 'ABC%';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31403259/viewspace-2143332/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31403259/viewspace-2143332/