OTN Appreciation Day: PL/SQL

We are posting these blog posts today as part of the OTN Appreciation Day, a celebration for the Oracle Technology Network as suggested by Tim Hall, inspired by Debra Lilley.

The mission was not too hard: write about your favorite bit of Oracle Technology.

As a developer and a core-tech DBA and APEX enthusiast… the choice was easy! PL/SQL (apart from how it is pronounced or even written ;-)!

Why? Easy!

Not just because PL/SQL is easy to learn, for a language that is basically so extremely powerfull! But foremost because it is an easy choice.
If you have a database like the Oracle database and you have your valuable data living inside that database, you want to maximize the potential value of that data. Use and manipulate it quickly, keep it safe, make it available in a sensible way. For that you use PL/SQL.
You create a safe haven for the data, making sure it can only be manipulated in a safe and pre-defined way by exposing data retrieval and manipulation through PL/SQL packages and business logic, creating an API-interface, controlling the access to one of your most valuable assets.

One of the master showmanship features of PL/SQL is Oracle Application Express! Based entirely on PL/SQL, showing how powerful it is to incorporate modern web-technology on top of data-centric, data-driven application development.
With this technology, I see very little need to create utterly complex multi layered (mind you, I explicitly did not say multi tiered!) software stack approach, to create agile and modern applications. Add Edition Based Redifinition, and you have the perfect CI/CD* Agile Rapid Web Application Development environment everybody dreams of! Please, for once accept this simple truth 😉

So, my choice for this celebration: Please embrace PL/SQL and make your world a better place.

Long live the Oracle Technology Network!! Hurray!!

* Continuous Integration / Continuous Delivery


#OOW16, San Francisco, looking back

In this post I just wanted to highlight a few things that have lingered with me since the 2016 Oracle Open World Experience.

Persistent DRAM
Now, here, being at home, I must admit that I cannot find very much documentation about this, but it got me thinking… A little paradigm-shift, where computers actually wouldn’t need moving parts anymore (ie. disks of any kind). Create devices that use these memory structures, quite possibly combined with flash-disks, to run entirely on RAM. The 3D XPoint Technology could be a nice example of this. I think I would applaud such machines.
I know, not a real export point I am making here, but if anyone has a better angle, I would love to read your comments.

Thick Database
This is a much better documented topic, much more tangible too.
Toon Koppelaars started this “new” approach with his talk at OTW16. You can review his presentation here and see the video’s of the presentation here and here.
I guess some really good points there. The creation of an application is a craft. You need to get the right materials and do a number of steps to get a solid foundation. Meaning you have to create a solid data-model (yes, even in the world of BigData, schema on write, etc.) most applications still rely on a data model and all that we were taught to go with that. Not much sense in repeating what’s in the presentation here though.
An eye opener and something to (re)consider!! I plan to talk about this a bit more later.

EBR & Oren
One of the best sessions I visited during OOW16 was the presentation by Oren Nakdimon accompanied by the illustrious Bryn Llewellyn.
The presentation discussed a true implementation of CI/CD using some of the capabilities of the Thick database paradigm as discussed above, combined with the possibilites that Edition Based Redefinition brings.
Using these technologies, Oren has been able to implement a rolling upgrade scenario for Moovit. I find this impressive.

Philippe Fierens & SPARC
I had the honor and pleasure of working closely together with my good friend Philippe Fierens during this edition of OOW. It always adds a dimension if you are able to tackle some of the challenges of the week as a team! Thank you Philippe.
Though Philippe I am also affiliated to the continuing efforts to build and maintain the Oracle SPARC architecture of which he is a strong advocate. Be sure to follow his blog to learn about the latest developments in this area.

Panel discussions on the last day
Saving the best for last… Literally!
On the last days there we some panel discussion regarding SQL / PLSQL and application architecture. I found these discussions to be quite meaningful and the interaction with the attendees was grand. Having people like Chris Saxon, Connor McDonald, Toon Koppelaars and Carry Millsap on a panel, there is no way you can go wrong!

