John Scott

I can barely believe it’s time for the second #JoelKallmanDay post (organised / initiated by the fantastic Tim at Oracle Base). We sadly lost Joel, who was a great friend and all-round great human in 2021.

These #JoelKallmanDay posts by people around the world are a celebration of technology, features, tip, tricks and basically anything you’d like to hold up and say “this is great” but also a way to acknowledge just how influential Joel was to many of us – I for one flourished in my career and personally through knowing Joel.

So, what is the thing that I’d going to post about on #JoelKallmanDay? Well, with so many options I had to rely on my gut instinct as the first thing that popped into my head and it isthis –

Unsurprisingly from me, it’s an Oracle APEX related feature, which is an option available to you when you wish to export an application

So what does it do? Well as the name implies, it allows you to export the application not as it is right now, but as it was at some point in the past.

The title of this post is Flashback (What a feeling), sorry for the dreadful pun, but it uses the underlying Flashback capabilities of the database to export the metadata of your APEX application. You are able to do this because APEX stores all the metadata of the application in underlying tables in the Oracle database, so using the Flashback capabilities is trivial (but extremely powerful). I also used the phrase “What a feeling”, because we’ve all had (or I’ve certainly felt) that dreadful feeling right after you’ve made a mistake that you need to recover from (you know the one, where your heart falls into your stomach).

Hopefully you can see how this could be useful, I’ve lost count of the number of times over the years when I’ve made a change to an application that I needed to easily revert, or perhaps deleted something from the wrong page and I’ve been able to export the application at a point in time before those changes.

One key thing to bear in mind, the amount of time you’re able to go back in time is not infinite, it’s worth looking at the popup help to see which database parameters control how far back in time you can go.

My best advice here is to increase (or talk to whomever can increase) the UNDO_RETENTION parameter to give you enough latitude that you’re able to go back far enough in time to recover from any mistakes (obviously there may be other knock-on effects and considerations from increasing the UNDO_RETENTION). Typically I feel happiest with an UNDO_RETENTION of at least 24 hours (that gives me a chance to go to sleep, wake-up and then realise a mistake has been made that I want to recover from, but that’s my personal preference).

Whilst, sadly we obviously cannot do an As Of to go back in time ourselves, I am very privileged to have so many happy memories of Joel. I miss you my friend.

This blog post is inspired by the Tim Hall post for Joel Kallman Day.

There are so many great Oracle innovations, or features that I could choose for this post. I was a very early adopter and huge advocate of Oracle APEX, I absolutely love SQL Developer, I’ve used SQLcl, ORDS and many other Oracle technologies that have enabled me to not only do my job, but to love my job.

So…what to choose, well I actually had a flashback to a conversation I had with Joel at one of the first conferences I ever went to. I won’t bore you with the fine detail of the conversation, but Joel responded to something I said with this (I’m paraphrasing from memory, but I think it’s pretty accurate)

“Have you looked in the help? Dude, we put that stuff in there for a reason, if it’s not in there let me know and I’ll get it added“

Now for those people who didn’t know Joel, I have to say that his tone in his reply was entirely positive, i.e. he wasn’t being dismissive or brushing off my question he was genuinely reminding me that Application Express has (and always has had) a fantastic set of documentation that comes along with it.

Tucked away in the header of your APEX Development environment is that helpful little Question Mark icon which will whisk you off into the APEX Documentation, the APEX Discussion Forum or learn more about Oracle APEX.

I have seriously lost count of the number of times I’ve used that icon to double-check the parameters needed for a particular API function, or to check what a particular Instance Admin parameter etc did.

Joel taught me a couple of lessons that day.

1. I learned that he was indeed correct, the thing I’d asked about was already documented, however there was a typo in the documentation.
2. He was a man of his word – as he had the typo fixed within a day of me reporting it back to him (that was back in the days when the help shipped with APEX rather than being online, but hey…it still showed me what type of person Joel was)

Don’t underestimate the power of that question mark icon…it might just hold the answer to your problem or lead you to learn more about Oracle APEX.

Breaking with a bit of a tradition of releasing on a Friday night, last night (Thursday) Oracle APEX 20.1 was released.

Faceted Search Enhancements. Building on the earlier release of faceted search, it has now been extended to include – Conditional Facets, Counts (such as 1k instead of 1000 etc)., Cascading LOVs.

Friendly URLs – this could be a huge one! It’s been on the list for years (ever since HTMLDB / APEX first became available). I’m interested to see just how easy this is to use (particularly for someone like me who has been using the f?p syntax for years).

Native PDF Printing. Primarily for Interactive Grids, so I’m not sure right now if this includes Interactive Reports too.

Mega Menus. Personally I’m not too sure about the naming (Mega Menus sounds a little cheesy). But, they do look pretty good.

