Really dumb triggers

Some people hate triggers, some people love triggers…

I am not that opinionated on them in either direction, but one thing I do hate, whether it be a trigger or not, is dumb code.  And today’s post just happens to be about dumb code in a trigger.

Consider this simple trigger (you see these everywhere pre 12c):

CREATE OR REPLACE TRIGGER MY_TRG
  BEFORE INSERT ON MY_TABLE 
  FOR EACH ROW
BEGIN
   SELECT MY_SEQ.NEXTVAL INTO  (more...)

Estimate Index Size With Explain Plan (I Can’t Explain)

I discussed recently an updated MOS note that details the needs vs. the implications of rebuilding indexes. Following is a neat little trick if you want to very quickly and cheaply estimate the size of an index if it were to be rebuilt or a new index before you actually create the thing. I meant to blog about this sometime […]

Kicking the Remote Presence Device tires with Beam

Beamnoel-beam

Remote Presence Devices or RPDs are finally becoming mainstream with products such as the Beam from Suitable Technologies. Today I kicked the tires of one (virtually of course) thanks to friend of the lab Dan Kildahl. I toured the newly renovated marketing offices at Oracle HQ. My first impression was really good. Around family and friends I am known as the clumsy game player. Yeah, I’m the one that gets constantly stuck against walls during first-person video (more...)

Delphix shines sunlight in data’s cloudy skies

About year ago or more, Oracle came out with a way create thin clone copies of a database in EM 12c called “Snap Clone”.

Screen Shot 2014-04-11 at 9.53.57 AM

Not sure this makes working with data any sunnier and certainly doesn’t add any sunlight to the cloud movement. Snap Clone technology has seen little adoption AFAIK. I’m not aware of a single customer reference yet. Why ? Because Snap Clone doesn’t solve the real problem. Snap Clone is complex, depends on cobbling other technologies together, (more...)

Lead DBA Position Phoenix Arizona with PeopleSoft

My company has posted a Lead Oracle DBA position located in Phoenix, Arizona which is where I also live.

You have to apply through our web site using this link:

https://usfood.taleo.net/careersection/USF_EXTERNAL/jobdetail.ftl?lang=en&job=14002202

We would love to get someone who has PeopleSoft skills.

You would be joining a friendly and experienced team of Oracle and SQL Server DBAs who support a wide variety of applications.  I’ve been here eight years and the (more...)

Strange moving filter predicates from index to table

It seems strange to me:
When all needed columns are in the index, filter predicates are expectedly applied to the index

select a,b from xt_test where a=1 and
(:b is null or b = :b)

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE  (more...)

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...)

Doxxy 1.2 has been released

Today we have great news for you: Docufy becomes Doxxy !
And not only the name improved!

Doxxy is a RAD-tool for generating operational reports. With its intuitive APEX UI, you easily configure your documents by adding DOCX-templates and SQL-queries. The engine is written in PL/SQL, which makes installation, integration and maintenance very straight forward. The tool comes as a packaged application for APEX 4.x.

The main concepts and principles are still the same:

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...)

Ready-To-Use SOA/BPM 11.1.1.7.1 Virtual Machine and Case Management Sample

There is a new excellent Virtual Box VM for the latest SOA/BPM 11.1.1.7.1. This one is my favourite, it includes latest Oracle products for SOA/BPM and WebCenter. All is configured, fine tuned and ready to use. Read more and download from Oracle OTN site - Pre-built Virtual Machine for SOA Suite and BPM Suite 11g.

The same as in previous release of this VM, you can start each server with one (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...)

Convergence

Convergence (\kən-ˈvər-jən(t)s\)

1  :  the act of converging and especially moving toward union or uniformity; especially :  coordinated movement of the two eyes so that the image of a single point is formed on corresponding retinal areas

2  :  the state or property of being convergent

3  :  independent development of similar characters (as of bodily structure of unrelated organisms or cultural traits) often (more...)

Insert Performance mit Bitmap und B*Tree Index

Bitmap Indizes machen eine Menge Ärger, wenn es um DML geht. Diese Erkenntnis ist nicht neu und weitgehend richtig, und ich habe hier auch schon mal gelegentlich einen - eher anekdotischen - Fall vorgeführt, in dem ein Insert in eine Tabelle mit Bitmap Index deutlich langsamer von statten ging als ein entsprechendes Insert in eine Tabelle mit B*Tree Index (wobei ich bei erneuter Betrachtung des Beispiels darüber nachdenke, ob das Ergebnis nicht ziemlich massiv durch (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...)

Installing ORE – Part A

This blog post will look at how you can go about installing ORE in your environment.

The install involves a 4 steps. The first step is the install on the Oracle Database server. The second step involves the install on your client machine. The third steps involves creating a schema for ORE. The fourth steps is connecting to the database using ORE.

In this Part A blog post I will cover the first two steps (more...)