OTN & a bow
Finally, looking back at this OOW, it was actually the first one I visited as a member of the OTN Oracle ACE community.
Boy, does that make a difference in how you experience Oracle Open World!!
Of course, you can chill and relax at the OTN Lounge, learn a lot of different things, spot Oracle Hero’s as the wander by if you are a “regular” visitor to OOW. And, by all means, I recommend you do as it is extremely valuable.
But the difference this time was that I really belonged there.
A very big thank you to Jennifer for the hard work you put into making all of this possible!
And, please, support Girls who Code, the initiative OTN sponsorred this year by tweeting a selfie with the hashtag #girlswhocode and the appropriate sticker!!

#doag2016 my picks and suggestions

As many conferences evolve over the years, the number of sessions on offer can easily be overwhelming. I have overheard many conference attendees wrestling with their choices for what to see and which sessions to attend.

For DOAG 2016 I have a short overview with my picks and with one or two tip-sessions. I hope this helps, though it is just my personal preference of course…

Please note that this post is based on the printed version of the conference planner and this may obviously be subject to change. Find the on-line version of the conference planner here!

Tuesday, November 15th
Connecting Oracle & Hadoop by Tanel Poder
Structuring an APEX Application by Alex Nuyten
Meet your match: Advanced Row Pattern Matching by Stew Ashton

How to identify the Right Workload for Database In-Memory by Andy Rivenes
Die Schlechten ins Kröpfchen – SQL analyse für DBAs by Martin Klier

Was die IT von der Luftfahrt lernen kann by Uwe Küchler
Using image copies for Oracle Database Backup by Ilmar Kerm
Using SQL Transaction Framework to rewrite Bad SQL on the fly by Kerry Osborne

Plötzlich Multitennant – was ändert sich für den DBA by Uwe Hesse
Oracle VM auf Exadata – Erfahrungen aus der Praxis by Christian Pfundtner
Einsatz von Maps in APEX by Denis Kubicek

Goto-session (TOP-tip)
–> Session got cancelled, but will be at UKOUG!
Patch you application with No Downtime (& No extra Costs!) by Oren Nakdimon
Hacking Oracle’s memory – About Internals & Troubleshooting by Stefan Koehler

XML in der Oracle DB by Wolfgang Nast
PL/SQL Performance – Best practices für Laufzeitoptimierung by Jan Gorkow

The Oracle Optimizer – Upgrading Without Pain by Nigel Bayliss
Erfahrung nach einem Jahr Fusion Middleware 12c by Jan-Peter Timmerman

Active Session History: Advanced Analytics by David Kurtz
MySQL for Oracle DBAs by Philipp Michaly
Deploying PL/SQL Applications, Building Rome in a Day by Alan Arentsen

Wednesday, November 16th.
Logical Replication in 12cR2 – What are the options now? by Vit Špinka
Function madness: Use and Abuse of PL/SQL Functions by Piet de Visser

Ensuring your Physical Standby is Usable by Michael Abbey
RMAN – From Beginner to Advanced by Marcin Przepiorowski

Oracle Secure Backup – eine Livedemo by Sven-Olaf Hilmer
Oracle Hacking Session by Kamil Stawiarski
Advanced Interactive Grids by Patrick Wolf

The Battle: Linux vs. Windows by Dierk Lenz, Johannes Ahrends and Martin Klier
Adaptive Features or: How I Learned to Stop Worrying… by Ludovico Caldara
Controlling Execution Plans – Workshop by Kerry Osborne

Application Express für den DBA? Geht das? by Joel Kallman
Und Sie bewegt sich doch by Lothar Flatz
APEX Desktop Apps – Interaktion mit dem Client System by Daniel Hochleitner

Hash Joins and Bloom Filters by Toon Koppelaars
Ansible für Oracle DBAs by Alexander Hofstetter

Delivering Continuous Availability for Database Services by Michael Timpanaro-Perrotta
Dbvisit – Oder doch lieber Data Guard by Andreas Kother
Chase the Optimizer Every Step of the Way by Mauro Pagano

Top 7 Plan Stability Pitfalls & How to Avoid Them by Neil Chandler
Advanced RAC Programming Features by Martin Bach
Weblogic 101 for DBA by Osama Mustafa

Bad Boys of Replication – Changing Everything by Björn Rost and yours truly

