My OTN 2 Minute Tech Tip: Predictive Queries

A few days ago I recorded a 2-minute tech tip with Bob Hubbard of OTN.

My topic was on Predictive Queries which are a new feature in the Oracle 12c Database.

The challenge was to talk about the topic within 2 minutes. That is a lot harder than you time. Believe me.

Check out the video on the Bobs OTN 2-Minute Tech Tip channel or click on the link below.

It was fun doing this (more...)

Call vs Exec

Just a reference to a really simple difference between CALL and EXEC.
I thought I had mentioned this before but couldn’t find it so…

EXEC/EXECUTE is a SQL*Plus command which wraps the proc call in an anonymous BEGIN … END; block.

CALL is a SQL command hence it is limited to SQL data types and there are other restrictions which the documentation sums up pretty well.

Because CALL is SQL, there is on key behavioural (more...)

GGSCI Commands : History and Command (!) recall

Ever get tired of typing commands?  Ever wish there was a way to recall commands from GGSCI?  Well, you are in luck!  There are two ways this can be done (listed below).

Commands:
1. History
2. ! (command)

The first option, History, is a command that allows you to view a list of the most recent commands executed in the GGSCI interface since the session started.  

Example:

GGSCI> history

Output:

GGSCI Command History

    (more...)

Non-CDB Architecture Is Officially Deprecated….

Yeah, it’s indeed true and official. So if you still haven’t started reading and learning Multitenant architecture introduced in 12.1.0.1 and enhanced in 12.1.0.2, it’s high time that you start doing it. Not because that it’s new but because the old architecture you are familiar with, it’s officially on the verge of extinction.

Oracle Database 12c Patching: DBMS_QOPATCH, OPATCH_XML_INV, and datapatch

Background

Oracle Database 12c brings us many new features including: the long needed ability to run OPatch and query the patch software installed in the Oracle Home programatically through SQL using the new DBMS_QOPATCH package.

If you’re a DBA working in an environment where patching consistently among databases is important, this is a welcomed enhancement. Sometimes one-off critical bug fix patches are important in the environment. Other times regular and consistent application of the quarterly (more...)

Oracle GoldenGate Processes – Part 4 – Replicat

The replicat process is the apply process within the Oracle GoldenGate environment.  The replicat is responsible for reading the remote trail files and applying the data found in cronilogical order.  This ensures that the data is applied in the same order it was captured.  

Until recently there was only one version of a replicat, that version was the classic version.  As of 12.1.2.0, there are now three distinct versions of a (more...)

Oracle GoldenGate Processes – Part 3 – Data Pump

The Data Pump group is an secondary extract group that is used to help send data over a network.  Although a data pump is another extract group, don’t confuse it with the primary extrat group. The main purpose of the data pump extract is to write the captured data over the network to the remote trail files on the target system.  

Note: if the data pump is not confgured then the primary extract group (more...)

Oracle GoldenGate Processes – Part 2 – Extract

The extract process of Oracle GoldenGate is used to perform change data capture from the source database.  The extract can be used to read the online transaction log (in Oracle the online redo logs) or the associated archive logs.  The data that is extracted from the source database is then placed into an trail file (another topic for a post) for shipping to the apply sided. 

To configure an extract process there needs to (more...)

Performance Problems with Dynamic Statistics in Oracle 12c

I’ve been making some tests recently with the new Oracle 12.1.0.2 In-Memory option and have been faced with an unexpected  performance problem.  Here is a test case:

create table tst_1 as
with q as (select 1 from dual connect by level <= 100000)
select rownum id, 12345 val, mod(rownum,1000) ref_id  from q,q
where rownum <= 200000000;

Table created.

create table tst_2 as select rownum ref_id, lpad(rownum,10, 'a') name, rownum || 'a'  (more...)

Oracle 12c column upgrades

While playing with 12c I tried the upgrade to the DEFAULT column syntax that now allows sequences.

I came across a basic error, but it's just a small trap for new players.

CREATE TABLE seq_test(a NUMBER)
/

