An APEX Database Monitoring App for XE – Guilty GUI pleasures

Guilty pleasures. For some, it’s a “diet” burger with “diet” fries, washed down with a “diet” shake. Others have a penchant for Kurt Geiger shoes. “I’m Welsh and I’m worth it”, they may well say. It may even be that Def Leppard track nestled in your playlist between Coldplay and Oasis.

In programming terms, APEX seems to fall into this category for me. On the one hand, it’s a declarative development environment. This means that, unless you’re very careful, the application you write for it is not going to be too portable to other front-end technologies. But, oh, it’s so nice to be able to bang out a bit of SQL and/or PL/SQL, click my mouse in the right place, and have a nice GUI application drop onto my browser.

If you’ve decided to try the latest and greatest APEX version on your XE installation, you’ll notice that the default Database Welcome Page disappears after the upgrade.
Rather than hunting around for it, I’ve decided to knock up something a bit better…well, different.
So, if you’d like to know how to get some interesting configuration information out of the database…or just want the entertainment value of watching me blunder about in APEX then read on…

A couple of points to note

As you are about to discover, I’m not an APEX expert. The steps here are the ones I used to make this app. There are almost certainly better ways of doing at least some of the things I’m doing here. I’m sure Jeff Kemp would have one or two suggestions.

The screenshots look a bit small in-line. If you open them in a new tab, you should see them in full-size.

The Standard XE Monitoring App

When you first login to a shiny new Oracle 11g XE installation, you’ll see a welcome page.

The application allows you to check details of :

  • Storage
  • Sessions on the Database
  • Database Parameter Settings

It also provides access to the APEX Development environment.

The New App

What I’m planning to do is a little bit different.
I’d like to be able to see, at a glance :

  • What database and APEX versions are in use
  • Details of my database such as what platform and host it’s running on
  • How full my tablespaces are
  • How much memory I’m using

I’d also like to be able to drill-down for more detailed information, as well as being able to look at the parameter settings.

I don’t need the app to contain details of running sessions – SQLDeveloper already has a pretty good tool for keeping an eye on those ( under the Tools/Monitor Sessions menu).

Ultimately therefore, we’d like to move from :

The standard Oracle XE 11g Home Page

The standard Oracle XE 11g Home Page

to :

That looks a bit different

That looks a bit different

The Application Owner

Before we start playing around in APEX, we need to create a database user to act as the application owner.
The application itself will be quite extensible. Once it’s up and running, I may want to add other information to it.
So, here we have our ‏first decision to make. We can either give the application owner access to everything we may want
to look at in the future, or we can follow best practice and just give it access to the data that it needs right now.
To create a new user, connect to the database as a user with DBA privileges (e.g. SYSTEM).

GRANT CONNECT to db_monitor IDENTIFIED BY pwd
/

If we go down the first “access to everything” route, the user creation is fairly simple :

GRANT SELECT ANY DICTIONARY TO db_monitor
/

By contrast, if we want to follow the principle of minimal privileges to do the job, we may want to consider creating a ROLE :

CREATE ROLE db_mon_app
/

…and grant privileges on the various objects the application needs to select from.
At this point, it’s worth noting that granting privileges on V$ views is a little bit different – i.e. you need to grant the privilege explicitly on the view underlying the public synonym :


GRANT SELECT on dba_registry TO db_mon_app
/
GRANT SELECT on v_$version TO db_mon_app
/
GRANT SELECT ON v_$database TO db_mon_app
/
GRANT SELECT ON v_$instance TO db_mon_app
/
GRANT SELECT ON dba_tablespace_usage_metrics TO db_mon_app
/
GRANT SELECT ON v_$memory_dynamic_components TO db_mon_app
/
GRANT SELECT ON dba_tablespaces TO db_mon_app
/
GRANT SELECT ON dba_data_files TO db_mon_app
/
GRANT SELECT ON dba_segments TO db_mon_app
/
GRANT SELECT ON v_$memory_target_advice TO db_mon_app
/
GRANT SELECT ON v_$parameter TO db_mon_app
/

…and then granting it to our application owner user…

GRANT db_mon_app TO db_monitor
/

Whichever route you’ve chosen, we should now have a database user called db_monitor who will act as our APEX application owner.

Creating a Workspace

To start with – assuming we’ve upgraded APEX ( the steps are the same for 4.1 and 4.2) – we’ll need to find the APEX Dev environment ( the one that used to be there on the XE Home Page). Simple enough really, just point your browser at http://localhost:8080/apex

You should now see a login screen. The workspace is INTERNAL and the User is ADMIN.
The password will be whatever you set during the APEX upgrade.