Oracle System Statistics by Paul Matuszyk
Compression – Technik und sinnvolle Umsetzung by Klaus Reimers
Node.js der Alleskönner by Kai Donato

Thursday, November 17th.
FAQ about Masking Sensitive Data in Oracle Database by Maja Veselica
Data Guard in Oracle 12.2 – Crash Course by Zoran Pavlovic

Mining the AWR v2: Trend Analysis by Maris Elsins
Regular Expressions: Say What? by Alex Nuyten

Databases Clone Using ACFS by David Hueber
R.I.P. Oracle Database by Markus Lohn

Writing Efficient SQL Statements by Joze Senegacnik
Validate User Input in APEX by Richard Martens

Backup und Recovery PoC auf der Recovery Appliance by Frank Schneede
Ready, Steady, GIT: Einführung eines Versionskontrollsystems by Carolin Hagemann

Warum sollte man die Multitennant Database Option Verwenden by Johannes Ahrends
Collections in PL/SQL by Frank Haney

Saving Lives at Sea – At an Industrial Scale Using Oracle Cloud Technology by Oliver Limberg and yours truly
Part 1: The NoPL/SQL and Thick Database Paradigms by Toon Koppelaars and Bryn Llewellyn

Part 2: The NoPL/SQL and Thick Database Paradigms by Bryn Llewellyn and Toon Koppelaars

Do not forget…
The first ever APEX Hack’a’thlon is going down on Friday the 18th of November at the DOAG Education day. If you are interested or just want more information, don’t hesitate to drop a line.


#OOW16, San Francisco

This year, 2016, is turning out to be an amazing year again, with #OOW16 being once again on of the apices!

Looking back

After the discovery of the Oracle community in 2012, as a result of a very first trip to downtown San Francisco in 2010 for #OOW10, an amazing chain of events was set in motion. This very first introduction in the Oracle World was as ‘a mere participant’ in this awe-inspring, large than life event.

Over these past few years I have met so many people, made so many new friends around the globe… This all literally changed my work, my life; basically everything changed.

After visiting Oracle Open World for the first time, I had the opportunity to work with Arjen Visser and the team of Dbvisit on building a strong brand for this amazing company in Europe. This also brought me back to San Francisco in 2014.
And boy, things have changed!
Not only was it a coming back, it was a fest of friendship, with so many people to meet, either brand new or in a chance to catchup once again. It was also the first time I had the opportunity to participate & share. With #RepAttack I had the opportunity to share knowledge about logical replication and the many benefits it holds for making the most out of your data.
Did I mention the utterly amazing fact of getting not only accepted by the Oracle Community, but also recognized, together with my dear friend from Belgium, Mr. Philippe Fierens, as a genuine Oracle ACE?

A new step

This edition of Oracle Open World, OOW16, again adds a brand new dimension to the visit to San Francisco!
Not only will it be as the Director Operations of Portrix Systems, supporting the Annual Swim in the bay event in cooperation with Oraclenerd Chet Justice, it will be as a selected speaker too. An opportunity I would have never anticipated to be possible.

Speaking-OOWWhen Your Database Server Crashes

I will be discussing the various aspects around the protection of data and how you can justify various investments to accomplish this.

Sunday, Sep 18, 10:30 a.m. – 11:15 a.m. | Moscone South—306

I cannot start tp imagine what the impact of this years trip shall be, I do know that I am looking forward to meeting many of you again. This year too, the OTN Lounge will be the base camp for the travels through the Open World landscape. Don’t hesitate to stop by and say hi!!

See you in San Francisco for #OOW16

SYSAUX LOB segment for auditing bug not released in Standard Edition

Last week we were struck by an issue, which turned out to be a bite from a bug!
SYSAUX table-space had quickly filled up to the “my data-file is full”-limit, which in the end was fixed by adding a data-file.

Strange thing though, that for a very small footprint database, we now have a very big SYSAUX table-space.

