查表空间大小和剩余空间,我通常用这个语句:
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.