Notifications
Clear all

How do I resolve ORA-12953? (maximum allowed database size of 11 GB)

8 Posts
6 Users
0 Likes
7,484 Views
0
Topic starter

How do I solve this problem?

ORA-12953: The request exceeds the maximum allowed database size of 11 GB
4 Answers
0
Topic starter

With a little luck, the following approach will work:

Login with sqldeveloper as "system" and execute:

select username from all_users order by 1;

This will list all your schemas. Hopefully there are some in here which you recognize from old builds, but no longer need. For each such schema, run:

drop user myusername cascade;

Obviously, substitute your user's name for myusername.

We have seen cases where this drop itself fails with a ORA-12953. Sometimes this can be solved by executing purge recyclebin.

After you do this, if you are lucky, it will free up enough space that ORA-12953 will go away and you can continue.

Unfortunately, sometimes this doesn't solve it ... seems like oracle hits high watermarks on disk sometimes and won't release the necessary blocks. When that happens, there is regrettably no other option we've found so far other than un-installing/re-installing XE. Obviously if you have to take this drastic step, you will have to run ant clean build on each of your views in turn, because their schemas will have been lost in this process.

yyildiz 2018-11-14 15:11:00

I also found these queries useful.

select sum(bytes)/1024/1024 size_in_mb from dba_segments;

Will tell you how much total space is occupied.

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments
group by owner;

Will tell you how much space is occupied per user.

You can run these queries and see which ones to drop or how much space you have after every drop.

0

Dropping usernames as described in the earlier answer will work for a while.

After a certain number of bootstraps, it may become necessary to uninstall and reinstall OracleXE on your PC though.

The reason why is that the Oracle database stores metadata about other schemas. Based on this metadata, Oracle reserves space for tables, even after they are dropped. This stored space continues to grow in size as you bootstrap projects.

On my PC, this can be observed in the .DBF files found at:

C:\oraclexe\app\oracle\product\11.2.0\server\database\SYSTEM_DATA.DBF
C:\oraclexe\app\oracle\product\11.2.0\server\database\SYSTEM_INDX.DBF

You can also view this metadata by running this query and noting the values in the "owner" column:

"SELECT * FROM dba_segments WHERE tablespace_name = 'SYSTEM_DATA'"

Even though the Oracle users and their schemas are removed, information about the schemas remain.

Neither the system user nor the user listed as owner have permission to delete from the dba_segments table. So it doesn't seem that we can remove the metadata.

Deleting the .DBF files from the file system causes Oracle to crash.

As a result, uninstalling and reinstalling OracleXE may become necessary at some point.

Sean Durkin 2019-09-26 11:09:00

Are you getting rid of unused instances using ant destroy? I don't see old destroyed instances in this query: SELECT distinct(owner) FROM dba_segments WHERE tablespace_name = 'SYSTEM_DATA';

Matt Nutsch 2019-09-26 11:09:00

I was using drop user myusername cascade; from SQL.

Maybe the ant destroy target takes care of this issue.

Sean Durkin 2019-09-26 11:09:00

It looks like ant destroy uses the exact same SQL, so I don't know why you're seeing that metadata and I'm not.

0

Also see XE admin tips at https://portal.onenetwork.com/display/DBA/How+to+Administer+Local+Oracle+XE+Databases

0

I am able to resolved below issues by doing drop tablespace.

Issue  ORA-12953: The request exceeds the maximum allowed database size of 11 GB

Solution –

Step – 1 : Go to your C:\views\ e.g. : C:\views\RTVN-24.0

                 Go to ant_dos.bat and do  clean-dataset and delete-data

Step – 2 : Login database using sysdba and perform below queries

                 sqlplus sys as sysdba

   select username from dba_users;

   drop <unwanted users> cascade;

Step – 3 : If you still face same issue then login using sysdba and drop unwanted tablespaces from database

               

                  Right click on tablespace and drop tablespace

 

After this step you should be good, if still facing issue then its your bad luck L

 

Last option is to uninstall existing oracle set up and install latest oracle and do clean build and reload dataset.

This post was modified 3 years ago 2 times by kvasaikar