NOTE – if you’ve forgotten the apex admin password, you can re-set it via the script apxchpwd.sql.
To do this go to your apex installation directory. Be careful. $ORACLE_HOME/apex has a version of this script but it will not work for the latest APEX version, connect to SQL*Plus and execute apxchpwd.sql.
This will give you the option to reset the admin password.

Once you’ve connected, you’ll see the main administration page :

admin_home

Click on the blue Create Workspace button in the top right-hand corner.

Creating the Workspace

On the Identify Workspace Screen, enter the following :

Workspace Name : DB_MONITOR
Workspace ID : 100000

workspace1

Click the big blue Next button.

On the Identify Schema Screen, the values you need to enter are :

Re-use existing schema? : Yes
Schema Name : DB_MONITOR

You will not need to specify either the Schema Password or the Space Quota.

workspace2

Click Next

On the Identify Administrator Page :

Specify the credentials of an admin user you want to setup for this page ( I’m using these credentials to do the development as well).
Note – although the e-mail field is mandatory, you can put a dummy address in there if you want without any ill-effects…for the purposes of this application at least.

workspace3

Click Next

Finally, you’ll get a screen detailing information for the proposed workspace :
workspace4

Click Create Workspace and ….

workspace5

Click Done.

We now need to logout of the Administration Application and login to the workspace.

Setting up the Application

This is where we begin creating the application, defining the UI theme we want to use, and setting up a home page.

The URL for the login page is the same as for the Admin page i.e. http://localhost:8080/apex

The only difference is that you specify the name of the workspace you’ve just created, and supply the credentials of the admin user for the Workspace.

login_ws

You will be prompted to change your password at this point. However, the default security settings mean that you should just be able to set it to exactly the same as the password you’ve just logged on with.

You should now find yourself in the APEX main Development page. You will notice that the name of the Workspace you are logged into appears in the top-right-hand corner of the screen.

dev_home

Of the four large icons in the top region of the page, the two we will be using are :

  • Application Builder – where we’ll be coding the APEX application iteslf
  • SQL Workshop – where we’ll be testing out our SQL statements

For now, click on Application Builder.

You won’t see much on the next page, but you simply need to click the Create button :

create_app

In the Method screen, select Database and click Next

app_method

In the Name screen, leave everything at the default values apart from …

Application Name : Database Monitor

app_name2

Click Next.

For the following few pages, you just need to Click Next :

  • Pages Screen
  • Shared Components
  • Attributes

Now we get to the interesting bit, the User Interface Theme.

I’ve selected Bluejay (Theme 22).

app_ui

Once you’ve made your selection, click Next.

The confirmation page should look something like this :

app_confirm

Provided your happy, click Create Application.

Congratulations, you now have an application consisting of a login screen and a blank home page :

new_app

Now we come to the fun bit, building the application itself.

The Home Page

We’re going to have four sections on the Home Page(called Regions in APEX) , arranged in two columns.

I’m going to go through the SQL we’re going to use to retrieve the information we require.
You can follow along with the queries by clicking on the SQL Workshop Tab at the top of the page
and then selecting SQL Commands.

Software Versions

The Oracle database comprises a fair number of individual software components. These are listed in the
DBA_REGISTRY dictionary view. The database version itself can be found in V$version.
The two version numbers we are primarily interested in are the Database version itself and APEX.
To get this information, we can run the following query :

SELECT banner
FROM v$version
WHERE banner LIKE 'Oracle Database%'
UNION
SELECT comp_name||' Version '||version as banner
FROM dba_registry
WHERE comp_id = 'APEX';

If you run this in the SQL Commands window you should see something like this :

versions

Now to add this to our application.
If you’ve used SQL Workshop, you’ll need to get back to the Application Builder page.
Click the Applicatin Builder Tab and you should see this view :

app_builder2

Click the Edit button under the Database Monitor application. This will take you back to the main Application Builder page for the application.

Now, in the Page listing grid, click on the link for the Home page.
This will take you to a tree view of all of the objects in the Home page ( there aren’t many at the moment) :

tree_view

One of the quirks of the APEX development environment is that you have two views of page components – this one is the default, but most of the work is done in the Component View ( well, it is for this application).
To switch, you need to click the Switch to Component View button shown below :

comp_view_button

You should now see this :

comp_view

In the Regions section, click the ‘+’ button to create a new region.

In the Region screen, select Report :

region1

Click Next

Select Classic Report :

region2

Click Next

In the Display Attributes screen :

Title : Database Version Information

Leave all other values at the default.

