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
to :
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).
If we go down the first “access to everything” route, the user creation is fairly simple :
By contrast, if we want to follow the principle of minimal privileges to do the job, we may want to consider creating a ROLE :
…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 :
…and then granting it to our application owner user…
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 :
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
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.
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.
Click Next
Finally, you’ll get a screen detailing information for the proposed workspace :
Click Create Workspace and ….
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.
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.
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 :
In the Method screen, select Database and click Next
In the Name screen, leave everything at the default values apart from …
Application Name : Database Monitor
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).
Once you’ve made your selection, click Next.
The confirmation page should look something like this :
Provided your happy, click Create Application.
Congratulations, you now have an application consisting of a login screen and a blank home page :
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 :
If you run this in the SQL Commands window you should see something like this :
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 :
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) :
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 :
You should now see this :
In the Regions section, click the ‘+’ button to create a new region.
In the Region screen, select Report :
Click Next
Select Classic Report :
Click Next
In the Display Attributes screen :
Title : Database Version Information
Leave all other values at the default.
Click Next.
Now, we need to ender the SQL Query to get the version information. It’s the same one we ran just now :
Accept remaining defaults.
Click Create Region.
Back in the Component View, we should see the Region we’ve just created :
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.
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 :
Note – if you want to go back to the development environment from here, you can use the links at the bottom of the page :
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 :
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 :
Back in the component view, click the region name ( Database Details)
Go to the Grid Layout Section and set :
Start New Row : No
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 :
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 :
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
Click Next.
For the chart type, select 3D Column
Click Next
Title – Space Usage by Tablespace
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 :
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 :
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
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 :
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
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 :
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 :
In the Chart Settings section set :
Chart Width : 600
Chart Height : 400
Now go down to the Display Settings section and set the following :
Animation : Slide from bottom centre
Value Postfix :MB
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 :
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 :
So, back in the Application Builder page click on the Create Page button
Select the Page type of Report.
Click Next.
This time, we’re going to create an Interactive Report
Click Next
In the Page and Region Attributes specify the following :
Page Name : Storage
Report Name : Tablespaces
Breadcrumb : Breadcrumb
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
Click Next.
Enter the same query that we’ve just used – i.e. :
Also, set the Link to Single Row View to No
Click Next.
On the next screen, click Create.
When you run the page, you should see something like this :
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 :
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 :
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
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.
Now specify the following :
Uniquely identify rows by : Unique Column
Unique Column : file_name
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.
With these changes applied the report should now look something like this :
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
For the Button Region :
Region : Tablespaces
Click Next
For the Button Position :
Select Create button in a Region Position
Click Next
For the Button Attributes :
Button Name : data_files
Label : View Data Files
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)
Click Create Button.
When you run the storage page again, the button should appear on the top-right.
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
We are not using tabs for this page.
For now, we’ll just use a standard query :
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…
Item Type : Hidden
Click Next
Item Name : P4_Tablespace_name
Region : Segments by Tablespace
Click Next
Accept the default values in the Settings screen and click Next.
In the Source settings :
Source Type : Always Null
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 :
Now go back to the query and add the predicate :
WHERE tablespace_name = : P4_TABLESPACE_NAME
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 :
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#
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 :
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 :
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. :
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 :
Actions/Save Report/ As default report settings
The report should now look something like this :
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 :
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.
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
Click Apply
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 :
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 :
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 :
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
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 :
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
In the Availability section :
Status : Available
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 :
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.
Filed under: APEX, Oracle Tagged: APEX Bar Chart, APEX Create a Workspace, APEX Create Application, APEX Pie Chart, apxchpwd.sql, create role, dba_data_files, dba_registry, dba_segments, dba_tablespaces, dba_tablespace_usage_metrics, grant select, MEMORY_TARGET, Oracle database memory usage, oracle database parameter settings, oracle database space usage, oracle instance host name, oracle instance host os, v$database, v$instance, v$memory_dynamic_components</a