Although I appear to have solved the problem of the tablespace being full I am perplexed that after altering the the datafile size that the tablespace size has remained the same. What is it that I don't understand?

SQL> alter database datafile '/usr/lib/oracle/xe/oradata/XE/system.dbf' resize 1G;

Database altered.

SQL>  select * from dba_tablespace_usage_metrics order by used_percent desc;

------------------------------ ---------- --------------- ------------
SYSTEM                  76600       76800   99.7395833
USERS                   18784      655360   2.86621094
SYSAUX                  45176     4194302   1.07708029
UNDO                      160       64000      .25
TEMP                    0     4194302        0
  • Which Oracle version? I noticed that in 10G dba_tablespace_usage_metrics quite often is showing not latest info. In with bigger databases it was almost useless. Jun 16, 2014 at 15:59
  • yeah in this case it is 10G, we have 11 in some cases, but don't know if they exhibit the same behavior. Jun 16, 2014 at 17:10
  • 1
    There are problems with this view in 10g, "The DBA_TABLESPACE_USAGE_METRICS View Needs to be Rebuilt in 10g (Doc ID 738101.1)": "Download and apply the patch from Metalink for Bug 6629893. This is a generic patch which is available for:,,, As this is a generic patch, this means the patch is essentially a script that rebuilds DBA_TABLESPACE_USAGE_METRICS prerequisite objects. This bug is fixed in the patchset."
    – miracle173
    Jun 17, 2014 at 7:16


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.