Get rid of ORA-04031 "cannot allocate XXXX Bytes ..."

Today a customer called telling me he was unable to use his software, because at login he receivces an
ORA-04031 "cannot allocate XXX Bytes ..."

First mind "aah out of shared pool".

First i tried an
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

But this doesn't solve the problem.

So i decided to dive into the database and take a look what's going on.

The following query determines the available memory for SHARED_POOL_SIZE in Oracle SGA

SQL> select sum (bytes)/1024/1024 from v$sgastat where pool=’shared pool’;

The above query provides the shared pool size in mega bytes.

The following query determines the total used memory by shared_pool in Oracle SGA.

SQL> select sum (bytes)/1024/1024 from v$sgastat where pool=’shared pool’ and name not in (’free memory’);

The total allocated memory for shared_pool in the init.ora or spfile can be detemined by

SQL> select value from v$parameter where name=’shared_pool_size’


I found a customer package consuming 400 megs of shared pool, i stopped the execution by killing the session and the problem was gone.

VxVM drop plex/mirror from volume and rename plex

Today i had to do some work on a storage system where new storages where installed and the old one should be unconfigured from the system.

By using

sh#> vxprint -g $DISKGROUP -rtLv $VOLUMENAME

i checked what mirrors (plexes) are available for the volume.

I selected the mirrors located on the old storage an dropped them from the volume with

sh#> vxplex -o rm dis $PLEXNAME

I noticed that the naming convention, after deleting the older plexes, does not look really pretty, because when the plex was created VxVM added a sequentiell number add the end of the plexname, so i decided to rename the plexes.

This was done with:

sh#> vxedit -g $DISKGROUP -p rename $OLD_PLEX_NAME $NEW_PLEX_NAME

After that all looks pretty well.

Rename Oracle temporary tablespace

Today i run into the problem, that a customer has created a temporary file for temp tablespace called ".dbf".

Because it is not possible to rename the file i did the following steps to get a usefull filename:

First i created a new temporary tablespace called temp2

SQL> CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/u05/oradata/db_1/data/TEMP2_01.dbf'
SIZE 1024M;

After that i switched the system to the new temp tablespace:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Than i dropped the old temp tablespace

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

and recreated it:

SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/u05/oradata/db_1/data/TEMP_01.dbf'
SIZE 1024M;

and changed the database back to tablespace named TEMP

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

and deleted TEMP2

SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

This all works online.