Search

Top 60 Oracle Blogs

Recent comments

May 2015

Lab Report: Oracle Database on EMC XtremIO. A Compression Technology Case Study.

If you are interested in array-level data reduction services and how such technology mixes with Oracle Database application-level compression (such as Advanced Compression Option), I offer the link below to an EMC Lab Report on this very topic.

To read the entire Lab Report please click the following link:   Click Here.

The following is an excerpt from the Lab Report:

Executive Summary
EMC XtremIO storage array offers powerful data reduction features. In addition to thin provisioning, XtremIO applies both deduplication and compression algorithms to blocks of data when they are ingested into the array. These features are always on and intrinsic to the array. There is no added licensing, no tuning nor configuration involved when it comes to XtremIO data reduction.

Smart Scan enhancements in Oracle Exadata 12c-inline LOBs

As part of the 12c release of the database and cellos Oracle introduced the ability to perform Smart Scans on inline LOBs. The enhancement is certainly for Secure Files only. And as always, if you would like to read up about Secure Files you can head over to Tim Hall’s website for the details.

To demonstrate the new behaviour I have used an 11.2.0.3 BP 22 database on Exadata 11.2.3.3.1 and 12.1.0.2.2 database on Exadata 12.1.2.1.0. The test case evolves around the following table:

CREATE TABLE loboffload (
 id number primary key,
 comments clob)
enable row movement 
tablespace users
lob (comments) store as securefile (
 enable storage in row
);

This is the short version, Oracle fills in the blanks and converts the table DDL to

Temp Table Transformation Cardinality Estimates - 1

Having published recently two notes about the Temp Table Transformation highlighting the heuristics based decision and other weaknesses, for example regarding the projection of columns, it's time to publish some more notes about it.The transformation can also have significant impact on cardinality estimates, both join and single table cardinality.Looking at the difference in the join cardinality estimates of following simple example:


create table t1
as
select
rownum as id
, mod(rownum, 10) + 1 as id2
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000
;

Datapump TTS quick tip

I was trying to transport a large number of tablespace datafiles from one database to another, and impdp kept crashing with “PL/SQL: numeric or value error: character string buffer too small.”

It turns out there is a limit on what you can put on a single line in a parameter file.  Hence if you have something like:

Oracle 12c – PL/SQL Implicit Result Sets

Implicit Result Sets

Beginning with Oracle 12c it is possible for SELECT statements to send results back to a client from PL/SQL without need for REF CURSOR definitions. Previously, PL/SQL required definition of a REF CURSOR as an OUT parameter adding complexity (and precision) not found in other products (argh… the older I get the more I believe that a module should do exactly one thing (and do it well)). However, it is common in some database products to use a Stored Procedure to return multiple result sets.

Using Oracle 12c’s newly implemented implicit result sets will allow reuse of SQL Server (and other database) stored procedures that return implicit result sets. Implicit result sets were originally targeted at JDBC clients but will probably work well in other environments like C#.NET or SQL Server TSQL.

From Product X to SQL Developer

I recently worked at a company that used "Product X" for all of it SQL and PL/SQL activities.  There’s no real need to reveal what "Product X" is, because this isn’t a post about whether as a product it was good, bad or somewhere in between.  "Product X" met the needs of the Oracle developers in the company, and that’s all that matters.  There was just one issue – for Product X, the company was paying a little under $100k annually in license fees.

EM Express versus EM Cloud Control

Recently I was asked the question “What is the real difference between EM Cloud Control [NOTE: I’ll refer to this as EM12c through the rest of this post] and EM Database Express in 12c?” It was (for me) a pretty easy question to answer, but I wanted to provide the questioner with a link to the place in the Enterprise Manager documentation where it covers that in detail. Somewhat to my surprise, I wasn’t able to find such a link – well, not quickly anyway. I think the reason for that is the documentation for EM Express (as it’s more commonly abbreviated to) falls under the database documentation which is owned by a different group in Oracle than the Enterprise Manager documentation. Well, that’s my speculation anyway. It may just be there in the documentation and I couldn’t find it in my quick search.

Friday Philosophy – Why I Volunteer for User Groups

I’ve just noticed a new page about me popping up on the UKOUG web site – It’s in the section about volunteer case studies, alongside people like Joel Goodman, Simon Haslam, Carl Dudley, Jason Arneil, Brendan Tierney and others who have been stupid good enough to give time and effort to the UKOUG.
{You can get to the page by going to the UKOUG home page (www.ukoug.org) and clicking the Membership or Member Activities tab and Case Studies & Testimonials under that and finally Volunteer Case Studies. Phew. Or follow the link I gave at the start and click on the other names.}

I’m not sure how long I’ve been up on there but only a couple of days I think.

EM12c : Login to GUI with the correct password causes authentication failure

So the other day I was trying to log in to my EM12c R4 environment with the SSA_ADMINISTRATOR user, and I got the error:

“Authentication failed. If problem persists, contact your system administrator”

I was quite sure that the password that I had was correct, so I tried with the SYSMAN user and had the same error. I still wanted to verify that I had the correct password , so I tried with the SYSMAN user to log in to the repository database, and was successful, so I know something was wrong there.


SQL> connect sysman/
Enter password:
Connected.

So I went to the/em/EMGC_OMS1/sysman/log/emoms.log and saw the following error

EM12c : Login to GUI with the correct password causes authentication failure

So the other day I was trying to log in to my EM12c R4 environment with the SSA_ADMINISTRATOR user, and I got the error:

“Authentication failed. If problem persists, contact your system administrator”

I was quite sure that the password that I had was correct, so I tried with the SYSMAN user and had the same error. I still wanted to verify that I had the correct password , so I tried with the SYSMAN user to log in to the repository database, and was successful, so I know something was wrong there.


SQL> connect sysman/
Enter password:
Connected.

So I went to the/em/EMGC_OMS1/sysman/log/emoms.log and saw the following error