Some investigation brought me to the Unified Auditing being standard active in database 12c (you can read up on that background with my friend Ann Sjökvist here).
We are faced though with a different (and possibly a little more obscure) Bug 20077418 – RECLAIMING THE SECUREFILE LOB SEGEMENT IN 12.1 Standard Edition.
What this bug boils down to is the following:
There is a lot of audit data recorded by default, the ORA_SECURECONFIG profile is running out of the box. I haven’t taken the time to figure exactly out what is written, where and how, but I know it involves a LOB segment (SYS_LOB0000091833C00014$ by SYSAUD) which is, in our case in comparison to the total database size, HUGHE!! The management of this audit data, usually driven by DBMS_AUDIT_MGMT, has absolutely no effect on this segment (at least not on shrinking it).

Searching for the mentioned bug you just find to EE bugs (18109788 & 22272580) but they at least they give _some_ clues… The actual bug is undisclosed and in status 11 (being worked on).
In the end it means that auditing is fine, even in SE, but, for the moment, restrain yourself… The data you gather cannot be managed (yet). And for the rest:

select policy_name
from audit_unified_enabled_policies

yields any results, consider switching this auditing off (eg.SQL> noaudit policy ORA_SECURECONFIG;)

Hope this helps…

#DBADev (Ops), who knows what is going on…

I have been considering writing this article for quite some time now.
APEX Connect 2016 in Germany’s capital Berlin and the DOAG Database days have finally persuaded me to talk more about #DBADev, let me explain why…

Whenever in the stone age…

During my career as DBA, I was always working closely together with Oracle Forms & Reports developers. In retrospect, the cooperation in that time was remarkable.
These Forms & Reports developers had always been used to working on a host-based platform.

For those of you who actually remember Oracle Forms & Reports and wonder…
Was there ever Forms & Reports host based?
Yes there was, but it is creepily long ago!!

Because of the nature of Forms & Reports, there always was a lot of consideration about where to place application code. This especially became true when PL/SQL was introduced and the migration to Oracle Forms & Reports 6.5 came about.
This brought the transition to client/server based computing and introduced physical distance between the database and the “front-end”.
Front-end between quotation marks, as in today’s world we don’t actually know “front-end” anymore in this same qualification. The “Frond-end” was always more elegantly and fittingly described as a “fat-client”, because of the sheer size of the software and utilities that were required on the end-users workstation.

The physical separation and distance between the presentation entity and the data manipulation engine required and inspired a lot of thought and debate on where the bulk of data processing had to be done.
You can imagine the impact of having a specific data manipulation done inside an Oracle Form that lived on a desktop on the other end of the network. Especially when the required data set is large. Having 1,000 records being fetched, where 2 where manipulated and then send back in bulk, repeated 100 times, 4 times a minute on a 10 Mbps network. OK, clear, that needs to be done smarter.
The solution: work with small data sets and do database side manipulation to limit client/server communication. And actually, that worked quite well!!

All good and fine… But how does this tie in to #DBADev? This already sounds so harmonious. And how could APEX Connect 2016 have inspired this article?

Well… Let’s see

Later on, I found that this cooperation appeared to be not so normal.
If you step out of the world of client-server computing and move on to “todays world”, that started more or less in the nineties with web based computing – or cloud-computing “avant la lettre“ or “my stuff on your computer” or however you describe it – you find a world that consists of “strange things”.
I find these things “strange things” because I believe they are suboptimal, and luckily I find myself not alone in this corner.
Suboptimal in a way that data manipulation solutions (lets call them applications for now) should be considered to be database agnostic. This independence dictates that you use the database as just a data store or even more accurately, as a persistency store. Blane Carter 2 minute TechTip

In another scenario these applications are designed and build by developers who are very good at creating intuitive and sharp looking user interfaces. Unfortunately often with a lesser developed understanding of the mechanics involved in dishing up and serving data to this newly established middle tier.

With the continuing professionalization of IT over the past 20 years, we have seen the creation of a wide variety of disciplines. These range from those who think about IT (architects, managers, designers) to those who build IT (programmers, engineers) to those who run IT (system administrators, operators) and the majority of these disciplines today are self-contained groups of professionals and specialists who excel at their own game. Basically that is good as the profession is wide and complex enough to support this.
The problem is that there is no longer anyone who has the whole picture.

Bring it on / together!

apex-logoAPEX Connect 2016, to me personally, was the first time I really saw #DBADev in practice. With the following two examples I want to illustrate my inspiration.

