THE WORLD DISCUSSES #PYTHIAN ON TWITTER. HAVE A QUESTION? USE OUR HASHTAG AND ASK AWAY.

Pythian Blog

Ensuring Table With Only One Row in Oracle 11g Using Virtual Column

There was a discussion on the OTN General database forum, in which the OP asked creating a table with just one row and restricting that table to just one row. Here is my attempt at it.

I created a table with two columns, and the second column is a virtual column and contains a constant. I created a unique index on this column. On every insertion, this second column always evaluates to 1, and unique index (which become the function based index on virtual column) ensures that only one row remains in the table.

oracle@test # sqlplus /nolog
 
SQL*Plus: Release 11.1.0.7.0
 - Production on Sat Aug 28 19:09:16 2010
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
idle> conn test/test
Connected.
test@test> create table t1
 (c1 number, c2 generated always as (1) virtual);
 
Table created.
 
test@test> create unique index idx1 on t1(c2);
 
Index created.
 
test@test> insert into t1(c1) values (1);
 
1 row created.
 
test@test> commit;
 
Commit complete.
 
test@test> insert into t1(c1) values (1);
insert into t1(c1) values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated
 
 
test@test> insert into t1(c1) values (2);
insert into t1(c1) values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated

Oracle-related events in Sydney area (September 2010)

Hello folks,

If it happens and you are in and around the Sydney area 1-2 weeks from now then you might be interested in taking part in the following events:

The first event is organized by Oracle Community in Sydney (sponsored by Pythian), during the next Sydney Oracle Meetup. The good news is that you can have free beer and pizza to recharge your batteries after a working day and have a good discussion with Oracle professionals in friendly environment.
- a – RAC, Grid, Cloud or on the way to Oracle Cloud
Wednesday 8 September 2010 17:30 p.m. to 19:30 p.m.

Usually, there are not many full day Oracle-related events happening in Sydney. More good news is that another one is coming soon. Attend and hear about new products and features from Oracle themselves and win a Car Navigator (as one of the lucky Pythian’s employees did at last Oracle event in Sydney recently :)
- b – Oracle’s Next Generation Data Centre Summit 2010 – Sydney
Thursday 16 September 2010 8:30 a.m. to 6:00 p.m.

See you around,
Yury – a DBA from down under

How to GNS process log level for diagnostic purposes (11G R2 RAC / SCAN / GNS) ?

Hello Everyone,

This is one of my fist posts under Pythian’s blog. I will try to keep those short and simple at the beginning.

Recently I was troubleshooting a new GNS (Grid Name Services) functionality.

For more information please see here: Oracle Clusterware Network Configuration Concepts.

I have noticed that there is a-trace-level parameter in the GNS process string.
# ps -ef | grep gns
root 26790 1 2 14:41 ? 00:00:00 /u01/app/11.2.0/grid/bin/gnsd.bin -trace-level 0 -ip-address 10.10.193.201 -startup-endpoint ipc://GNS_hostrac01_23867_408c49e351f1f6a8
root 26825 17210 0 14:41 pts/1 00:00:00 grep gns

Unfortunately there is no description as of now in the documentation or MOS on how to change it to generate invaluable diagnostic information.
NOTE: I am sure the documentation will be updated in Database 12c version (c for Cloud ;)

For a time being the following should work for you:

# /u01/app/11.2.0/grid/bin/crsctl modify resource ora.gns -attr "TRACE_LEVEL=6"
# /u01/app/11.2.0/grid/bin/srvctl stop gns
# /u01/app/11.2.0/grid/bin/srvctl start gns

I hope that this advice will help you to diagnose your GNS issue.

I will blog about the way I resolved future GNS-related issues later on.

It looks like I have said too much for my very first post already.

See you around,

Yury

Just another DBA from down under

Bloggers Meetup @ Oracle OpenWorld 2010

Oracle OpenWorld Bloggers Meetup It’s that time of the year again — Oracle OpenWorld time — and it’s my pleasure to announce our regular Oracle bloggers meetup again this year. We all know that Oracle community has grown this year so we expect to see folks from all the different technologies including MySQL, Java, Sun hardware folks in addition to the core Oracle database and apps crowd.

