Another reason to upgrade to OEM 12C

Posted: January 5, 2015 in Database Administration, OEM12c

As far as electronics go, I am that guy who upgrades to the latest “free phone.” I am the proud owner of the I-Phone 4. I have owned it for two years and didn’t get it until Verizon offered it for free. I probably would not even own a smart phone if my employer didn’t strongly suggest I should be connected to the company email. Of course, now that I have internet on my phone, I can’t seem to function properly without it.

When it comes to the OEM product, don’t be the last one to upgrade to 12C. My last project still utilized the 11G and 10G versions of OEM. One reason they may not have upgraded was a perceived lack of support for 11G software. This is an incorrect assumption since OEM 12C was released in October 2011, prior to the release of Oracle 12 database to the general public in 2013. It also supports the use of Oracle Streams.

One of the major flaws I have noticed in OEM 11G is in tablespace management. The summary page for tablespace management displays the total size of the tablespaces and the percentage full. What it omits is the maximum space for individual datafiles or tablespaces. For instance, if you create a datafile with a size of 4GB autoextensible to 32GB, the summary page will show a 4GB datafile. If 3.8 GB of data is inserted into the tablespace, the summary page will show the datafile to be 95% full. In reality, the datafile is less than 12% full. If you click on the tablespace, it will show you all of the datafiles, their maximum size, and their allocated size. This can be a time consuming operation with OEM 11G as it tends to run slow. It also can create a false alert that might send you emails you don’t really desire. Before adding a new datafile, you need to know the maximum size of the datafile and the free space on the OS file system. OEM 12C addressed this issue and shows maximum size as well as current allocated size. It is much more accurate for tablespace usage alerts.

I searched for and created a simple query of dba_data_files to use in conjunction with the tablespace usage summary page on OEM 11G. Other useful queries can be found at

select tablespace_name, sum(bytes)/1024/1024 as tbs_size, sum(maxbytes)/1024/1024 as “Max_Size” from dba_data_files
group by tablespace_name;

After awhile, I only used the dictionary tables dba_freespace, dba_segments and dba_data_files to give me information on the database. The OEM tool was just too slow for regular usage. The reason for this slowness was likely due to sharing server resources with the database itself. It is a good idea to place the OEM tool on its own server but this is not always possible due to budget constraints.

Don’t be the last DBA to switch to OEM 12C. It really is a huge improvement over all of its previous versions.

Thanks for reading!!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s