The first talk of this genre was @alexnuijten with his confessions, and subsequent smart tips and best practices in “Structuring an APEX application”.
As a pure database developer like Alex, you are automatically more prone to thinking about “DBA-stuff”. A lot of these best practices, although they are very database centric, like using a view for each application screen, are obviously primarily there to help the developer. And, don’t get me wrong, that is a very good thing! Alex inspires to try and combine the best of both worlds, which helps getting the most out of your application, your database, and therewith frankly, out of your total investment.

The second example was the information-packed presentation by Dietmar Aust @daust_de, called “Oracle APEX Scripting – die Kommandozeile ist Dein Freund“ (the command line is your friend).
Much more than “just about developing”, this presentation bridged gaps in more than one way. Perhaps it is even #DBADevOps if you think about it.

The recent DOAG Database days held a few additional surprises with the presence of @cczarski and @nielsdb. A very will pitched presentation by Bruno Cirone really sparked the growing interest in the topic!!

It is funny how an idea that was initiated some 18 months ago, conceived together with Sabine Heimsath @flederbine has grown and evolved out of natural demand. For me, this is one other aspect of the industry, where APEX is setting new frontiers.
With a growing awareness and more people recognizing the gap, the deficits it is bringing and the benefits cooperation brings, I have good hopes.

APEX is not only the technology that enables you to create web-based apps super quickly, it is also the technology that brings developers and DBA’s truly closer to each other, ensuring a maximum bang for the buck when it comes to utilizing your database infrastructure investments!
I am not saying we are there, but this is definitely a first step in the right direction!


Username & password required at Weblogic domain startup

When installing a new WebLogic Domain for any a-specific Oracle (Fusion) Middleware application or any other implementation requiring a WebLogic domain like ORDS for instance, a new ‘home’ is created under [MW-home]/user_domains/. [MW-Home] translates, for instance, to /u01/oracle/product/Middleware.

ordsTo start your brand-new domain, or perhaps and rather, to automate the startup of your domain, you would use the supplied [MW-home]/user_domains/[DomainName]/startWebLogic.sh command-file.
This file will start the Weblogic domain (the Admin Server) and the deployed components. After this start, you will be able to follow through with the administration over the web-console. Typically its URL is: http://[ServerName]:[PortNumber]/console.

One nasty thing you can run into, is that starting the server can require you to enter username and password during the run of [MW-home]/user_domains/[DomainName]/startWebLogic.sh. Of course this is rather annoying because it requires interaction which is not good for auto-start. Regular input-tooling you can wrap around this command-file, for example with input redirection, would require you to save your username / password combination in plain text. That is certainly never a good idea!!

Luckily there is a trick to enable your WebLogic domain to start without this interaction. And it also makes sure that username & password are not stored in plain text. Actually it is quite easy to get this facility in place.

This is how:

Go to [MW-home]/user_domains/[DomainName]/Servers/AdminServer/security and create a plain text file called boot.properties.

This file gets two lines:
username: Your WebLogic Username
password: Your WebLogic Password

Basically, this is now a plain-text recording of the username and password on the server, which seems quite scary.

Good thing though, is that when you have successfully run [MW-home]/user_domains/[DomainName]/startWebLogic.sh command file, which will now continuously run through, username and password will be encrypted:

#Thu Mar 10 14:11:38 UTC 2016

You will now be able to call [MW-home]/user_domains/[DomainName]/startWebLogic.sh from your startup-script without having to worry about the need to interactively entering username / password or have to worry about plain text storage of these to artifacts.

Hope this helps!


The importance of meetup.com

The Oracle community convenes at the various events, SIG-meetings and gatherings that are organized by the national Oracle User Group organizations. This is, for my reckoning, one if the most important parts of the power of the Oracle user community.

During these events, local Oracle stars are joined by the travelling Rock Stars of the Oracle Tech community, together they share knowledge and experience to teach and learn about the tricks of the trace. As said many times before, by people much greater than me, this is truly a unique and powerful way to nurture and grow the combined knowledge about Oracle products and the best ways to use them. My favorite quote remains that of Monty Laitiolais: “This truly is a celebration of Tech!” where he obviously was referring to the yearly KScope happening, but which easily translates to many of the Oracle events around the globe.