As also, head over to https://apex.oracle.com/en/platform/features/whats-new/ to check it out and download APEX 20.1 or use it (for free!) on https://apex.oracle.com

I saw a tweet by Kris Rice with a very cool hint of something that may make it into Oracle SQLcl.

If you’re not familiar with utPLSQL it’s an Open Source framework for PL/SQL developers to define PL/SQL and SQL tests.

In my opinion, anything that makes it easier to run test means we (as Developers) have less reasons to not incorporate testing into our workflow.

Looking forward to this a lot!

Oracle APEX 18.1 has introduced the capability to easily add Features to your APEX application.

So what are Features you might ask? Well in APEX you have always been able to add region types to pages, for example Charts, Reports etc. Think of Features are being pre-built components that span more than just a single region, or page. Current options for features include –

• About Page
• Access Control
• Activity Reporting
• Configuration Options
• Feedback
• Theme Style Selection

I’m sure in future versions of APEX this list will grow, but for now lets take a look how we can incorporate a feature.

Let’s create a brand new application via the Wizard –

Once we select Next > we can see the new Features option

At this stage you can add multiple Options, but lets add the ability for users to modify the Theme Style Selection by themselves.

We can now create the application (as an aside – I love the new dynamic progress bar you see while the application gets created).

Once the application is created, we can login to it and see that an Administration section has been created for us automatically.

You’ll notice the Desktop Theme Style drop-down already contains a number of themes

Changing the Theme and pressing Apply Changes, the new theme takes effect immediately (no need to logout, neat!).

A great feature is that Administrators can enable this feature to end users

Once you do this, then a Customize link will appear at the bottom of the page for End Users.

Clicking this link allows end users to select one of the themes and apply it to the application – note the theme choice is specific to the End User and persists between sessions.

Now, you might say – but John we could already do something like this manually ourselves, but the point here is you didn’t need to do anything manually. Just by selecting an option and not writing a single line of code, you have declaratively added functionality to your application that allows your End Users to customise the application to how they like it – and they will like it!

Pretty much immediately after I read the announcement that the Oracle Exadata Express service was available in Europe I decided to sign up to test it out.

Looking at the 3 options available (Exadata Express – X20, X50 and X50IM), I decided to go for the X20 option – primarily because I was interested how you connect to these instances, rather than.

After looking at the pricing options, I noticed a couple of points that jumped out at me.

Firstly you get 1 PDB, no mention of an option to purchase additional ones (I’m guessing you would need to sign up for a new instance rather than being able to clone an existing PDB for Dev / Test / Prod etc).

Secondly it has APEX already installed, which is obviously great if you want to get up and running with APEX right away.

Ok, so let’s go through the signup process and see how smooth it is…..!

After clicking the “Buy Now” button, I’m redirected to the Oracle Store (which if you didn’t notice is an APEX application!).

Clicking on the X20 option let’s me, choose whether I want to be billed –

• Month-to-Month
• 1 Year
• 2 year
• 3 Year

I must admit, I was slightly confused at this stage what the benefit of going for 1-Year or 2-Year etc versus Month-to-Month was. I didn’t seem to get a discount for going multi-year and in terms of flexibility for the same cost I could sign up month-to-month and cancel whenever I wanted to (please feel free to point out if I’m being dumb here, but I think they could highlight the differences clearer).

Then it’s a simple matter of Adding my choice to the cart, hitting checkout and paying for it (nope I’m not going to show you that bit, too many personal details on that page!).

All in all, I was pretty impressed – not too many clicks to sign up. I do have to say that I still find the Oracle Cloud payment / invoicing aspect slightly disconnected versus say Amazon. In Amazon AWS they already have my payment details – I just launch a new instance and get billed for it. Whereas with Oracle Cloud, I need to go through paying for each new instance before I can launch a new one (so in essence I’m paying to increase my quota for a specific service type). It might sound like a small quirk, but part of the real ‘wow’ factor of Amazon is the immediacy of being able to spin up an instance on demand quickly. Oh well…I’m sure there’s reasons for doing it this way.

So, after I sign up it tells me that I’ll receive an email once my service is available and that I can keep checking on progress via my Orders.

So, I wait…

and wait…

and wait…

About 2 1/2 hours (150 minutes!) later (I lost track of time but it was roughly then), I receive an email –

Note – I’ve omitted the majority of the email since it contains a lot of details on my service URL, CSI etc.

Again, not to gripe too much but 2.5 hours seems WAY too long to wait. As a frequent Presenter at Conferences it would be nice to walk through showing how easy Exadata Express is to setup, but I’m not sure the attendees would wait 2.5 hours for my email confirmation to come through.