region3

Click Next.

Now, we need to ender the SQL Query to get the version information. It’s the same one we ran just now :

SELECT banner
FROM v$version
WHERE banner LIKE 'Oracle Database%'
UNION
SELECT comp_name||' Version '||version as banner
FROM dba_registry
WHERE comp_id = 'APEX';

Accept remaining defaults.

region4

Click Create Region.

Back in the Component View, we should see the Region we’ve just created :

new_region_view

Now, in the Region area, on the Database Version Information line, click on the Report link.
Under Column Attributes set the Heading Type to None
Under Layout and Pagination set the Pagination Scheme to No Pagination Selected.

std_rpt_settings

Click on the Apply Changes button on the top right-hand-side of the page.

Back in the Component View, click on the Run button.

At the logon screen that appears, enter the credentials of the admin user you setup when you created the region.
You should now see :

version

Note – if you want to go back to the development environment from here, you can use the links at the bottom of the page :

dev_links

For now, we can use the link for Application 100 to go back to the Application Builder.

Database Details

Here we come to the question which often throws those unfamiliar with Oracle ( and some who are) – what is a Database and what is an Instance ?
The two terms seem to be used interchangeably but they must mean different things, right ?
In Oracle terms, the database is the collection of files that hold data and the Instance is the software and background processes that operate on the database.
An Instance MUST run against one, and only one database.
A database will only usually be accessed by a single instance, unless you’re in a RAC environment ( in which case you won’t be running XE).
The truth is that the term “database” is commonly used to mean both the database and the instance ( in the same way that the term “Linux” refers to GNU/Linux).
The reason for this digression into arcane terminology ? Well, the information we’d like to know about our database ( a term which I will use henceforth to mean both the database and the instance) is

  • The name of the database
  • the name of the host server the database is running on
  • the Operating System running on the host
  • When the database was started
  • The current status of the database
  • Whether logins are restricted
  • Whether the database is in archivelog mode

To get this, we need to look at two views – v$database and v$instance.
As I just said, if you’re not running RAC, there will only be one database and one instance, hence only a single row in each of these views.
This means that you can go to the SQL Workshop/SQL Commands and enter the following query without the risk of a visit from Mr Des Cartes :

SELECT db.name "Database Name", 
    ins.host_name Host, 
    db.platform_name OS,
    ins.startup_time "DB Started", 
    ins.status, ins.logins, 
    db.log_mode "Archivelog Status"
FROM v$database db, v$instance ins;

In the Component View, create a new Region ( by clicking the ‘+’ button in the Regions section)
Create the new region in the same way as before specifying :

Region Type : Report
Report Type : Classic Report
Region Title : Database Details

The source query is the one we’ve just run in the SQL Workshop :

SELECT db.name "Database Name", 
    ins.host_name Host, 
    db.platform_name OS,
    ins.startup_time "DB Started", 
    ins.status, ins.logins, 
    db.log_mode "Archivelog Status"
FROM v$database db, v$instance ins;

Back in the component view, click the region name ( Database Details)
Go to the Grid Layout Section and set :

Start New Row : No

grid

Click Apply Changes

Back in the Component View, click on the Reports link for the Database Details Region.

As with the previous region, under Layout and Pagination set Pagination Scheme to No Pagination Selected.

Click Apply Changes

From the Component View, run the page and you should see something like :

two_regions

All of this is pretty useful stuff, but this is really a Dashboard isn’t it. If so, then where are the graphs ?
I was just coming to that…

Tablespace Utilization by Percentage

Oracle doesn’t allocate space in one amorphous lump. Rather it does it by tablespace.
A Tablespace is essentially a container that comprises one or more physical files ( datafiles).

Working out exactly how full your tablespaces were used to take a bit of messing about. Fortunately, in 11g, we have
a handy view that gives us this information. Well, to a point.

Anyway, back in SQL tool, let’s see what happens when you run this :

SELECT tablespace_name, CEIL( used_percent)
FROM dba_tablespace_usage_metrics
ORDER BY 2 DESC;

Incidentally, don’t get too concerned about any high values you get in terms of the percentage of space used.
The figures in the DBA_TABLESPACE_USAGE_METRICS view are based on the space currently allocated. A tablespace that has
one or more datafiles set to AUTOEXTEND ( i.e. grab more space when needed) can actually grow up to a value specified in DBA_TABLESPACES…but we’re getting a bit ahead of ourselves here.

Some databases can end up with rather a lot of tablespaces over time. However, XE is fairly well behaved and should only have 5 or so. Therefore, we could fit this information onto a bar chart…

