5 Replies Latest reply: Dec 26, 2012 5:47 AM by GiuseppeL RSS

    Check for disk consuptiom for XE

    GiuseppeL
      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
          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
            Interesting, but wich tablespaces really matters in space consumption for XE versions?
            • 3. Re: Check for disk consuptiom for XE
              phaeus
              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
                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
                  Thanks all for the information provided. I will check for it.

                  Regards.