NL History

Even the simplest things change – here’s a brief history of nested loop joins, starting from 8i, based on the following query (with some hints):

select
	t2.n1, t1.n2
from
	t2,t1
where
	t2.n2 = 45
and	t2.n1 = t1.n1
;

There’s an index to support the join from t2 to t1, and I’ve forced an (unsuitable) index scan for the predicate on t2.

Basic plan for 8i (8.1.7.4)

(more...)

RHEL7 and Oracle Linux 7 beta

Nearly two weeks ago, Oracle announced the Oracle Linux 7 Beta 1. Being the Linux fanboy I am, I downloaded it straight away from here.

Oracle Linux is a clone of the Red Hat Enterprise Linux (RHEL) distribution. The RHEL7 beta, and therefore OL7 beta, distro is based on a cut of Fedora 19, although depending on who you ask, it’s possibly more a mix of Fedora 18, 19 and 20… Suffice to say, there are (more...)

Get the Happiest Job on Earth!

Have you ever met one of those rare breed of people who is unusually fond of his/her job and, says, TGIM (Thanks God its Monday) instead of TGIF (Thanks God its Friday)? Chances are he/she is a DBA (database administrator). Most of us already know that the DBA position can be a very rewarding career. […]

The post Get the Happiest Job on Earth! appeared first on VitalSoftTech.

Missing Password for Database Link Bug

So there I was, working on another database duplication project, the requirement is to save the existing database links.

Sounds pretty easy, right?

SELECT OWNER, DB_LINK, DBMS_METADATA.GET_DDL('DB_LINK',DB_LINK,OWNER) as DDL FROM DBA_DB_LINKS;

Wrong and I know why I am getting bald. Pulling my hair out.

After searching for hours, I found DBMS_METADATA.GET_DDL database link password missing

Another 11.2.0.4 Bug.

I believe the bug was introduced when I modified user’s password as (more...)

Presenting at ODTUG Kscope14 Conference in Seattle June 22-26 2014

  Just a short note to say I’ll be presenting at the Oracle Development Tools User Group (ODTUG) Kaleidoscope 14 Conference this year in beautiful Seattle, Washington on June 22-26 2014. I had a fantastic time when I attended this conference a few years ago when it was held in Monterey so I’m really looking forward to […]

Result Cache concept and benefits

This feature was first introduced in Oracle 11g and was meant to increase performance of repetitive queries returning the same data. This feature is interesting if your application always look for static data, or data that is rarely updated, for these reasons, it is firstly destinated to Data Warehouses databases (OLAP) as many users will [...]

The post Result Cache concept and benefits appeared first on Oracle DBA Scripts and Articles (Montreal).

Restoring Controlfile When AUTOBACKUP Fail

Allow me to present the snapshot of a day from the life of John--the DBA at Acme Bank. On this particular day a database John manages crashed entirely and had to be restored from the backup. He takes regular (backupset) RMAN backups to tape. Since everything--including the controlfile--had crashed, John had to first restore the controlfile and then restore the database. The controlfile is always backed up with the backup database command. John was sure (more...)

Some new Oracle Database 12c Articles

I’ve recently put some new Oracle 12c articles on the website.

The privilege usage stuff is really cool. Normally, trying to figure out what you can remove from a user is always a complete pain in the ass. Some of the databases I’m (more...)

Quick documentation for new PeopleSoft DBAs

I did a quick survey of the latest PeopleSoft manuals to find a set of links to pass on to a couple of coworkers of mine that are interested in doing PeopleSoft DBA work so I thought I’d include the links in a post.  This might give a new PeopleSoft DBA some highlights without having to read the entire manual set.

This page has a nice picture of how the environments connect:

http://docs.oracle. (more...)

Enterprise Manager not effected by Heartbleed

The Oracle Security team has indicated that Enterprise Manager Cloud Control, Grid Control as well as Ops Center are not effected by the Heartbleed vulnerability. This is mentioned in the internet published document on the Oracle Technical Network (OTN):

http://www.oracle.com/technetwork/topics/security/opensslheartbleedcve-2014-0160-2188454.html