ALTER TABLE seq_test MODIFY (a NUMBER DEFAULT sage_seq.NEXTVAL)
/

SQL Error: ORA-02262: ORA-2289 occurs while type-checking column default value expression

*Cause: New column datatype causes type-checking error for existing column
default value expression.
*Action: Remove the default (more...)

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions (more...)

LGWR terminating instance due to error 338

Recently we came across a issue where our DB crashed with ORA-00338 error . Errors in file /oracle/diag/rdbms/orcl11g/orc11g/trace/orc11g_lgwr_24118.trc: ORA-00338: log 2 of thread 1 is more recent than control file ORA-00312: online log 2 thread 1: '/oracle/oradata/orcl11g/redo02.log' LGWR (ospid: 24118): terminating the instance due to error 338 DB couldn't be restarted as it gave same errors…

Oracle 12c Implicit Result Sets in SQL Developer 4.1

Ever want to run a Stored Procedure or a PL/SQL anonymous block and just want to "print out" the results of a query ?  Even as a little bit of debug information?

In Oracle 11g you have to create a SQL*Plus REFCURSOR variable and then bind it within the anonymous block  or  pass it as an argument to a procedure/function. Run the code and then print the refcursor.

This requires a bit of know how in (more...)

Is Oracle Database Time Correct? Something Is Not Quite Right.

Is Oracle Database Time Correct? Something Is Not Quite Right.


Oracle Database tuning and performance analysis is usually based on time. As I blogged HERE, the Oracle "database time" statistic is more interesting than simply "time spent in the database." It is the sum of CPU consumption and non-idle wait time. And Elapsed Time is the sum of all the database time related to perhaps a session or a SQL statement execution. However...

Installing ORDS 3.0 (Early adopter) in Weblogic.

Now the Oracle REST Data Services 3.0 Early Adopter is available, let us have a look how it’s to be installed in Weblogic 12c!

I’ve ommitted the installation or upgrade of Apex here as there is plenty of documentation on that topic, and the ORDS can be applied for other uses than being an Apex Listener since v2.0.4.

Installing ORDS

Download the Application Express and ORDS 3.0 installer from the Oracle (more...)

Oracle 12.1.0.2 Bundle Patching

I’ve spent a few days playing with patching 12.1.0.2 with the so called “Database Patch for Engineered Systems and Database In-Memory”. Lets skip over why these not necessarily related feature sets should be bundled together into effectively a Bundle Patch.

First I was testing going from 12.1.0.2.1 to BP2 or 12.1.0.2.2. Then as soon as I’d done that of course BP3 was released.

So (more...)

Installing Oracle 12.1.0.2 – Part 2: Creating an Instance

In the earlier post we installed the Oracle version 12.1.0.2 (software only) but still didn’t create a database instance. In this post we will go over an installation procedure of a basic CDB database instance. This will be the first instance on that database server (and I’ll explain later why this is important).

For installing and configuring the database instance we will use one of my favorite tools – the DBCA: Database (more...)

Installing Oracle 12.1.0.2 – Part 1

It’s been a few months since 12.1.0.2 came out. Here is a basic guide for installing Oracle 12c (12.1.0.2).
This is a two-part post – in the first part we will install the software and in the next part we’ll go over creating the new database instance.

When I first looked at the installation steps of the new version, it didn’t look much different from earlier versions (version 11 (more...)

12.1.0.2 Introduction to Zone Maps Part III (Little By Little)

I’ve previously discussed the new Zone Map database feature and how they work in a similar manner to Exadata Storage indexes. Just like Storage Indexes (and conventional indexes for that manner), they work best when the data is well clustered in relation to the Zone Map or index. By having the data in the table […]

ECO 2014 Slides

Just a quick note that I posted slides for the 2 talks I did at ECO in Raleigh this week:

Keynote: Creative Problem Solving (for Oracle Systems)

In-Memory In Action (without Tanel Poder) :)

Great crowd. I really enjoyed myself.

Note: You can also find other presentations on my Whitepapers/Presentations page via the link at the top of the screen.