SQL语句大全—查看表空间VIP免费

数据库各个表空间增长情况的检查： SQL >SELECT A.TABLESPACE_NAME,( 1 -(A.TOTAL)/B.TOTAL)* 100 USED_PERCENT FROM (SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL 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; SQL >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 4 DESC ; 查看各个表空间占用磁盘情况 : SQL >COL TABLESPACE_NAME FORMAT A20; SQL >SELECT B.FILE_ID FILE_ID, B.TABLESPACE_NAME TABLESPACE_NAME, B.BYTES BYTES, (B.BYTES-SUM (NVL(A.BYTES, 0 ))) USED, SUM (NVL(A.BYTES, 0 )) FREE, SUM (NVL(A.BYTES, 0 ))/(B.BYTES)* 100 PERCENT FROM DBA_FREE_SPACE A,DBA_DATA_FILES B WHERE A.FILE_ID=B.FILE_ID GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES ORDER BY B.FILE_ID; 数据库对象下一扩展与表空间的 free 扩展值的检查： SQL >SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK UNION SELECT A.INDEX_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME FROM...