Since quite a while now, the phenomenon meetup.com has emerged. It is an on-line place where people can initiate meet ups of like-minded people. Either being a small initiative with just a few people in a cafe up to bigger, or perhaps more commercially colored, happenings. Whatever the subject or idea, from travel to innovation and from hobby to profession, you can find a meet up to suit your needs.

Even though the richness and the broadness of the activities of the Oracle User Group organizations worldwide, recently I have seen more and more activities of Oracle aficionados on Meetup.com.
As far as I recall, the ever-vibrant APEX community started organizing these kinds of events under the flag of APEX-meet ups, using this platform. Gathering to share best practices and share experiences about APEX and all the various bit and pieces that adhere to this technology.
If you would look at the following list you would conclude that it even has quite a big list of meetups there… The adoption of meetup.com by the Oracle community is growing rapidly.

Is this a bad thing for the ‘regular’ Oracle user community?

I think not.

From my experiences participating in both “regular” Oracle user group events as well as in Oracle-related meet ups, I think they have a complimentary function.
The traditional user group events are usually more “speaker – audience” oriented, which is a very good format for educating and teaching. A format that is indispensable because it enables a larger group of people to gain knowledge and understanding quickly and effectively.
The meetups have a, let’s call it, more informal character, one where the interchange of information and knowledge is more of a group event. And, let’s face it, the social aspect of meetups is also a little more on the foreground, which in itself is a good thing too.
The need for this kind of contact already was there in the form of SIG’s. During the traditional events, with the emerging of the round-table phenomenon, this has been even more obvious.

Conclusive I would like to state: Let’s embrace meet ups. Go find or organize a meet up – preferably about our much-loved Oracle technology – in your neighborhood. Find and inspire people, share, learn and laugh! It is worth your time, I can tell from experience.

bad gui

Why GUI sucks…

Of course we all know GUI stands for Graphical User Interface, just as CLI stands for Command Line Interface, right!
Or, rather, a GUI is this nice, flashy screen where you can easily roam with your mouse, comparable to a multiple choice quiz, where the right answer is there for the picking.
A CLI on the other hand is this dark, mysterious blinking cursor… Nothing happens unless you know more or less what you are doing. Comparable to an open questions quiz.

Sparked by a recent Twitter discussion, I decided I should probably write the umpth blog post about this to make my contribution to this lasting dispute.

This post discusses GUI in relation to system administration, not necessarily in relation to data-entry or data manipulation applications that are used in front offices all over the world. I guess CLI has no place in a world like that…

bad gui

Why GUI sucks?
I have done my fair share of installing, scripting, ad-hoc fiddling, testing and trying. And, I have found myself in the situation where I worked with younger computer geeks or even in situations where nobody had the time to figure anything out – stuff just had to be made to work.

Probably in the few lines above, we could already have the basics for this discussion!

But, why then does GUI suck?

GIU’s suck because they are limiting, labor (or rather RSI) intense and require you, the operator, to be there, physically clicking away on your computer.

They are limiting, or at least most of the time they are, because it is often quite hard to get a visual representation of each and every function of a device / program / system etc. If you consider, for instance, a networking device and then try to imagine having to create a GUI that lets the operator configure and define each and every parameter of a specific VLAN or VPN. And then also bear in mind that the GUI has to stay crisp, clean and intuitive.
For this reason, I have seen many vendors who have created a GUI for basic setup only, relying on the professionals to find their way in the CLI. They GUI can then stay intuitive enough to at least get the basics done.

GUI’s that aim not to be limiting, of which there also are a few out there too, need to sacrifice a lot of the things that a good GUI should stand for:

  • Short click paths (3 clicks from anywhere to get where you want to be)
  • Intuitive (don’t have to guess or read a manual to use a GUI)

So, what you end up with, then, is a maze of riddles, where you can easily spend a good day setting up some new functionality. Somehow I believe this is not what the designers had set out for nor is it a valid solution for most tasks at hand.

Labor intense
I personally find GUI’s often, quite labor intense. Not just for the absence of the ability to automate tasks, though. Especially if there is a lot of specific configuration that needs to be done, you often end up left and right clicking until your hands start hurting.
And, in the end, you always end up with the eerie feeling that you missed out on that one specific setting that would really put the icing on your configuration.

