Gluent New World #04: Next Generation Oracle Database Architectures using Super-Fast Storage with James Morle

It’s time to announce the 4th episode of Gluent New World webinar series by James Morle! James is a database/storage visionary and has been actively contributing to Oracle database scene for over 20 years – including his unique book Scaling Oracle 8i that gave a full-stack overview of how different layers of your database platform worked and performed together.

The topic for this webinar is:

When the Rules Change: Next Generation Oracle Database Architectures using Super-Fast Storage

Speaker:

What’s altering my table……



We had an unusual problem with datapump this week when exporting a large schema. A couple of small 'static data' tables were consistently throwing the error:

ORA-01466: unable to read data - table definition has changed

These tables were done towards the end of the export (which ran for 3 hours by the way even with parallel 6) so i could more understand a snapshot tool old error but this was actually saying the table (more...)

Bitmap Counts

A question came up on the Oracle-L list server a few days ago about a query whose plan showed several bitmap operations. The problem was that the A-Rows column reported by a call to dbms_xplan.display_cursor() was showing numbers that semed to be far too small. In fact the query was producing a parallel execution plan, so the “actuals” for the parallel server operations were reporting zeros because the OP had used the “allstats last” (more...)

AWR analysis: another case study

A few weeks ago, I received a request to review an AWR report for a database suffering from instance-level performance issues. Here are the the key parts of that report (with some masking):

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
XXXX           XXXXX     XXXXX               1 10.2.0.5.0  NO  XXXX

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
 (more...)

IPython/Jupyter Notebooks for Oracle

Topic: In this short post you can find examples of how to use IPython/Jupyter notebooks for running SQL on Oracle.

IPython/Jupyter notebooks are one of the leading free platforms for data analysis, with many advantages, notably the interactive web-based interface and a large ecosystem of readily available packages for data analysis and visualization. Moreover IPython/Jupyter notebooks are a very handy format for sharing code and data as you will see in the examples.
See also (more...)

8 queens chess problem: solution in Oracle SQL

This is just another solution of this problem for a chessboard, but you can choose any size of the checkerboard:

