Shared Memory usage per database instance.

[Gulam Mohiuddin]

From the following output, I want to know exactly which Oracle database instance is using wich Shared Memory Segment.

It just gives the user names who owns the Shared Memory Segement. So if we have ten different database instances under the same user, it would be difficult to figure out shared memory usage per each database instance.

# ipcs -mob
Shared Memory:
T ID KEY MODE OWNER GROUP NATTCH SEGSZ
m 537 0x441806fc --rw-r--rw- root sys 0 13812
m 3093 0x491806fc --rw-r--rw- root sys 0 16344
m 26 0x411806fc --rw-r--rw- root sys 1 4508024
m 23 0xe0da3354 --rw-r----- ora816 dba 6 36040704
m 20504 0xa122aef0 --rw-r----- ora816 dba 6 81096704
m 11803 0x6a7dfce7 --rw-r----- oracle dba 6 8290304
m 12304 0x55fef118 --rw-r----- oracle dba 7 5439488
IPC status from /dev/kmem as of Mon Mar 19 11:09:21 2001

Thanks.

[James A. Donovan]

The last column shows the segment size of the shared memory allocated. This number is equivalent to your database's SGA size. So all you need to do is know the size of each SGA of each database and you'll know what shared memory segment belongs to each database.

[John Palmer]

From the output you supplied, the only way would be to get the size of the SGA from oracle
svrmgrl (or sqlplus if 8i)
connect internal
show sga

and tie that up to the segment size. That assumes however that no two instances have the same SGA size and that all instances occupy a single shared memory segment.

Another possible way is to use ipcs -am. This will give you a CTIME (Time segment created) column which could be cross referenced with the time that the database was started. This is not foolproof though.

You could also arrange for each instance to be run under a different UNIX user account.

Regards,
John