oracle相关查询指标

查询表空间的free space

SQL> select tablespace_name, count(*) AS extends,round(sum(bytes) / 1024 / 1024, 2) AS MB,sum(blocks) AS blocks from dba_free_space group BY tablespace_name;

TABLESPACE_NAME 		  EXTENDS	  MB	 BLOCKS
------------------------------ ---------- ---------- ----------
SYSAUX					1      35.75	   4576
UNDOTBS1			       11     106.56	  13640
USERS					1	3.69	    472
GXGAT_DATA				2     509.44	  65208
SYSTEM					2	9.56	   1224
HBK_DATA				1      40.88	   5232

6 rows selected.

查询表空间的总容量

SQL> select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;

TABLESPACE_NAME 		       MB
------------------------------ ----------
UNDOTBS1			      120
SYSAUX				      670
USERS					5
GXGAT_DATA			     1900
SYSTEM				      700
HBK_DATA			       50

6 rows selected.

查询表空间使用率

SELECT total.tablespace_name,
       Round(total.MB, 2)           AS Total_MB,
       Round(total.MB - free.MB, 2) AS Used_MB,
       Round(( 1 - free.MB / total.MB ) * 100, 2)
       || '%'                       AS Used_Pct
FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_data_files
        GROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name;