Once again, in the component view, click on the Create Region button ( + ).
This time, select Chart as the type of Region.
Click Next

You have a number of chart types from which to choose. On this occasion, we’re going to select :

Chart Rendering : Flash
Chart Type : Column

bar_chart

Click Next.

For the chart type, select 3D Column

3d_col

Click Next

Title – Space Usage by Tablespace

col_title

Click Next

For the Display Attributes :

Chart Title : Space Usage by Tablespace

At this point, let’s also get a bit flash with Flash and set :

Chart Animation : Slide from Bottom Center

Y Axis Title : Percent Used
X Axis Title : Tablespace Name

Click Next

Now, we need to enter the query to retrieve the data into the chart.
For this type of chart, the query must return 3 columns called LINK, LABEL and VALUE :

SELECT NULL link,
    tablespace_name label,
    CEIL(used_percent) value
FROM dba_tablespace_usage_metrics;

Now Click the Create Region Button

Before we finally run our chart, we need to make it just a little bit smaller.

Back in the Component View, click on the Space Usage by Tablespace link.

Now click on the Chart Attributes Tab :

chart_attr

In the Chart Settings section set :

Chart Width : 600
Chart Height : 400

Run the page now and you should see, appearing before your eyes :

Flash by name, flash by nature

Flash by name, flash by nature

Ooohhh, a spangly animated chart. What’s that ? You want to do another one ?

Oh, all right then…

Memory Allocation

If we wanted to find the total amount of memory being used by the database we need to find the size of two different structure – the SGA and the PGA.

Easiest way to do this is :

SELECT component, 
	current_size/1024/1024 as Current_MB, 
FROM v$memory_dynamic_components
WHERE UPPER(component) IN ('PGA TARGET', 'SGA TARGET')
/

Now we’ve started on this chart malarky we may as well bake this information into a big pie…

Click the Create Region button

Select region type of Chart.

Click Next

This time, the Chat type will be : Pie and Doughnut (mmmm, feeling hungry for some reason)

Click Next

Select 3D Pie

3d_pie

Click Next

Title : Memory Allocation

Click Next

Chart Title : Memory Allocation (MB)

Once again, the query we enter needs to return the three values for link, label and value :

SELECT NULL link,
   SUBSTR(component,1,3) label,
   current_size /1024/1024 value
FROM v$memory_dynamic_components
WHERE UPPER( component) IN ('PGA TARGET', 'SGA TARGET');

Now, for some reason, APEX doesn’t want to give me the option to adjust the display settings when creating the Pie chart.
For the moment, we’ll just create it and then edit it. So…

Click Create Region.

Back in the Component View, click on the Memory Allocation link.

Now click on the Chart Attributes Tab :

chart_attr

In the Chart Settings section set :

Chart Width : 600
Chart Height : 400

chart_settings

Now go down to the Display Settings section and set the following :

Animation : Slide from bottom centre
Value Postfix :MB

pie_display_settings

Now click on the Region Tab ( next to the Chart Attributes Tab you just clicked at the top of the page).

Now edit the region itself so that it displays nicely next to the tablespace chart
Go to the Grid Layout Sections.
The settings here will be the same as those for the Database Details Region – i.e. :

Start New Row : No

Now click Apply Changes.

Now when you run the page you should see something like this :

home_page_final

At this point Deb wandered by and commented on how clever and talented I was to produce such a masterpiece.
I tried to sound modest and told her it was just a little something I’d knocked up.
Yes I should feel guilty. After all, APEX did all the hard work. However, it’s nice to be appreciated :)

So, we now have something that looks rather more exciting than you’d expect from a few lines of SQL and checking a few boxes. However, we want to have some more in-depth information available. We are techies. We need facts, not just pretty pictures.

Storage – the gory details

We know what percentage of the space in our tablespaces has been used, but how does this translate into physical space remaining ? Do we need to do anything to allocate more space or will this happen automatically ?
And just what is it that’s taking up all this space anyway ?
To answer these questions, we’ll need to create some more pages …

The Main Storage Page

We’re going to display some more details about the Tablespaces in the database.
DBA_TABLESPACES is probably the best place to start. However, selecting everything from this view is probably a bit of overkill for our purposes.

Whilst tablespaces generally have the same block size defined as the database itself, that’s not always the case.
Tablespaces can have one of three statuses – ONLINE, OFFLINE or READ ONLY.
Tablespaces generally store PERMANENT information. This is the data you keep in your tables and indexes in the databases.
Addtionally there are tablespaces where space is reserverd for TEMPORARY storage, such as sorts done in resoliving a query that are too large for physical memory. There are also tablespaces used for UNDO – holding the before image of a block where the change has yet to be commited to the database.