with 
 t as (select level i, cast(level as varchar2(1)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(98+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level  (more...)

GoldenGate 12.2 cases and quotes

Avoid using quotes and case sensitivities if you can.

Process Abends : OGG-00919 Error in COLMAP clause referring to : @GETENV(“GGHEADER”…. (Doc ID 1635935.1)

1. Replace double quote with single quote.

Change reference is GETENV statement to use single quotes rather than double quotes, as example is :

@GETENV(‘GGHEADER’,’BEFOREAFTERINDICATOR’)

2. Specify NOUSEANSISQLQUOTES in GLOBALS parameter.

The default is now USEANSISQLQUOTES.

USEANSISQLQUOTES

When capturing and mapping object names, such as table names, Oracle GoldenGate always recognizes (more...)

GoldenGate 12.2 discard vs exception table

I have seen several blogs demonstrating using database exception table for error handling.

Truth be told, I have never used the exception table method and curious to know what’s the difference.

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

First, there’s manual work to create database exception table for error handling.

Let’ me know how long it takes you to configure database exception table for error handling.

Second, with new features, you (more...)

GoldenGate 12.2 checkprm and oggerr

At first, I was wondering what’s the true benefit of the new feature (checkprm) since there exists CHECKPARAMS which does this.

For someone lazy like me, it means not having to modify code to add and remove CHECKPARAMS from GoldenGate parameter files.

Once, there was oerr and now there is oggerr to look up error codes.

Reference: Oracle GoldenGate Release Notes for 12c (12.2.0.1)

$ checkprm dirprm/e_hawk.prm

2016-06-11 07:29:47  INFO    OGG-02095  Successfully set  (more...)

Moving to Seattle! Our beautiful rental available in San Francisco

27552681126_8d52a09180_z

More news coming, but for now, we (family and I) are moving to Seattle! We will be giving up our gorgeous rental house in San Francisco thus it will be available to the next lucky family.

Elegant mediterranean style family home on a quiet tree lined street in a sweet residential neighborhood just minutes from downtown and the Peninsula. 

Photos of house

.

Perfect location for commuting to Peninsula on 280  or downtown SF (more...)

Assertions in a future Oracle release

I just found this link on OTN to vote for including assertions in a future release of the Oracle database.

A great idea – please vote for it.

One of the most important votes this month…well, I do live in England! Smile

New Canberra Event

For those that are based in or near Canberra, I’ve created a new page on my website to host information on upcoming events that might be of interest to you. The first of these is a session for Oracle partners on the data challenges that customers face. Just to give you a few more reasons to come along, it’s also the first in a series of Beer and Pizza nights we will be hosting, so (more...)

“Unstructured Data” – No such thing!

I keep hearing this term lately and I dislike it.

There is no such thing as Unstructured Data. All data has structure. If it didn’t have structure we wouldn’t be able to use it.

What about free text? Well, that’s just a single column value (stored in a CLOB in Oracle, for example) and the free text is, more often than not, on a row with other columns, such as identifiers and timestamps, i.e. (more...)

My Glamorous Life : What’s the point?

Last night I put out a video of my trip home.

I’ve written about the My Glamorous Life series of videos before (here). A couple of people commented recently that “nothing happens in them” and they are “quite boring”. That’s the point really. 🙂

  • The travelling sounds kind-of glamorous, but most of it is very dull. Being at different places is great. Travelling to and from them is as dull as ditch water. A (more...)

Uniquely parallel

Here’s a surprising (to me) execution plan from 12.1.0.2 – parallel execution to find one row in a table using a unique scan of a unique index – produced by running the following script (data creation SQL to follow):


set serveroutput off
set linesize 180
set trimspool on
set pagesize 60

alter session set statistics_level = all;

variable b1 number
exec :b1 := 50000

select /*+ parallel (3) */ id, v1 from  (more...)

Parsing … no big deal eh ?

Most of us have probably seen the standard demo when it comes to emphasizing the need for sharable SQL, aka, using bind variables where appropriate.  The demo traditionally compares two similar scripts, where one of them generates a lot of SQL statements with literals, and the other recasts the same script with bind variables for dramatic improvement.

Here’s a simple version I’ve whipped up:


SQL> create table t ( x int primary key) organization  (more...)

GPIOZero on Non-Zero Raspberry Pi

So there are some cool features that are built into the GPIOZero library/module.  One of the challenges I think many of us that have a lot of projects that we want to work on, is that we end up having to translate it to the version of RPI that we just happen to be working on or have available.  This can be frustrating when the code is available (more...)

Combining Features – Wrong Results With Scalar Subquery Caching

Quite often you can get into trouble with Oracle when you start combining different features.In this case of one my clients it is the combination of user-defined PL/SQL functions that can raise exceptions (think of currency conversion and a non-existent currency code gets passed into the function), DML error logging and attempting to improve performance by wrapping the PL/SQL function call into a scalar subquery to benefit from the built-in scalar subquery caching feature (more...)

My New Udemy course "Oracle Private Database Cloud"


Friends,


Happy to announce that I have created and published my very first Udemy course on the "Oracle Private Database cloud" as an Udemy Premium Instructor. I hope you find this course beneficial.

About This Course:

Published 6/2016 English
https://www.udemy.com/oracle-private-database-cloud/



Course Description:

This course will introduce you to the concepts of the Oracle Private Database cloud. You will be taught techniques for building, configuring, and managing a private database cloud with Oracle Enterprise (more...)

12c FETCH PERCENT

A nice little feature in 12c is the FETCH FIRST n ROWS syntax, which is a simple shorthand to avoid using inline views and the like to get a subset of the rows from what would normally be a larger resultset.

Here’s a simple example showing the syntax


SQL> select *
  2  from t
  3  order by 1
  4  fetch first 8 rows only;

         R
----------
         1
         2
         3
         4
         5
         6
         7
         8

8  (more...)