Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

September 2012

Exchange Partition, Virtual Columns And Column Statistics

Submitted by randolf.geist on Sun, 2012-09-09 15:01

Here is an odd bug that can lead to some nasty side effects when using the EXCHANGE PARTITION technique. It is probably there for a very long time, simply because it depends on the usage of virtual columns, and the basic technique of virtual columns was introduced way back in the Oracle 8i times with the introduction of Function Based Indexes.

The problem isn't the exchange partition operation itself, but the accompanying swap of object statistics information, in particular the column statistics.

Look the following sequence of DDL and DML commands and pay then special attention to the output for the column statistics before and after the EXCHANGE PARTITION operation:

DDL triggers

Submitted by Jonathan Lewis on Sat, 2012-09-08 18:14

One of the partitioning features that Oracle introduced relatively recently was the “interval partition”, for example:

create table transactions (
	account_id		number(8)	not null,
	transaction_date	date		not null,
	transaction_type	varchar2(2)	not null,
	transaction_id		varchar2(10)	not null,
	amount			number(10,2)	not null,
	padding			varchar2(100)
)
partition by range (transaction_date)
interval (numtoyminterval(1,'MONTH'))
(
   partition p200801 values less than (to_date('01-FEB-2008','DD-MON-YYYY'))
)
;

When I insert data into this table for a partition that doesn’t yet exist, Oracle will work out which partition it should be and create it automatically before doing the insert. The benefit of this trick, of course, is that the DBAs and developers don’t have to write any code to add partitions in anticipation of time passing and new data appearing.

VirtualBox 4.1.22 Released…

Submitted by oraclebase on Fri, 2012-09-07 23:14

I’ve not finished updating my VirtualBox guest additions for 4.1.20 and here comes 4.1.22. :)

You can find the changelog and downloads in the usual places.

Happy upgrading!

Cheers

Tim…

 

OOW Bloggers

Submitted by Jonathan Lewis on Fri, 2012-09-07 20:04

Pythian, in the guise of Alex Gorbachev, is organising the annual Oracle Bloggers meetup at Openworld, but if you qualify they’d like you to sign up.

Temporary Tables

Submitted by Jonathan Lewis on Fri, 2012-09-07 13:43

After the success of the last Oracle/SQL Server discussion, James Murtagh of Redgate has arranged another online discussion – this time about the different ways in which temporary tables are implemented and used. As before I’ll be doing the Oracle bit and Grant Fritchey will be doing the SQL Server bit.

Update: Now that the event is over, you can listen to the recording at this URL.

Science

Submitted by Jonathan Lewis on Fri, 2012-09-07 13:32

Sometimes I wish …  http://www.nature.com/nature/journal/v489/n7414/full/489170a.html

WordPress 3.4.2 Released…

Submitted by oraclebase on Thu, 2012-09-06 23:59

WordPress 3.4.2 has been released. You can see the bug fixes in the changelog and download from the usual place.

Happy upgrading. :)

Cheers

Tim…

Oracle OpenWorld 2012 – Bloggers Meetup

Submitted by alexgorbachev on Thu, 2012-09-06 20:40

Oracle OpenWorld Bloggers Meetup Oracle OpenWorld 2012 is just over a month away, and yes, we are organizing the Annual Oracle Bloggers Meetup — one of your top favorite events of OpenWorld.

What: Oracle Bloggers Meetup 2012

When: Wed, 3-Oct-2012, 5:30pm

Null – again

Submitted by Jonathan Lewis on Thu, 2012-09-06 18:41

Here’s a (camouflaged) constraint definition I came across a little while ago in a production system:


create table t1 (
	v1 varchar2(10),
	constraint c1 check (v1 = 'A' or v1 = null)
);

Quick question – will the following insert statement work or return an error ?

insert into t1 values('B');

The answer is that the row gets inserted – and that’s probably not an intended result.

It’s amazing how often NULL rears its ugly head as the cause of unexpected behaviour. Remember that a constraint accepts a value if the constraint does not evaluate to false. If you put ‘B’ into the constraint definition above, we get:

       'B' = 'A' or 'B' = null
hence   FALSE or NULL
hence   NULL

Oracle Security Search Is Annoying and protecting PL/SQL code

Submitted by pete on Thu, 2012-09-06 10:39

This post if not specifically about Oracle Security but I got here because of Oracle security so i am going to talk about Oracle security first...:-) I am working this morning on a proof of concept code for a security....[Read More]

Posted by Pete On 06/09/12 At 11:38 AM