The Logging status indicates the default logging attribute for segments (tables and indexes) in the tablespace
Force Logging is set to YES if logging is mandatory for direct path inserts into segments in the tablespace.
Segment extents are now usually LOCALLY managed ( i.e. space is allocated by reference to information in the datafile headers). However, it is possible to have this information referenced from the Data Dictionary (DICTIONARY managed).
Finally, we’d like to know how much space is being used and the maximum amount of space that a tablespace can occupy.
This information is stored in blocks, so we’ll want to convert it to MB.

All of which can be encapsulated in the following query :

SELECT ts.tablespace_name, ts.block_size, ts.status,
    ts.contents, ts.logging, ts.force_logging,
    ts.extent_management,
    CEIL((met.used_space * ts.block_size)/1024/1024) as used_space_mb,
    CEIL((met.tablespace_size * ts.block_size)/1024/1024) as max_space_mb
FROM dba_tablespaces ts, dba_tablespace_usage_metrics met
WHERE ts.tablespace_name = met.tablespace_name
ORDER BY 1;

So, back in the Application Builder page click on the Create Page button

new_page

Select the Page type of Report.
Click Next.

This time, we’re going to create an Interactive Report

new_interactive_rep

Click Next

In the Page and Region Attributes specify the following :

Page Name : Storage
Report Name : Tablespaces
Breadcrumb : Breadcrumb

storage_region

Click Next.

In the Tab Options :

Tab Options : Use an existing tab set and create a new tab within the existing tab set
New Tab Label : Storage

storage_tab

Click Next.

Enter the same query that we’ve just used – i.e. :

SELECT ts.tablespace_name, ts.block_size, ts.status,
    ts.contents, ts.logging, ts.force_logging,
    ts.extent_management,
    CEIL((met.used_space * ts.block_size)/1024/1024) as used_space_mb,
    CEIL((met.tablespace_size * ts.block_size)/1024/1024) as max_space_mb
FROM dba_tablespaces ts, dba_tablespace_usage_metrics met
WHERE ts.tablespace_name = met.tablespace_name;

Also, set the Link to Single Row View to No

storage_query

Click Next.

On the next screen, click Create.

When you run the page, you should see something like this :

storage_new

If you want to add the sort order we got from the Order By clause, you can do this whilst running the report.
Click the column heading Tablespace Name and select the Sort Ascending icon :

ts_sort_order

So, we now have some useful information about our tablespaces. However, we still don’t know if we have to do anything about allocating extra space…

Datafiles

To start with, we want to know which datafiles are associated with which tablespace.
We’d like to know the file name, the size, the maximum size and – quite importantly – whether the datafile is autoextensible.
If the datafile is Autoextensible, then the tablespace will extend automatically until it reaches it’s defined maximum size.
It would also be good to know if the file is online and available to the database.

Given that, in XE at least, there aren’t likely to be too many datafiles, we can show the complete list in one go.
The view that holds information on the datafile is DBA_DATA_FILES.

The query we need is :

SELECT tablespace_name, file_name,
	CEIL(bytes/1024/1024) size_mb,
	CEIL(maxbytes/1024/1024) as max_size_mb,
	autoextensible,
	online_status, status
FROM dba_data_files;

What we’re going to do here is to create a page and then link to it from the storage page by means of a button.
So…

In the Application Builder, click the Create Page button.
In the Create Page dialogs select :
Report
Interactive Report

In Page and Region Attributes :
Page Name : data_files
Report Name Data Files
Breadcrumbs : breadcrumb

Create Breadcrumb Entry

Entry Name : Data Files
Parent Entry : Select the Storage page

datafile_region_attr

Click Next

For the Tab Options, accept the default (i.e. Do not use tabs)
Click Next

The SQL Query is the one above i.e.

SELECT tablespace_name, file_name,
	CEIL(bytes/1024/1024) size_mb,
	CEIL(maxbytes/1024/1024) as max_size_mb,
	autoextensible,
	online_status, status
FROM dba_data_files;

Now specify the following :

Uniquely identify rows by : Unique Column

Unique Column : file_name

datafile_query_attr

Click Next

Click Create.

For the finishing touch, we need to actually run the page.

First to sort out the ordering ( i.e. alphabetically by tablespace_name) :
Click on the tablespace_name column header and select the yellow up arrow.

Now we want to group by tablespace_name

Click on the Actions button and select Format/Control Break
Select Tablespace_name and Apply.