Operator presence
Last, but not least… For a GUI to work, you need to be at your workstation. Period.
Anybody who has ever worked on automated testing of applications that rely on a GUI, knows about the hideous crime of having to script test-cases, either working with hidden button-labels, screen coordinates, etc. Where these scripts fail every other day because a developer moved a window to a better spot or used a new button-label. You end up coding your application just to make it testable.
No, GUI requires operator presence, making it useless for automation or scaling.

good_cliThe bliss of CLI
Okay, middle Ages… or Stone Age…
Nothing really fancy, just a black (or, if you are feeling frivoled, you may choose some nice color) square on your screen with a blinking underscore – most often. And then you say; GUI sucks?

One of the challenges in this hyper fast moving world full of smart phones, tablet PC’s and what have you, loaded with intuitive and fast apps, is to realize that actually “hard core IT” is hard core.
You need to learn your stuff first, know what you do and know about the consequences of choices you make. You will have to learn to be able to walk the walk and to talk the talk. Once you have mastered that, this blinking underscore is no longer a roadblock but a invitation! Just like after mastering a foreign language, you will know what to say and do to open up the potential at your fingertips.

And now, reality
Of course, the above is ranting is just one side of the story.
It is even just one side of the story in hard core IT!

As already stated above, sometimes there is no time to really dive into stuff and get to know the tools you need to get to work for you. I am pretty sure we all have been in a place where we needed to get a project done or some functionality realized, where we just did not have the right devices.

What are your options at such a moment?
Get a hardcore IT specialist who does “talk the talk”?
Probably it will not be cheap and probably it will be a very thorough configuration, but just not exactly as you need it to be… Though still a valid option, even in a number of cases it’s a no-go.
This… is where a good GUI comes in handy.
It will allow you, yourself, to organize that which needs organizing in an orderly fashion. Okay, the GUI will have to be accurate and well thought through, but I that goes for all interfacing, that is also true for the CLI.

Seeing this story unfold… I guess I still think GUI sucks. (sorry!)
But GUI has a place, a very well earned place in a super-fast and highly demanding world. Still I am convinced that if you are working in a highly professional environment, having to do intricate stuff on ever live environments, I would say a good script for a CLI is the only way you can create some assurance that whatever change you need to execute will actually have a predictable result.

And putting in the effort of learning how to use any CLI? Well, I guess that’s why it is called “professional IT”.


This blog post is inspired on work I have been doing on Standard Edition databases and the returning confusion about what is and what is not part of Standard Edition.

DBA_FEATURE_USAGE_STATISTICS is a tool in determining license usage for the Oracle database. It is good to understand the implications of each entry, know what is happening in your database and thus be able to have a substantial conversation about the usage of your license, being SE, SEO, SE2 or EE!

This list is the full list of DBA_FEATURE_USAGE_STATISTICS and I have found no source where there is a mapping of these features to database editions. As it is a lot of tedious work I call upon the community to help complete the list and make it as accurate as can be. So, if you have news, improvements, other bits of information, please send it to me and I will make sure it gets added!

WARNING: Still… with all the work that goes into these answers, it is not the law, it is a very serious interpretation of facts which will pay a part in helping you make the right decision when it comes to database licensing.

