Recently a colleague came by and asked whether I could help him with finding out how much memory was already assigned to Oracle databases. It was fun to find out what was exactly he needed and to construct the one-liner bit by bit, as I detail below. When it seemed to work ok, I wrapped it in a small shell script to make it more flexible and reusable and deployed it to all Oracle database servers using CFEngine. You can find the final shell script on Github. I’d suggest you use the shell script instead of the one-liners below, as it is more accurate.
But let’s now play a bit with the shell. Since part of the memory used by Oracle is dynamically assigned, using tools like ‘free‘ will not really help to determine how much memory is allocated. My colleague told me we’d to look at the ‘PGA‘ (Program Global Area) and ‘SGA‘ (System Global Area) values in the Oracle settings of each database.
From the Oracle docs:
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas. A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA. You use database initialization parameters to set the size of the instance PGA, not individual PGAs.
These settings can be found in the ‘spfile*.ora‘ of a given database. This is how I found all of them:
find /oracle/product/*/db_1/dbs/spfile*.ora
Because these are binary data files, so you need ‘strings‘ to get the readable out put. The settings we were looking for had ‘target‘ in their name, so we could grep them out like this:
strings -a /oracle/product/11.x.y.z/db_1/dbs/spfiledbname.ora |\ grep -i target
We’re especially interested in settings that started with ‘*.pga‘ or ‘*.sga‘. Example:
*.pga_aggregate_target=955252736 *.sga_target=1610612736
Of course, grep can do that too. On the lines we found, the value of the setting (next to the ‘=’) can be in kilobytes (as shown above), but also in megabytes (ending in ‘M’ or ‘m’) or gigabytes (ending in ‘G’ or ‘g’).
The question was: How to quickly sum these values on the command line to get an impression of the assigned memory. My colleagues usually did this by hand, but now there were a bit too many. You shouldn’t do this by hand anyway, if you ask me. So I gave it a try:
First we need to select the correct lines of all ‘spfiles‘ of all databases on a server:
find /oracle/product/*/db_1/dbs/spfile*.ora |\ xargs strings -a |\ grep target |\ grep -iE '\*.[ps]ga'
This resulted a list of all the settings we were looking for. The same as above, but for all databases, one per row. The next thing to do, to be able to sum them up, is to remove everything except the values we want to sum. The command ‘cut’ can do this, using a delimeter ‘=’ and then the second field. It looks like this:
find /oracle/product/*/db_1/dbs/spfile*.ora |\ xargs strings -a |\ grep target |\ grep -iE '\*.[ps]ga' |\ cut -d= -f2
It returned all kind of different values, like:
1500M 2g 754974720 2G 351272960 2g 2561671168
To sum them, one needs to convert everything to be of the same order. In this case values without a postfix were in kilobytes, so we should convert gigabytes and megabytes to kilobytes. Since I’d need a calculator later on anyway, I used ‘sed‘ to convert these values as shown below:
find /oracle/product/*/db_1/dbs/spfile*.ora |\ xargs strings -a |\ grep target |\ grep -iE '\*.[ps]ga' |\ cut -d= -f2 |\ sed -e 's/G/*1024*1024*1024/gi' |\ sed -e 's/M/*1024*1024/gi'
This results in:
1500*1024*1024 2*1024*1024*1024 754974720 2*1024*1024*1024 351272960 2*1024*1024*1024 2561671168
For this to be calculated with ‘bc‘, we need it all on one line and ‘+’ between the rows. That step can be done with the command ‘paste‘. We want all values to be on one line (-s) and use ‘+’ as a delimiter (-d):
find /oracle/product/*/db_1/dbs/spfile*.ora |\ xargs strings -a |\ grep target |\ grep -iE '\*.[ps]ga' |\ cut -d= -f2 |\ sed -e 's/G/*1024*1024*1024/gi' |\ sed -e 's/M/*1024*1024/gi' |\ paste -s -d+
This results in:
1500*1024*1024+2*1024*1024*1024+754974720+2*1024*1024*1024+351272960+2*1024*1024*1024+2561671168
Final step is to feed this calculation to ‘bc’ and you’re done:
find /oracle/product/*/db_1/dbs/spfile*.ora |\ xargs strings -a |\ grep target |\ grep -iE '\*.[ps]ga' |\ cut -d= -f2 |\ sed -e 's/G/*1024*1024*1024/gi' |\ sed -e 's/M/*1024*1024/gi' |\ paste -s -d+ |\ bc
This results in:
11683233792
If you want to convert it to display gigabytes instead of kilobytes, add this ‘awk‘ command to instruct ‘bc‘ to divide and display two decimals (otherwise it will floor down the value):
find /oracle/product/*/db_1/dbs/spfile*.ora |\
xargs strings -a |\
grep target |\
grep -iE '\*.[ps]ga' |\
cut -d= -f2 |\
sed -e 's/G/*1024*1024*1024/gi' |\
sed -e 's/M/*1024*1024/gi' |\
paste -s -d+ |\
awk {'print "scale=2; (" $1 ")/1024/1024/1024"'} |\
bc
This results in:
10.88
This means 10.88 GB of memory has been allocated to databases.
A nice example of the power of the Linux shell 🙂
Last week I attended the 
Remi Bergsma's blog 