-- 查看表空间总大小和使用率
SELECT a.tablespace_name,total_mb,free_mb,(total_mb - free_mb) used_mb,round((1 - free_mb/total_mb)*100, 2) pct_used FROM (SELECT tablespace_name, sum(bytes)/1024/1024 total_mbFROM dba_data_filesGROUP BY tablespace_name ) a, (SELECT tablespace_name, sum(bytes)/1024/1024 free_mbFROM dba_free_spaceGROUP BY tablespace_name ) b WHERE a.tablespace_name = b.tablespace_name;TABLESPACE TOTAL_MB FREE_MB USED_MB PCT_USED ---------- ---------- ---------- ---------- ---------- SYSTEM 1040 3.0625 1036.9375 99.71 SYSAUX 660 36.1875 623.8125 94.52 UNDOTBS1 25 4.375 20.625 82.5 USERS 7 .9375 6.0625 86.61
-- 查看表空间使用情况
col file_name for a30 col tablespace_name for A10SELECT tablespace_name,file_name,bytes/1024/1024 MB,maxbytes/1024/1024/1024 max_GB,autoextensible,status FROM dba_data_files ORDER BY tablespace_name;TABLESPACE FILE_NAME MB MAX_GB AUT STATUS ---------- ------------------------------ ---------- ---------- --- --------- SYSAUX /opt/oracle/oradata/FREE/sysau 660 32768 YES AVAILABLEx01.dbfSYSTEM /opt/oracle/oradata/FREE/syste 1040 32768 YES AVAILABLEm01.dbfUNDOTBS1 /opt/oracle/oradata/FREE/undot 25 32768 YES AVAILABLEbs01.dbfUSERS /opt/oracle/oradata/FREE/users 7 32768 YES AVAILABLE01.dbf
二、-- 查看表空间类型
SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE FROM DBA_TABLESPACES;TABLESPACE CONTENTS EXTENT_MAN ALLOCATIO ---------- --------------------- ---------- --------- SYSTEM PERMANENT LOCAL SYSTEM SYSAUX PERMANENT LOCAL SYSTEM UNDOTBS1 UNDO LOCAL SYSTEM TEMP TEMPORARY LOCAL UNIFORM USERS PERMANENT LOCAL SYSTEM