This discussion is archived
5 Replies Latest reply: Dec 26, 2012 3:47 AM by GiuseppeL RSS

Check for disk consuptiom for XE

GiuseppeL Newbie
Currently Being Moderated
Hi all again,

I readed thath XE has a limit of 11GB of data in disk (althoug here, sais 4GB but is not relevant for my question).

There are some SELECT, function, or some way to calculate how data is consumed over all database, avoiding system data thath don't counts in this limit?

Regards.
  • 1. Re: Check for disk consuptiom for XE
    phaeus Pro
    Currently Being Moderated
    Hello,
    maybe this helps you.
    http://www.orafaq.com/wiki/Tablespace#Check_free.2Fused_space_per_tablespace

    regards
    Peter
  • 2. Re: Check for disk consuptiom for XE
    GiuseppeL Newbie
    Currently Being Moderated
    Interesting, but wich tablespaces really matters in space consumption for XE versions?
  • 3. Re: Check for disk consuptiom for XE
    phaeus Pro
    Currently Being Moderated
    Hello,

    you can not resize your datafile over 11G.

    For example:
    If you create one tablespace over 11G.
    SQL> create tablespace appdata datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' size 18G;
    create tablespace appdata datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' size 18G
    *
    ERROR at line 1:
    ORA-12953: The request exceeds the maximum allowed database size of 11 GB
    If you try to create to tablespaces over 11G
    SQL> create tablespace appdata datafile '/u01/app/oracle/oradata/XE/appdata01.dbf' size 10G;
    
    Tablespace created.
    
    SQL> create tablespace appindex datafile '/u01/app/oracle/oradata/XE/appindex01.dbf' size 5G;
    create tablespace appindex datafile '/u01/app/oracle/oradata/XE/appindex01.dbf' size 5G
    *
    ERROR at line 1:
    ORA-12953: The request exceeds the maximum allowed database size of 11 GB
    If your try to overallocated with a existing tablespace (example system)
    SQL> alter database datafile 1 resize 5G;
    alter database datafile 1 resize 5G
    *
    ERROR at line 1:
    ORA-12953: The request exceeds the maximum allowed database size of 11 GB
    So your actual size of your datafiles matters.

    regards
    Peter
  • 4. Re: Check for disk consuptiom for XE
    orafad Oracle ACE
    Currently Being Moderated
    Peter is correct. The docs are perhaps a bit fuzzy on the "user data" limitation, specially since you don't even need any data to be able to reach the limit.
    http://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#BABFCCBC

    As said, Datafile size (excluding temp and undo) is limited to 11 GB (or just above), so check v$datafile.bytes column.


    A quick demo:
    SQL> select file#, name, ts#, round(bytes/1024/1024,1) size_MB
      2  from v$datafile;
    
         FILE# NAME                                  TS#    SIZE_MB
    ---------- ------------------------------ ---------- ----------
             1 C:\ORACLE\ORACLEXE\APP\ORACLE\          0        390
               ORADATA\XE\SYSTEM.DBF
    
             2 C:\ORACLE\ORACLEXE\APP\ORACLE\          1        720
               ORADATA\XE\UNDOTBS1.DBF
    
             3 C:\ORACLE\ORACLEXE\APP\ORACLE\          2        150
               ORADATA\XE\SYSAUX.DBF
    
             4 C:\ORACLE\ORACLEXE\APP\ORACLE\          4          5
               ORADATA\XE\USERS.DBF
    
             5 C:\ORACLE\ORACLEXE\APP\ORACLE\          6         10
               PRODUCT\11.2.0\SERVER\DATABASE
               \TEST01.DBF
    
             6 C:\ORACLE\ORACLEXE\APP\ORACLE\          7        750
               ORADATA\XE\DATAFILE\O1_MF_AGRD
               ATA_7J6517LT_.DBF
    
    
    6 rows selected.
    
    SQL> select count(*), round(sum(bytes)/1024/1024,2) tot_GB
      2  from v$datafile;
    
      COUNT(*)     TOT_GB
    ---------- ----------
             6       2025
    
    SQL> select count(*), round(sum(bytes)/1024/1024,2) tot_GB_TEMP
      2  from v$tempfile;
    
      COUNT(*) TOT_GB_TEMP
    ---------- -----------
             2         896
    
    SQL> -- so, at least 9 GB should remain according to theory
    SQL> -- resizing Sysaux data file (note- dbf file names are confusing):
    SQL> alter database datafile 2 resize 10100m;
    
    Database altered.
    
    SQL> select count(*), round(sum(bytes)/1024/1024,2) tot_GB
      2  from v$datafile;
    
      COUNT(*)     TOT_GB
    ---------- ----------
             6      11405
    
    SQL> -- this is 11 GB minus current undo tbs size
    SQL> alter database datafile 2 resize 10600m;
    
    Database altered.
    
    SQL> alter database datafile 2 resize 10650m;
    alter database datafile 2 resize 10650m
    *
    ERROR at line 1:
    ORA-12953: The request exceeds the maximum allowed database size of 11 GB
    
    
    SQL> select count(*), round(sum(bytes)/1024/1024,2) tot_GB
      2  from v$datafile;
    
      COUNT(*)     TOT_GB
    ---------- ----------
             6      11905
    
    SQL> -- seems 500 MB or so is allowed for "inital" db size
    Also, with the sysaux vs undo tbs confusion, a side note/demo:
    SQL> select tablespace_name, sum(bytes)/1024/1024 free_mb
      2  from dba_free_space
      3  group by tablespace_name;
    
    TABLESPACE_NAME                   FREE_MB
    ------------------------------ ----------
    SYSAUX                            9934,25
    UNDOTBS1                           81,375
    ...
    
    SQL> alter database datafile 3 resize 2000m;
    
    Database altered.
    
    SQL>
    SQL> select tablespace_name, sum(bytes)/1024/1024 free_mb
      2  from dba_free_space
      3  group by tablespace_name;
    
    TABLESPACE_NAME                   FREE_MB
    ------------------------------ ----------
    SYSAUX                            9934,25
    UNDOTBS1                         1931,375
    ...
    
    SQL> alter database datafile 3 resize 150m;
    
    Database altered.
    
    SQL> -- data file 3 is undo tbs, despite the file name (sysaux.dbf != sysaux tbs)
    SQL>
    SQL> alter database datafile 2 resize 700m;
    
    Database altered.
    Edited by: orafad on Dec 24, 2012 2:32 AM
  • 5. Re: Check for disk consuptiom for XE
    GiuseppeL Newbie
    Currently Being Moderated
    Thanks all for the information provided. I will check for it.

    Regards.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points