Category Archives: Standard Edition

Twitter hashtags Oracle Community

Twitter Hashtag….ok, so what’s in use at the moment?

During the “twitter turbulence day” of Oracle Standard Edition (SE2) release, I and Jan Karremans had a chat about lack of knowledge about useful hashtags, and a webpage full of such hashtags would be nice.

So here are the hashtags that I am aware of:


  • When you have something that reflect our latest Oracle Standard Edition R2 (SE2), please use this. You ask why not #SE2? Well it’s already in use for other purpose. For example SE2(r) is a registered trademark. Please use the orclse2 hashtag.


  • This hashtag has been used before the #orclse2, and was addressing stuff about Oracle Standard Edition. Please use it.


  • Is there a Standard Edition Round table that you like us to know about? Please add this hashtag, and I’m sure you get the attention you hope for.


  • Since there are many amazing Oracle User Groups, arranging lots of conferences, and everybody creates their own conference hashtag, we came up with the idea of an “umbrella-hashtag”. In this way a twitter filter can easily be used to grab information about conferences that’s HOT. So if you like to get more “buzz”, please add this hashtag.


  • Whenever somebody have information addressing the formidable tool APEX, this is the hashtag to use.

Whenever a new hashtag comes to myknowledge, this page will be updated.

Take care, stay well, and enjoy the summer!

– Ann

Turku 6th of July 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

OUG Harmony15 Reflections


OUG Harmony15 Reflections

This years Harmony Conference was a result of the collaboration  between Oracle User Group Finland (=OUGF), Oracle User Group Estonia (=OUGE), and Latvian Oracle User Group (=LVOUG), and the event was held on the 11th and 12th of June in the Swissôtel Hotel in vicinity of medieval Hansa city of Tallinn in Estonia.

Thanks OUG Harmony15 organisation for accepting me as one of the 19 speakers.

This years Keynote Speaker was Graham Wood, and the other speakers were Kellyn Pot’Vin-Gorman, Brendan Tierney, Bryn Llewellyn,Kai Yu, Emre Baransel, Frank Nimphius, Jan Karremans, Juris Trošins, Luís Marques, Marcel-Jan Krijgsman, Martin Widlake, Nikolay Savvinov, Øyvind Isene, Philippe Fierens, Radu Pârvu, Robert Lockard, and Simo Vilmunen and we all were giving our best to the attendees.

speakerHarmony15 (1)     Thanks to OUG Harmony

Ilmar Kerm and his girlfriend were amazing hosts, and thanks for both showing the Town of Tallinn and the Viru Bog Study trail to us, as well as sharing your thoughts with us. The speakers dinner party was also well-arranged and of high standard, as well as the conference. Thanks to you all!

Copyright/AnnSjökvist A Sniper can it be compared with Standard Edition? 

After talking to Robert Lockard I needed to check the definition on “sniper”, and couldn’t help myself for letting my imagination fly away.

The definition of a sniper (I took the liberty of replacing one word, and apologies if by doing so I offend anybody)

“A sniper is a marksman or qualified specialist who operates alone, in a pair, or with a sniper team to maintain close visual contact with the “community” …

As a Standard Edition advocate and an old sportsperson in shooting, I could not just resist this comparison. Strange humour of mine. 🙂

Thanks Robert for bringing your thoughts to the table. They made me realise things I never had thought about in that context.

My own talk on Standard Edition was attended by 20 listeners. I really enjoyed the talk, and it felt as I was able to awaken the awareness of Standard Edition pitfalls, opportunities, and challenges. Thanks to all who were attending.

The fourth Standard Edition Round table was also a success. The room was almost full, just a couple of seats left. Even thought there was no official feedback collected, we got positive feedback from the attendees, so yes we felt together with Jan and Philippe that we did a good job. Thanks guys for yet a great RT! (c)       Need for Collaboration

Another memorable discussions I had, was with Nikolay Savvinov on how to increase the collaboration between different people within the business society. Thanks Nikolay for bringing your thoughts to the table. They made me realise things I never had thought about.

I don’t know how many of you have had a Finnish Sauna experience, but it’s common that people, even strangers just join the crowd in the Finnish sauna wearing nothing but their birthday suit (=no clothes). By doing so, everybody is equal and everybody can join the conversation without preconceptions.

Maybe if we all would be able to enjoy a “mental Finnish sauna” at work, we would be able to collaborate a little better. We might be able to listen to our team members despite the genre they represent – ceo, dba, developer, customer, scientist, solution conductor etc.

What do you think? Would the business society benefit if people could let loose and start enjoy a “mental Finnish sauna”? Would it help us start listening and provide more respect to other people’s views and opinions?

So until next conference, take care, stay well and enjoy your week!


Turku 17th of 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

Engage Big Data in Standard Edition

bigdata_5v  No need to hesitate

Time to Engage Big Data in Standard Edition

Last week I asked “Big Data in Standard Edition Seriously” and after one week of studying at EMC Education Centre, I truly believe that smaller companies should start to embrace the opportunities that this technology can bring.

When asking different people the question “What is  Big Data?“,  many different explanations will be provided. But the one I received from EMC Education Centre, opened my mindset about the Big Data concept:

Big Data is really about ANY attribute that challenges the constraints of a system capability or business need

It’s not only about size – examples

spreadsheetI have a 50 MB spreadsheet document that I cannot share it with my colleague via e-mail or a shared storage due to limitations of space – I just bumped into the “challenge of a system capability”

marketingMy CMO assistant made a 1,1TB marketing ad, that cannot be displayed remotely in real time to the board of Executives – once again, I just bumped into the “challenge of a system capability”

timeglassMy CMO came up with a new service, and a new 1PB marketing video need to be edited and rendered, but the Editor’s job is interrupted due to lack of resources on his computer – once again, I just bumped into the “challenge of a system capability”

So to my understanding, at all three examples we are facing “Big Data”.

One other thing that EMC Education pointed out to me, is :

“How Can We Get Closer to Big Data & Start Taking Advantage of it?”

That question I think, is not an exclusive property for big enterprises , but also applicable to smaller companies who is running their applications on an Oracle Standard Edition Database environment.

Since I am a novice on Big Data, and in the beginning of my learning path, I asked one of the instructors Barry Heller about his thoughts on how a small company could start to use their “Big Data”, to grow their understanding and sees the opportunities of the insight such analyse will bring:

  • what new services should they implement?
  • how to grow their business?
  • do we need to change our processes or create new ones?

Advices from the expert Barry Heller (c)

  • identify one pain point or opportunity within a company and produce a quick win.
  • Possible projects may be to build a
    • recommender system,
    • reduce customer churn,
    • identify upselling
    • cross-selling opportunities
  • For statistical analyses
    • the R programming language is a great place to start.  
    • the Apache Hadoop ecoystem is great place to start for
      • storing
      • processing
      • analysing Big Data.  

Beyond the technology, another important aspect is how the project will be staffed; small companies may not have the expertise in-house.

Thanks Barry for guidance, and to me, this sounds like an opportunity to all companies running an Oracle Standard Edition Database; Big Data is available to all business – it’s not about the size.

So why not start thinking about transforming your data to the next level, and look into new business options/possibilities?

Even as a novice, I must say THAT would be a great challenge to have a Standard Edition Database Environment, and start a Big Data project, to find new opportunities of any kind to the company!


Take care, stay well and enjoy this Monday!

– Ann

Turku 18th May 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