Either there’s an element of human interaction going on here (why? Surely all this can be automated), or Oracle is so inundated with people signing up for the service that I ended up at the end of a very long queue. Either way I really do hope this signup time decreases in future or I predict people getting frustrated waiting for the mail to come through.

So that’s it, I now have access to an Exadata Express instance to play with. In the next blog post I’ll go through setting it up and accessing it.

I was very interested to see the announcement by Oracle that the Exadata Express service was now available in Europe

https://blogs.oracle.com/dbaas/exadata-express-cloud-service-now-available-in-europe

I’ve been using the Oracle DBaaS service since it was (more or less) first publicly available and have been very impressed with the general performance and availability,

If you’re not familiar with it, the Exadata Express service comes in 3 flavours,

• Exadata Express – X20
• Exadata Express – X50
• Exadata Express – X50IM

As you would expect, the main differences are around the amount of memory and storage you get, together with some differences in feature availability.

At the time of writing, it breaks down as –

• Exadata Express – X20
  • 20GB Storage
  • 3GB PGA, 3GB SGA
  • 120GB / Month data transfer
• Exadata Express – X50
  • 50GB Storage
  • 5GB PGA, 5GB SGA
  • 300GB / Month data transfer
• Exadata Express – X50IM
  • 50GB Storage
  • 5GB PGA, 10GB SGA (5GB RAM for use with Database In-Memory Column Store)
  • 300GB / Month data transfer

So what about cost (prices correct at time of writing)?

• Exadata Express – X20
  • $175.00 / Month (£141.00 / Month)
• Exadata Express – X50
  • $750.00 / Month (£604.00 / Month)
• Exadata Express – X50IM
  • $950.00 / Month (£765.00 / Month)

To be honest, when I saw these prices – I wondered what the break-point would be for choosing Exadata Express versus spinning up a dedicated DBaaS instance. The X20 looks like a decent price, the X50 and X50IM, well I’m not quite sure yet….I suspect the prices are too high for the average individual user, but the specs aren’t high enough for corporate users (50Gb storage these days isn’t a lot).

So…next steps…I’m going to sign up for the X20 and start kicking the tires!

So, I just saw this tweet which we’ve all been waiting for!

This release has been very eagerly awaited, since it features-

• Interactive Grid
  • Supports Fixed Headers, Frozen Columns, Sorting, Aggregation etc
  • Supports all Item types, including Plugins!
  • Master -> Detail -> Detail -> Detail
• Oracle JET Charts Integration
  • Range of charts, Bar, Line, Area, Range, Radar, Pie etc
  • Declarative
  • HTML5 (no more Flash!)
• Universal Theme Enhancements
  • Per-user Theme preferences
  • Modal Dialog Auto Resize (yay!)
  • Asynchronous Dynamic Actions
  • Live Template Options (this will be a huge time-saver!)
• New and Improved Packaged Applications

I’m downloading this right now and can’t wait to test it out.

This is my contribution to the excellent OTN Appreciation Day idea by Tim

The Flashback (particularly Flashback Query) features of the database have saved my neck many (too many!) times over the years.

For example retrieving the value of the Employees salary as it was 10 minutes ago:

SQL> select sal 
 from emp 
 as of timestamp sysdate - interval '10' minutes 
 where ename = 'SCOTT';

This feature can be used almost everywhere Oracle is used, for example it’s embedded into the Export Application feature of Oracle Application Express

As an extension to this, using the Flashback Data Archive feature allows you maintain an archive  of your application data that you can query in real time – without having to write your own logic.

Flashback – an extremely useful but often little known and underused feature of Oracle!

Oracle have updated the Application Express Early Adopter instance to EA2, you can try it out here – https://apexea.oracle.com/

There are some fairly significant changes and enhancements in EA2 versus the EA1 release, particularly around the Interactive Grid feature (which every APEX developer can’t wait to get their hands on!). For example, the list below shows some very cool additions.

There seem to have been some improvements to the Jet Charts too, this is shaping up to be the best APEX release ever IMHO.

In previous blog posts I’ve covered some of the useful new commands available in SQLcl.

One extremely useful enhancement is the ability to rerun (repeat) the last command, which can be used to monitor changes in another window while you perform work in your main window.

For example, let’s say – as a DBA – I want to monitor how many users are logged into the database.

Firstly lets logging as SYS (yes this is a local database – obviously use a less privileged account in production).

bash-3.2$ ./sql sys@localhost:11521:xe as sysdba

SQLcl: Release 4.1.0 Release Candidate on Tue Jun 17 18:42:12 2016

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Password? (**********?) ******
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

 

SQL>

Now lets run our command –

SQL> select username,
2 count(*)
3 from v$session
4 group by username;

USERNAME COUNT(*)
------------------------------ ----------
                                       22
