This discussion is archived
5 Replies Latest reply: Jan 18, 2013 2:55 AM by orafad RSS

how to resize 11g express datafile.

947771 Newbie
Currently Being Moderated
Hi,

i am using 11g express,windows 2008, (new bie)

i want to extend the size of userdata file. becasue while inserting record it is showing database has reached it's maximum size 11GB.

1) Please tel me solution, because i heard there is limitation in express edition.

2) why in document it is written "oracle Database XE storage is limited to 5 GB", but it is showing message that it has reached maxsize 11gb,
and when i calculated max space of table space it was 32767.984375+600+32767.984375+11264+32768 =110168
and when i caluclated max space of dataifle it was 32767.984375+600+32767.984375+11264=77400 ( temp was not showin in report of datadisctionary)

3) and from where i should run this command system or sys , ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
RESIZE 11000M; that is 11Gb of size.

yours sincerely

Edited by: 944768 on Jan 17, 2013 8:06 AM

Edited by: 944768 on Jan 17, 2013 10:32 PM

Edited by: 944768 on Jan 17, 2013 10:41 PM
  • 1. Re: how to resize 11g express datafile.
    Brian Bontrager Expert
    Currently Being Moderated
    10g XE had a 4GB limit on user data. (The documentation you saw may reflect the old limit plus a little extra for system data.)
    11g XE has an 11GB limit on user data.

    To extend beyond 11GB of data you need a licensed edition (not XE).
  • 2. Re: how to resize 11g express datafile.
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    The space limitation in XE is documented - http://docs.oracle.com/cd/E17781_01/install.112/e18802/toc.htm#BABIECJA

    HTH
    Srini
  • 3. Re: how to resize 11g express datafile.
    947771 Newbie
    Currently Being Moderated
    i haved asked few more question after reading doc.

    yours sincerely
  • 4. Re: how to resize 11g express datafile.
    Udo Guru
    Currently Being Moderated
    i haved asked few more question after reading doc.
    Okay, let's try to go through each of them directly:
    i want to extend the size of userdata file. becasue while inserting record it is showing database has reached it's maximum size 11GB.
    1) Please tel me solution, because i heard there is limitation in express edition.
    You can't achieve this by resizing the data file: If your database has acutally reached the limit (and the message you get is clear about that) you can't add more data.
    Either delete existing data to make room for new, or upgrade to another Edition of Oracle Database (e. g. Standard Edition One).
    In {thread:id=2487651} one of the "compact" options was compression. This could be an option, too...
    2) why in document it is written "oracle Database XE storage is limited to 5 GB", but it is showing message that it has reached maxsize 11gb,
    As pointed out, there is a 4 GB Limit in 10.2 XE and a 11 GB Limit in 11.2 XE. There never has been a 5 GB Limit and the message concerning the maxsize for your 11.2 XE database is accurate.
    and when i calculated max space of table space it was 32767.984375+600+32767.984375+11264+32768 =110168
    and when i caluclated max space of dataifle it was 32767.984375+600+32767.984375+11264=77400 ( temp was not showin in report of datadisctionary)
    The maximum sizes don't matter at all. The database counts the bytes acutally consumed by user data. There have been a few discussions on how to monitor this yourself in order to know you are close to the limit and take some action before you hit it. One solution is to use the means provided in XE database home page, another to run queries like the one provided here: {message:id=10688588}
    3) and from where i should run this command system or sys , ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
    RESIZE 11000M; that is 11Gb of size.
    This can be run by any dba connected to your database, e. g. using sqlplus (command line client included in your XE install) or SQL Developer (GUI-based client, separate download).
    The current release of SQL Developer (and the XE database home page) offer features to manage data files using a GUI rather than entering the commands yourself.

    If I didn't answer all your questions clear enough or you need additional information, please let us know where you are struggling.

    -Udo
  • 5. Re: how to resize 11g express datafile.
    orafad Oracle ACE
    Currently Being Moderated
    Udo wrote:
    2) why in document it is written "oracle Database XE storage is limited to 5 GB", but it is showing message that it has reached maxsize 11gb,
    As pointed out, there is a 4 GB Limit in 10.2 XE and a 11 GB Limit in 11.2 XE. There never has been a 5 GB Limit and the message concerning the maxsize for your 11.2 XE database is accurate.
    The message about 10g XE, refers to "system" file size + 4 GB "user" file size which means somewhere near 5 GB. Not sure if limit logic has changed with 11.2.

    The maximum sizes don't matter at all. The database counts the bytes acutally consumed by user data. There have been a few discussions on how to monitor this yourself in order to know you are close to the limit and take some action before you hit it.
    Max size does not matter, but perhaps not "bytes consumes" either, depending on what that refers to exactly.

    My tests shows a database file size limit, see:
    Re: Check for disk consuptiom for XE

Legend

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