datafile_ctrl_break

With these changes applied the report should now look something like this :

datafiles

To ensure that the page retains these settings:

Click Actions/ Save Report
Save : as default report settings
Default Report Type : Primary
Click Apply

In terms of navigation, we can get back to the Storage page from the Datafiles page, simply by using the breadcrumb at the top of the page.
To get from the storage page to the datafiles page, we’ll need to add a button on the Storage Page.

Click on the Storage breadcrumb to take us back to the main storage page.
Click Edit Page 2

In the Component View click the plus next to Buttons

create_button

For the Button Region :

Region : Tablespaces

btn_region

Click Next

For the Button Position :
Select Create button in a Region Position

btn_pos

Click Next

For the Button Attributes :

Button Name : data_files
Label : View Data Files

btn_attr

Click Next

For the Button Display Properties, leave everything as Default and click Next.

For the Action When Clicked :

Action : Re-direct to page in this application
Page : 3 ( the data files page)

btn_final

Click Create Button.

When you run the storage page again, the button should appear on the top-right.

datafiles_button

Click on the button and you should now go to the Data Files page.

There is one final question that we need to answer in our Storage section – what’s hogging all the space in a given tablespace ?

For this, we’re going to need a link from each tablespace reported on the Storage tab into a listing of the segments contained in that tablespace….

Segments

Quick and general definition : a segment is an object which holds persistent data, most commonly tables and indexes.

I always find this sort of thing a bit fiddly in APEX. We need to create a new Interactive Report where part of the predicate is based on values being passed from the Storage Screen…but we can’t code THAT bit until we’ve done the Segments page and we can’t TEST the segments page until we’ve passed in the value.
Any APEX experts reading this, please shout if there’s an easier way…

Anyway, back in the Application Builder, hit the Create Page button.

Create an Interactive Report.

Page Name : Segments
Region Name : Segments by Tablespace
Breadcrumb : Breadcrumb

Breadcrumb Entry Name : Segments
Parent Entry : Storage

segments_attr

We are not using tabs for this page.

For now, we’ll just use a standard query :

SELECT owner, segment_name, segment_type,
	tablespace_name, 
	CEIL(bytes/1024/1024) as size_mb
FROM dba_segments;

Link to Single Row View : Yes
Uniquely Identify Rows by : Unique Column
Unique Column : segment_name

Once we’ve created the page, we need to go back and create a new hidden item on the page. This will hold the name
of the tablespace that we’re going to run the page for.

In the Component View, Create a new item…

new_item

Item Type : Hidden

hidden_item

Click Next

Item Name : P4_Tablespace_name
Region : Segments by Tablespace

hidden_attr

Click Next

Accept the default values in the Settings screen and click Next.

In the Source settings :

Source Type : Always Null

hidden_src

Click Create Item.

Now we need to go back to the query and add the hidden column to the predicate.

In the Component View, click the Segments by Tablespace Region :

edit_region

Now go back to the query and add the predicate :

WHERE tablespace_name = : P4_TABLESPACE_NAME

hidden_qry

Click Apply Changes

We now have a report which will use the value of the hidden field at runtime.
Now we have to make sure that our hidden field will have a value at runtime.

Go back and Edit Page 2 ( The main Storage Page).

In the Component View, click on the Tablespaces Interactive Report.

Edit the tablespace_name column :

edit_col

In the Column Link Section of the Page :

Link Text : #TABLESPACE_NAME#

Target : Page in this applicatin Page : 4 ( Segments)

Name : P4_TABLESPACE_NAME Value : #tablespace_name#

col_link

Click Apply Changes.

Now, to test :

Run Page 2

Click on a Tablespace Name value, let’s go for SYSAUX.

This should now take us to a list of Segments stored in that tablespace :

segments_final

As with the Data Files page, you can return to the main storage page using the breadcrumb.

Our storage details are complete…time now to move on to …

Memory

As we’ve already seen, Oracle allocates memory to two main areas. The Process Global Area (PGA) is used for storing user-specific session information ( package variable settings, Globabl Temporary Tables etc).
The Shared Global Area (SGA), in contrast, comprises several components. Some of these components are not set, so we want to focus on those that are.
We can also see the PGA setting and work out the total memory allocation for the database.
We can see the current and maximum likely sizes of these components with the following query :

SELECT component, CEIL(current_size/1024/1024) as current_mb, 
    CEIL(max_size/1024/1024) as max_mb
FROM v$memory_dynamic_components
WHERE current_size > 0
AND component != 'SGA Target';

Looks like we’ve got a query for our main Memory page…

