Category Archives: license

Standard Edition 2 – DBA’s Toolbox

 SE2 Toolbox

Article about Some useful tools from my SE Toolbox…and some SE2 License stuff at the end 😉

A New kid on the block – SQLCLI

As a Standard Edition DBA, I’m sure you have numerous times used the good old SQL*PLUS.  Recently I noticed that the new SQLCLI tool is not a common known fact among “DBA’s not attending any conferences”, so  I like to promote this nice new tool to the Standard Edition database DBA’s toolbox.

The Oracle SQL Developer Product Manager Jeff Smith’s team has developed this new tool “SQLCLI”, and it can be downloaded from SQLDeveloper homepage. (scroll down the page).

After the download and installation, it is ready for use. Note! On my mac the “sql” didn’t run, so I just started the java jar file from the “sqlcli/lib- directory:

cd $HOME/sqlcl/lib
SE2mac$ java -jar oracle.sqldeveloper.sqlcl.jar hr@//se12t1:1521/se2pdb

Why not Check some youtube videos about SQLCLI, to get the drift about this tool?

SQLDeveloper – a must in SE/SE2

This tool has grown in maturity since the first version, and I think it’s the first tool to add to your SE/SE2 toolbox. It is free and will bring a nice value to your DBA job.

Check this link to get started.

SQLDeveloper pitfall in SE/SE2:

In older versions of SQLDeveloper, I faced some pitfalls with the predefined DBA reports. For example I was able to use the “AWR reports”  without any notifications about violating the license agreement in my Standard Edition database.

The latest version of SQLDeveloper, version 4.1.1, will give you a warning about the license requirements:

(c)Ann Sjökvist

And why do you need to READ this note and answer NO?

By answering YES, I am able to use these AWR reports without any problems, and if proceeding I will violate the License agreement, since I am on a Standard Edition 2 database environment.

Note! Also  in a Enterprise Edition database this violation is possible, if you haven’t purchased the “Diagnostic&Tuning Pack”.

By answering YES, you will be able to use the reports as shown in the screenshot below:

(c) Ann Sjökvist

The “dialog” warning, looks like a normal behaviour independently of the software. For example TOAD software provide with the same kind of dialog during the installation phase. And remember, you can at any later state enable the pack, by changing the softwares preferences/properties; so  an other challenge to remember.

Standard Edition 2 on the Menu


After a long wait amongst people in the Standard Edition Community, Oracle Corporation finally released the edition of the Standard Edition Database on the 1st of September.

With this release, lots of buzz has been generated in all Social Medias available, and lots of blog posts have been generated.

As always, a message can be interpreted in many ways, and both good and less good stuff can be found out there on what this SE2 realease bring to the table; after you have carefully read the license documents available, and still feeling unsure, please check with your local Oracle LMS before making any false or unecessary assumptions on the impacts on your business.

Some small rules to remember about SE2

    • The minimums for Database Standard Edition 2 are 10 Named User Plus licenses per server
      • This metric is used in environments where users can be identified and counted.
    • Install onto a servers that have a maximum capacity of 2 sockets (*see)
      • Oracle Database Standard Edition 2 database may use a maximum of 16 CPU threads(**see) at any time
    • Oracle Real Application Clusters, Oracle Database Standard Edition 2 may only be licensed on a maximum of 2 one-socket servers
        • on RAC – each Oracle Database Standard Edition 2 database may use a maximum of 8 CPU threads per instance at any time
        • Hard partitioning (*** see) can be used on 2 sockets servers to enable you to run RAC on 2 two socket servers with both CPUs populated
      (*)When licensing Oracle programs with Standard Edition One, Standard Edition 2, or Standard Edition in the product name, a processor is counted equivalent to an occupied socket; however, in the case of multi-chip modules, each chip in the multi-chip module is counted as one occupied socket.

      (**)From the CPU's perspective, a thread (short for "thread of execution") is merely an ordered sequence of instructions that tells the computer what to do.
      So when someone talks about a "multithreaded processor," they're talking about a processor that can execute multiple instruction streams simultaneously. There are two ways that a processor can perform such a feat: simultaneous multithreading, and using multiple cores. Neither of these methods is mutually exclusive, and both are often used together.
*** HARD partitioning physically segments a server, by taking a single large server and separating it into distinct smaller systems. 
*** SOFT Partitioning: Unless explicitly stated elsewhere in this document, soft partitioning (including features/functionality of any technologies listed as examples above) is not permitted as a means to determine or limit the number of software licenses required for any given server or cluster of servers.

Take care, stay well and let’s enjoy the Autumn.

Helsinki, 24st September 2015 Standard Edition R2 is planned for Q3 CY2015

(c) Ann Sjökvist
Finland 2:30 am – Summer The Best Time to Visit


3rd of July 2015 SE Day Standard Edition R2 is planned for Q3 CY2015

NOTE – UPDATE – 8th of July 2015

Changes has been announced today, so PLEASE CHECK these new documents!

Doc2027072.1 Disappeared.

Replaced by updates in Doc1905806.1 and Doc742060.1

(c) Ann Sjökvist

What an amazing day of turbulence on twitter!

NOTE – 3rd July 2015 Breaking News

Standard Edition (SE) and Standard Edition One (SE1) are replaced by Standard Edition 2.

Customers running SE or SE1 will need to migrate their licenses to SE2 to be able to upgrade to Note 2027072.1

During last months the Standard Edition Community have often asked themselves:

“When will Standard Edition Database release 2 ( arrive?”

Yesterday I received a question from Laurent Leturgez , asking if I had any information about SE12.1.0.2:

(c)Ann Sjökvist

Since I hadn’t, I asked the Database Product Manager at Oracle Dominic Giles,  and shortly the Standard Edition Community  received the information we all been waiting for:


The Standard Edition Database version (Rel2) is planned to arrive in Q3 CY2015! And Support extended 6 months

Thanks Dominic for assisting the Oracle Standard Edition Community during this time of uncertainty.

Twitter Turbulence – 3rd of July 2015 (Europe temp > 35°C)

However, this message kicked off some “turbulence on twitter”, and besides facts, of cause also lots of seeds of rumours were planted.

(c) Ann Sjökvist

Please check out Laurent’s blog

(c)Ann Sjökvist

(c) Ann Sjökvist

Please check out Stefan’s blog

(c) Ann Sjökvist

(c)Ann Sjökvist

(c) Ann Sjökvist

Please read Ludovico’s thought about SE2 from his blog

(c) Ann Sjökvist

Please read Jan’s own thoughts about Standard Edition Rel2. Jan thanks for the mention on your blog.

(c) Ann Sjökvist

Please check out Lothar’s blog.

(c) Ann Sjökvist

(c) Ann Sjökvist

(c) Ann Sjökvist

Please check out Alberto’s blog.

(c) Ann Sjökvist

Please check out Franck’s blog.

(c) Ann Sjökvist

(c) Ann Sjökvist

Please check out Dominic’s blog. Thanks Dominic for your help! I am sure everybody in Oracle Standard Edition Community really appreciate your helping hand!

(c) Ann Sjökvist

As Ludovico mentioned on one of his tweets, we absolutely need the twitter buzz around Standard Edition, but we also know the effect of “bad apples in the basket”.

So would it be more beneficial for Standard Edition Community to give the amazing team working on Oracle Standard Edition a break? I am sure Dominic will provide us with more facts when possible.

I liked the initiative from Franck asking for information on Standard Edition REL2 New Features. We need more initiative like this. Thanks Franck for supporting the Standard Edition Community!

Oracle Note 2027072.1 on SE2

Please read Note 2027072.1 for more information. The  License Agreement policy will change between Standard Edition / Standard Edition One and But once again. The expected release:

The release of Oracle Database SE2 is planned for Q3 CY2015

And the extended support for Standard Edition is 6 months.

Take care, stay well, and let’s enjoy this beautiful and warm weather! Yes finally even Scandinavia have got warm weather.


Turku 4th of July 2015 – Happy Independence Day to you in U.S!


Network Encryption in Standard Edition


This topic is doubtfully something to write about, and I was very pleased to find this blog post about

Native Network Encryption and SSL/TLS are not part of the Advanced Security Option

As you  already might have noticed, I look into things from a Standard Edition Database point of view, and to me Tim Hall’s post was executed in a more “Standard Edition Mind Thinking” fashion, and that is for me so amazing to notice, since there are not too many blogs out there at the moment, which are blending the “Standard Edition Database challenges” flavour into their post.

Please check his blog, but the main thing for SE is:

“Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.”

GDRP – Standard Edition Database – any options out-of-the-box?

updated: 31.7.2017

Oracle released this document in January 2017:

“Accelerate Your Response to the EU General Data Protection Regulation (GDPR) – Using Oracle Database Security Products”

When you have an Enterprise Edition Database, and money are no issue, it looks like a pretty straight solution.

The “Oracle Maximum Data Security architecture” gives a nice overlook about what is available for EE:Oracle Corp.(c) + my added SE notes

(pic:Oracle Corp (c) + My SE notes)

Take care, stay well, and enjoy the weekend!


Turku 3rd July 2015

ADO in Oracle Standard Edition?

Automatic Data Optimisation (=ADO) in Oracle Standard Edition?

Time to inspect the Automatic Data Optimization feature that Oracle12c provides from a Standard Edition Database perspective.

Updated 22.6.2015: I feel privileged having peers reading my blog, and receiving feedback.  Standard Edition Community needs a blog with facts, so Thanks Franck Pachot for correcting me on Basic Compression. I would also like to thank my friend Joel Goodman for advice on this topic.

I am actually pleased that I made this mistake since it only reminded me on how easy it is to misinterpret and make wrong assumptions. 

I found this very good post on basic table compression by Jonathan Lewis. As a none native English speaking person, both the way the text below is written and the use of the word “core” made me make the wrong assumptions, and I undermined my own advice “always check and double-check the License Document”. 


“Of the three mechanisms, index compression and basic table compression are part of the core product, while OLTP compression is part of the separately licensed “Advanced Compression Option (ACO)”. In this first article we will restrict ourselves to creating data using basic table compression, leaving the question of updating and deleting compressed data to the second article, which will lead us into “OLTP” compression in the third article. We’ll leave index compression to stand alone in the fourth and fifth articles.” (Jonathan Lewis).

NOTE!! The Basic Compression is not Standard Edition Database Proof.  So do not use it in a Standard Edition Database! 

So I checked the License information on Oracle9i:


So wait, doesn’t it tell us that Compression is ok to use like J Lewis said? No! Once again I made the wrong assumption! The structure of the Oracle9i document is not the same we are used to  (SE1 | SE | EE).

In Oracle9i the structure is  (SE | EE|Personal Edition) – Yes Oracle tells us if a feature is available in Personal Edition:



EndOfUpdate 22.6.2015

Original post:

Explanation for a Business Management’s type of person.

The Whitepaper on ADO feature tells us:

The rapid growth of data presents daunting challenges for IT, both in cost and performance. Although the cost of storage keeps declining, fast-growing data volumes make storage one of the costliest elements of most IT budgets. In addition, the accelerating growth of data makes it difficult to meet performance requirements while staying within budget.

Explanation for a more Technical person:

Automatic Data Optimization (=ADO) automatically moves and compresses data according to user-defined policies based on the information collected by Heat Map. Heat Map automatically tracks modification and query timestamps at the row and segment levels, providing detailed insights into how data is being accessed.

Explanation in a Graphical view 

(c) Ann Sjökvist
(c) Ann Sjökvist

Note: I am not an expert on HW, so when making this graph the choice of storage might not be the best, but to my understanding both the raid50 and Hadoop HDFS alternative seem like good options. To my understanding HDFS is available as a plug-in to ADO, even thought by doing so, the full “data travel path” will not be automatically managed by the database, but that’s a whole different story.

Explanation from the License perspective:


Clear and loud. We cannot use it in a Standard Edition Database environment.

This new Automatic Data Optimisation feature can exploit the functionality of the Partitioning Option and the Advanced Compression Option, and neither of those capabilities are available in a Standard Edition Database Environment.

Note: the partitioned views is not a requirement of ADO, but without the pertaining functionality you will lose the flexibility of using different storage tiers and different compression on different parts of the same table. And as you already maybe have read from my blog, we can create partitioning views in our Standard Edition Database Environment, and by doing so, we can achieve some degree of flexibility also in our Standard Edition Database Environment.

ADO feature allows active data to live on high performance storage like solid state technology, and older inactive data to live on high capacity drives, and allows the DBA to leverage different storage technologies for the same table.  The magic behind all this is the new technology called Heat Map.

Heat map tracks the last access or update time for segments and extents and can also track at the database block level. This includes the time of last modification and access of tables and partitions. There is also a Row level Heat Map that tracks modification times for individual rows (aggregated to the block level).

How does this magic come into place?

  1. You should enable Heat Map (alter system set heat_map = on scope=both)(manually performed)
  2. Heat Map Statistics are collected
    • V$HEAT_MAP_SEGMENT (MMON collects and “write” the info to this view), HEAT_MAP_STAT$ (dbms_scheduler regularly flushes the info to this table from previous view), DBA_HEAT_MAP_SEG_HISTOGRAM & DBA_HEAT_MAP_SEGMENT (are views based on the previous table)
  3. You create ADO Policy on table (manually)
  4. ADO Policy get evaluated
  5. ADO actions will be executed

A beautiful feature but we cannot use it in Standard Edition.

switch Can we enable ADO in Standard Edition? 

There are things a Standard Edition DBA can unintentionally perform, when not alert, and the software will not tell you if a license agreement violation will occur by continuing performing the task you were just about to perform.

Will Oracle give us a helping hand by performing the steps above to activate Heat Map and ADO feature in a Standard Edition Database?

 sandbox      Sandbox and Testcase

  • Oracle Standard Edition version 12.
  • Database Patch Set Update :
  • Linux 3.8.13-55.1.5.el6uek.x86_64
  • An out-of-the-box installation of the Standard Edition Database
SQL> SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY') as Environment FROM dual union all SELECT sys_context('USERENV', 'CDB_NAME') FROM dual union all SELECT sys_context('USERENV', 'CON_NAME') FROM dual;
SQL>ALTER SYSTEM SET heat_map=ON scope=both;
 alter system set heat_map=on scope=both
 ERROR at line 1:
 ORA-02097: parameter cannot be modified because specified value is invalid
 ORA-00439: feature not enabled: Heat Map

Bang! We cannot even perform step one from the list above. Great and Thanks Oracle!

By Ian Leonard

Can we mimic ADO in Standard Edition?

NOTE!! The Basic Compression is not Standard Edition Database Proof.  So do not use it in a Standard Edition Database!

Note: Starting from 12c we can use the SecureFile for LOBS, but remember the SecureFile Compression is part of the Advanced Compression option, and that is an Enterprise Edition option.

Basic Compression – NOT OK in Standard Edition

Basic Table Compression N N Y

The Oracle Advanced Compression option contains the following features:

SecureFiles Compression and Deduplication


--CTAS with basic compression enabled
create table t_se1 compress basic
select * from [some_table] where [column] = [value];
--Direct path insert into empty table defined as compressed
create table t_se1 compress basic
select * from [some_table] where rownum = 0;

insert /*+ append */ into t_se1 select * from [some_table] where [column] = [value];

How about moving  data according to usage?

Archived data is usually cold, so that should be easy to move to a less expensive storage tier. But even so, I do think this requires a good data model, knowledge about not only the application capabilities, but also good knowledge about how the end-user is actually using the application and what is the business process that they are following. Again, the importance of good collaboration between people is vital.

(c) Ann Sjökvist Figuring out what can be moved in SE

This SQL shows information about segments read and write occurrence per date, and according to the License Document, these views are Standard Edition Proof :

WITH se_ado as (
     SELECT o.owner,o.object_name, o.object_type,s.db_block_changes_delta bcd,
to_char(begin_interval_time,'mm/dd') c1, 
         sum(physical_reads_total) reads, 
         sum(physical_writes_total) writes 
          dba_hist_seg_stat s, 
          dba_hist_seg_stat_obj o, 
          dba_hist_snapshot sn 
        WHERE o.owner='SEHC'and s.obj# = o.obj#
        and sn.snap_id = s.snap_id
        GROUP BY o.owner,o.object_name,o.object_type,s.db_block_changes_delta,to_char(begin_interval_time,'mm/dd')
        ORDER BY 4,1,3)
select se_ado.* from se_ado where se_ado.writes=0 or se_ado.reads=0
SEHC SE_INDB_ARCH TABLE       0 06/21    0      8
SEHC SE_INDB_ARCH TABLE      16 06/21    0      8

With this script as a starting point, plus lots of evaluation on what are your company’s metrics on moving data to a less expensive storage tier destination, and both some scripting and some maintenance windows,  I think you could mimic ADO to some degrees.

When all your metrics have been evaluated, a scheduled job could perform the collection on a regular basis, that send an email with the results to the DBA.

The magic of moving objects around will be very difficult to mimic, because as the license document reminds us of, ONLINE functionality is only available in Enterprise Edition Database Environment.



So for us Standard Edition DBA’s, we need to have a maintenance window. In my experience, this is not a big issue, when all the parties have agreed and are committed to a solution that now a then requires a maintenance window.

Solution good enough for your company or not?  It depends.

Personally I am a strong believer of the concept that I came up with during a discussion at the Harmony15 Conference; a Mental Sauna” meeting. Briefly it’s about not “pull rank” on someone else. You have no uniform on, so no visible rank to fall back on. In a Finnish Sauna it’s tradition to join, having either only your birthday suit  or a swimming suit on, so it is understandable very difficult to tell is a person a priest, a CEO, a plumber, or what so ever, and that nurtures an environment were people freely exchange their thoughts.

Note: The Finnish Sauna tradition is something special, so if you have no experience of it, please read the article before making any assumptions. 

I believe the result after such a “Mental Sauna”-meeting on for example “Shall we mimic ADO or go for the genuine ADO solution?” would be more uniform since all parties (people like: Application Vendor, the Business Owner, the IT department, support, the end-user, and solution conductor etc) have already discussed the advantages and disadvantages of the topic, and therefore have a stronger commitment to the solution.


Pretty cool feature Oracle engineers have created, isn’t it?

If you are running on a Standard Edition Database, and find this ADO feature appealing, why not start a “mental sauna”-meeting and see which road you choose to take.

Take care, stay well, and enjoy the summer!


21st June 2015

Application Continuity Standard Edition Proof?


(c)Oracle WhitePaper 12c

Oracle12c Application Continuity in Standard Edition

This morning I read some stuff on the new 12c feature APPLICATION CONTINUITY and TRANSACTION GUARD.

To my understanding this feature is more a JDBC driver feature than a database feature, so I said “yes” of cause this is Standard Edition Proof, but after having some issues summing-up the facts around  this feature, I hope you could assist me on this question

Update 8th June 2015: No this feature is not Standard Edition Proof, so please tell your Java Developer NOT TO IMPLEMENT.

Thanks Markus Michalewicz, Franck Pachot, Ludovico Caldara, and Philippe Fierens for your intervention, and for giving us the facts on this one!


If you are interested in, what made me ask this question, keep on reading.

“Is this feature Oracle Standard Edition Proof or not?” The Answer:NO

Facts from Oracle’s own documents

By googling on the option we can see from OTN what it can provide us:

Application Continuity (AC) is a new feature available with the Oracle Real Application Clusters (RAC), Oracle RAC One Node and Oracle Active Data Guard options that masks outages from end users and applications by recovering the in-flight database sessions following recoverable outages.

Transaction Guard is a reliable protocol and tool available with the Oracle Database 12c that returns the outcome of the last in-flight transaction after outages that make the database session unavailable. It is used by Application Continuity to ensure that an in-flight transaction is committed successfully, no more than once. It can also be used directly by applications to return a known outcome to the end users.

RAC feature is by default included in the Standard Edition Database for free, so it should be ok – right?

From the white paper we can read this

Introduced in Oracle Database 12c, Application Continuity strengthens the fault tolerance of systems and applications that use an Oracle database. Application Continuity is available for

  • Oracle WebLogic Server
  • Oracle Universal Connection Pool, used standalone or as a data source for a third party Application Server
  • Standard 3rd Party JDBC application servers using the JDBC PooledConnection interface
  • Oracle JDBC-Thin Driver

WebLogic Server is a product on its own, and with its own license model, but the three others is to my understanding Standard Edition proof “methods”.

12c Support information

Application Continuity for Oracle Database 12c supports the following client and server features:

Oracle Database 12c Client

Oracle JDBC Replay Driver 12c or later. This is a JDBC driver feature provided with Oracle Database 12c for Application Continuity, referred to as the “replay driver” onwards.

This text is from the WP, and below is how the text continues:



General information on JDBC: The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases…(picture below)


More information:

Can third party vendors distribute Oracle’s JDBC drivers along with their own software?
If you are a third party software company (and Oracle partner) then please check out Oracle’s licensing terms spelled out at Oracle Licensing Agreement  Please contact your local Oracle sales rep for more details.

Behind the link, you will find the License Rights:


Oracle License Document 

Here is where I start to have problems summing-up the facts.

According to the license, we cannot use the Application Continuity and since the Transaction Guard is part of the AC, not that either.(TG is used by Application Continuity to ensure that an in-flight transaction is committed successfully, no more than once.)


RAC is included for free in a Oracle12c Standard Edition Database (Note: not in SE one), and this feature relies on JDBC that is Standard Edition proof, and the License Document tell us this feature is not Standard Edition Proof.

So what do you think?

“Is Application Continuity and the Transaction Guard features Oracle Standard Edition proof or not?”

The Answer is : NO – we cannot use this feature with a Standard Edition Database.


And a reminder….

Note: Oracle licensing is a complicated business. The notes here are only a guide. You should always discuss your licensing with Oracle License Management Services or any other third-party company, who are professionals on this business.

Take care, stay well, and let’s enjoy the summer….Please send some summer warm weather!

…In Finland it’s only 14 degrees at the moment, and a cold wind…


Turku 7th June 2015


Row Pattern Match in Oracle 12c Standard Edition?

Row Pattern Match – Standard Edition proof

Note: Oracle licensing is a complicated business. The notes here are only a guide. You should always discuss your licensing with Oracle License Management Services or any other third-party company, who are professionals on this business.

The Oracle12c Database has provided the Oracle community with many new features, and one of them is  “the Row Pattern Match“.

In this video by Tom Kyte from December 2013, we can se that he put this feature on the Top 12 features list.

What is Row Pattern Match?

Oracle now provides a completely new native SQL syntax for pattern matching.

It has adopted the regular expression capabilities of Perl by implementing a core set of rules to define patterns in sequences (streams of rows) using SQL.

This new inter-row pattern search capability complements the already existing capabilities of regular expressions that match patterns within character strings of a single record.

The 12c MATCH_RECOGNIZE feature allows the definition of patterns, in terms of characters or sets of characters, and provides the ability to search for those patterns across row boundaries.

This is a nice feature, and nice features usually are delivered through some options or management packs, and only to the Enterprise Edition database.

This post asks the question is: “the Row Pattern Match feature Standard Edition Proof? Can I use it?”

You can check this article by Tom Kyte with an example on how to use this feature. Here is the example from that article:

  3  ( PARTITION BY symbol
  4    ORDER BY tstamp
  6       STRT.tstamp AS start_tstamp,
  7       LAST(DOWN.tstamp) AS 
  8       LAST(UP.tstamp) AS end_tstamp
 12    DEFINE
 13      DOWN AS 
         DOWN.price < PREV(DOWN.price),
 14      UP AS UP.price > PREV(UP.price)
 15  ) MR
 16   ORDER BY MR.symbol, 

——————  ————————— ————————— —————————
XYZ     01-SEP-12 03-SEP-12 07-SEP-12
XYZ     07-SEP-12 10-SEP-12 13-SEP-12

Question: PARTITION – so I cannot use the “Row Pattern Matching”-feature?  Or is this referring to something else and not the Oracle Partitioning context?

By checking the Oracle12c License Document and the Enterprise Manager License documents, I cannot find anything referring to the keywords. There is though one row on Oracle Advanced Analytics (=OAA) option, and a second row on Oracle On-Line Analytic Processing (OLAP). Both options are only available in an Enterprise Edition Database.

  1. Oracle OTN site on OLAP, you can find this video explaining the features of OLAP. After looking at this video, OLAP option can to my understanding, make use of any available Analytic Functions as well as basic SQL commands together with its own Cube/OLAP specific “language”, so I am tempted to assume that “Row Pattern Matching” is not  part of the OLAP option.
  2. Oracle OTN site on Oracle Advanced Analytics 


“Analyze “market baskets” to discover associations, PATTERNS and relationships”

Can it be so, that “Row Pattern Matching” is part of OAA option?

Reaching out for help from an Advanced Analytic Expert

Since the documentations is not very straightforward on answering  my question, (or maybe its the fact that English is not my mother tongue), I thought I better reach out to one of my peers, Brendan Tierney – who is an expert on Advanced Analytics, and see what kind of thoughts he might have about this new feature:

“As far as I am aware of, the Row Pattern Matching is part of the suite of Analytic Function in Oracle, and as far as I know, it is not part of the Advanced Analytics Option.  It has never been mentioned or associated with Oracle Advanced Analytics.”

Promising….. How about PARTITION?

“The  PARTITION clause is a special feature of the Analytics Functions that allows for the processing/analytics to be performed on different groupings of attributes and their values.”

This is also verified in the above Analytic Function document (Note that the term “partitions” used within the context of analytic functions is unrelated to Oracle Partitioning.)

BTW: Brendan is going to speak about “Predictive Analytics”,  “Data Miner”, and”Predictive Queries” at OUGE Harmony15 Conference in Tallinn. Why not join?

Thanks for sharing your knowledge, and helping me out!


How cool would it be to find a link within the Oracle License Documentation to a  list on for example the OAA options all packages, functions, tables, views, procedures etc that it is using – It would give a very quick answer to Standard Edition specific questions. Now finding an answer to the question “SE Proof?”, can be pretty time consuming, because the information is scattered around. Or a Standard Edition Spokesperson, to whom you could send your question(s) to? Wouldn’t that also be pretty cool?

The “Row Pattern Matching” feature seems to be part of the general Analytic Functions Suite that seems to be a standard functionality of the Oracle database,  and I couldn’t find any text referring to the Enterprise Edition options mentioned above.  I am tempted to say “yes, it’s SE proof”

What’s your thoughts? Is this new feature Standard Edition Proof or not?

June: Message from an Oracle License Expert: Yes it is Standard Edition Proof.

Take care (also remember your data), stay well and lets enjoy the long and bright Summer Nights of Scandinavia!

– Ann

Turku 1st June 2015

Pipelined Table Function in Standard Edition


Pipelined & Parallel Table Function in Standard Edition?


Earlier today I received a question from Mr. Lothard Flatz concerning a featured that to my understanding has been available since Oracle9i.


Thank you Lothard. And without any further information, I assume this question targets the database version 11gR2 or 12cR1.

My answer is … It depends…

To be frank, my first thought was “yes this is ok to use in a Standard Edition Database”, but after reading more about this feature, I started to hesitate and doubt my own answer. Why?

Please keep on reading, and you can judge for your self, am I making the correct assumptions or not.

Hopefully somebody among my peers can either verify or correct my assumptions in the end of this post.

Please also NOTE this:

Oracle licensing is a complicated business.

The notes here are only a guide. You should always discuss your licensing with Oracle License Management Services or any other third party company, who are classified as professionals on this business.

What is this Pipelined Table Function?

From the “Database Data Cartridge Developer’s Guide” 11gR2 document we can read:


And while you are making your self acquainted with the subject,  why not also read this article from Tim Hall about this feature before continuing.

“parallel execution” – in standard edition? 

As you noticed from the picture above, Oracle documentation mentioned the word “parallel execution”, and that is always generating the question “is this ok in a Standard Edition Database environment, or not“.

First we need to check the  Oracle License Document from release 11.2 for some guidances:


We can only find something on the PL/SQL Function Result Cache.

I assume this is referring to the RESULT_CACHE clause, since that will to my understanding, boost the performance by reusing function results, instead of recalculating.

From the internet, we can find more information about RESULT_CACHE from either this post by Tim Hall or from the reference guide from 12c.

Note to my self: “So is the License document also referring to the DBMS_RESULT_CACHE package, and therefor we cannot use it in Standard Edition?”

Parallell Table Function

Since the “Table Function”- feature also has the parallel option available, it might be good to analyse it as well. The “Database Data Cartridge Developer’s Guide” 11gR2 document tell us this about parallel table functions:


And the 11gR2 License Document will tell us these things about parallelism usage (same in 12c):

    • Parallel backup and recovery                      N N Y
    • Parallel query/DML                                          N N Y
    • Parallel statistics gathering                          N N Y
    • Parallel index build/scans                              N N Y
    • Parallel Data Pump Export/Import          N N Y
    • In-memory Parallel Execution                     N N Y
    • Parallel Statement Queuing                         N N Y
    • Parallel capture and apply via XStream N N Y
    • Parallel spatial index builds                           N N Y

The 12c Enterprise Manager License Document

I couldn’t find any additional information about “Pipelined Table Functions” or “Parallel Table Functions” from this document, so from the information provided by the License documents I would summaries the result:


Yes, to my understanding one of the features is available in our Standard Edition Database Environment – the Pipelined Table function.
But the other one – the Parallel Table Function I would like to classify as a “forbidden fruit”, and the reasons why is what the License Document tell us about Parallelism and this information from the Database Data Cartridge Developer’s Guide:
  1. For a table function to be executed in parallel, it must have a partitioned input parameter. Parallelism is turned on for a table function if, and only if, both the following conditions are met:
    1. The function has a PARALLEL_ENABLE clause in its declaration
    2. Exactly one REF CURSOR is specified with a PARTITION BY clause- I hope somebody among my peers could verify or correct this assumption.

So what do you think?
As I mentioned earlier. The last resort when unsure, is asking Oracle for assistant.
Take care, stay well, and enjoy the weekend!
Turku 15th May 2015

Real-Time SQL Monitoring in Standard Edition


RealTime SQL Monitoring

Real-Time SQL Monitoring in Oracle Standard Edition

Feature is RED ALERT if you have a Standard Edition Database

Remember this feature needs the “Tuning pack”, and that we cannot buy when we are working on a Standard Edition Database Environment.

Please also keep in mind that if you have a mixed environment with both Enterprise Edition databases, and some Standard Edition databases,  and you use the Enterprise Manager Cloud Control/Database Express tool, YOU MAY NOT use this feature on any database that has not been licensed with the Tuning Pack.

Anyhow, why not keep on reading, since this feature might bring new business value to your work as a DBA, and you only need to provide the solid information on Real Time SQL Monitoring features benefits to your manager.

I found this good article by Yasin Baskin about Real-Time Monitoring, so you can get more aquatinted with the subject. It was released the 7th of May this year.

Below you will find a small summary about this feature.

What is this Real-Time SQL Monitoring?

11gFrom the Oracle 11g Documentation we can see what this neat little feature can provide us:

Real-time SQL monitoring, introduced in Oracle Database 11g, provides a very effective way to identify run-time performance problems with resource intensive long-running and parallel SQL statements.

12cand from the Oracle12c documentation we can see it has been enhanced:

AWR has been enhanced in Oracle Database 12c to include reports from Real-Time SQL Monitoring, Real-Time ADDM and Database Operations Monitoring.

….Traditionally, this analysis has been done using reactive methods like SQL tracing, but the addition of Real Time SQL Monitoring enables you to monitor SQL statements while they are executing. Live execution plans of long running SQL are automatically displayed on the SQL Monitor page in Oracle Enterprise Manager using new, fine- grained SQL statistics that are tracked out-of-the-box.

Has it always been there?

11gSQL Monitoring was instrumented and available for command line use in the first release of the Oracle Database 11g. The graphical user interface to monitor active running SQL made its debut in Grid Control and Database Control

12cIn Oracle12c you can find this tool as before, the name has just changed, so in 12c we can use both Oracle Enterprise Manager Cloud Control and Oracle Enterprise Manager Database Express.


Take care (remember to take care of your data as well), stay well, and enjoy the day!


Turku 14th May 2015


dbms_space – Standard Edition proof?

dbms_space -Can we use this package to the fullest in Standard Edition?

Updated: 27.5.2015 Confirmation received from an Oracle License Expert….scroll down to

the red part ,There is the confirmation.

27th May 2015/Ann

The Original post 12th April 2015 starts here

When I was googling for information about this DBMS_SPACE package, I found that some people say “No you need the diagnostic & tuning pack”, and other people say “go ahead and use it”.

So this requires a test case.

This situation of finding out “can we use something or not” is for us Oracle Standard Edition DBA’s basic “daily“- routines (at least for a customer oriented DBA).

Oracle has started to help out more and more, when comparing to old documentation vs. new documentation, but there are still stuff out there that need to be better documented, or even better; the software should tell us if something is not ok to use.

I feel that this DBMS_SPACE package is one of those things that need better documentation.

 Please NOTE:

Oracle licensing is a complicated business. The notes here are only a guide. You should always discuss your licensing with Oracle License Management Services or any other third-party company, who are professionals on this business.

SUMMARY of Test Case on the DBMS_SPACE package

“Technical blog post sometimes become to lengthy with all the tech stuff“, so for you who are not keen on knowing the details, here is a brief summary of the test cases performed on DBMS_SPACE and the challenges I think it causes us Standard Edition DBA’s.

Some test results I have received are of a surprise to me. And I would love to hear from you gurus, is my test method to simple and need corrections, or why is the outcome as it is?

<27th May 2015 – Confirmation from an Oracle License Expert START

According to a letter from an Oracle expert on License  that I received, the DBMS_SPACE package is Standard Edition proof: 
“the DBMS_SPACE can be used in Standard Edition, since it is making use of DBA_HIST_SEG_STAT and DBA_HIST_SEG_STAT_OBJ views, and doesn’t require the Diagnostic Pack”
Let’s take a look at the Enterprise Manager License document:

“All data dictionary views beginning with the prefix DBA_HIST_* are part of this pack, along with their underlying tables.
The only exception is the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license.”

“There are neither any explicit information found about this package from Oracle License Document nor the Enterprise Manager License document, saying that this DBMS_SPACE package is part of any management pack”.

As always when you get questions about Oracle License, follow the note above, and verify with an Oracle License Expert. Thanks Oracle License Expert for your assistant!
Note to myself: On the internet you can find an article on how to Measuring Database Space Growth if you would like another method on how to accomplish the growth measurement, instead of using this package.

27th May 2015 -Confirmation from an Oracle License Expert  END>

….and back to the post written on the 12th Apr 2015 …

From the Oracle Document on Diagnostic Pack API’s, I cannot find any information about DBMS_SPACE (if it’s ok to use in Standard Edition or not)

This post by Tim Hall on DBMS_SPACE (old yes, but still valid?) Crunchy “To Be Or Not To Be?”; Question still remains.

Test environment & methods information:

  • fresh newly installed Standard Edition with one PDB
    • control_management_pack_access     string NONE
  • Oracle Linux 6.6
  • VirtualBox
  • These tests are done connected to the CDB. I will later write about the results from the PDB.
  • TC methods:
    • 1) Read Oracle License document
    • 2) Read PL/SQL reference document
    • 3) run the DBMS_SPACE.[function][procedure]
    • 4) run DBMS_FEATURE_USAGE_INTERNAL (update usages)
    • 6) Reporting the test results