APEX_PUBLIC_USER                        1 
SYS                                     1

As you can tell, I’m running APEX in my DB (hence the single APEX_PUBLIC_USER session).

Now let’s say we want to continuously run that command, you just use the REPEAT command –

SQL> help repeat
repeat <iterations> <sleep>
Repeats the current sql in the buffer the specified times with sleep intervals
Maximum sleep is 120s

So, let’s say I want to run it 5 times, with an interval of 1 second –

SQL> repeat 5 1

This will run the previous command 5 times (with 1 second between each run), and you should see output similar to –

Running 5 of 5 @ 18:51:11.900 with a delay of 1s

USERNAME      COUNT(*)
                    22
APEX_PUBLIC_USER     1
SYS                  1

I’v e used this many times to monitor APEX instances which I suspect are getting a lot of web requests, it’s also useful to check things like V$SQL during tuning sessions. The ability to be able to re-run a command at a scheduled interval is such a simple feature and yet invaluable (I’ve lost count of the number of times I’ve manually done that with SQLPlus scripts)

One of the new settings in SQLcl is the ability to set different output formats when running queries. You can access this via the set command –

SQL> help set
SET
---

Sets a system variable to alter the SQLcl environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page

SET system_variable value

where system_variable and value represent one of the following clauses:

help set <setting> for more details

APPINFO
ARRAYSIZE
AUTOCOMMIT
AUTOPRINT
AUTORECOVERY
AUTOTRACE

BLOCKTERMINATOR
CLEAR
CMDSEP
COLSEP
CONCAT
COPYCOMMIT
COPYTYPECHECK
DEFINE
ECHO
EDITF[ILE]
EMBEDDED
ENCODING
ESCAPE
ESCCHAR
EXITCOMMIT
FEEDBACK
FLUSH
HEADING
HEADSEP
LDAPCON
LINESIZE
LONG

LONGCHUNKSIZE
NET
NEWPAGE
NOVERWRITE
NULL
NUMFORMAT
NUMWIDTH
PAGESIZE
PAUSE
RECSEPCHAR
SERVEROUTPUT
SHOWMODE
SQLBLANKLINES
SQLCASE
SQLCONTINUE
SQLFORMAT  <------------ Here it is!
SQLPLUSCOMPATIBILITY
SQLPREFIX
SQLPROMPT
SUFFIX
TAB
TERMOUT
TIME
TIMING
TRIMOUT
TRIMSPOOL
VERIFY
WRAP

Viewing the command specific help shows the available output formats

SQL> help set sqlformat
SET SQLFORMAT
SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}

So, let’s try a couple of tests against the ubiquitous emp table, firstly lets query it without making any changes to the SQLFORMAT settings

We can view what the current setting is via the show command

SQL> show sqlformat
SQL Format : Default

Let’s try the JSON format

As you can imagine the CSV format does exactly what you’d expect

You can imagine the previous formats are very useful combined with a SPOOL command to save the data to a file on the filesystem in a particular format.

The most common format I use is ANSICONSOLE (which works very well with a large monitor), which does a great job at auto-sizing the column sizes and always displaying the full column name (instead of a shorten abbreviated name).

As you can see, using SQLFORMAT is an extremely useful way to format your SQL query output

In the last post we covered the basics of connecting to a database with SQLcl.

If you’re familiar with Unix, you’ll be familiar with the history command. SQLcl also has a history command and once you start to use it you’ll wonder why sqlplus didn’t have one added years ago (how many times have you mistyped a select statement?).

Once you’ve connected to a database, typing ‘help’ will list the available commands, and you can see the alias command listed.

The general format of the command is

ALIAS
------

alias [<name>=<SQL statement>;| LOAD [<filename>]|SAVE [<filename>] | LIST [<NAME>] |
DROP <name> | DESC <name> <Description String>]

Typing alias by itself will list any previously defined aliases

SQL> alias
locks
sessions
tables
tables2
SQL>

You can see I have some aliases already defined, but lets create a new one. One thing I commonly do on remote databases is check what the server time(zone) is. I frequently find myself typing the command –

select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual

so, let’s create an alias for that –

SQL> alias time=select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;
SQL>

Now we just need to type ‘time’ and it will run the full command –

SQL> time