Main Memory Page

Back in the Application Builder, click the Create Page button.

Create and Interactive Report

Go through the Create Page dialogs accepting the defaults except for :

Page Name : Memory
Region Name : Memory Components

Tab Options : Use existing tab set
New Tab Label : Memory

The query is as above – i.e. :

SELECT component, CEIL(current_size/1024/1024) as current_mb, 
    CEIL(max_size/1024/1024) as max_mb
FROM v$memory_dynamic_components
WHERE current_size > 0
AND component != 'SGA Target';

Link to Single Record View : No

Run the Report.

Set to sort by current_mb desc
Actions Format Aggregate
New Aggregation
Function : SUM
column : Current Mb
Click Apply.

Do the same for the Max Mb column :

aggregate

Actions/Save Report/ As default report settings

The report should now look something like this :

mem_main

NOTE - the totals may be a little on the high side as we’ve used the CEIL function in the query so that we get nice whole numbers on the report.

Memory Target Advice

By default, Oracle will automatically allocate the amount of memory specified in the memory_target parameter to wherever it feels it is needed.
However, it does also offer some free advice as to the effect of changing the amount of memory you allocate to the database.

This can be found in v$memory_target_advice :

SELECT memory_size_factor, memory_size, estd_db_time_factor
FROM v$memory_target_advice
ORDER BY memory_size_factor;

A quick description of the columns is probably in order at this point.
The memory_size_factor is the factor by which the memory allocation would change
(e.g. 1.25 – increase by 25%, 0.75 – decrease by 25%, 1 – current setting).
The estd_db_time_factor is how much quicker ( if the value is less than 1)
or slower ( if value is greater than 1) Oracle thinks the database would run
were you to make the appropriate change to the amount of memory available to
the database.

This view is similar to V$SGA_TARGET_ADVICE, which, as it’s name suggests, is specifically for the SGA.

Once again, create an Interactive Report Page :

The Page is an Interactive Report.

In the Page and Region Attributes…

Page Name : Mem_advice
Region Name : Memory Advice
Breadcrumb : Breadcrumb
Entity Name : Memory Advice
Parent Entry : Memory

The query is the one specified above – i.e.

SELECT memory_size_factor, memory_size, estd_db_time_factor
FROM v$memory_target_advice
ORDER BY memory_size_factor;

Link to Single Row View : No

Once created, go into the report…

Actions/Format/Highlight

