spool /home/oracle/tablespace.sql
SELECT file_name, tablespace_name, bytes/(1024*1024) bytes1, autoextensible, maxbytes/(1024*1024) maxbytes1, user_bytes/(1024*1024) user_bytes1 FROM dba_data_files order by tablespace_name;
FILE_NAME TABLESPACE_NAME BYTES1 AUT MAXBYTES1 USER_BYTES1
--------------------------------------------- ------------------------------ ---------- --- ---------- -----------
/u01/oradata/huang/cwmlite01.dbf CWMLITE 20 YES 32767.9844 19.9375
/u01/oradata/huang/drsys01.dbf DRSYS 20 YES 32767.9844 19.9375
/u01/oradata/huang/example01.dbf EXAMPLE 153.125 YES 32767.9844 153.0625
/u01/oradata/huang/indx01.dbf INDX 25 YES 32767.9844 24.9375
/home/oracle/disk6/huang/jinlian_index.dbf JINLIAN_INDEX 60 YES 32767.9844 59
/home/oracle/disk6/huang/jinlian_index02.dbf JINLIAN_INDEX 10 NO 0 9
/u01/oradata/huang/odm01.dbf ODM 20 YES 32767.9844 19.9375
/u01/oradata/huang/perfstat_01.dbf PERFSTAT 256 NO 0 255.9375
/u01/oradata/huang/smallundo1.dbf SMALLUNDO 2 NO 0 1.9375
/u01/oradata/huang/system01.dbf SYSTEM 380 YES 32767.9844 379.9375
/u01/oradata/huang/tools01.dbf TOOLS 10 YES 32767.9844 9.9375
/u01/oradata/huang/undotbs01.dbf UNDOTBS1 200 YES 32767.9844 199.9375
/u01/oradata/huang/users01.dbf USERS 25 YES 32767.9844 24.9375
/u01/oradata/huang/xdb01.dbf XDB 45 YES 32767.9844 44.9375
14 rows selected.
SELECT a.tablespace_name, a.bytes/(1024*1024) bytes_used, b.largest, round(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used FROM (SELECT tablespace_name, sum(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ORDER BY a.tablespace_name;
TABLESPACE_NAME BYTES_USED LARGEST PERCENT_USED
------------------------------ ---------- ---------- ------------
CWMLITE 20 5439488 74.06
DRSYS 20 10813440 48.44
EXAMPLE 153.125 3866624 97.59
INDX 25 26148864 .25
JINLIAN_INDEX 70 61865984 2.86
ODM 20 10878976 48.13
PERFSTAT 256 172163072 35.86
SYSTEM 380 8847360 97.78
TOOLS 10 10420224 .63
UNDOTBS1 200 153026560 9.03
USERS 25 24576000 6.25
XDB 45 262144 99.44
12 rows selected.