Since Oracle provide with two scripts for checking the usage of a feature/option (see below), I just used them (see below for the Doc Id).

According to the documentation, Oracle automatically update the statistics every 7th day, and according to Dan Morgan (see below), this can be overrides by running the package DBMS_FEATURE_USAGE_INTERNAL, so of cause I used that instead of waiting 7 days after every test case :). The Description for this package: “Collects usage statistics for all features”.

RESULTS: My Oracle Standard Edition did not register any use of these (question: test methods to simple? ), and I would have expected that at least one function should have been registered.

Or is it just so that this package can be used to the fullest in a Standard Edition Database? What do YOU think?

As I already pointed out. I think there are something fishy in here, and I will continue to investigate to why my Oracle Standard Edition didn’t record the use of at least one function, that use the AWRF.

We already know the facts – we cannot buy the diagnostic & tuning pack for our Standard Edition database environment, so when the documentation states that something is using the AWRF, at least I would have expected my standard edition database to register the function that I ran.

If the reason is that my test case methods is to simple – than this is just awesome, since I just redefine my test methods.

BUT if the reason is something within Oracle, that for example the registration of using something is more complicated, than running this DBMS_FEATURE_USAGE_INTERNAL package as Dan Morgan told us to, than this is not good – how on earth can a Standard Edition DBA be sure of not violating the license agreement, if neither the software nor the documentation is guiding us?