Name : current_size
Background Color : select green (#99FF99)
Highlight Condition :
Memory Size Factor = 1

mem_adv_highlight

Click Apply

mem_adv_final

To make this change part of the default report…

Go to Actions and select Save Report.

Save : As Default Report Settings
Default Report Type : Primary
Click Apply

Now we need to link this page to the main memory page. Once again, we’ll do this with a button
on the parent page.

Go back to the main memory page and open the Component View.

Click the + to add a button.

Select a region for the button : Memory Components
Click Next.

Position : Create button in a region position

Click Next.

Button Name : Memory Target Advice
Click Next.

Leave the Position settings at the default and click Next.

Action : Re-direct to page in this application
Page : 7 (or whatever the memory advice page number is)

Click Create Button.

When we now run the page we should see the button in the top right hand side :

mem_adv_btn

Parameters

Database parameters. In oracle, there are lots of them. They can be categorized in various ways.
Oracle defines 20 or so parameters as BASIC – i.e. the minimal ones that must be set for the database to work.
It also holds a record of which parameters are set to non-default values.
It even holds details of what level the parameter can be set ( session, system, instance) and whether a parameter
is depracated.

What we’re going to do is just get everything from the V$PARAMETER view, setup a couple of options for an
Interactive Report ( view basic params and params with non-default values).
If we then need to look at other classifications, we can amend the report at run-time.

The main query then is :

SELECT *
FROM v$parameter;

So…
Create Page
Report/Interactive Report
Click Next

Page Name : Parameters
Region Name : Parameters

Breadcrumb : Breadcrumb

Click Next

Tab Options : Use an existing tab set and create a new tab within the existing tab set.
New Tab Label : Parameters

Click Next.

Enter the query :

SELECT *
FROM v$parameter;

Link to Single Row : Yes
Uniquely Identify Rows By : Unique Column
Unique Column : num

Click Next.

Click Create.

Run the report.

Here we want to select the columns to display, namely :
Name – the parameter name
Display Value – the value it’s set to
Description – a description of the parameter
Update Comment – comment entered when parameter was changed

In the Actions Menu, choose Select Columns.

Select the appropriate columns

display_cols

Click Apply

Now Click the name column and select the Up arrow icon to sort the parameters alphabetically.

Now Save this as the Default Report.

Note – Although this setting means that not all of the columns will be visible on the report when you run it, you will still be able to see them in the Single-Row View. Simply click on the icon to the left of each row.

We now want to create versions of the report which show Basic parameters and parameters set to non-default values.

Basic Parameters first :

Still in the Reports Runtime…

Click Actions and select Filter from the dropdown menu

Filter Type : Column
isbasic = ‘TRUE’
Click Apply.

Now click Actions and select Save Report

Save : As Named Report
Name : Basic Parameters
Description : Parameters defined as basic

Click Apply

In the Reports drop-down next to the Action button, select Primary Report.
You should now see the original report.

Now to create the non-default parameters version of the report :

Click Actions and select Filter

Filter Type : Column

isdefault = ‘FALSE’

Click Actions and select Save Report

Save : As Named Report
Name : Non-Default Parameters
Description : Parameters set to values other than the Oracle default value

Click Apply.

When we next go to this report, we should see the primary report view with the two alternative views we’ve created in the Reports drop-down :

rep_options

Releasing the final Application

Once you’re happy with your application, it only remains to make it available in a normal runtime ( as opposed to development) mode.

Go to the main Application Builder page for the application and click Edit Application Properties

app_builder

In the Availability section :

Status : Available

app_avail

Click Apply Changes at the top of the page.

The URL for your app should now be :

http://localhost:8080/apex/f?p=101

…where 101 is the number of your application.

Setting the shortcut

The desktop shortcut for xe does not contain a url, but points to a batch script.
For linux, the script is at :

$ORACLE_HOME/config/scripts/gettingstarted.sh

You simply need to edit this script to read :

for i in firefox mozilla konqueror ; do
        if test -x "/usr/bin/$i"; then
                /usr/bin/$i http://localhost:8080/apex/f?p=101
                exit $?
        fi
done

When you now click on the getting started link, you’ll be prompted for a username and password.
This is the logon to the new application.
Enter the appropriate credentials and you’re nice new home page should appear before your eyes.
At this point my guilt has finally outweighed my pleasure. I’m off for a run followed by a cold shower….or maybe a stroll followed by a cold beer at the nearest pub.

9 thoughts on “An APEX Database Monitoring App for XE – Guilty GUI pleasures

  1. Great stuff! I’ve had a “database dashboard” for a while, which gives me a summary of tablespace free space and the status of my jobs, as well as some other metrics specific to my applications – but it could only see the objects for that particular workspace; but recently I’ve started creating new workspaces and they don’t get reported, and have been thinking of doing exactly what you have done here. Just haven’t had time to sit down and work out all the privileges required.

    You’ve done the hard work for me, thanks! I’m going to grab some of this and implement it myself as well. Maybe add the dbms jobs and scheduler in as well.

    You said: “One of the quirks of the APEX development environment is that you have two views of page components – this one is the default, but most of the work is done in the Component View ( well, it is for this application).”

    I’m pretty sure that Component View and Tree View both provide the same capabilities, just in different ways, so you could have done all of those actions (such as creating pages and regions) in either view. Personally I always use Tree View now, but it’s basically a matter of trying them out and picking which one suits you.

  2. Jeffrrey,

    thanks for the tip about the Component and Tree Views. I’ll have another play around with the Tree View.
    I’d be interested to see which additions you make to the App when you implement it. Scheduled Jobs sounds like a good place to start, but I’m sure there’s other Data Dictionary information that may be useful, depending on the Applications that people have running on their databases.

    Mike

  3. Will you post the exported application SQL? I’d like to play with this app, but I just don’t have the time to go through the steps of building it right now. Were there any particular reasons why you focused on using this application for XE only and not in production? Thanks!

    • I’ve started building my own version of it, it has a few extra features that I find useful. The most useful bit is a job viewer (both dbms_jobs and scheduler jobs). Eventually I want to add some job controllers so I can turn jobs on and off and modify them from the interface. I’ve also added a very basic schema browser, but it’s deliberately low featured because I think SQL Developer is a better tool for browsing a schema.

      Anyway, it’s shared here (no warranties express or implied!):

      https://docs.google.com/file/d/0BzA2qXymQXV2UlloSXlrYktGQU0/edit?usp=sharing

      • Jeff,

        Thanks, sounds like you’re extending and customising this app, which is what I had in mind when I wrote it.
        Will definitely take a look. Thanks.

        Mike

      • Thanks Mike! Works great — when I have time I’ll tinker with some customizations of my own and follow up :)

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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