An excerpt from this document:

1.0 Oracle products that, while using OpenSSL, were not subject to CVE-2014-0160

Global Product Security has determined that the following products are using OpenSSL cryptographic libraries (more...)

RAC 12c enhancements: adding an additional SCAN-part 1

Based on customer request Oracle has added the functionality to add a second SCAN, completely independent of the SCAN defined/created during the cluster creation. Why would you want to use this feature? A few reasons that spring to mind are:

  • Consolidation: customers insist on using a different network
  • Separate network for Data Guard traffic

To demonstrate the concept I am going to show you in this blog post how I

  1. Add a new network resource
  2. (more...)

Indexing Foreign Key Constraints With Invisible Indexes (Invisible People)

In my previous post I discussed when deleting rows from parent tables, how Bitmap Indexes based on the FK constraint can prevent the expensive Full Tables Scans (FTS) on the child tables but not the associated exclusive table locks. Last year, I discussed how it was possible in Oracle Database 12c to have multiple indexes on the same column list. […]

Divergent

I’ve just got back from watching Divergent. It is a really good film!

Don’t believe any bullshit comparisons to that crappy Hunger Games. It is so much better than that rubbish! It’s probably a good idea not to read any summaries of the plot either, since it sounds kind-of stupid on paper. In the film it makes a lot more sense and just works.

Very pleased I went to see it. One of the better Sci (more...)

SQL*Loader Express bug

I’m still studying for my Oracle 12c OCP exam and I was trying to run a simple example of using SQL*Loader Express and the first thing I did blew up and I think it is a bug.  When I load a table with one or two columns it works fine, but when I load a table with 3 or 4 columns the last column is not loaded.  Tell me this isn’t a special (more...)

ORA-01430

This was tested on Oracle 11.2. If you try to use a column name twice in the same table, you get an ORA-01430: 

SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL> alter table tab1 add (col1 number)
  2  /
alter table tab1 add (col1 number)
                      *
ERROR at line 1:
ORA-01430: column being added already exists in table
 
SQL>

Performance Testing with Agile Data

apple_orange

Performance testing requires full, fresh data

Many organizations don’t even attempt to test performance until very late in their development cycle because it is only in the UAT (or equivalent) environment that they have a full copy of their production data set.  Errors and inefficiencies found at this stage are expensive to fix and are often promoted to production due to pressures from the business to meet release schedules.

Delphix customers give each developer, or (more...)

Switch to Integrated Replicat

Earlier I wrote a blog post about moving the Oracle Golden Gate Extract from “Classic”  capture to “Integrated” capture.   In this post, we will take a look at how to move a “Classic” replicat (apply) to an “Integrated” replicat (apply) process.

The initial step to migrate the replicat from “Classic” to “Integrated” are the same as the extract.  Look here for the initial details.  This post picks up when we are getting (more...)

Move from classic capture to integrated capture

Most Oracle Golden Gate installations, venture to say 80-90%, use the “Classic” capture architecture.  What I want to show in this post is how to switch from “Classic” capture to “Integrated” capture.

In the Oracle Golden Gate 12c documentation, the steps provided work but there are a few things that we need to be aware of when trying to switch to integrated capture mode.  The steps that are outlined here show you (more...)

Oracle Linux 7 Beta 1 (fixed with CVE-2014-0160 with openssl-1.0.1e-23.0.1.el7)

Last post, I blog about Oracle Linux Beta 1 that I would like to learn more after it has changed to use Systemd targets. In this post, I just wanted to update for some people who have installed Oracle Linux 7 Beta 1 or have been testing it. On Oracle Linux Beta 1, that uses openssl version (openssl-1.0.1e-23.el7) and it's issue about CVE-2014-0160. Users can go Oracle Linux Early Access Downloads and download (more...)

log file switch (archiving needed)

I was running a test in Oracle 11.2 and the SQL below, which usually takes around 1 minute, seemed to be running forever:

SQL> begin
  2   for a in 1..8 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

I looked up the session's SID in V$SESSION to see if there was a row with wait_time set to zero. This means (more...)