I hope you enjoyed this post, and if you would like to know more details about the tests, just keep on reading.

And to you who are not so much into details I wish you a nice Sunday!

Take care, stay well, and see you soon again.


Turku 12th April, 2015

DETAILS onTest Case on the DBMS_SPACE package

First I check the Oracle 12 License document to see what information can be found about the DBMS_SPACE package.

  • There are no specific information about DBMS_SPACE in the license document.

(Note: With Oracle, the right to use or not to use something depends on the version of the database, so remember to verify against the right database versions license document).

Next step is to check the DBMS_SPACE PL/SQL reference document to see if there might be any license related information in there:


One function in particularly – the OBJECT_GROWTH_TREND, caught my attention:


“…will be retrieved from Automatic Workload Repository Facilities (AWRF), or the current space usage will be computed and combined with space usage deltas retrieved from AWRF”

This is to my understanding pretty straight forward – This function uses AWRF, and that means the Diagnostic Pack, and that we CANNOT use in an Oracle Standard Edition.

How about the rest of the procedures/functions?

My third step; I googled a little bit more on the subject and from  this great blog (the example SQL was used in this tests) by Dan Morgan I saw this dependencies list:


The Dependencies part of this post, mentiones tables starting with WRI$, WRH$, X$,  and at least the two first prefixed tables are AWR related, so for me this is clearly a signal: more investigations are required.

