SLOB Patch 2014.09.01 – AWR Post-Processing Script (awr_info.sh) Fix.

The SLOB kit comes with a little script that extracts interesting information from the awr.txt file produced at the end of a SLOB test. This is just a quick blog entry to point folks to a patched version of awr_info.sh that works properly with all Oracle Database 11g releases as well as Oracle Database 12c.

Oracle changed AWR format in the 11.2.0.4 and 12c releases so the old awr_info.sh (more...)

Oracle 12.1.0.2 and Data Warehouses

If you follow Blogs and Tweets from the Oracle community you won’t have missed hearing about the recent release of the first patch-set for Oracle 12c. With this release there are some significant pieces of new functionality that will be of interest to Data Warehouse DBAs and architects. The headline feature that most Oracle followers will know of is the new in-memory option. In my opinion this is a game-changer for how we design reporting (more...)

SQL> Select * From Alert_XML_Errors;

Once you are able to show the xml version of the alert log as data in database table Alert_XML, it would be nice to checkout the errors with accompanying timestamps from within view Alert_XML_Errors. Like this, with the help of 2 types and a pipelined function.

su - oracle
. oraenv [ orcl ]
[oracle@localhost ~]$ sqlplus harry/*****
....
SQL> desc alert_xml
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TEXT                                               VARCHAR2(400 CHAR)

SQL> CREATE OR REPLACE  (more...)

Webinar Followup (Aug. 27): In Search of Plan Stability – Part 1

Thanks to everyone who attended my August 27th webinar entitled In Search of Plan Stability - Part 1. You can download the presentation materials from these links:

Presentation PDF
Scripts
Q&A

I'll update this post to provide a link to the recording shortly.

Come back in November for Part 2. Hope to see you then!

Thanks!

DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity in 12c

12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I'm going to use it to see how temporal validity queries are actually executed. All tests are done using 12.1.0.2 Enterprise Edition.

First I'll create a table for (more...)

SQL> Select * From Alert_XML;

By mapping an external table to some text file, you can view the file contents as if it were
data in a database table. External tables are available since Oracle 9i Database, and from Oracle
11gR2 Database on, it is even possible to do some inline preprocessing on the file.

The following example of this feature picks up on standard output of shell script “get_alert_xml.sh”.
It isn’t referencing any file, but take notice of (more...)

My Speaking Schedule for Oracle Open World 2014

A quick post to let you know about the two presentations that I’ll be doing at Oracle Open World 2014.

Session ID:         UGF4482
Session Title:     “Getting Started with SQL Pattern Matching in Oracle Database 12c
Venue / Room:  Moscone South – 301
Date and Time:  9/28/14, 13:30 – 14:15

Session ID:          CON4493
Session Title:      “Regular Expressions (more...)

In-memory Consistency

A comment on one of my early blogs about the 12c in-memory database option asked how Oracle would deal with read-consistency. I came up with a couple of comments outlining the sort of thing I would look for in a solution, and this note is an outline on how I started to tackle the question – with a couple of the subsequent observations. The data is (nearly) the same as the data I generated for my previous article on the (more...)

AIOUG Tech Day at Hyderabad (Aug 23rd)

Hi there, Last week, I spoke at AIOUG Tech Day at Hyderabad. All India Oracle Users Group (AIOUG) in association with TechMahindra organized one full day of techtalk event. Believe me, the Danube auditorium was a state-of-art facility with 500 capacity. Fabulous venue and huge crowd with similar interest. I thought of sharing my experience … Continue reading

The Next Big Thing

Oracle’s 12.1.0.2 was released a few weeks ago (You can download it from OTN here: Oracle 12.1.0.2 Download). While technically a minor point release, it contains a couple of major features that would normally be rolled out in a more substantial version change like 12cR2 or perhaps V13. Of course the most highly anticipated feature is a new option (Oracle In-Memory Option) that provides a column (more...)

Sqlnet tracing during nightly hours…

A TNS error at night…

Sometime ago my data warehouse colleague came to me with a TNS error. At night times he runs his batch jobs in order to update his data warehouse. That night one of his jobs did not run properly and generated an ORA-12592 error. He had to restart this job during daytime.

It turned out it was not the only occurrence of this TNS error. A couple of days later he (more...)

%sql: To Pandas and Back

A Pandas DataFrame has a nice to_sql(table_name, sqlalchemy_engine) method that saves itself to a database.

The only trouble is that coming up with the SQLAlchemy Engine object is a little bit of a pain, and if you're using the IPython %sql magic, your %sql session already has an SQLAlchemy engine anyway. So I created a bogus PERSIST pseudo-SQL command that simply calls to_sql with the open database connection:

%sql PERSIST mydataframe

The result is (more...)

Renaming an Oracle Apache Target in EM12c

When installing Enterprise Manager 12c, the host value can come from a number of places for different applications/tiers.  For most, it comes from the environment variable $ORACLE_HOSTNAME, (for Windows Servers, %ORACLE_HOSTNAME%).

The OHS1 target, aka Oracle Apache in the middle tier of the EM12c environment pulls it’s value from the etc/hosts file, (for Unix as well as Windows) and so it is vulnerable with a virtual host name or host name change occurs.  (more...)

Dealing with Wi-Fi Problems the DBA Kevlar Way

As much as KSCOPE 14 fixed any and all Low-T levels, it couldn’t do anything for our the WiFi problems we were experiencing back in May. I’ve been a telecommuter for almost 4 straight years now and was quite frustrated when our WiFi service became dismal back then.

We called Comcast service and worked through trouble-shooting with their support multiple times, only afterward, still unable to download files or watch Netflix into the first three (more...)

Integrated Application Heartbeat for Oracle GoldenGate

Over the last two weeks I’ve been working on a heartbeat monitoring solution for a client.  This is not the normal heartbeat solution provided by Oracle as described in note 1299679.1; yet very similar.  The approach that I configured is similar to a traditional heartbeat setup but uses the same extract and replicats already being used by the database/application.  For simplicity reasons, I like to call this approach an Integrated Application Heartbeat.

In order (more...)

Autoformat ANY amount item, anywhere

If you’re building a “finance-ey” application you probably have plenty of fields that should show and accept monetary amounts – and quite possibly these items may be implemented in a variety of ways – ordinary apex number items, edit fields in tabular reports, or even dynamically generated items using APEX_ITEM.text.

In my case I had all three, scattered throughout the application. Our users routinely deal with multi-million dollar amounts and they had trouble checking the amounts visually, (more...)

Mobile Apps for Oracle E-Business Suite

First-rate announcement from Oracle through Steven Chan Blog for Mobile Apps strategy on Oracle eBusiness Suite.  With the rapid advent of social media and our behavior to see everything on smart phones. We as users of Oracle eBusiness suite started feeling old fashioned with the release of Oracle Fusion Applications where everything was coming for [...]

The post Mobile Apps for Oracle E-Business Suite appeared first on ORAERP.COM - Blogs.

★ How BIG is Oracle OpenWorld?

Oracle OpenWorld

Here is how big it was in 2013. Compare it to its size in 2012. It is safe to assume that it will be bigger in 2014!

I will attend this year’s event by invitation from the Oracle ACE Program. Prior to the start of the conference, I will be attending a two day product briefing with product teams at Oracle HQ. It’s like a mini OpenWorld but only for Oracle ACE Directors.

During the (more...)

Remote DML with DBMS_PARALLEL_EXECUTE

A comparison of sucking data into a table over a db link using DBMS_PARALLEL_EXECUTE.

This particular example is based on something I needed to do in the real world, copying data from one database into another over a db link. Datapump is not available to me. Tables in question happen to be partitioned by a date-like number (boo!) hence some of the specific actions in the detail.

I think it’s a good example of (more...)

Adapt – Learn New Things

Nothing lasts forever.  Sand-piles crumble.  Companies rise and fall.  Relationships change.  Markets come and go.  It’s just the nature of things.  Adapt or die.  Personally, I like this feature of life…can’t imagine anything worse than stagnation.  There’s nothing better to me than exploring and learning new things.

As Oracle continues their push into cloud-based enterprise applications, we’re seeing some of that fundamental change play out in the (more...)