OTN Yathra 2015 just got over!!

Quite well said – Investment in knowledge is the gives you the best returns. It was a great fun last week to be the speaker at OTN Yathra 2015, organized by All India Oracle Users Group (AIOUG). The OTN tour covered seven cities and I was the part of it at three cities i.e. Hyderabad, Bangalore … Continue reading

SQL Developer: Viewing Trace Files

Just a quick plug for looking at raw sql trace files via SQL Developer.

There is a nice Tree View:
sqldev_trace

Which can be expanded:
sqldev_trace_expand

Also summary view of statistics, filterable:
sqldev_trace_stats

And a list view, filterable and orderable:

sqldev_trace_list

Some sort of right click summary for binds/waits might be a nice addition.


Exadata Documentation Available

Please join me in welcoming the Exadata product documentation to the internet. It’s been a long time coming, but glad it’s finally made an appearance!

Real time sql monitoring – bulk bound statement

Moving through some observations of an ongoing investigation tackling the easiest ones first.

Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.

Statement was sql id 9v9n97qj8z1dg:

	
delete /*+ dynamic_sampling(4) */
from sys.wri$_optstat_histhead_history
where savtime < :1
and rownum <= NVL(:2, rownum);

From the real time sql (more...)

COUNT STOPKEY – NVL

Yesterday I mentioned issues with a recursive delete operation on statistics history.

This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.

COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.

For example:

create table t1 as select * from dba_objects;

alter session set statistics_level = all;

 (more...)

Goldengate: Problems with character sets

One complication that you may face with replicating data using Goldengate (or other tools) is when your source character set is different to your destination character set. This is particularly true when the source character set is UTF-8 and the destination is not.

If the application does not sanitise (or you do not want to sanitise) inputs to restrict them to the lowest common denominator within your systems, you will need to ensure that you (more...)

Meet me in Austin?

Late breaking news (aka I forgot to post this earlier)! I will be speaking at the first meeting of the Austin Oracle Users Group for 2015. It will be this coming Friday February 27, 2015 starting at 11:00 AM. I will give an intro talk on Oracle SQL Developer Data Modeler (the FREE data modeling […]

Understanding vs Resolution – Statistics History Cleanup

Today I helped resolve a problem quickly but to the detriment of my understanding of exactly what was going on and why.

And as part of the quicker resolution, I had to destroy the evidence which would have helped understand better.

So… now need to go back and figure it out if I have time to model it properly, etc.

Here’s what little I know so far.

What happened was that there were a number (more...)

Exadata X5 – A Practical Point of View of the New Hardware and Licensing

Oracle recently announced its latest iteration of Exadata – X5-2. It includes a refresh of the hardware to the most recent Xeon® E5-2699 v3 CPUs. These new CPUs boost the total cores count in a full rack to 288. This is higher than the current 8 socket “big machine” version X4-8, which has only 240 cores.

But the most exciting part is the all flash version of Exadata. In the previous generation – X4 – (more...)

Exadata 12c New Features RMOUG Slides

I've finally gotten around to post my RMOUG Slide Deck on Slideshare. Hopefully this is helpful to folks looking at new features in Exadata.

Running two oracle installations from the same terminal

Two posts from me on the same day. The other one about Datapatch is about a brand new utility in 12c and is probably new to most people. This post caused mixed reactions when I mentioned it at work last week. Some people laughed at my naivety in not knowing about it, others took the same view as me and were interested to hear about it as it may prove useful one day.

A colleague (more...)

Issue with Datapatch – AKA SQL Patching Tool after cloning a database

There have been a few changes in the way patches are managed and monitored in 12c and whilst looking at this I found a potential problem that might occur when you clone or copy databases around, or even build them from a template file.

Firstly when you apply a PSU and run an opatch lsinventory command you now see a description of the patch rather than just a patch number – here showing that PSU (more...)

Oracle Manual Standby – Applying Log

If you are running Oracle EE, there are many books on Data Guard. However, for Oracle SE and manual standby, have fun searching.

I wanted to get a better understanding of registering logfile.

Depending on how the standby environment is monitored, not registering logfile may yield incorrect results when checking standby lag.

When logfile is not registered, v$archived_log is not updated.

STANDBY: check status

[oracle@armor:hawk:/home/oracle]
$ sqlplus / as sysdba @stby.sql

SQL*Plus: Release 11. (more...)

Connections Types in SQLcl

Uncategorized
| Feb 20, 2015

We support many ways to connect in SQLcl, including lots from SQL*Plus which we need to support to make sure all your SQL*Plus scripts work exactly the same way using SQLcl as with SQL*Plus.

I've added several ways to show how to connect to SQLcl.  If there is one you want to see added that is not here, let me know and I'll add it to the list.  So far, We have below:

opatch lsinventory gives “line 384: [: =: unary operator expected”

I noticed the error message when running lsinventory against a  12.1.0.2 Oracle_Home. As the command worked I didn’t think anymore of it until on the same server against an 11.2.0.1 home I got the same error message.

opatch lsinventory
 tr: extra operand `y'
 Try `tr --help' for more information.
 /app/oracle/product/11.2.0.1/dbhome_1/OPatch/opatch: line 384: [: =: unary operator expected

There is a Mos note which provides a solution (more...)

255 columns

Here’s a quick note, written and some strange time in (my) morning in Hpng Kong airport as I wait for my next flight – all spelling, grammar, and factual errors will be attributed to jet-lag or something.

And a happy new year to my Chinese readers.

You all know that having more than 255 columns in a table is a Bad Thing ™ – and surprisingly you don’t even have to get to 255 to (more...)

Functions bar NDS

My students asked if you could embed an OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in (more...)

Changing a database link password

I recently found out  that it is possible to change a database link password without dropping and recreating a database link in its entirety.

To be honest I thought this might have existed forever and I had just never come across it but it actually come out in 11GR2

The ALTER DATABASE LINK statement can be used and you do not need to specify the target service either  – all you need  is to run (more...)

New ASM power levels in 11.2.0.2 and beyond

I recently saw the following command in a script that was to be run and thought an error had been made and the power level should have been 5 not 500.

ALTER DISKGROUP DATA REBALANCE POWER 500;

Upon doing some research it was not a mistype but a new method of disk balancing which came in from 11.2.0.2

Previously setting the power limit from 0 to 11 basically caused an additional number (more...)

Lets catch up at OTNYathra 2015

All, Its time to be at AIOUG stage once again and the event would be the OTNYathra 2015. The event has been a great success in the past and has been able to generate/receive wide recognition and appreciation. OTNYathra focuses to evangelize the Oracle technologies to a broader and passionate audience. OTNYathra is a grand tour which is conducted … Continue reading