Fourth step: As many of you already know, Oracle Support provide two scripts on “Database Options/Management Packs usage” (Doc ID 1317265.1), to help us find out what is actually happening in our database.

My test is very simple. After running the specific function/procedure, I ran these scripts, to see what my Oracle Standard Edition database thinks about life.

Note: from DOC:
“Note that the feature usage data in the view is updated once a week, so it may take up to 7 days for the report to show recent usage of options and/or packs.”

This can be fixed according to Morgan’s Library that has some notes about how to force an update by using


Baseline before tests: 



So after a clean installation (click-click method) there are 17 options in use by default out of 82 options (ver


Returns recommendations/findings of segment advisor run automatically by the system or manually invoked by the user


Now the check. Has my database recorded this?


Conclusion on  DBMS_SPACE.ASA_RECOMMENDATIONS Function : Nothing was recorded by Oracle Standard Edition.


Determines the cost of creating an index on an existing table


Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage


Conclusion on  DBMS_SPACE.CREATE_INDEX_COST PROCEDURE : As in TC1, nothing was recorded by Oracle Standard Edition.


Determines the size of the table given various attributes

Overload 1:


Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage


Conclusion on  DBMS_SPACE. CREATE_TABLE_COST Procedures: As in TC1, and TC2. Nothing was recorded by Oracle Standard Edition.

Test Case 4: DBMS_SPACE.FREE_BLOCKS Procedure

Information about free blocks in a table, index, or cluster. Only work on tablespaces w/o ASSM. With ASSM use dbms_space.space_usage

t4 (1)

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage


Conclusion on  DBMS_SPACE. FREE_BLOCKS Procedure : As in the previous test cases, nothing was recorded by Oracle Standard Edition.


Checks whether datafile is droppable


Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

Conclusion on  DBMS_SPACE. ISDATAFILEDROPPABLE_NAME Procedure  : As in the others test cases, nothing was recorded by Oracle Standard Edition.


Returns the list of segments that are associated with an object


Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE. OBJECT_DEPENDENT_SEGMENTS Function  : Same result as in the previous TC’s. Nothing was recorded by Oracle Standard Edition.


Returns the space used, space allocated, and percentage of chained rows in a table. 


Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE.OBJECT_SPACE_USAGE Function  : Same result as in the previous TC’s. Nothing was recorded by Oracle Standard Edition.

Test Case 8:  DBMS_SPACE.SPACE_USAGE Procedures

Shows the space usage of data blocks under the segment High Water Mark. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. This procedure can only be used on tablespaces that are created with auto segment space management.

Overload 1



Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage


SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE.SPACE_USAGE Procedures : As in previous tests. Nothing was recorded by Oracle Standard Edition.

Test Case 9: DBMS_SPACE.UNUSED_SPACE Procedure

Returns information about unused space in a table, index, or cluster 

   segown varchar2(30):='SYS';
   segname varchar2(30):='USERS_T';
   segtype varchar2(30):='TABLE';
   partname varchar2(30);
   totblock number;
   totbytes number;
   unusedbl number;
   unusedby number;
   lu_ef_id number;
   lu_eb_id number;
   lu_block number;
DBMS_SPACE.unused_space(segown, segname, segtype, totblock,
totbytes, unusedbl, unusedby, lu_ef_id, lu_eb_id,
lu_block, partname);
dbms_output.put_line('Total Blocks: ' || TO_CHAR(totblock));
dbms_output.put_line('Total Bytes: ' || TO_CHAR(totbytes));
dbms_output.put_line('Unused Blocks: ' || TO_CHAR(unusedbl));
dbms_output.put_line('Unused Bytess: ' || TO_CHAR(unusedby));
dbms_output.put_line('Last Used Extent File ID: ' ||    TO_CHAR(lu_ef_id));
dbms_output.put_line('Last Used Extent Block ID: ' || TO_CHAR(lu_eb_id));
dbms_output.put_line('Last Used Block: ' || TO_CHAR(lu_block));
Total Blocks: 8
Total Bytes: 65536
Unused Blocks: 3
Unused Bytess: 24576
Last Used Extent File ID: 6
Last Used Extent Block ID: 176
Last Used Block: 5

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage


SQL> @/home/oracle/scripts/used_options_details

<No Rows Returned>

Conclusion on  DBMS_SPACE.UNUSED_SPACE Procedure  : As in all other test cases. Nothing was recorded by Oracle Standard Edition. What would you say? Is it fare to assume it is ok to use in a standard edition database?


Pipelined table function where each row describes the space usage of the object at a specific point in time

This function on the other hand, should update the use of something that is not ok in our Standard Edition database, since it according to the documentation make use of the AWRF.

Since it’s all about history, let’s insert 150 rows to the USER_T

SQL> exec gen_data('USERS_T',150);
SQL> commit;

And now we run the dbms_space.object_growth_trend function

set linesize 121
col timepoint format a40

FROM TABLE(dbms_space.object_growth_trend('SYS', 'TAB$', 'TABLE'));
11-MAR-15 PM 8996062 12582912 INTERPOLATED 
12-MAR-15 PM 8996062 12582912 INTERPOLATED 
......(shortened the list)......
 09-APR-15 PM 8996062 12582912 INTERPOLATED 
10-APR-15 PM 8996062 12582912 GOOD 
11-APR-15 PM 8996062 12582912 PROJECTED 
12-APR-15 PM 8996062 12582912 PROJECTED 
13-APR-15 PM 8996062 12582912 PROJECTED 
14-APR-15 PM 8996062 12582912 PROJECTED 
15-APR-15 PM 8996062 12582912 PROJECTED 
36 rows selected.

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage


What! My Oracle Standard Edition database didn’t record this?


<no rows returned>

More investigations

 select NAME,
 detected_usages USED,
 to_char(first_usage_date,' hh24:mi:ss') FirstUsage,
 to_char(last_usage_date,' hh24:mi:ss') LastUsage,
 aux_count AuxCount
 from dba_feature_usage_statistics
 where detected_usages >0;

NAME                 USED FirstUsage
LastUsage           AuxCount
Automatic SGA Tuning 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Automatic Undo Management 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1 
Deferred Segment Creation 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1689 
Automatic Maintenance - Optimizer Statistics Gathering 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 
Automatic Maintenance - Space Advisor 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic Maintenance - SQL Tuning Advisor 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic Segment Space Management (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic SQL Execution Memory 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Character Set 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Locally Managed Tablespaces (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4 
Locally Managed Tablespaces (user) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Oracle Managed Files 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Oracle Multitenant 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1 
Partitioning (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4.2 
Recovery Area 5 10.04.2015 17:24:26 10.04.2015 20:07:49 5033164800 
Logfile Multiplexing 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Server Parameter File 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Virtual Private Database (VPD) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
LOB 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
SQL Plan Directive 5 10.04.2015 17:24:26 10.04.2015 20:07:49 135 
SecureFiles (user) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 325 
SecureFiles (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 374 
Heat Map 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Job Scheduler 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4 
Oracle Java Virtual Machine (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 32082 
Unified Audit 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
26 rows selected.

Conclusion on  DBMS_SPACE. OBJECT_GROWTH_TREND Function  :  Nothing was recorded by Oracle Standard Edition, which to my understanding is strange, since the PL/SQL reference guide  said:

“…will be retrieved from Automatic Workload Repository Facilities (AWRF), or the current space usage will be computed and combined with space usage deltas retrieved from AWRF”

What do YOU think about theses results?

27th May 2015: A confirmation received from an Oracle License Expert was added to this post. Scroll up to the red text, if interested.

Have a nice Sunday! Take care, stay well, and see you soon again!


Turku 12th April, 2015