oracle 查表空间大小和剩余空间

查表空间大小和剩余空间，我通常用这个语句：

SQL>

set linesize 150

column file_name format a65

column tablespace_name format a20

column used_percent format a10

column free_percent format a10

SQL>

 select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024)) total_m,round((d.sumbytes-f.sumbytes)/1024/1024) used_m,round(f.sumbytes/1024/1024,2) free_m,round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2)||'%' used_percent,round((f.sumbytes)*100/d.sumbytes,2)||'%' free_percent from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,(select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablespace_name) d  where f.tablespace_name= d.tablespace_name(+) order by (d.sumbytes-f.sumbytes)*100/d.sumbytes desc; 

对于自动扩展的表空间，虽然看着使用百分比达到90%多，但实际可用数据空间还很多，这个时候还需要继续查询datafile文件分配的大小：

TABLESPACE_NAME    TOTAL_M     USED_M     FREE_M USED_PERCE FREE_PERCE

-------------------- ---------- ---------- ---------- ---------- ----------

SYSTEM                1200        886 314.5 73.79%  26.21%

SYSAUX                1200        870     329.88 72.51% 27.49%

USERS                    5  1   3.63 27.5%   72.5%

UNDOTBS1           75 18   57.44 23.42%  76.58%

PERFDB                2500        483    2016.88 19.33% 80.68%

ICNFGSPACE             500 54     446.38 10.73%   89.28%

IMAP_DB           800 52     747.69 6.54%    93.46%

PERFDB_IDX            1600 58    1542.13 3.62%    96.38%

ALARMDB          1000  8     991.63 .84% 99.16%

IMAPSMDB          600  5     595.06 .82% 99.18%

NMSGUEST          400  1     398.81 .3%  99.7%

 

TABLESPACE_NAME    TOTAL_M     USED_M     FREE_M USED_PERCE FREE_PERCE

-------------------- ---------- ---------- ---------- ---------- ----------

IMAPTMDB          800  2     798.25 .22% 99.78%

IMAPLOGDB        1200  2    1197.81 .18% 99.82%

 

13 rows selected.

 

例如上面的PERFDB_IDX看起来虽然已经使用了96.38%，但实际上其对应的数据文件perfdb_idx.dbf最大可用空间为15G，当前只用了1.6G，并且它是自动扩展的，所以不用担心空间不足。

 

SQL> column    FILE_NAME FORMAT A58;

SQL> column    TABLESPACE_NAME FORMAT A18;

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 BYTES_M, MAXBYTES/1024/1024 MAX_M,AUTOEXTENSIBLE from dba_data_files;

 

FILE_NAME                           TABLESPACE_NAME BYTES_M      MAX_M AUT

---------------------------------------------------------- ------------------ ---------- ---------- ---

/opt/oracle/oradb/oradata/inomc/users01.dbf           USERS             5 32767.9844 YES

/opt/oracle/oradb/oradata/inomc/undotbs01.dbf         UNDOTBS1              75 32767.9844 YES

/opt/oracle/oradb/oradata/inomc/sysaux01.dbf          SYSAUX         1200 32767.9844 YES

/opt/oracle/oradb/oradata/inomc/system01.dbf          SYSTEM         1200 32767.9844 YES

/opt/oracle/oradb/data/imap_db.dbf                IMAP_DB              800       1600 YES

/opt/oracle/oradb/data/imaplogdb.dbf              IMAPLOGDB           1200       1600 YES

/opt/oracle/oradb/data/imapsmdb.dbf               IMAPSMDB             600       1600 YES

/opt/oracle/oradb/data/imaptmdb.dbf               IMAPTMDB             800       1600 YES

/opt/oracle/oradb/data/alarmdb.dbf                ALARMDB             1000       2000 YES

/opt/oracle/oradb/data/perfdb.dbf                 PERFDB         2500      16000 YES

/opt/oracle/oradb/data/nmsguest.dbf               NMSGUEST             400 800 YES

 

FILE_NAME                                                       TABLESPACE_NAME           BYTES_M       MAX_M          AUT

----------------------------------------------------------               -------------------------------             -----------------      ------------         ----------------

/opt/oracle/oradb/data/perfdb_idx.dbf                        PERFDB_IDX                       1600             15000           YES

/opt/oracle/oradb/oradata/inomc/icnfgspace.dbf        ICNFGSPACE                       500               32767.9844   YES

 

13 rows selected.