All,
Here's a simple script that I use to get the overall and per-tablespace usage from an Oracle DB. I use this when recreating a DB on another system, so that I can pre-allocate the datafiles and tablespaces before export/import. We often do this when replicating a client environment for thorough internal testing or migration assistance.
You must be logged in as sys or system in order to run and the output looks like:
Code:
SQL> @mw-dbusage.sql
TOTAL_GB
----------
41.3867188
USED_GB
----------
15.9564209
TABLESPACE_NAME PERCENT_USED TOTAL_MB USED_MB
------------------------------ ------------ -------- --------
INDX 97 5,750 5,570
DATA 99 5,250 5,197
LOB1 95 2,000 1,895
ARCHIVE 15 8,000 1,182
LOB4 93 1,000 925
SYSTEM 99 540 533
SYSAUX 98 290 285
LOB3 21 1,000 205
LOB2 9 1,000 90
EXAMPLE 52 150 78
LOB5 6 1,000 60
PART_4 6 1,000 59
LOB7 5 1,000 50
LOB6 4 1,000 40
PART_1 3 1,000 31
PART_3 3 1,000 30
PART_2 3 1,000 28
UNDOTBS1 13 175 22
REPORT 1 1,000 14
REPORTINDX 1 1,000 14
BPL_DAY4 0 1,000 4
BPL_DAY7 0 1,000 4
BPL_DAY2 0 1,000 4
BPL_DAY5 0 1,000 4
BPL_DAY6 0 1,000 4
BPL_DAY1 0 1,000 4
BPL_DAY3 0 1,000 4
USERS 59 5 3
28 rows selected.
SQL>
Hope this helps!
--Chris