So… all of you Oracle bloggers attending Oracle Open World 2010
… you are invited to attend this Oracle Bloggers Meetup during OOW 2010 — a chance to meet your online buddies face-to-face in relaxed and informal atmosphere.

When: Wed, 22-Sep-2010, 5:30pm

Where: Lower Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103.
Read the rest of this entry . . .

Shuck & Awe #11: Hunting for Perl

[yanick@enkidu shuck]$ perl -V:news

Do you regularly scuba dive in a motley sea of other peeps’ codebase, trying to bring on surgical changes without doing too much collateral b0rking on the code formatting? If so, Steffen Mueller has a nifty trick to share with you. Using Text::FindIndent, he shows how to configure Vim such that it can magically adapts to any indentation policy.

Talking of Vim, Andy Lester let us know that Vim 7.3 is out, with a bunch of upgrade to its Perl-related support files (and that includes brand-new support for Perl 6).

CPAN is great, CPAN is awesome. But, as we all know, the leviathanesque amount of distributions it contains is sometime daunting. Which module should I use to perform $random_task? Jesse Thompson proposes to look at how many other modules are dependent on a distribution as a metric, and provides a greasemonkey script to retrieve that information straight on the CPAN search page.

This year we’ve seen the rise of a lot of über-cool cpan* and perl* utilities. The latest, announced by Cornelius, is a little speed-demon called cpansearch. Written in C (which gives it mongoose-like response time) it is a module searching tool. Already cool on its own

$ time cpans XPath | head
Source list from: http://cpan.nctu.edu.tw/modules/02packages.details.txt.gz
Apache::AxKit::Language::XPathScript     - 0.05 (M/MS/MSERGEANT/AxKit-1.6.2.tar.gz)
Apache::XPointer::XPath                  - 1.1 (A/AS/ASCOPE/Apache-XPointer-1.1.tar.gz)
AxKit2::Transformer::XPathScript         - 0 (M/MS/MSERGEANT/AxKit2-1.1.tar.gz)
B::XPath                                 - 0.01 (C/CH/CHROMATIC/B-XPath-0.01.tar.gz)
Cindy::XPathContext                      - 0 (J/JZ/JZOBEL/Cindy-0.15.tar.gz)
Class::XPath                             - 1.4 (S/SA/SAMTREGAR/Class-XPath-1.4.tar.gz)
Config::XPath                            - 0.16 (P/PE/PEVANS/Config-XPath-0.16.tar.gz)
Config::XPath::Reloadable                - 0.16 (P/PE/PEVANS/Config-XPath-0.16.tar.gz)
Email::MIME::XPath                       - 0.005 (H/HD/HDP/Email-MIME-XPath-0.005.tar.gz)

real    0m0.168s
user    0m0.016s
sys     0m0.020s

it can yet achieve higher levels of radness if combined with other Perl tools like cpanm:

# install all that is tiny
$ cpans -n Tiny | cpanm

YAPC::NA and YAPC::Europe came and went, but Karen Pauley reminds us that there’s still YAPC::Asia happening in Tokyo in October, and that the tickets are now on sale.

What? Didn’t attend any YAPC::* yet this year? Oh well, at least Matt S Trout points us where we can download videos of some of their talks.

Have you noticed that you can’t use the 5.10 features (like the smart match, say, given / when) under the Perl debugger? Pablo Marin-Garcia did, and dug to find out why. Also check the comments for a dirty way to force the debugger into a more modern attitude.

Moose is a mighty beast, but it’s not the fastest ungulate you’ll ever meet. But thanks to Dave Rolsky, it now compiles 10% faster than it used to. w00t!

Alberto Simões reports that the Perl Foundation accepted grants for 2010Q3 are in. From the look of it, lots of documentation — game development with SDL, Perlbal, Perl 6, Parrot — is coming our way.

