SQL> comp sum of nfrags totsiz avasiz on report
SQL> break on report
SQL> set pages 999
SQL> col tsname format a16 justify c heading 'Tablespace'
SQL> col nfrags format 999,990 justify c heading 'Free|Frags'
SQL> col mxfrag format 999,999 justify c heading 'Largest|Frag (MB)'
SQL> col totsiz format 999,999 justify c heading 'Total|(MB)'
SQL> col avasiz format 999,999 justify c heading 'Available|(MB)'
SQL> col pctusd format 990 justify c heading 'Pct|Used'
SQL> select total.TABLESPACE_NAME tsname,
2 D nfrags,
3 C/1024/1024 mxfrag,
4 A/1024/1024 totsiz,
5 B/1024/1024 avasiz,
6 (1-nvl(B,0)/A)*100 pctusd
7 from
8 (select sum(bytes) A,
9 tablespace_name
10 from dba_data_files
11 group by tablespace_name) TOTAL,
12 (select sum(bytes) B,
13 max(bytes) C,
14 count(bytes) D,
15 tablespace_name
16 from dba_free_space
17 group by tablespace_name) FREE
18 where
19 total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
20 /
Free Largest Total Available Pct
Tablespace Frags Frag (MB) (MB) (MB) Used
---------------- -------- --------- -------- --------- ----
TS_DE_DATA 56,065 3,968 643,072 189,751 70
TS_EVT7_DATA 31,841 96 184,320 12,335 93
TS_DE2_DATA 1,573 3,968 204,800 37,745 82
SYSAUX 72 3,968 32,760 30,345 7
UNDOTBS1 208 2,099 65,528 17,836 73
TS_DU_DATA 34,635 3,968 212,992 87,537 59
TS_DO_DATA 26,335 3,968 278,528 82,068 71
TS_ACCT4_DATA 84 3,968 94,208 39,436 58
TS_ACCT2_DATA 70 3,968 94,208 38,741 59
TS_EVT4_DATA_TEST 142 2,893 5,120 4,591 10
USERS 11 3,968 32,760 32,091 2
TS_ACCT3_DATA 1,026 3,968 94,208 43,268 54
TS_APP3_DATA 713 3,968 36,864 25,845 30
TS_EVT6_DATA 30,424 383 247,760 31,285 87
TS_ACCT5_DATA 1,100 3,172 114,688 20,056 83
TS_ACCT1_DATA 272 3,968 847,792 257,925 70
TS_DIM_DATA 2,580 3,968 36,864 14,569 60
TS_ACCT6_DATA 4,997 3,968 122,880 39,141 68
SYSTEM 11 3,968 32,760 30,234 8
TS_EVT2_DATA 5,379 3,968 245,760 120,306 51
TS_EVT4_DATA 30,602 3,968 606,208 124,423 79
TS_IND1_DATA 6,565 3,968 253,952 124,415 51
TS_APP1_DATA 1,999 3,968 40,960 26,901 34
TS_EVT3_DATA 3,548 3,968 245,760 107,864 56
TS_DO1_DATA 40,361 3,790 315,392 92,414 71
TS_ACCT7_DATA 16,379 3,968 479,232 252,626 47
ITNMAGT 1 99 100 99 1
UNDOTBS2 1,748 1,629 65,528 14,807 77
TS_PMID_DATA 83,432 11 552,960 69,305 87
TS_DE1_DATA 13,208 3,966 204,800 24,012 88
TS_EVT1_DATA 16,100 3,968 245,760 97,695 60
DBFSTBS 126 3,968 430,080 429,720 0
TS_EVT5_DATA 18,142 64 512,000 87,365 83
TS_APP2_DATA 1,336 3,968 61,440 21,903 64
TS_SHARE_DATA 190 3,968 86,016 44,467 48
-------- -------- ---------
sum 431,275 ######## ########
35 rows selected.