Cannot start Oracle-XE 18c due to being full

4082131

    Cannot Start Oracle-XE 18c in order to clear out data

    ====================================================================================================================

    We are having an issue where an Oracle XE instance fills up and for a potentially unrelated reason crashes and cannot be restarted.

     

    What this means is I cannot get back into the instance to cleanout data to resume operation. No matter what path is taken, I continue to be prevented from opening the PDB.

     

    This basically then means I have to rebuild the thing from scratch which is loads of fun.

     

    SQL> ALTER PLUGGABLE DATABASE XEPDB1 OPEN READ ONLY;

    ALTER PLUGGABLE DATABASE XEPDB1 OPEN READ ONLY

    *

    ERROR at line 1:

    ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

     

     

    SQL> ALTER PLUGGABLE DATABASE XEPDB1 OPEN RESTRICTED;

    ALTER PLUGGABLE DATABASE XEPDB1 OPEN RESTRICTED

    *

    ERROR at line 1:

    ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

     

     

    SQL> ALTER PLUGGABLE DATABASE XEPDB1 OPEN READ ONLY FORCE;

    ALTER PLUGGABLE DATABASE XEPDB1 OPEN READ ONLY FORCE

    *

    ERROR at line 1:

    ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

     

    Any suggestions on how I might get the data out or get the process started so I can move forward?

      • 1. Re: Cannot start Oracle-XE 18c due to being full
        Markus Flechtner

        Hi,

         

        I assume that your CDB$ROOT is open.

        So, did you try to resize your CDB$ROOT (by shrinking one more datafiles)?

        This could help you to come below the limit so that you can open your PDB.

         

        HTH

        Markus

        • 2. Re: Cannot start Oracle-XE 18c due to being full
          Dude!

          I think the first things you want to do is to check the actual alert.log, which should be located at:

           

          $ORACLE_BASE/diag/rdbms/<dbname_in_lower_case>/$ORACLE_SID/trace/

           

          Check for clues.

           

          Next, before you make any changes, I suggest to perform a full RMAN backup.

           

          Perhaps you can reclaim some space from the temp datafile, though I'm not sure if the space counts toward the 12GB limit. But perhaps worth a try:

           

          SQL> ALTER TABLESPACE TEMP SHRINK SPACE;

          • 3. Re: Cannot start Oracle-XE 18c due to being full
            Peter_L_

            Hi,

            I run into the same. It's a very ugly error.

            My only suggestion is to install an Oracle Standard Edition Home and try to run/append the datafiles of XE from the new home, then shrink the tables and export/import back into XE.

            Delete the auxiliary dbhome.

            Cheers Peter

            • 4. Re: Cannot start Oracle-XE 18c due to being full
              Peter_L_

              Hi Markus,

              Same issue in my testlab.

              My XEPDB1 was unable to "open read write" or "open read only" (ora-12954). No chance to cleanup the user tablespace in the mounted XEPDB1. The only way was to append the XE datafiles in a "auxiliary" Oracle 18c home. There I could start this XE. Now I could see which objects filled up the user tablespace. In my case there was the user tablespace mostly filled by fulltext search indexes. The indexes were regular synchronized by a scheduler job with "ctx_ddl.sync_index". I assume during  this user maintenance jobs with ctx_ddl package the 12GB space limit will not correctly checked and an overfill will sadly allowed. I think it's a XE bug.

              However now I could drop the fulltext indexes. Export schema, recreate the tablespace with compress (this is allowed in XE) and import back.

              After appending datafiles back to XE at last I could start the XE instance without problems.

               

              Cheers Peter

              • 5. Re: Cannot start Oracle-XE 18c due to being full
                Prog1024

                I am having the same issue , but i am unable to understand the procedure for "append the XE datafiles in a "auxiliary" Oracle 18c home" i tried to take users tablespace dbf file to another system with a clean xe database install and added this dbf to previously present USERS tablespace in newly installed XE database but i cannot see any content from the tablespace

                 

                Can you kindly provide some detail on how to perform this procedure , also is it needs to be imported into home database or can it work in a fresh xe install like i am doing?