Does anyone remember Mazinger Z? Each time we thought that giant robot achieved the peek of ultimateness, it would interface with a new ship/contraption/coffee machine and become even awesomer. Moose, with its MooseX cohorts, is a little bit like that. But with antlers. Florian Ragwitz shows us how the raw power of parameterized traits given by MooseX::Role::Parameterized can now be harnessed by MooseX::Declare.

use MooseX::Declare;
use 5.10.0;

role Gizmo ( Str :$codename ) {

    has 'upgraded' => ( is => 'rw' );

    my %gizmo_ability = (
        'wingy_thingy'  => 'fly like a butterfly',
        'smash_o_tron'  => 'squish things',
        'expresso_core' => 'make darn good coffee',
    );

    method "summon_$codename" {
        say "Giant robot summons its $codename";
        $self->upgraded(1);
    }

    method unleash_power {
        say $self->upgraded
            ? "Giant robot can now $gizmo_ability{ $codename }"
            : "No gizmo? No super-power for you"
            ;
    }
}

class GiantRobot::Omega {
    with Gizmo => { codename => 'expresso_core' };
}

my $robot = GiantRobot::Omega->new;

$robot->unleash_power;         # No gizmo? No super-power for you

$robot->summon_expresso_core;  # expresso core, to me!

$robot->unleash_power;         # *mouahaha*
[yanick@enkidu shuck]$ perl -E'sleep 2 * 7 * 24 * 60 * 60 # see y'all in 2 weeks!'

Log Buffer #201, A Carnival of the Vanities for DBAs

Log Buffer is the DBA community’s Carnival of the Vanities-style blog of blogs about the database world. It is the source DBAs and others interested in databases count on for weekly news of industry happenings, and what their colleagues around the world are working on.

This week’s edition, Log Buffer #201 has been compiled and published by Craig Mullins on his Data and Technology Today blog. Craig has contributed to Log Buffer since the early days, and we’re happy to have him involved yet again. Enjoy!

Oracle Exadata HCC (Hybrid Columnar Compression) removes the 255 columns limit

There is a little know fact that Oracle table compression doesn’t work with tables that have more than 255. It’s has been reported here and compression limitations in general here.

As a follow up from my previous post on Exadata Design, where I question the use of dimensions for certain attributes in data warehouses, I figured I should test whether HCC works with tables that have more than 255 columns.

And it does.
Here’s my test case.

Setup:

create table ctest (
col_1  varchar2(30) default 'JUSTSOMEDATA',
col_2  varchar2(30) default 'JUSTSOMEDATA',
...
col_255  varchar2(30) default 'JUSTSOMEDATA'
);
insert into ctest (col_1) select 'OTHERDATA' from dba_objects;
insert /*+APPEND*/ into ctest select * from ctest;
commit;
insert /*+APPEND*/ into ctest select * from ctest;
commit;

Build various scenarios (NOTE: second create adds the 256th column):

/*REGULAR*/
create table ctest_cmp compress as select * from ctest;
create table ctest_cmp1 compress as select t.*, 'NEW' col_256 from ctest t;

/*HCC QUERY*/
create table ctest_hcc compress for query low as select * from ctest;
create table ctest_hcc1 compress for query low  as select t.*, 'NEW' col_256 from ctest t;

/*HCC ARCHIVE*/
create table ctest_hccA compress for archive low as select * from ctest;
create table ctest_hccA1 compress for archive low  as select t.*, 'NEW' col_256 from ctest t;

And the results:

SQL> select round((bytes)/1024/1024) as mb ,  segment_name from user_segments t where segment_name like 'CTEST%' order by segment_name;

        MB SEGMENT_NAME
---------- ----------------------------
       824 CTEST
         3 CTEST_CMP
       824 CTEST_CMP1
         1 CTEST_HCC
         1 CTEST_HCC1
         1 CTEST_HCCA
         1 CTEST_HCCA1

Michigan OakTable Symposium (MOTS)

Michigan OakTable Symposium (MOTS) is a unique event taking place just before Oracle OpenWorld — 16-17 September. Why unique? This is the first conference (is it not?) where all presenters are members of OakTable Network, a group that gathers number of like-minded IT professionals with scientific approach to Oracle database technology and to the life in general.