Feature Standard Edition
Active Data Guard – Real-Time Query on Physical Standby NO !
Advanced Replication NO !
Application Express YES
ASO native encryption and checksumming NO – EE option !
Audit Options NO !
Automatic Maintenance – Optimizer Statistics Gathering YES
Automatic Maintenance – Space Advisor YES
Automatic Maintenance – SQL Tuning Advisor NO !
Automatic Memory Tuning
Automatic Segment Space Management (system) YES
Automatic Segment Space Management (user)
Automatic SGA Tuning YES
Automatic SQL Execution Memory YES
Automatic SQL Tuning Advisor NO !
Automatic Storage Management
Automatic Undo Management
Automatic Workload Repository
AWR Baseline NO !
AWR Baseline Template NO !
AWR Report NO !
Backup BASIC Compression
Backup BZIP2 Compression
Backup Encryption
Backup HIGH Compression
Backup LOW Compression
Backup MEDIUM Compression
Backup Rollforward
Backup ZLIB Compression
Baseline Adaptive Thresholds
Baseline Static Computations
Bigfile Tablespace
Block Media Recovery NO !
Change Data Capture NO !
Change-Aware Incremental Backup
Character Semantics
Character Set
Client Identifier
Clusterwide Global Transactions
Compression Advisor
Crossedition Triggers
Data Guard NO !
Data Mining NO – EE option !
Data Recovery Advisor
Database Migration Assistant for Unicode
Database Replay: Workload Capture NO ! 1
Database Replay: Workload Replay NO ! 1
DBMS_STATS Incremental Maintenance
Deferred Open Read Only
Deferred Segment Creation NO !
Direct NFS
Dynamic SGA
Editioning Views
EM Database Control
EM Grid Control
EM Performance Page
Encrypted Tablespaces
File Mapping
Flashback Data Archive NO ! 2
Flashback Database NO !
GoldenGate NO – EE option ! 3
Hybrid Columnar Compression NO !
Instance Caging NO !
Internode Parallel Execution
Job Scheduler
Label Security NO – EE option !
Locally Managed Tablespaces (system) YES
Locally Managed Tablespaces (user)
Locator YES
Logfile Multiplexing
Long-term Archival Backup
Materialized Views (User) NO !
Messaging Gateway NO !
MTTR Advisor
Multi Section Backup
Multiple Block Sizes
OLAP – Analytic Workspaces NO – EE option !
OLAP – Cubes NO – EE option !
Oracle Database Vault NO – EE option !
Oracle Java Virtual Machine (system) YES
Oracle Java Virtual Machine (user)
Oracle Managed Files
Oracle Multimedia
Oracle Multimedia DICOM
Oracle Secure Backup
Oracle Text
Oracle Utility Datapump (Export)
Oracle Utility Datapump (Import)
Oracle Utility External Table
Oracle Utility Metadata API
Oracle Utility SQL Loader (Direct Path Load)
Parallel SQL DDL Execution NO !
Parallel SQL DML Execution NO !
Parallel SQL Query Execution NO !
Partitioning (system) YES
Partitioning (user) NO – EE option !
PL/SQL Native Compilation
Quality of Service Management NO !
Read Only Tablespace
Real Application Clusters (RAC) YES 4
Real-Time SQL Monitoring
Recovery Area
Recovery Manager (RMAN) YES
Resource Manager NO !
Restore Point
Result Cache NO !
RMAN – Disk Backup
RMAN – Tape Backup
Rules Manager
SecureFile Compression (system) YES
SecureFile Compression (user)
SecureFile Deduplication (system) YES
SecureFile Deduplication (user)
SecureFile Encryption (system) YES
SecureFile Encryption (user)
SecureFiles (system) YES
SecureFiles (user)
Segment Advisor (user)
Segment Shrink
Semantics/RDF NO !
Server Flash Cache
Server Parameter File
Shared Server
Spatial NO – EE option !
SQL Access Advisor
SQL Monitoring and Tuning pages NO – EE option !
SQL Performance Analyzer NO !
SQL Plan Management NO !
SQL Profile
SQL Repair Advisor
SQL Tuning Advisor
SQL Tuning Set (system) YES
SQL Tuning Set (user)
SQL Workload Manager
Streams (system) YES 5
Streams (user)
Transparent Data Encryption
Transparent Gateway YES – option
Transportable Tablespace NO ! 6
Tune MView
Undo Advisor
Very Large Memory
Virtual Private Database (VPD) NO !  7
Workspace Manager
  1. Unless used for upgrade to Enterprise Edition.
  2. Unless used without history table optimization.
  3. Goldengate can also be used with Standard Edition, it is a separate product.
  4. RAC on Enterprise Edition is an option.
  5. No capture from redo.
  6. Import transportable tablespaces in all editions.
  7. Policies on XDB$ACL$xd_sp in sys.v_$vpd_policy are internal ( “out of the box”) policies that are used by XDB to control the access to certain internal tables. All the logic is implemented in the xdb.DBMS_XDBZ package and there is no way one can control / influence the way this is working.