TO_CHAR(SYSDATE,'DD
-------------------
16/06/2015 19:43:55

Such a simple thing, but so incredibly useful.

I also often find myself returning to a SQLPlus session and wondering which user I’m logged in as – now with SQLcl I can easily create an alias (called whoami to simulate the Unix command), like this –

SQL> alias whoami=select user from dual;
SQL> whoami

USER
------------------------------
JES

If you want these aliases to persist between database connections, then I can save my current aliases like this –

SQL> alias save aliases.sqlcl
ALIAS-007 - Aliases saved to aliases.sqlcl

and then reload them in a future session

SQL> alias load aliases.sqlcl
Aliases Loaded

it’s worth noticing that if you examine the file, it is stored in XML format –

<?xml version = '1.0' encoding = 'UTF-8'?>
<aliases>
<alias name="whoami">
<description/>
<queries>
<query>
<sql><![CDATA[select user from dual]]></sql>
</query>
</queries>
</alias>
<alias name="sel">
<description/>
<queries>
<query>
<sql><![CDATA[select :one]]></sql>
</query>
</queries>
</alias>
<alias name="time">
<description/>
<queries>
<query>
<sql><![CDATA[select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual]]></sql>
</query>
</queries>
</alias>
</aliases>

I can remove any aliases I want using the drop command –

SQL> alias drop whoami
Alias whoami dropped

The (very) cool thing is that you’re not just limited to SQL aliases, you can alias PL/SQL too.

In the last post I covered downloading and running SQLcl, now let’s try and connect to a database.

I have an Oracle XE database running on my local machine, so lets try and just enter the username and password of a valid schema to connect to that.

bash-3.2$ ./sql

SQLcl: Release 4.1.0 Release Candidate on Tue Jun 14 19:42:12 2016</span>

Copyright (c) 1982,2016, Oracle. All rights reserved.

Username? (''?) jes
Password? (**********?) **********
USER = pre3_prod
URL = jdbc:oracle:thin:@localhost:1521/orcl
Error Message = IO Error: The Network Adapter could not establish the connection

ok, so that didn’t work…

The issue here is that it defaults to localhost and port 1521 (which is where my instance is running), but the service name it defaults to (orcl) isn’t valid.

Ok, so let’s try again this time passing the correct parameters

bash-3.2$ ./sql

SQLcl: Release 4.1.0 Release Candidate on Tue Jun 14 19:42:12 2016
 
Copyright (c) 1982,2016, Oracle. All rights reserved.

Username? (''?) jes@localhost:1521/xe
Password? (**********?) **********
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

Great, success!

Actually SQLcl supports a number of different options when it comes to connection strings, namely –

• jdbc:oracle:thin
• jdbc:oracle:oci8
• jdbc:oracle:kprb
• jdbc:default:connection
• jdbc:oracle:kprb:
• jdbc:default:connection:

So now we’re connected let’s explore the available commands using the help command

SQL> help
For help on a topic type help <topic>
List of Help topics available:
/ @ @@ ACCEPT ALIAS APEX
ARCHIVE_LOG BREAK BRIDGE BTITLE CD CHANGE
COLUMN COMPUTE CONNECT COPY CTAS DDL
DEL DESCRIBE DISCONNECT EDIT EXECUTE EXIT
GET HISTORY HOST INFORMATION INPUT LIST
NET OERR PASSWORD PAUSE PRINT PROMPT
REMARK REPEAT RESERVED_WORDS REST RUN SAVE
SET SHOW SHUTDOWN SODA SPOOL SSHTUNNEL
STARTUP STORE TIMING TNSPING TTITLE UNDEFINE

this hasn’t formatted very well in this code editor, but here’s a screenshot

In the next post, we’ll explore the ALIAS command.

I blogged briefly before here and here about SQLcl and thought I’d write a walkthrough of using it while I explored the various features.

Firstly how do you install it? Well head over to the download page here.

You should get a zipfile (I renamed the file for brevity) –

 
bash-3.2$ ls -al 
total 34120 
drwxr-xr-x 3 jes staff 102 14 Jun 19:09 . 
drwxr-xr-x+ 76 jes staff 2584 14 Jun 19:09 .. 
-rw-r--r--@ 1 jes staff 17466359 14 Jun 19:09 sqlcl.zip 

unzipping this extracts quite a few files

 
bash-3.2$ unzip sqlcl.zip
Archive: sqlcl.zip
 inflating: sqlcl/bin/sql
 inflating: sqlcl/bin/sql.bat
 inflating: sqlcl/bin/sql.exe
 inflating: sqlcl/lib/SQLinForm.jar
 inflating: sqlcl/lib/commons-codec.jar
 inflating: sqlcl/lib/commons-logging.jar
 inflating: sqlcl/lib/httpclient.jar
 inflating: sqlcl/lib/httpcore.jar
 inflating: sqlcl/lib/httpmime.jar
 inflating: sqlcl/lib/jackson-annotations.jar
 inflating: sqlcl/lib/jackson-core.jar
 inflating: sqlcl/lib/jackson-databind.jar
 inflating: sqlcl/lib/javax.json.jar
 inflating: sqlcl/lib/jline.jar
 inflating: sqlcl/lib/jsch.jar
 inflating: sqlcl/lib/ojdbc7.jar
 inflating: sqlcl/lib/oracle.dbtools-common.jar
 inflating: sqlcl/lib/oracle.dbtools.http.jar
 inflating: sqlcl/lib/oracle.dbtools.jdbcrest.jar
 inflating: sqlcl/lib/oracle.sqldeveloper.sqlcl.jar
 inflating: sqlcl/lib/oraclepki.jar
 inflating: sqlcl/lib/orai18n-mapping.jar
 inflating: sqlcl/lib/orai18n-utility.jar
 inflating: sqlcl/lib/orai18n.jar
 inflating: sqlcl/lib/orajsoda.jar
 inflating: sqlcl/lib/osdt_cert.jar
 inflating: sqlcl/lib/osdt_core.jar
 inflating: sqlcl/lib/xdb6.jar
 inflating: sqlcl/lib/xmlparserv2.jar

this should have created a sqlcl sub-folder in the directory you unzipped the file in

 
bash-3.2$ ls -al
total 34120
drwxr-xr-x 4 jes staff 136 14 Jun 19:37 .
drwxr-xr-x+ 76 jes staff 2584 14 Jun 19:09 ..
drwxr-xr-x@ 4 jes staff 136 14 Jun 19:37 sqlcl
-rw-r--r--@ 1 jes staff 17466359 14 Jun 19:09 sqlcl.zip

inside the sqlcl folder you’ll find a bin and lib folder

 
bash-3.2$ cd sqlcl
bash-3.2$ ls -al
total 0
drwxr-xr-x@ 4 jes staff 136 14 Jun 19:37 .
drwxr-xr-x 4 jes staff 136 14 Jun 19:37 ..
drwxr-xr-x@ 5 jes staff 170 14 Jun 19:37 bin
drwxr-xr-x@ 28 jes staff 952 14 Jun 19:37 lib

in the bin directory you will find the sql command

 
bash-3.2$ cd bin
bash-3.2$ ls -al
total 840
drwxr-xr-x@ 5 jes staff 170 14 Jun 19:37 .
drwxr-xr-x@ 4 jes staff 136 14 Jun 19:37 ..
-rwxr-xr-x@ 1 jes staff 13781 14 Jun 19:37 sql
-rwxr-xr-x@ 1 jes staff 2585 14 Jun 19:37 sql.bat
-rwxr-xr-x@ 1 jes staff 406507 14 Jun 19:37 sql.exe

you’ll notice there’s a sql command, a sql.bat and a sql.exe file (since this is a single download regardless of operating system).

Since I’m on a Mac I can just invoke the sql command

 
bash-3.2$ ./sql

SQLcl: Release 4.1.0 Release Candidate on Tue Jun 14 19:42:12 2016</span>

Copyright (c) 1982,2016, Oracle. All rights reserved.

Username? (''?)

and we’re good to go! in the next post I’ll connect to a database and explore some of the commands.

Oracle SQLcl contains some very useful built in commands that APEX developers might find useful.

For example, lets say I’m connected to a schema and want to know what APEX applications are installed in the workspace linked to that schema, I can simply issue the APEX command and it will list the APEX applications, for example:

SQL> apex
WORKSPACE APPLICATION_ID APPLICATION_NAME BUILD_STATUS LAST_UPDATED_ON
JES_DEMO 105 LDAP Demo Run and Develop 12-JUN-16
JES_DEMO 280 Amazing jQuery Run and Develop 12-JUN-16

Let’s say you want to export an application from the command line, we can use the APEX EXPORT command like this:

SQL> spool f105.sql
SQL> apex export 105

...application spools out

Now I can do cool things like automate exports from my application using cron, how cool is that?

I see a lot of potential in SQLCl and it keeps improving with each release, go and check it out!

After a long (but I’m sure well worth it) wait, Oracle just announced APEX 5.1 Early Adopter is available.

Head over to https://apexea.oracle.com/ to sign up and request a Workspace.

The two big items everyone has been waiting for are

• Interactive Grids
• Jet Charts

but reading through the “Additional Features in Application Express 5.1” section on the EA homepage it looks like there are lots of other interesting improvements and enhancements.

In a previous post I posted about a Java Heap memory issue we were having with Glassfish.

One of the ways we tracked down the cause of the issue and by how much to increase the memory by was to use the asadmin command to generate a memory report – which is a really useful way to see a snapshot of how your Glassfish instance is consuming memory.

You can run a command like

./asadmin generate-jvm-report --type memory

which should give output similar to

[oracle@ahi-dhh-prod bin]$ ./asadmin generate-jvm-report --type memory
Enter admin user name> admin
Enter admin password for user "admin">
The uptime of Java Virtual Machine: 47 Hours 2 Minutes 44 Seconds
Memory Pool Name: PS Eden Space
Memory that Java Virtual Machine initially requested to the Operating System: 122,683,392 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 239,599,616 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 264,241,152 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 45,212,424 Bytes

Memory Pool Name: PS Survivor Space
Memory that Java Virtual Machine initially requested to the Operating System: 20,447,232 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 2,097,152 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 2,097,152 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 312,976 Bytes

Memory Pool Name: Code Cache
Memory that Java Virtual Machine initially requested to the Operating System: 2,555,904 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 17,956,864 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 50,331,648 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 17,726,208 Bytes

Memory Pool Name: PS Perm Gen
Memory that Java Virtual Machine initially requested to the Operating System: 67,108,864 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 99,614,720 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 201,326,592 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 99,278,352 Bytes

Memory Pool Name: PS Old Gen
Memory that Java Virtual Machine initially requested to the Operating System: 326,631,424 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 441,974,784 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 536,870,912 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 372,432,888 Bytes


Name of the Garbage Collector: PS MarkSweep
Number of collections occurred using this garbage collector: 244 Bytes
Garbage Collection Time: 37 Seconds 768 Milliseconds
Name of the Garbage Collector: PS Scavenge
Number of collections occurred using this garbage collector: 54,911 Bytes
Garbage Collection Time: 330 Seconds 761 Milliseconds

Heap Memory Usage:
Memory that Java Virtual Machine initially requested to the Operating System: 489,924,096 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 683,671,552 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 716,177,408 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 418,149,376 Bytes

Non-heap Memory Usage:
Memory that Java Virtual Machine initially requested to the Operating System: 69,664,768 Bytes
Memory that Java Virtual Machine is guaranteed to receive from the Operating System: 117,571,584 Bytes
Maximum Memory that Java Virtual Machine may get from the Operating System: 251,658,240 Bytes. Note that this is not guaranteed.
Memory that Java Virtual Machine uses at this time: 117,004,560 Bytes

Approximate number of objects for which finalization is pending: 0


Command generate-jvm-report executed successfully.

As you can see there’s a lot of output here, but I’ve highlighted the section which shows how much Heap Memory we’re currently using and what the maximums are set to. By regularly monitoring these values we were able to hone in on how much memory was the optimal amount to allocate to the Glassfish instance.

I recently encountered some issues with our Oracle Cloud Glassfish server encountering out of memory issues.

This manifested itself by Glassfish becoming unrepsonsive and eventually crashing, digging into the logfile we found entries like this

server.log_2016-04-08T18-32-27:[#|2016-04-04T18:21:56.472+0000|SEVERE|oracle-glassfish3.1.2|null|_ThreadID=74;_ThreadName=Thread-2;|Java heap space
server.log_2016-04-08T18-32-27:java.lang.OutOfMemoryError: Java heap space

After a lot of Googling, I found that you can find out the current Java settings that are being used by Glassfish by running the list-jvm-options command, like this:

[oracle@prod bin]$ ./asadmin list-jvm-options
Enter admin user name> admin
Enter admin password for user "admin">
-XX:MaxPermSize=192m
-XX:PermSize=64m
-client
-Djava.awt.headless=true
-Djavax.management.builder.initial=com.sun.enterprise.v3.admin.AppServerMBeanServerBuilder
-XX: UnlockDiagnosticVMOptions
-Djava.endorsed.dirs=${com.sun.aas.installRoot}/modules/endorsed${path.separator}${com.sun.aas.installRoot}/lib/endorsed
-Djava.security.policy=${com.sun.aas.instanceRoot}/config/server.policy
-Djava.security.auth.login.config=${com.sun.aas.instanceRoot}/config/login.conf
-Dcom.sun.enterprise.security.httpsOutboundKeyAlias=s1as
-Djavax.net.ssl.keyStore=${com.sun.aas.instanceRoot}/config/keystore.jks
-Djavax.net.ssl.trustStore=${com.sun.aas.instanceRoot}/config/cacerts.jks
-Djava.ext.dirs=${com.sun.aas.javaRoot}/lib/ext${path.separator}${com.sun.aas.javaRoot}/jre/lib/ext${path.separator}${com.sun.aas.instanceRoot}/lib/ext
-Djdbc.drivers=org.apache.derby.jdbc.ClientDriver
-DANTLR_USE_DIRECT_CLASS_LOADING=true
-Dcom.sun.enterprise.config.config_environment_factory_class=com.sun.enterprise.config.serverbeans.AppserverConfigEnvironmentFactory
-Dosgi.shell.telnet.port=6666
-Dosgi.shell.telnet.maxconn=1
-Dosgi.shell.telnet.ip=127.0.0.1
-Dgosh.args=--nointeractive
-Dfelix.fileinstall.dir=${com.sun.aas.installRoot}/modules/autostart/
-Dfelix.fileinstall.poll=5000
-Dfelix.fileinstall.log.level=2
-Dfelix.fileinstall.bundles.new.start=true
-Dfelix.fileinstall.bundles.startTransient=true
-Dfelix.fileinstall.disableConfigSave=false
-XX:NewRatio=2
-Xmx128m
Command list-jvm-options executed successfully.

You can see a lot of info here, the Heap memory parameter is the Xmx one, which we can adjust by deleting the current setting:

./asadmin delete-jvm-options --target server-config -- '-Xmx128m'

and then assigning a new value

./asadmin delete-jvm-options --target server-config -- '-Xmx256m'

Then we restarted the Glassfish server and haven’t seen the issue occur since.
It’s important to not just blindly choose a value here, you need to understand why you’re running out of heap memory and not just increase it for the sake of it (but that’s a post for another day).

Oracle Cloud DBaaS provides a lightweight version of Enterprise Manager called Enterprise Manager Express which is enabled by default (just not perhaps enabled ‘fully’, as you’ll see).

You can check if EM Express is up and running by checking the Listener Status on your DBaaS instance.

[oracle@DEMO ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 31-MAR-2016 14:27:54

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 30-MAR-2016 23:52:34
Uptime 1 days 14 hr. 35 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DEMO/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/ORCL/xdb_wa
llet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCL.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "pdb1.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

notice the item running on Port 5500 here, that’s the EM Express instance running in the Container DB (CDB) of my Multi-tenant 12c database.

So you could either open up Port 5500 via the network rules or use an SSH tunnel, which I’ll do using a command like this:

[jes@mac oracle-cloud]$ ssh -L 5500:localhost:5500 -i oracle_cloud_rsa opc@129.191.3.59
[opc@DEMO ~]$

Now with my SSH tunnel in place I can navigate to https://localhost:5500/em (it’s important to remember the /em on the end since unfortunately it won’t redirect by default).

You should now see the EM Express login page

Enterprise Manager Express – Login Page

You can login using ‘sys’ or ‘system’ together with the password you specified during the DBaaS setup.

Enterprise Manager Express – Landing Page

Whilst it doesn’t offer the full functionality of Enterprise Manager, EM Express does ways to perform the most common DBA type tasks such as creating tablespaces, creating users etc.

Depending on the Database Edition you opted for, there is even a pretty nifty Performance Hub where you can review performance issues and drill down to find out the cause of issues.

Enterprise Manager Express – Performance Hub

So is this a replacement for Enterprise Manager (or Enterprise Manager Cloud Control)? No, absolutely not – there are many pieces of functionality available in EM that aren’t implemented in EM Express. For example of glaring omission I found was there was no way to query Scheduler Jobs (to create, delete or query if they’d executed for example).

One thing I did find confusing at first was that I tried to create a tablespace. However I couldn’t find the option! You’d expect it to be under the ‘Storage’ tab, but it wasn’t there.

The penny finally dropped when I realised there was no way to drill down into the PDB information either. The EM Express instance running on port 5500 really only shows information about the CDB, whereas to create a tablespace you would create it for (or within) a PDB.

Therefore you need to enable EM Express for the PDB you want to create a tablespace for. To do that you need to login to the PDB and enable it, like this (you can find more information on this here).

[oracle@DEMO ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 13 14:47:44 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: system@pdb1
Enter password:
Last Successful login time: Fri May 13 2016 14:25:33 +00:00

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exec dbms_xdb_config.sethttpsport(5501);

PL/SQL procedure successfully completed.

SQL>

the important bit here is the call to dbms_xdb_config.sethttpsport where I pass in the port number I want to enable it on (you need to ensure nothing is running on that port already). By convention I like to increment the port, e.g. 5500, 5501, 5502 etc but really it’s an arbitrary choice.

Once you’ve done that you should be able to launch an SSH tunnel and navigate to the Enterprise Manager Express instance running just for that PDB.

Enterprise Manager Express – PDB View

You can see we now have a PDB-centric Enterprise Manager Express view, and we have the tablespace menu item to create tablespaces within this PDB.

I could see this becoming a little tedious / confusing if you have a lot of PDB’s to manage since you have the overhead of enabling them all (and using multiple ports) and also having to logout and login to different ones each time you wanted to perform some maintenance. On the other hand it’s a nice way to tie down access and restrict users to being able to administer a specific PDB.

I’m hoping Oracle enhances EM Express in the future to plug some of the gaps (e.g. scheduler jobs), but in the meantime at least you have something out of the box when you create a DBaaS instance.