It happens in Ann Arbor, Michigan — a place I wanted to visit for a very long time. For those of you who are cost conscious and don’t have much in their education budget, this conference is a great value priced at a third of the Oracle OpenWorld pass and I can assure you that quality of presentations is on par with or higher then the top Oracle OpenWorld sessions. The conference is organized by volunteers from OakTable Network and few good friends as a non-commercial event so you will hear no marketing crap whatsoever.

Unlike, huge OpenWorld crowd, which has always intimidated me a little bit, MOTS is going to be a cozy event with strictly limited cap of only 300 participants – ideal size to support peer networking and opportunity to mingle with some of the folks you’ve been dying to talk to.

I especially enjoyed this video promotion: Read the rest of this entry . . .

Exadata storage server software 11.2.1.3.1 released

As of this afternoon, version 11.2.1.3.1 of Oracle’s Exadata storage server software, is out in the wild. This is the first publicly available version of the 11.2.1.3 branch, a major release including a full OS image with an update to Oracle Enterprise Linux 5.5. A number of bugs causing cell server crashes and hangs have been fixed, including 9472035, 9870117, and 9722560.
Read the rest of this entry . . .

Log Buffer #200, A Carnival of the Vanities for DBAs

Hello, and welcome to Log Buffer, a weekly blog about happenings in the database industry. Today is a celebration of the 200th edition of Log Buffer, and we’d like to give a special shout out to all of our guests hosts over the past 4 plus years. Their contributions have been invaluable in helping to grow Log Buffer into the community source of favorite database-related blog posts that it is today.

We’ve come a long way since our inaugural issue, Log Buffer #1, published July 14, 2006. This week enjoy mix of contributed links and the latest posts from past contributors.

Mart Rittman (Log Buffer #11) reminds everyone that OBIEE 11gR1 is available for download. His colleague, Venkatakrishnan J follows up with posts on a few new and important features including reporting on CLOBs – Lookups, map views – integration with Mapviewer, handling double columns – ID/description interoperability, lookup tables – sparse and dense lookups and vertical clustering.

Adam Machanic, of SQLblog.com and host of Log Buffer #21 is reflecting on 6 plus years of blogging in the database world, and wants to know who his readers are.

Lewis Cunningham (Log Buffer #32) notes that the call for abstracts is open for ODTUG/Kaleidoscope 2011. Submission deadline is October 26, 2010. He also talks about a problem with success and offers good advice to make sure you’re not getting “crusty”.

Coskan Gundgobar (Log Buffer #49) responds to a slow system waiting on library cache lock and credits Tanel Poder’s scripts as the solution.

Jeremy Schneider (Log Buffer #55) points out that DBCA is missing from the 11gR2 ASM/grid installation.

Hubert Depesz Lubaczewski (Log Buffer #57) writes about OMNIPITR – hot-backups on slave – they really work.

Edie Awad (Log Buffer #73) references 5 interesting posts in his Monday, August 16th roundup including working with long columns, and database performance for developers.

Robert Treat (Log Buffer #127) shares on his personal opinion on what people should do following the announcement that OpenSolaris is cancelled and is to be replaced with Solaris 11 Express.

Gary Myers (Log Buffer #181) has a little fun with passwords on Sydney Oracle Lab.

Kent Milligan suggests to process your DB2 for i indexes in parallel and thus improve the overall performance of your database server.

Steve Karam, the Oracle Alchemist reports strange behaviour with MEMORY_TARGET.

Chris Presley shares Paul White’s post on viewing another session’s temporary table.

While a few have fallen off the map, it’s nice to see many members of our database blogging community still going strong.

Start NowWith Pythian - database design, management and emergency handling capabilities...

Live Updates

pythian: RT @alexgorbachev: Oracle OpenWorld 2010 - Bloggers Meetup announced! http://bit.ly/92Qg76
more



Testimonials

  • Serge Racine

    DBA, Brookfield Energy

    We are very satisfied by the service given to us by Andre and Shakir in support of our recent data quality and reorganization initiative.... more