Software Engineer Thoughts

What a news! Oracle Buys Sun !

2009-04-20T22:36:00.001+02:00

Written by Paweł Barut
What a news: Oracle Buys Sun !! I'm not the first one that blogs about it, but anyway let me express my comments. What does it mean for IT:
1. Oracle software will be installed on almost every PC; Sun Java might be renamed to Oracle Java. It will give wider recognition for Oracle trademark also to people that are not so much engaged with IT Industry.
2. Oracle becomes hardware producer. Well, you might know already HP-Oracle Database Machine (Exadata), but this was joint-venture. Now Oracle will produce hardware and software that runs on it. If everything goes right next "Database Machine" will be fully "Oracle".
3. Sun Solaris is #1 platform for running Oracle, and the second one is Linux. This could mean that rate for Linux implementation in enterprises will be lower, as many companies would choose OS and DB from one vendor. I know that Oracle is also offering Linux, but Oracle sales will be promoting Solaris, as this deals will be better for Oracle.
4. Oracle now owns 2, ehh, 3 databases: Oracle, Berkley XML DB and now MySQL. I could mean that MySQL will get even bettor, or rather it will mean that Oracle will offer MySQL customer to migrate to Oracle. So far Oracle does not have good record for supporting free editions of Databases. We will see...
5. With Oracle-Sun merge, Oracle will be shipping now full stack: hardware,OS, Database, Midlleware, ERM, CRM and many others. Oracle is becoming giant now...

Read also what others have to say:

Mark Rittman - Oracle Buys Sun - What Does It Mean for Oracle BI&DW?
Pythian - Oracle Buys Sun
Marco Gralike - Oracle to buy Sun, Ehh MySQL, Ehh Glashfish…
Tanel Poder - Oracle buys Sun - hooray!
Tim Hall - Oracle Buy Sun Microsystems…
Kevin Closson - Everyone Else is Doing It!
Jaffar - Oracle to acquire SUN?

Keep reading,
Paweł

Caution: FOR loop and CONTINUE in Oracle 11g

2009-04-06T11:59:00.001+02:00

Written by Paweł Barut
Friend of mine showed me interesting issue of using CONTINUE Statement. CONTINUE is an new statement in Oracle 11; it allow to skip processing of current iteration of look, and go to begging of next iteration. So here is working sample:

SQL> set serveroutput on
SQL> begin
2    dbms_output.put_line('=== START');
3    for i in 1..15 loop
4      dbms_output.put_line('before:'||i);
5      if mod(i, 5) = 0 then
6        dbms_output.put_line('CONTINUE');
7        continue;
8      end if;
9      dbms_output.put_line('after:'||i);
10    end loop;
11    dbms_output.put_line('=== STOP');
12 end;
13 /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:6
after:6
before:7
after:7
before:8
after:8
before:9
after:9
before:10
CONTINUE
before:11
after:11
before:12
after:12
before:13
after:13
before:14
after:14
before:15
CONTINUE
=== STOP

But when we use CONTINUE in loop, that is based on implicit cursor, it gives wrong results:

SQL> begin
2    dbms_output.put_line('=== START');
3    for r in (select level num from dual connect by level <= 115) loop
4      dbms_output.put_line('before:'||r.num);
5      if mod(r.num, 5) = 0 then
6        dbms_output.put_line('CONTINUE');
7        continue;
8      end if;
9      dbms_output.put_line('after:'||r.num);
10    end loop;
11    dbms_output.put_line('=== STOP');
12 end;
13 /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:101
after:101
before:102
after:102
before:103
after:103
before:104
after:104
before:105
CONTINUE
=== STOP

In that case CONTINUE statement goes to iteration no 101 instead of going to iteration 6, as expected!!
To make the long story short, I've experiment with few setting and found that setting PLSQL_OPTIMIZE_LEVEL to 1 this block runs as expected:

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;
SQL> begin
2    dbms_output.put_line('=== START');
3     for r in (select level num from dual connect by level <= 115) loop
4       dbms_output.put_line('before:'||r.num);
5       if mod(r.num, 5) = 0 then
6         dbms_output.put_line('CONTINUE');
7         continue;
8       end if;
9       dbms_output.put_line('after:'||r.num);
10     end loop;
11     dbms_output.put_line('=== STOP');
12 end;
13 /
=== START
before:1
after:1
before:2
after:2
before:3
after:3
before:4
after:4
before:5
CONTINUE
before:6
after:6
[... many lines cutted out from output...]
after:99
before:100
CONTINUE
before:101
after:101
before:102
after:102
before:103
after:103
before:104
after:104
before:105
CONTINUE
before:106
[... many lines cutted out from output...]
after:114
before:115
CONTINUE
=== STOP

For me it looks as bug in PL/SQL Optimizer on level 2. In that case PL/SQL does bulk collect for 100 rows when FOR LOOP with cursor is used. Seems that CONTINUE in that case causes to fetch next portion of records instead of taking next record from buffer. I've reported bug to Oracle Support, but until it will get fixed, be aware of possible wrong results.
This was tested on Oracle 11.1.0.6 and 11.1.0.7 Enterprise Edition.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

Book Review: Processing XML documents with Oracle JDeveloper 11g

2009-03-29T12:55:00.001+02:00

Written by Paweł Barut
This time I would like to recommend a book Processing XML documents with Oracle JDeveloper 11g published by PacktPub.com. As I have some experience with XML processing inside Oracle DB (XMLDB) I was lacking the knowledge of XML manipulation in Java, especially using Oracle JDeveloper. This book is rather not for beginners - you should have basic knowledge of what is XML, at least theoretically. From the book you can learn how to manage XML Schemas in JDeveloper, then write XML documents based on those schema and finally validate them in JDeveloper or write Java code to validate it automatically. Chapters that I value much are those describing how to generate PDF documents. There are two methods described - one using Apache FOP API , and Oracle XML Publisher. As I was not generating PDF's this way so far, I found those 2 chapters very interesting. There is also chapter on generatin Excel by transformation of XML using Apache HSSF API .

Book is very practical. If you want to start with subjects described above then this book is for you. Of course it does not mean that you do not have to study documentation. However, it will be easier for you to make first steps. Beside describing Oracle JDeveloper, author also shows how to install and use Oracle Berkley XML DB to store, query and update XML Documents - using command line and XML DB API.

I especially recommend eBook version. All URLs in eBook version are active, so linking to web pages are very simple. Also coping sample code is easy. This book contains many practical and usefull tips on XML processing and tools as well. So if those XML aspects are in your interest, then it's definitelly good to invest in this book.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

Multiplying texts and null effect

2009-03-15T22:39:00.000+01:00

Written by Paweł Barut
This time short notice on side effect of implicit type conversion ans short expression evaluation. Let me show this on example - try this statement:

SQL> select 'a'*'b' from dual;
select 'a'*'b' from dual
*
ERROR at line 1:
ORA-01722: invalid number

As expected we get error. It is due to fact that multiplexing operator (*) is expecting numbers on both sides, an implicit conversion takes place. However this statement:

SQL> select ''*'b' from dual;

''*'B'
----------

does not give error. How is it possible? In oracle empty string is in fact an null. null multiplied by any number gives null, so due to optimization oracle decided to evaluate this expression to null.
As you know also 0 (zero) multiplied by any number gives zero. So let's test it:

SQL> select '0'*'b' from dual;
select '0'*'b' from dual
*
ERROR at line 1:
ORA-01722: invalid number

In that case Oracle did not optimized evaluation, and decided to make full calculation. I do not know the reason for different behaviour but is repeatable (at least on instances I've access to).
My recommendation: always make sure that you provide data in proper data-type. Make explicit data conversion using to_number, to_date or to_char functions.

Keep reading,
Paweł

--
Related Articles on Paweł Barut blog:

OOW2008 day 5 – It’s A Wrap

2008-09-26T06:52:00.003+02:00

Written by Paweł Barut
Before I start to summarize my Day 5 at Oracle OpenWorld, I would like to add few words to day 4 (Wednesday).

Managing Very, Very Large XML Documents with Oracle XML Database
It was very good session. It one of those where practical experience was shared. Presenters showed step by step how to load very large XML files to DB:

Setting up XML schema
Schema annotation technique and few directives
Direct Insert Store for XML
Differences with loading XML into XML DB in 10.2g and 11g

The Appreciation Event.
It was very nice concert on Treasure Island. I’ve especially liked Seal. Beside that there was lot of good food and drinks.

Day 5

I will start with session Oracle’s New Database Accelerator: Query Processing Revolutionized. As I’ve expected it was related to announcement made yesterday. My yesterday’s description was not perfect. Now I will try to fix this. First of all we have 2 new machines. But one them is included in the second one.
Oracle Exodata Storage Server - it is hardware from HP: 2 Intel quad-core processors, 12 disks (300GB, 15RPM or 750 GB, 10 RPM) with disk controller optimized for best bandwidth and 2 InfiniBand connectors to connect to external equipment. The code for this product is HP DL180G5 (at least that was on one of slides). This computer is sold with preinstalled Oracle Enterprise Linux 5.1. The main role of this machine is to store database files. It cannot be used to store normal files.
The second hardware is HP-Oracle Exodata DB Machine - This one is rack box equipped with 14 Oracle Exodata Storage Servers and 8 DB Servers each with 8 Intel processors. On those DB Server runs Oracle Enterprise Linux 5.1 and Oracle RDBMS 11g (11.1.0.7). Even more – 6 such DB Machines can be connected into cluster.
Where is the revolution? In the way Oracle DB communicates with storage. There is new protocol iDB that allows to push query predicated down to storage. With this, number of data transferred from Storage to DB Server is minimized. This feature is called Smart Scan. It can be leveraged only when full table (or partition) scans occurs. And it still keeps all read consistency.
And here is technical spec from Oracle.

And short on my other sessions: Oracle Database Performance on Flash Drives
Very interesting session showing results of different approaches for using Flash drives. As an conclusion there was presented formula, when Flash drives can help with performance, and when it is better to stay with fast rotating drives, and when even with low cost but high capacity drives. As an side note when we consider power usage, Flash drives can be even more economic then traditional rotating drives.

Oracle ACE Director: Birds-of-a-Feather Tips and Techniques
Session lead by Oracle ACE’s: Lewis Cunningan, Arup Nunda, Edie Awad, Mark Rittman, Tim Hall, Hans Forbich and Bradley Brown. The session was Q&A style. ACE’s were answering questions based on own experience, and that is sometimes different that Oracle’s official recommendation.

Real-World XML DB Examples from Oracle Support
This was rather chaotic session, and did not gave me useful information- IHMO waste of time.

And the last session by Tom Kyte Reorganizing Objects
Tom have done great job showing different myths about when DBA’s should reorganize tables and indexes. It was really Great speech. Might be there was too much material for an 1h session, and everything was shown little bit in an hurry.

The day has ended with small party It’s A WRAP
While writing this I’m sitting in hotel and watching Fireworks over the San Francisco Port. Tomorrow I’m leaving San Francisco for 18h trip back to home.

Cheers,
Paweł
--
Related Articles on Paweł Barut blog:

OOW2008 day 4 – HP-Oracle Exadata Server Announcement = Extreme Performance

2008-09-25T01:25:00.004+02:00

Written by Paweł Barut
Today is very busy day for me. It’s just after Larry Ellison keynote on which first Oracle Hardware was presented. I’m a little big skeptic if Oracle is realy going for hardware business. It is rather that Oracle had great idea how to solve problems with really big databases and growing demand for storage, and joined forces with HP to create new quality in data processing. So, how it looks: in one box we have 2 intel 4 core processors and 12 disks as storage – it is called The Exadata Programmable Storage Server. This machine is not storage, and is not pure DB Server as well. It can process queries (so it is DB), and it stores Data (so it is Storage). But it needs separate DB Server to work at full performance. How it works: DB Servers receives request for data. It then retries data from Exadata Storage Server, but data get initially filtered, so number of data transferred from Storage Server to DB Server is reduced. It allows much better overall performance.
At least this is my understanding. I will go also to DemoGround to get direct look at this machine, and more detailed specification.

Today I’ve also participated in few sessions.
Soup-to-Nuts RAD Development Using Oracle SQL Developer and Oracle Application Express It was quick show how to create simple application using APEX and SQL Developer. It focused on modeling capabilities of SQL Dev, and integration between APEX and SQL Dev. It was shown, how to view APEX objects in SQL Dev, and how to leverage this integration. There was also presentation of new functionality in APEX to migrate Oracle Forms to APEX.

Agile Database Testing Techniques (IOUG) This was very interesting session giving practical inside on how to organize unit tests in DB, how to validate that upgrade scripts run successfully, and how to prepare DB environment for Daily Builds. Presenter shared his real world experience and this was the biggest value of this session.

SQL Tuning Roundtable with the Experts This one was rather boring, as topics and answers were almost exactly the same as on session “Inside Oracle Database 11g Optimizer: Removing the Mystery” that I was participating yesterday.

Now I’m sitting in OCP Louge, and in few minutes I’m going for last session Managing Very, Very Large XML Documents with Oracle XML Database and then for The Appreciation Event.

Cheers,
Paweł

BTW. This is my post # 100.
--
Related Articles on Paweł Barut blog:

OOW2008 day 3

2008-09-24T06:08:00.004+02:00

Written by Paweł Barut
This day was quite good for me. I’ve finally overcome problems with jet-lag.

I’ve started day on session “Inside Oracle Database 11g Optimizer: Removing the Mystery”. It was very good session explaining how new features in optimizer works. It provided inside on Plan Profiles, improved bind variable picking, improved performance for statistics collection and new statistics for correlated columns.

My next session was not exactly what I was expecting. “Global-Scale Web 2.0 Data Platforms: Sharding with Oracle Database”. I was expecting technical inside, but it mostly was marketing speech on “why Oracle is better then MySQL”. Break between sessions I’ve used to ride on Cable Car:

I’ve ended day on session “Top 10 Things You Wanted to know about ASM”. As I were not using ASM yet, it was good overview of features, and best practices for using ASM. Now, I’m going to rest to be prepared for next day, and big keynote by Larry Ellison.

Cheers,
Paweł
--
Related Articles on Paweł Barut blog:

OOW2008 day 2 – Oracle Beehive

2008-09-23T17:23:00.006+02:00

Written by Paweł Barut
This day started with Keynote presented by Oracle President Charles Phillips. He spoke about Oracle acquisitions, and more complete portfolio of products that Oracle have now. What caught my attention most was announcement of new product, new collaboration platform Oracle Beehive. It joins features of email, calendar, document sharing, web conference and few more in one suite. What is most important those tools are open, so user can use ex. MS Outlook to access mails or calendar entries. Security is one of the key features. When you share document with others, they can see document in theirs workspace. They can even make local copy of this document. When document is deleted by original author, then all copies of the document become unusable for anyone. This is because all shared documents are encrypted and signed. While opening document there is validation of access rights for document. For me it seems as this is based on technology acquired from Stallent. It seems that this product will try to get market from MS Sharepoint and IBM Lotus.

Today I took some time to see Exhibition Hall, and walk around the city. While walking I’ve found one interesting bus – I was Bus that was advertising Microsoft SQLServer:

It looks pretty old :). Microsoft does not want to be absent in San Francisco during Oracle’s big conference.

I’ve also participated in few session on SOA (Service Oriented Architecture) and AIA (Applications Integration Architecture). Basically I wanted to have better understanding of Oracle offering in SOA area.

Cheers,
Paweł

--
Related Articles on Paweł Barut blog:

Arriving San Francisco – OOW2008 day 1.

2008-09-22T02:41:00.007+02:00

Written by Paweł Barut
My trip to San Francisco went well. No delays, no problems with flight reservation – see what happened to Tim. I had small problems with hotel reservation, but finally everything is fine – I’m in different hotel then I’ve reserved. I’m suffering little from jet-lag. I slept an hour or two in plane, and then I could not sleep during night. So I feel little tired. The weather in SF is great, especially when comparing to very cold and rainy weather in Krakow last week.

My first experience of San Fracinsco is not very good. Seems to be not very friendly, and is definitely not of my style. I might change my mind when see more places in SF. But Oracle Conference is organized very well, so my attitude is improving :).

For today, I’ve planned mostly session regarding Security and Identity management. First of those session “Oracle Security Risk” was very good. Especially speaker Tanya Baccam from SANS.org was impressive. Presentation was about common problems with security in web based and forms application that use Oracle DB as backend. One type of attack was new to me: CSRF – Cross Site Request Forgery.

Next one was Security Roundtable – lot of questions from audience on variety of topics. Most hot one: applying CPU patches, Auditing, DB Valut, Encrypting data and transmissions.

Then was Oracle Identity Management lead by Matt Topper. Good overview of identity products from Oracle stack.

My last one session “Storage: A New Paradigm for Database” was also very good session. Ari Kaplan show now storage solutions can improve Backup/Restore times for database and decrease demand for storage space. He also presented new RAID-DP concept, and claims that it can provide better performance, with lower storage demand, and provide higher availability. This is something that I need to investigate in more details.

For the evening I’m going to take part in Bloggers Meetup.

Cheers,
Paweł
--
Related Articles on Paweł Barut blog:

Preparing for Oracle OpenWorld 2008

2008-09-13T11:41:00.003+02:00

Written by Paweł Barut
It's just few days left to Oracle OpenWorld 2008. First of all my trip starts on Saturday 20th at 13:00 in Kraków, Poland. Then I switch planes in Munich, Germany, and land in San Francisco at 19:20. Just 6 hours ahead, but when I add 9 hours difference in time zones, that it adds up to over 15 hours travel. The worst thing will be adaptation to totally different timezone. I will stay in Americas Best Value Inn at Hallam Street - I hope this is in reasonable walking distance from Moscone Center. I've created an map on Google Maps - the street view feature is great - now I know how the hotel neighborhood looks like. I've also checked transportation services by BART.
And the most important thing - My OOW2008 Schedule:

I do not expect big changes in this schedule, but still, some smaller changes are possible. I could skip one or more sessions to see Exhibition Hall or go for Unconference.
I'm also looking forward for meeting other Oracle Bloggers during Blogger Meetup organized by Eddie Awad.
Update: I forgot to mention about After Dark at Oracle OpenWorld where are evening parties listed.

See you in San Francisco,
Paweł

--
Related Articles on Paweł Barut blog:

DB Link to Oracle 11g

2008-08-13T20:59:00.001+02:00

Written by Paweł Barut
As you know in Oracle 11g passwords are case sensitive by default. This applies to connecting via SQL*Plus or other client tools. And it also applies to database links between databases. So when you link from Oracle 10g to Oracle 11g create database link like this:

CREATE DATABASE LINK my_link
CONNECT TO remote_user IDENTIFIED BY "CaSe_SeNsItIvE_PaSsWoRd"
USING 'TNS_ID_11G';

Do not forget to enclose password by double-quote marks!
When you do not set password this way, you will be getting:

ORA-01017: invalid username/password; logon denied.

Hope this small tip will be useful for someone.

Paweł

More on Creating Database Links.

--
Related Articles on Paweł Barut blog:

Lost Pictures - Recovered Pictures

2008-08-04T23:09:00.002+02:00

Written by Paweł Barut
This weekend I've spend great time with family, and we make lot of pictures. But when I've arrived home, and plugged camera to PC, it looked like this:
All pictures seems gone ...
For us computer geeks data lost is not an option. I've started to look for tools that can help me recover those data. In such situation rule number one is: not allow to write anything on this storage. Any additional write could lead to total data lost. I've googl`ed for software that could help. I've found lot of software that does "FAT Recovery", "Recover Digital Camera", "Data Recovery" - most of them priced 49-200$. Well, not big money, but still quite many when you need to recover 50 pictures. And you never know, if this software is worth it, and will solve your problem.
Finally I've found really great software that I would like to recommend: PC INSPECTOR™ smart recovery. It run very fast, and recovered almost all pictures. When looking into Camera counter and number of files, only one picture is missing. This software is freeware, but authors wants us to "Pay What You Want". And I've did it, as this software did his job perfectly.
Hope it help someone else to recover lost pictures.

Paweł

--
Related Articles on Paweł Barut blog:

Oracle OpenWorld 2008

2008-07-17T22:44:00.001+02:00

Written by Paweł Barut
It's only 2 month left to Oracle OpenWorld 2008. Few news about this conference and my first time ever participation. Last week I've registered for OOW2008 as Blogger. Yesterday I've received confirmation email - I was accepted. I'm really looking forward to be in San Francisco and meet with all Oracle geeks, enthusiast and other bloggers.
I still need to apply for US Visa, and organize my trip. And of course choose sessions in which I would like to participate.

There is also one good news for people in Eastern Europe, Post Soviet Republics, Middle East & Asia. People from those countries can get discount on registration fee.

Attendees will need to enter EMKT as their priority code during "Step 2" of the registration process.
The countries from the EE, CIS & MEA regions which qualify for the discount are:

African Operations (except South Africa), Albania, Armenia, Azerbaijan, Belarus, Bosnia & Herzegovina, Bulgaria, Croatia, Czech Republic, Cyprus, Estonia, FYR Macedonia, Georgia, Hungary, Iraq, Iran, Jordan, Kazakhstan, Kyrgyzstan, Latvia, Lebanon, Lithuania, Malta, Moldova, Montenegro, Oman, Palestine, Poland, Romania, Russia, Serbia, Slovakia, Slovenia, Syria, Tajikistan, Turkey, Turkmenistan, Ukraine, Uzbekistan, and Yemen.

Do not wait. Register for Oracle OpenWorld 2008 !

Paweł

--
Related Articles on Paweł Barut blog:

Oracle Database 11g Release 2 is coming

ORA-00904: "XMLROOT": invalid identifier

2008-05-17T11:55:00.000+02:00

Written by Paweł Barut
Some time ago I've had noticed strange problem with XMLRoot function. I was installing application on production server and I've noticed that code:

SQL> select XMLRoot(xmltype('<a>a</a>'))
2 from dual;

gives error:

select XMLRoot(xmltype('<a>a</a>'))
*
Error in line 1:
ORA-00904: "XMLROOT": invalid identifier

WTF, it was running perfectly on development and test environment!
Quick search revealed that XMLROOT is function in XDB schema, which was missing in production environment. I've just copies source code for function from test environment and I could proceed further.
After some time, I've decided to check why this function was missing?
Quick search showed that function is created by script ?\demo\schema\order_entry\xdbUtilities.sql
Strange, well documented function is created only when you install demo schemas? Seems that there should be another explanation.
Then I've found that in documentation this function has 2 mandatory attributes, while my code has only one attribute. So there are 2 versions of XMLRoot function:

SQL function; see documentation
Simplified version created by demo in XDB schema - this version can be also used in PL/SQL

Conclusion: my original code should look like that:

SQL> select XMLRoot(xmltype('<a>a</a>'), version '1.0', standalone yes)
2 from dual;

XMLROOT(XMLTYPE('<A>A</A>'),VERSION'1.0',STANDALONEYES)
------------------------------------------------------------------------

<?xml version="1.0" standalone="yes"?>
<a>a</a>

This can run without XMLROOT function in XDB schema.

Hope this will help someone to save some time.
Cheers,Paweł

--
Related Articles on Paweł Barut blog:

Oracle CPUApr2008 for DB: 10.2.0.4 patch set and first install

2008-04-20T15:40:00.001+02:00

Written by Paweł Barut
To my surprise CPUApr2008 is not available for latest patch-set of Oracle 10gR2 (10.2.0.4) that was released in first quarter 2008. In note that goes together with patch 6810189:

The following are the updated components of this patch set:
* Up to January 2008 CPU is included in this patch set
...

Based on this I was expecting that CPUApr2008 will be also available for 10.2.0.4 patch-set. In note 552248.1 you will find then:

1.3 Database 10.2.0.4 Patch Set
The Database 10.2.0.4 Patch Set includes the CPUApr2008 content.

So the CPUApt2008 was available to all at least month before official release and was included in 10.2.0.4 patch set! Is this situation secure?

Anyway I had to install CPUApr2008. It's good that there is no need to recompile views this time. I've sucessfully installed it on test server, with minor problem. In installation log I've found one problem:

BEGIN emd_maintenance.recompile_invalid_objects; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYSMAN.EMD_MAINTENANCE" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"SYSMAN.EMD_MAINTENANCE"
ORA-06512: at line 1

Hmm... After reconnecting, this commend run without errors:

SQL> conn / as sysdba
Connected.
SQL> BEGIN sysman.emd_maintenance.recompile_invalid_objects; END;

PL/SQL procedure successfully completed.

After all, server runs ok, and I do not find any problems within applications running on this.

Cheers Paweł

--
Related Articles on Paweł Barut blog:

Oracle Database 11g Release 2 is coming

2008-04-15T22:52:00.000+02:00

Written by Paweł Barut
I've just received Oracle Partner Network newsletter with invitation to Oracle Database 11g Release 2 Roadshow. On this Roadshow Oracle is also going to find partners for beta testing. My guess: This year Oracle 11g R2 will be the main topic on Oracle Open World.
Schedule of Roadshow in Europe:

May 12, 2008: UK, Reading
May 13, 2008: Benelux, De Meern
May 14, 2008: Germany, Muenchen
May 15, 2008: Italy, Milan
May 16, 2008: Turkey, Istanbul
May 19, 2008: Czech Republic, Prague
May 20, 2008: Poland, Kraków
May 21, 2008: Sweden, Stockholm
May 22, 2008: Spain, Madrid

If you are OPN member then you probably also get invitation.
I've registered. I hope to get confirmation soon. I do not expect many technical details. I rather expect marketing speech. For me most important would be to meet people that work on new DB version, and become beta tester for 11gR2.

Cheers Paweł

--
Related Articles on Paweł Barut blog:

Which Temporary Tablespace is used for sorts?

2008-03-24T18:34:00.001+01:00

Written by Paweł Barut
This time I will write about "Which Temporary Tablespace is used for sorts" ?. I was not wondering about this much, as usually there is only one Temporary tablespace in DB. Lets assume situation, as show on picture:

User A

Assigned to Temporary Tablespace TEMP_A
has table TA

User B

Assigned to Temporary Tablespace TEMP_B
has table TB
owns procedure PB (definer rights)

Both users has access rights for all above object (Select on Tables and Execute on Procedure)

So let's discuss some situations:

User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
User A executes procedure PB. Procedure PB opens cursor on table TB (or TA or any other). If disk sort is required then tablespace TEMP_B is used.

For me it was bit surprising. Especially that I did not find anywhere in Oracle documentation description for this behaviour:

TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments.

I was expecting that all sort segments will be created in tablespace that is assigned to that user. I was hopping to solve one of my issues that way. But it occurred that, sort segment is created by user B, because procedure PB uses user B rights. It is reasonable, as this is consistent with granting access to objects, and temporary objects are treated the same way as permanent ones. On the other hand select is run for user A - shouldn't TEMP_A be used in all cases? What is your opinion on that?

Cheers Paweł

--
Related Articles on Paweł Barut blog:

Horrible practice of storing users passwords

2008-03-09T19:24:00.000+01:00

Written by Paweł Barut
I've read today terrifying article about un-ethical storage of users password. It is often common practice that users passwords are stored unencrypted in database. But this case is even more dreadful. It about software, G-Archiver, that is available to everyone. If you ever used this software, you should change your G-mail password right now.
So what is the problem:

G-Archiver has build in user-name and password for one of g-mail accounts.
Whenever someone uses G-Archiver and provides his credentials for g-mail, user-name and password is send to author of this software
Additionally, any one who finds out the user-name and password stored in G-Archiver, can get passwords of thousand of previous users.

Go and read. Hope there are not too many of software that works like this one. It is really terrifying, that some people are so irresponsible. It could be well planed action to collect gmail users and passwords, or it could be just lack of imagination what are the consequences. I would like to believe that it was the second one.

But also big corporations do not care for security enough. It is common practice, that users password is stored internally as plain text. It can be easily found out by using "I forgot my password" feature. If in return you will get email with your original password, then it is a crappy web side. And you should never reuse password passed to such web side as this is big risk for you. Take a look at Password Security: It’s Not That Hard (But You Still Can’t Get It Right) for further explanation and examples.

Cheers Paweł

--
Related Articles on Paweł Barut blog:

Useful links on Oracle XML DB

2008-02-18T23:17:00.000+01:00

Written by Paweł Barut
Here is bunch of useful links related to Oracle XML DB and handling XML data in Oracle PL/SQL. It's mostly for my own reference, but I hope it will be useful for others too.

How do i store the counts of all tables by Laurent Schneider
Converting Between XML and Oracle Objects by Lewis Cunningham
Problems Calling .Net Web Services from UTL_DBWS by Stellan
First Tests of 11g Native Web Services and Revisiting 11g Native Web Services by Paul Gallagher
Oracle 11g Security - XMLDB is here to stay… by Marco Gralike
HOWTO: Create XMLType Table for Binary XML Usage by Marco Gralike
HOWTO: Saving XML data directly to disk by Marco Gralike
select from a comma-separated string by Laurent Schneider. BTW. Note that I've demonstrated this technique 7 month earlier :)

Cheers Paweł

--
Related Articles on Paweł Barut blog:

CPUJan2008: One thing that every DBA should know about it.

2008-02-11T22:54:00.000+01:00

Written by Paweł Barut
While reading documentation for latest Oracle security patch CPUJan2008 for database I've noticed one new required step. This step is: Recompile Views. One whould say that it's nothing strange, but to run this script database have to be started in UPGRADE MODE. For non-RAC installations it's not big issue, as anyway you have to plan downtime. But Oracle suggest that this recompilation can take 30 minutes:

"In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes."

Practically in my case it took no more than 5 minutes, so it's not big deal.
But it could be problem for RAC installations, that used when CPU is applied on one node, then catcpu.sql is run, then second node is being patched. So normally DB will be all the time available. But if it's needed to start database in UPGRADE mode it means that downtime is required. According to documentation it's not required to run view recompilation script during paching process. This script can be run after,

"however, the CPU installation will not be complete until the view recompilation is completed."

You can ommit this step only if:

Databases was created with Release 11.1.0.6 or later
Databases was created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied

I hope it also means that after CPUJan2008 was applied you will not have to run this script after next CPU came out (CPUApr2008, etc).

Cheers Paweł

--
Related Articles on Paweł Barut blog:

Spam, Spam filters, Being Spammer, Being Filtered-out ...

2008-02-03T18:46:00.001+01:00

Written by Paweł Barut
My thoughts about Spam. Some time agou I wrote about spam in comments on my blog, but this time it will be about email spam. This is something that from time to time irritates me a lot. Spam is something that nobody wants to see in his mails. And to solve this problem there are many spam filters, IP Block lists and other solutions. But none of them is 100% accurate. And this is what causes problems. Spam filers should be solving problems, but many times creates new ones.
In ideal situation spam filter eliminates 100% of spam, and passes 100% of emails that are expected by users. But it's not true. I will now show example situations, that lead me to conclusion, that spam filter are useless.
Situation 1.
Spam filter did not recognized spam mail, and I have to manually figure out that this is spam. So I need to one more click to delete message.
Situation 2.
Spam filter deletes mail that was intended for me. This was false alarm as it wasn't spam.
Situation 3.
I've send email to customer/friend. His spam filter blocked it. I did not received any delivery failure message.

In my opinion situation 2 and 3 are very dangerous and I would like to avoid any of those situations. In my opinion those situations makes spam filters useless. It is especially dangerous if this block is done by service provider, and when you cannot see list of spam being filtered out. This is what really annoys my and makes me angry. In fact it makes whole email system unreliable (I do not want to say useless), as you never know if you recipient get your email or not.

I do not know what is solution for this. I can see few options, but none of them is perfect:

Each and every email should be signed digitally by sender, and additionally by his service provider. Spam filers should be able to verify this and honor such signing, and not consider this to be spam. Of course spammers could find way to sign theirs mail too, and vanish this approach.
Everybody should use "return receipt" to confirm mail delivery. Well, quite simple, but personally I never allow my mailer to send confirmations, as I do not want to reveal when I've read mail.
Make mail system payable. So for every mail you send you have to pay small amount of money. $0.01 per email should not be problem for real email users, but could cost fortune for spammers. For this money service providers should ensure that your mail will reach recipient.
Use captcha to validate that email is send by real user. I could work as this: when spam filter suspects spam, it sends back email to sender with link to web page on which user will have to provide answer to captcha to make his mail pass throu spam filter.

At the end I would like to ask you: How do you deal with spam?

Cheers Paweł

--
Related Articles on Paweł Barut blog:

How to shrink Oracle Context Index

2008-01-23T21:50:00.000+01:00

On of databases I've found that one of Context Indexes is getting bigger and bigger everyday. It was around 5-10% grow per day for table DR$MY_TEXT_I$I which contains tokens(word). No of records indexed was almost the same, but index grown was unexpectedly big. It was due fact, that quite many rows were modified every day, and it caused re-indexing those rows during index synchronization (CTX_DDL.SYNC_INDEX). So I've decided to reclaim this space.

Method 1.

This was my first though:

Drop Index ... /Create Index ...
or Alter Index ... Rebuild

But those methods have some disadvantages:

Users cannot perform text searches during this operation,
It does not solve problem for longer time, as index will still grow ...

So I had to find root cause and eliminate it.

Method 2.

While looking for root cause I've find out, that Index has never been optimized, so it kept old (obsolete) data. My solution was:
SQL> exec ctx_ddl.optimize_index('MY_TEXT_I', 'FULL', 120);
SQL> alter table DR$MY_TEXT_I$I enable row movement;
SQL> alter table DR$MY_TEXT_I$I shrink space cascade;

With this method table DR$MY_TEXT_I$I took 30% of its original size.
So let me explain why this worked. With ctx_ddl.optimize_index context index was internally optimized. It means information about old documents were completely deleted and index was internally minimized. Remember - third parameter limits time (in minutes) allowed for optimization. If it is really big index it can take hours. But you can run this optimization many times, until your index will be fully optimized.
Then I've just shrunk table (shrink space cascade). But this operation requires to enable row movement on table first.

But this was one time operation. To avoid this problem in future I've scheduled job to run ctx_ddl.optimize_index on regular basis. Now this table has grown a little, but is no longer growing so fast.

Foot note:

It was tested on Oracle EE 10gR2 (10.2.0.3),
I'm not sure if row movement is supported by Oracle for Context Index table DR$<index>$I. It worked for me and I did not experience any problems since turning it on.

Cheers, Paweł

I've been tagged 3 times; 8 things about me

2008-01-12T17:17:00.000+01:00

Few busy days without any blog reading and after I've gone through my feeds it occurred that I've been tagged 3 times:

Thanks You for tagging.
Now I feel that I really need to write something about me:

I was born and grow up in Krosno in south-east Poland (see satellite picture),
I 15th when Communism fall down in Poland. At this moment Poland is member of Schengen Agreement, and I can travel around Europe without passport. Lot of changes in last 19 years,
I'm Thawte Web of Trust Notary,
In 1993 I took 2nd place in Nation Contest in Programming for College Students. Unfortunately I was 6 month too old to take part in international final :(
In 1990 I've sold my first program; small DB written in Clipper. I did not think that this program will be used for so many years. In 1999 I had to do some changes to support year 2000,
My first program was written in Basic and was run on PSP-80 (page in Polish)
I'm married and we have 2 daughters (6 and 2 years old),
My favourite TV show is Top Gear with Jeremy Clarkson , Richard Hammond and James May.

I will not invite anyone directly as I do not want to accused of spam. All my readers that have blogs are welcome to post 8 things about themselves.

Cheers,
Paweł

Do not trust Google Maps

2007-12-05T18:23:00.000+01:00

Ok, Might be title is too strong, but you should not trust Google Maps when searching for streets in Krosno, Poland (city where I was born). Here is sample, try to search for "Lwowska, 38-400 Krosno, Poland" and you get:
Search is working correctly, but names on street do not match reality. Instead of "Lwowska" street is "Jana Lenarta". Another example is "Platynowa" instead of "Podkarpacka". It seems that all street names are messed up on map. Hope they fix it soon.

Cheers, Paweł

Rant: Great fun with Wii

2007-12-02T11:48:00.000+01:00

I'm not a computer game player, but Nintendo Wii really impressed me. I was with a visit to my friend and he showed me his new gadget. We started to play for a while, and time passed by. It's really addictive. The best thing is that you do not seat at computer and use joystick or keyboard, but you stand up and move whole body to play. It really gives pleasure, and we have good time. If you do not know Wii yet check this Wii commercial.

Have fun
Paweł

More on PHP with DRCP support

2007-11-21T22:20:00.000+01:00

I was asked to compare DRCP to SHARED server connections. I've also promised to give some more statistics and comparisons for DRCP and standard connectivity.
First few results to compare to SHARED server as asked in comments to my previous post:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect DEDICATED server						Not tested
2	oci_pconnect DEDICATED server	1200	84	63	116	4077
3	oci_connect SHARED server	1200	280	264	150	1290
4	oci_pconnect SHARED server	1200	82	78	93	3965
5	oci_connect with DRCP	1200	104	94	45	3265
6	oci_pconnect with DRCP	1200	78	78	23	4396

In this case SHARED server is configured to use 4 dispatchers and max process = 1000.

Cheers, Paweł

Testing new OCI8 (1.3.0 beta) for PHP with DRCP Support

2007-10-27T18:53:00.000+02:00

I've seen Christopher Jones announcement on availability of beta OCI8 extension for PHP with support for DRCP. I've decided to give it a try. On my test machine running OEL5 I've installed latest PHP 5.4.2 and then replaced OCI8 sources with new OCI8 1.3.0 beta release. I've configured PHP:
./configure --with-oci8=$ORACLE_HOME --with-apxs2=/usr/sbin/apxs --with-config-file-path=/etc/httpd/conf --enable-sigchild --without-sqlite --without-mysql
compiled it and installed. Everything went fine, and on sample page I've displayed phpinfo():

I've changed php.ini to enable Database Resident Connection Pooling:

oci8.connection_class="TESTPOOL"

and tested that it is used in PHP.
Then I've decided to test and compare performance of different methods of connection to Oracle from PHP. I've created simple PHP page, that connects to Oracle, execute simple query that returns one row, and inserts one row to another table. I've prepared test script for Apache JMeter against this page.
Test specification:

Run on single processor machine
Oracle and Apache+PHP run in VMware
DRCP setup to have max 4 processes (3 processes effectively used, 4^th for authentication only)
6 simultaneous users opening the same web page 200 times
4 versions of PHP script were used:

oci_connect used to connect to Oracle without using DRCP (Database Resident Connection Pooling)– in this case new connection is created for every request from web browser. Connection is then closed at the end of script
oci_pconnect used to connect to Oracle without using DRCP – in this case new connection is created for every apache process that is responding to request from web browser. Connection is then closed at the end of script
oci_connect used to connect to Oracle with using DRCP (Database Resident Connection Pooling)– in this case for every request connection is obtained from pool and given back to pool at the end of script, seems that in this case only process is reused (PURITY=NEW), while session is not (see my observations when using SQL*Plus )
oci_pconnect used to connect to Oracle with using DRCP (Database Resident Connection Pooling)– in this case for every request connection is obtained from pool and given back to pool at the end of script. But in this case full potential of DRCP is used (PURITY=SELF)

Here are the results:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect without DRCP	1200	989	875	828	359
2	oci_pconnect without DRCP	1200	123	94	187	2505
3	oci_connect with DRCP	1200	117	94	115	2907
4	oci_pconnect with DRCP	1200	82	78	30	4060

I must say that I'm positively impressed by performance achieved by version 3 where oci_pconnect with DRCP was used. I expected that performance for version 2,3,4 will be very similar. But I've observed 60% increase in Throughput and 33% shorter response times when comparing version 2 and 4 (oci_pconnect with and without DRCP). I've changed one setting for my test in JMeter: I've turned on Keep-Alive for http transfer and here are the results:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect without DRCP						Not tested
2	oci_pconnect without DRCP	1200	84	63	116	4077
3	oci_connect with DRCP	1200	104	94	45	3265
4	oci_pconnect with DRCP	1200	78	78	23	4396

In this case I've observed less differences on Throughput and Response times between version 2 and 4. This is because with Keep-Alive causes that the same apache processes are used for requests coming from the same web browser. As connection is kept open, reduces time to establish connections between clients (web browsers) to apache server. Thats why we can see higher throughput. Additionally for oci_pconnect without DRCP less amount of sessions are created. Without Keep-Alive apache opened much more sessions, as it had to create more processes and every process had to connect to Oracle. Lets summarize my observations:

oci_pconnect with DRCP works stable in both cases (with and without Keep-Alive) giving maximun throughput and very low standard deviation,
oci_pconnect with DRCP has high standard deviation what means that there are lot of request with response time much higher then average,
In both cases connecting to Oracle with use of DRCP performed better than without.

Next time I'll try to go into extremes to see how DRCP behaves.
Cheers, Paweł

I've got spammed (in comments)

2007-10-26T22:36:00.000+02:00

Due to quite massive spam I'm receiving today in comments I had to turn on word verification for comment posting. Hope it will protect me from spam for future. And sorry for inconvenience when commenting,

Cheers, Paweł

Using Named Parameters within SQL

2007-10-14T13:59:00.000+02:00

One of the Oracle 11g new feature that I like is ability to use Named Parameter syntax in functions calls inside SQL. For example it is possible to run such query:

select DBMS_METADATA.get_xml(
          object_type     => object_type,
          name            => object_name) 
     from user_objects
where object_type in ('TABLE', 'VIEW');

It was not possible in previous Oracle versions (before 11g). It was only possible to specify parameters by position:

select DBMS_METADATA.get_xml(object_type, object_name) from user_objects
where object_type in ('TABLE', 'VIEW');

This is great feature as now SQL is more compatible with PL/SQL. And Named Parameters syntax is my favorite method of calling functions, as it gives me direct knowledge what value is assigned to parameters. Specifying parameters by position requires to remember sequence of parameters in function definition. Additionally named parameters syntax allows programmer to skip any of parameter that has default value, or to change order of parameters in function call. It is also possible to mix those 2 notations in single statement:

select DBMS_METADATA.get_xml(object_type, name => object_name) from user_objects
where object_type in ('TABLE', 'VIEW');

Hope you like it

Cheers, Paweł

Comments in SQL*Plus

2007-10-09T20:16:00.000+02:00

This Warning from Eddie Awad remind me of problem with comments I've had some time ago. I've prepared an script that was intended to run in sqlplus. Then I've extensively tested it, and as a last step, I've commented it. And ... did not test again. When this script was run on production caused errors. The problem was in the way that sqlplus interpret comments.
Example:

SQL> select * from dual;

D
-
X

runs fine, while this:

SQL> select * from dual; -- some comments

stops at second line waiting for input:

SQL> select * from dual; -- some comment
  2

It was really unpleasant experience.

Cheers, Paweł

More on Oracle 11g DRCP

2007-09-28T20:25:00.000+02:00

Due to comments from Sreekumar Seshadri and Krishna Mohan Itikarlapalli from Oracle, I've corrected my previous post on DRCP Functionality. I must own up that I've omitted reading chapter on DRCP in OCI Programmers Guide. Based on name "Database Resident Connection Pooling" I implied that it's only required to configure it at Database Server Side. In Fact it is partially true, because if you want to take advantage of Connection pooling it's needed to change client code too. There are 3 levels of benefits of DRCP and OCI enhancements in Oracle 11g:

Configuring DRCP at DB Side without any change in Application. In this case only processes are pooled, and every time new connection is made, new session is created. This is what I've observed in previous post when I was using SQL*plus.
and 3. need changes in Application in code that connect to DB. I will just quote OCI Programmers Guide:
- Applications that use OCISessionGet() outside of an OCISessionPool, and specify the connection class and set purity=SELF will be able to reuse both DRCP pooled server processes and sessions. However, following an OCISessionRelease(), OCI will terminate the connection to the connection broker. On the next OCISessionGet() call, the application will reconnect to the broker. Once it reconnects, the DRCP will assign a pooled server (and session) belonging to the connection class specified. Reconnecting, however, incurs the cost of connection establishment and re-authentication. Such applications achieve better sharing of DRCP resources (processes and sessions) but do not get the benefit of caching connections to the connection broker.
- Applications that use OCISessionPool APIs and specify the connection class and set purity=SELF make full use of the Database Resident Connection Pool functionality through reuse of both the pooled server process and the associated session and also getting the benefit of cached connections to the connection broker. Cached connections do not incur the cost of re-authentication on OCISessionGet().

So to really test DRCP I need to write some code to test it.

Cheers, Paweł

Oracle 11g DRCP: Functionality Test

2007-09-18T22:22:00.000+02:00

This post is continuation of my series of post about Database Resident Connection Pooling.
Let start with session and processes. My test shows that Pooled Servers are shown in v$session view only when there is user connected, that means session is busy. For inactive servers only process in v$process view is reported. To identify this processes we can run query like that:

select * from v$process
where program like '%(L0%)';

If our Connection pooling is started, at least one process will be reported. This is ~~so called Connection Broker~~ process that is authenticating connections and assigning them to another server for processing. According to my tests ~~Connection Broker~~ this process usually have highest number in brackets (ex. L003 if you set up max number of processes to 4). To see busy servers run this query:

select * from v$session
where program like '%(L0%)';

Note that Connection Broker is never reported in v$session view.

In next test I've tested if global variables in packages are stored between connections. My test confirms that packages are always initialized after new connection is taken from pool. That's very good, and is what I was expecting. This is advantage over traditional connection pooling at client side like in Java, where environment is totally shared between consecutive use of the same connection. (This is true when only process if reused, when PURITY=NEW)

My next test was concerning usage of context. I've created sample CONTEXT, initialized it in session and reconnected. I've ensured that session was assigned to the same process (pooled session) and read context. My test also confirmed that context is reinitialized between connections. In traditional connection pooling it will not happen. Also when using PHP and persistent connection you have to take care of clearing global variables and context after acquiring session from pool (or before giving it back to pool). Database Resident Connection Pooling will make programmers life easier, at least on that field. (This is true when only process if reused, when PURITY=NEW)

My last test for today was performance test. Test was run on rather poor desktop machine with single processor.

Testcase	Normal Connection	Pooled Connection	%Improvement
Connect and Disconnect 1000 times	226 sec	154 sec	31%
Connect, Insert one row, Disconnect 1000 times	309 sec	178 sec	42%

This results are very promising. First we see 30% improvement of time to connect to DB. What is more important we can see 40% less time on repeating the same simple statement after establishing each connection. The only explanation is that Pooled connection can reuse already parsed statement. That is good news if we think about scalability.

You may be also interested in Oracle Official White-paper on DRCP.

At the end I would like to thank Christopher Jones as he was involved in DRCP development. I've mailed with him few times last year about problems with PHP-Oracle connectivity and lack of connection pooling.

Cheers, Paweł

Blog roll reorganization

2007-09-16T13:20:00.000+02:00

Today i've reorganized my blogroll. Until now I was reading news using OraNA.info maintained by Eddie Awad. But there is too many post that are out of my interest. So i've decided to subscribe to feeds from some of them. Here is full list: Adrian Bilington, Alex Gorbachev, Alexander Kjerulf - CHO, Alexander Kornbrust, Alison Holloway, AMIS Blog, Blogging about Oracle, Carl Backstrom, Chen Shapira, Christopher Jones, Coskan Gundogar, Creating Passionate Users, Dimitri Gielis, Dominic Brooks, Don Seiler, Eddie Awad, Eric S. Emrick, Escape from Cubicle Nation, Gary Myers, H.Tonguç Yilmaz, Howard Rogers - Dizwell, Jeff Moss, Jeremy Schneider, Jonathan Lewis, Jorrit Nijssen, Kevin Closson, Laurent Schneider, Lewis Cunningham, Lutz Hartmann, Marco Gralike, Niall Litchfield, OraQA, Patch, Patrick Wolf, Penelope Trunk, Pythian Group, Robert Baillie, Sayed Jaffar Hussain, Steven Feuerstein, Steven Karam, Tanel Poder, Tim Hall, Tom Kyte, Venkatakrishnan J, YAS.

Cheers, Paweł

Operating on XMLType containing & (Ampersands)

2007-09-13T21:45:00.000+02:00

I was reading today Lewis post on How to Deal With Oracle XML and Ampersands ("&") in XML Documents. Personally I prefer different way. I use XMLElement function to generate XML documents. In my opinion this is more elegant way. The sample from Lewis post will look like that:

set define off
declare
  v_xml xmltype;
begin
  select xmlelement("ROWSET", 
           xmlelement("ROW", 
             xmlelement("COLA", 'lewis&me')))
    into v_xml 
    from dual;
end;
/

Disadvantage of this method is that you have to use SQL. But in most cases it's used while retrieving data from database, so it should not be a problem. Some time ago I was proposing to read document Mastering XML Generation in Oracle Database 10g Release 2. It is really good source of knowledge on XML manipulation inside Oracle database.
If you want to extract correctly ampersands and other special characters like < and > from XML document I suggest to read my post on differences between extract and extractvalue functions. If you want to manually convert & to & you can use function dbms_xmlgen.convert with second parameter set to 1:

SQL> select dbms_xmlgen.convert('me&amp;lewis', 1) from dual;

me&lewis

Cheers, Paweł

Alternative Syntax for Insert

2007-09-05T22:05:00.000+02:00

Alternative Syntax for Insert

Let me show my idea of SQL improvement, that I would like to see. Traditional insert works like that:

insert into table_a
    (column_1, column_2, column_3, column_4)
  values
    (value_1, value_2, value_3, value_4);

What I would like to have is something like that:

insert into table_a
   set column_1 = value_1
      ,column_2 = value_2
      ,column_3 = value_3
      ,column_4 = value_4;

With this syntax code would be easier to maintain. Especially, when you have to add or delete, or find error in insert to table with many columns. Biggest advantage of this would be close relationship between column name and value that will be set for that column. In INSERT that we know it column name and value are quite far from each other, so even for table with 10 columns it might be hard to find corresponding entries.
In PL/SQL there is an workaround to get similar solution:

declare
    v_row table_a%rowtype;
begin
    v_row.column_1 := value_1;
    v_row.column_2 := value_2;
    v_row.column_3 := value_3;
    v_row.column_4 := value_4;
    insert into table_a values v_row;
end;

But it's PL/SQL not pure SQL. I really would like to have this syntax in next Oracle release...

Cheers, Paweł

One Year Blogging

2007-09-04T19:22:00.000+02:00

It's one year since I started blogging. It's time for some statistics and summary. During last year:

I've published 69 posts - it's 1 post every 6 days (on average)
51 post are about Oracle (74%)
Over 29200 page views
84% of traffic comes from Google
35% of traffic is from US, Next on list are: UK (8%), India (7%), Germany (5%) and Poland (4%)

Top 10 of most often visited pages:

Oracle 11g DRCP: Database Resident Connection Pooling - second attempt

2007-08-31T23:52:00.000+02:00

In my last post I wrote about unsuccessful attempt to check functionality of DRCP: Database Resident Connection Pooling. Now it's time for success story :) The problem with previous situation was, that Oracle was not fully functional. And that was because I manually set, that i have all proper versions of linux packages, what was not true.
Now I've installed Oracle Enterprise Linux v 5.0 and Oracle 11g over it, with some help of Tim instructions.
I've added entry in TNSNAMES.ORA on linux box:

DB11GPOOL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)(SERVER=POOLED)
    )
  )

And tried to connect to pool

[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 01:44:53 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server

So I've started connection pooling (as SYS at Oracle):

exec DBMS_CONNECTION_POOL.START_POOL();

And tried again:

[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 02:02:37 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select 1 from dual;
         1
----------
         1

This time successful login.
Now I've left this session untouched for some time (more then 300 second - default time-out for pool)

SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2821
Session ID: 125 Serial number: 21

As you can see, session was timed-out by pool, and disconnected.
Ok, lets change parameters for pool to observer behaviour, when max number of session is reached.
Execute it as SYS:

begin
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','1');
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE','4');
end;
/

After I set it, I'was able to have 3 (not 4) simultaneous sessions using Connection pool. Whenever I start 4-th or 5-th session it is waiting for connection. You do not get any error, session just hung up. When one of active session is released (closed or timed-out) then one of waiting sessions is being connected. When I was looking for explanation, I found out one that in view V$CPOOL_STATS:

NUM_OPEN_SERVERS=4 - Total number of busy and free servers in the pool (including authentication servers)
NUM_BUSY_SERVERS=3 - Total number of busy servers in the pool (not including authentication servers)
NUM_AUTH_SERVERS=1 - Number of authentication servers in the pool

So, we have to have at least 1 authentication server in our pool. We should be aware of this when we setup small number of MAXSIZE of pool.
When I first read about database connection pooling I was thinking that when you start using all connections in pool, than next attempt to connect will open new normal session. But it will not be good. Mechanism of queues is better, because client will not overload database server. Might be it's not good for clients, as they might wait long time. On the other hand in well written application sessions should be short and closed immediately when no more needed in request.
More tests of DRCP in next posts...
Cheers, Paweł

DRCP: Database Resident Connection Pooling

2007-08-27T23:15:00.000+02:00

Finally I've found time and managed to install Oracle 11g. I did it on Fedora Core 5 run in VMWare Server on Windows 2K Pro.
My first attempt was to install it on Fedora Core 6 but I didn't succeed, and even I managed to corrupt VM totally. My second try was to create new VM with FC5. I've applied all available updates, and tried to install Oracle 11g. Oracle gave warning that some packages do not match requirements, but after manually marking check-box it proceeded and installed successfully. And seems to be fully functional. Do not try this for production DB :)
After that I've spend lot of time trying to connect to this DB from my Windows box. I was reconfiguring VM Network setting, FC Network setting many times without any sign of progress. Finally I found out that it can be firewall that is causing problem. It was really silly that I spend so much time to solve so simple problem...
Any way my Oracle 11g is up and running, so I started to test one of th features that are in my interest: DRCP: Database Resident Connection Pooling.
I've enables DRCP:

SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
PL/SQL procedure successfully completed.

And created sample user for tests:

SQL> create user pooltest identified by pool ;
User created.
SQL> grant connect to pooltest;
Grant succeeded.

I've added entry in TNSNAMES.ORA on Windows box:

DB11GPOOL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)(SERVER=POOLED)
    )
  )

And tried to connect using POOLED server:

c:\Temp>sqlplus pooltest/pool@db11gpool

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 27 22:30:08 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-56606: DRCP: Client version doesnot support the feature

So, DRCP needs proper client version to work. It's not a good news, as I thought that this will not require any changes at client side :(
I have to make my test on single box, or install Oracle on one more box (VM). Ok, first try from the same box where DB installed:

[oracle@vm111 admin]$ sqlplus pooltest/pool@db11gpool

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Aug 27 22:41:51 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Aborted

Seems that I have to dig little more more to make it working.

Cheers, Paweł

UPDATE: Success story on DRCP in next post.

It's feature not a bug ;)

2007-08-24T08:54:00.000+02:00

Friend of mine send me an link to funny picture:

Cheers, Paweł

Oracle 11g is released - my links

2007-08-18T13:18:00.000+02:00

As you probably know, Oracle 11g is released - now only for linux x86. I've already downloaded it, but had no time to install and play with it. Anyway I've decided to make summary of bloggers post on 11g for my future reference:

Oracle 11g Documentation
Oracle 11g new parameters by Howard Rogers "dizwell"
Oracle 11g new parameters part 2 by dizwell
Oracle 11g new parameters part 3 by dizwell
Bind variable peeking in 11g by Yas
Invisible Indexes by Yas
Security for XMLDB (DBMS_NETWORK_ACL_ADMIN) by Marco Gralike
11g Native Web Services by Paul Gallagher (Tardate)
FOLLOWS Clause in Create Trigger Statement by Jurgen Kemmelings
New PL/SQL Compiler Warning in Oracle 11g by Jurgen Kemmelings
Oracle 10.2 versus 11.1 by Julian Dyke
Oracle11g: Where’s My Alert Log? by Kevin Closson
Oracle Database 11g Release 1 (11.1) Installation On Enterprise Linux 4.5 and 5.0 by Tim Hall
Oracle 11g Release 1 RAC On Linux Using NFS by Tim Hall

There is much more posts about Oracle 11g but those listed above got by attention.

Cheers, Paweł

Pictures on Google map

2007-07-26T19:36:00.000+02:00

Few days ago I wrote about idea to integrate Google Maps and Picasa pictures in the way that you can see pictures on map. Today I've discovered that it is already done in very similar way. When you go to maps.google.com there is an new tab My Maps. Then you can check "Photos from Panoramio" and you can scroll and pane map to see pictures from different places. Really cool feature. I'm looking forward for same functionality with Google Picasa. BTW, Panoramio was (will be) bought by Google in May 2007. I wonder if they integrate it with Picasa ...
Have Fun with pictures

Cheers, Paweł

Oracle 11g Announced: Features Preview

2007-07-11T20:15:00.000+02:00

Oracle 11g was today announced. As an engineer I'm interested in new features of 11g release. I was already writing about this here and here. But this were just my thoughts based on others relations from Oracle Open World 2006.
Now official list of Oracle 11g new features is published. Here is page that contains more technical information on Oracle 11g.
Based on new features list I choose some that are very useful in project that I'm work now on, and I see them as important:

Binary XML storage and XML path indexing for schema-less XML documents
Result caches: improves speed of repeated execution of queries and function calls that access read-only or read-mostly data.
Database Resident Connection Pooling: enables faster connections to the database for application, that do not provide connection pooling (ex. PHP).
Faster triggers, including more-efficient invocations of per-row triggers
Faster simple SQL operations
SecureFiles: ti's new solution for storing large objects (LOBs) and datatypes such as XML
Automatic compilation for PL/SQL and Java in the database: new “native” compilation of PL/SQL that do not need C compiler.

New features that look interesting, and I want to play with them:

Semantic Technologies: native support for Resource Description Framework (RDF) and Web Ontology Language (OWL) standards,
Oracle Spatial enhancements: Richer, more-interactive map application development
New partitioning capabilities:
- Partitioning by parent/child references,
- Partitioning by virtual columns,
- More composite partitioning options, including range/range, list/range, list/hash, and list/list,
- Interval partitioning, which allows you to automatically create new partitions based on intervals, such as every month or every day,
Oracle Flashback Data Archive: enables fast query access to old versions of the data.
Expanded support for standards, such as XML Query (XQuery) 1.0 and service-oriented architecture (SOA)

I'm waiting for Oracle 11g to be available for download...

Cheers, Paweł

Extracting Data to Excel using only SQL*Plus

2007-07-09T19:21:00.000+02:00

Lately I had to quickly extract some data from Oracle DB to Excel. There are a lot of tools that can do that, ex. Toad or SQL Developer, but I had only access to SQL*Plus. Additionally I was on Windows machine and database was in UTF8. And extracted data was also using many different languages.
Here are steps I used to archive my goal:

start command line (CMD)
set NLS_LANG variable
```
set NLS_LANG=.UTF8
```
start sqlplus and log to database
```
sqlplus user/pwd@db
```

set long line size and avoid paging

SQL> set linesize 4000
SQL> set pagesize 0

set markup html in sqlplus
```
SQL> set markup html on spool on
```
It is important to use spool on clause, as it adds html header to spool file when spooling is started
start spooling
```
SQL&gt; spool a.html
```
run your query
turn off spooling
```
SQL&gt; spool off
```
open file in Excel

I've tested this procedure on Oracle 10g R2 (10.2.0.1 and 10.2.0.3) and Excel 2003

Cheers, Paweł

After Vacations: Tagging Photos with Geo Location

2007-07-07T23:16:00.000+02:00

Welcome back after my vacations. This time nothing about Oracle, but about some idea that was in my mind for few years and now is life: Google Picasa has new functionality: putting pictures on maps. I'm using Picasa to share pictures with friends and family for more than year now. And often I was asked: where did you take this picture? Now I can tag pictures with Geo coordinates and then are shown on map. Take a look at my public galleries on picasa, especially latest one with some pictures from my vacations.
There are still some features that I would like to see;When playing with google maps I would like to have option to show pictures from specific place. Pictures from public galleries could be presented. There might be an copyright issue, but it could be solved easily: users will have option to allow (or not) to publish pictures this way.
And I must buy Camera with build in GPS receiver to simplify mapping pictures...

Cheers, Paweł

Dynamically set name of spool file in SQL*Plus

2007-06-11T20:16:00.000+02:00

Small tip for today. Often there is a need to set name of spool file based on current time and might by also database you are connected to. It's often needed when script is run as scheduled task and there is a need to generate files with different names. So here is an solution for using date:

define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'

If there is a need to have also name of database included into filename then it can be read from global_name view:

define logname=date
column clogname new_value logname
select 'prefix_'||substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||
 to_char(sysdate, 'yyyymmdd') clogname
from (select global_name, instr(global_name, '.') dot from global_name);
spool '&logname'

Hope you find it useful.
I would also like to share an photo I made in Rome in February this year

To see more pictures you are welcome to follow Rome 1, Rome 2, Rome 3

Cheers, Paweł

Log Buffer #48

2007-06-08T16:54:00.000+02:00

Welcome to 48 edition of Log Buffer. While I read Log Buffer regularly for many months already it's first time I do review of databases blogosphere. Thanks to Dave Edwards from Pythian for inviting me.

This edition will be mostly related to development tools, techniques and environments for database applications. Let start with Inside Oracle APEX where Patrick Wolf presents internals of APEX Repository. In turn Dimitri Gielis is discussing how to reuse Oracle APEX components effectively. If you still do not know what APEX is, you can begin with Oracle Application Express—What’s it All About? by Peter Lorenzen.

Jonathan Bruce at his Weblog is reviewing and recommending DataDirect Connect for ADO.NET Oracle provider for Visual Studio.
Anton Scheffer from AMIS is solving Sudoku with single SQL statement using MODEL Clause; really impressive method, I must find time to experiment with MODEL clause as it seems to be very powerful.

Linear Algebra is not commonly known feature of Oracle database. Marcos M. Campos on Oracle Data Mining and Analytics is presenting how it can be used for Principal Components Analysis and Visualization. Unfortunately complexity of examples rather will not help with wider usage of UTL_NLA package.

Jonathan Lewis on his Oracle Scratchpad presents differences in new (Oracle 10g) and old sorting algorithm. Difference is visible only for those who were using side effect of sorting by ROWID in previous oracle releases.

Jorrit Nijssen alias Jornica issued an warning on using after statement trigger and returning clause together as there is an side effect.

For many of Oracle DBAs it might be weird idea: Kevin Closson is proposing to install Oracle over NFS. He states that it's cheaper, simpler and will be even better with upcoming Oracle 11g. By the way Oracle 11g will be launched on 11th of July according to Eddie Awad. This announcement made big rumour in Oracle blogosphere and Lutz Hartmann is now documenting new feature of collecting statistics in 11g.

Laurent Schneider is discussing Best Practices for Aliasing columns in SQL queries. Dominic Delmolino on Oracle Musigns suggest another method: uniquely name columns. Also Steven Feuerstein on Toad World shows good practice: "Don't put COMMIT; in you code!".

Kevin Closson and Doug Burns discuses what is the role of Oracle, OTN, Technorati and readers for bloggers community.

Jeff of his Jeff' SQL Server Weblog that database design should not be made due to desired output. "We often see bad designs submitted at SQLTeam.com with the justification of that design being the client wanted it that way! ... you need to demonstrate to them that even when storing the data properly, we can *still* create a View which returns those exact results, making them happy".

Data Quality: Where to Validate? Beth on Confessions of a database geek is reviewing levels where validation should occur also giving some recommendations.

Roland Bouman shows What MySQL can do to enter the off-line Web. In my opinion the idea of using Web applications offline will be very hot in nearest future.
Peter Zaitsev is discussing an issue with degraded performance after upgrading MySQL 4 to MySQL 5. He also presents some workaround for that problem.
Paul McCullagh on PrimeBase XT shows how to manipulate BLOBs in MySQL using BLOB Streaming Engine.
Ronald Bradford on Technical Notes and Articles of Interest reviews Clustering solution for MySQL database.
Kevin Burton is predicting end of RAIDs . Instead he proposes to use many instances of MySQL with replication mechanism.
Xaprb announced release of MySQL Archiver 0.9.1 tool to archive content of critical OLTP tables.

JonC is answering most common Security Questions Applied to MySQL and pointing that biggest challenge is to deal with human errors. Alex Kornbrust is presenting THC Orakel new tool to Sniffer Oracle Passwords. He also points few errors in white-paper that is accompanying the tool.

Mark Rittman is on vacations leaving us with lot of BI Articles and Links to read.

Steve Jones on SQL Musings talks over High Availability of SQL Server. Euan Garden on his blog announces interesting Web Casts about new features of SQL Server 2008. While Jamie Thomson show how Group by Grouping Sets will work in Katmai: SQL Sever 2008.

Josh Berkus is reporting on JPUG 2007 PostgreSQL Conference and his keynote on "PostgreSQL Today and Tomorrow". "This was the most challenging conference for me so far since the entire conference was in Japanese, a language of which I have no comprehension."

At the and some news from my country. Jakub Pawlowski is summarizing XV PLOUG (Polish Oracle Users Group) Seminary - "Oracle Application Server 10g R3: applications, productivity, security, reliability."
That's all for today. Wait for next Log Buffer on June 15th presented by Coskan Gundogar.

Paweł

Rows to single string or aggregating strings

2007-06-04T23:50:00.000+02:00

In oracle there is no build-in group function that will concatenate strings, just like max summarizes all numbers. But there is quite easy way to do that in pure SQL. Assume we want to have get as an query result list of tables and column. But we want all columns to be concatenated and comma separated. Result should look

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS	BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS	BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE	CCH_URL, CCH_TIME, CCH_VALUE

Lets start with hierarchical query to build "tree" for each table. The only one branch of this tree will start with first column and end with last one. SYS_CONNECT_BY_PATH gives as option to track that branch:

SQL> select table_name, 
  2        SYS_CONNECT_BY_PATH(column_name,  ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  ;

TABLE_NAME	COLUMN_LIST
BLOGS	, BGH_ID
BLOGS	, BGH_ID, BGH_NAME
BLOGS	, BGH_ID, BGH_NAME, BGH_FEED_URL
...	...

Then I just take max path and truncating leading comma gives me expected result:

SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  group by table_name;

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS	BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS	BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE	CCH_URL, CCH_TIME, CCH_VALUE

Function Max works becouse if one string (ex.: "abc") is an prefix of the second one (ex: "abc123") than this second one is threated as bigger. I must warn that it might be not true for all NLS settings.

But what if we want columns to be ordered alphabetically? Than we have to use Row_Number function to calculate column position in table and make same changes to SQL:

SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from (select table_name,  column_name,  ROW_NUMBER() OVER (partition by table_name ORDER BY column_name) AS curr  from user_tab_columns)
  4  start with curr = 1
  5  connect by table_name = prior table_name
  6             and curr = prior curr +1
  7  group by table_name;

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_AUTHOR, BLC_AUTHOR_URI, BLC_BLG_ID, BLC_CONTENT, BLC_IDENTIFIER, BLC_PUBLISHED, BLC_PUBLISHED_TEXT, BLC_UPDATED, BLC_UPDATED_TEXT, BLC_URL
BLOG_POSTS	BLG_BGH_ID, BLG_CONTENT, BLG_ENTRY, BLG_ID, BLG_IDENTIFIER, BLG_PUBLISHED, BLG_PUBLISHED_TEXT, BLG_TITLE, BLG_UPDATED, BLG_UPDATED_TEXT, BLG_URL
BLOGS	BGH_FEED_URL, BGH_ID, BGH_NAME
FEED_CACHE	CCH_TIME, CCH_URL, CCH_VALUE

Hope you will find it useful and fun.
All samples where run on Oracle 10gR2 XE 10.2.0.1.
Schema used in this sample is described in Blogger Backup tool entry.

Paweł

Oracle Blog Aggregators and Bloggers Community

2007-05-28T23:32:00.000+02:00

Today I've noticed that my blog became aggregated on official Oracle Blogs site. For me it is really good news as it can bring me more readers. My blog is also aggregated at OraNA.info by Eddie Awad (Oracle blogger of the year 2006) and on orablogs.com by Brian Duff (Oracle employee). Unfortunately I cannot find any of my post on OTN Semantic Web. I hope it will change in near future, as this is still beta release...
For me it seems that latest discussions about Oracle Bloggers community have positive impact. Official list of Oracle blogs not maintained by Oracle employees is more complete now. Also The Pythian Group is doing great job for Oracle community by publishing series of Log Buffer.

Cheers, Paweł

Solving ORA-22905: cannot access rows from a non-nested table item

2007-05-28T23:14:00.000+02:00

Some time ago i wrote about Binding list variable. In fact it is conversion of string to rows. My example works fine in SQL but when used in PL/SQL it might cause error ORA-22905: cannot access rows from a non-nested table item. I do know what are the necessary conditions for this error, as it does not always happen in PL/SQL. I faced it usually for quite complicated queries. To overcome this problem I just cast binding variable to varchar2 as shown below:

  procedure test_proc(p_list in varchar2)
  is
  begin
    for r in (select items.extract('/l/text()').getStringVal() item
              from table(xmlSequence(
                extract(XMLType(''||
                  replace(cast (p_list as varchar2(4000)),
                          ',','')||'')
                      ,'/all/l'))) items) loop
      null;
    end loop;
  end;

It worked for me in all situations where I faced ORA-22905 on Oracle 10gR2 (10.2.0.3)

Paweł

OraNA.info upgraded

2007-05-22T22:10:00.000+02:00

Eddie Awad just announced that he has improved functionality of Oracle Blogs aggregator OraNA.info. Now it shows post from long history, not only last 20. It is now really usable.
And if you are Oracle Blogger, go and join OraBloggers group on Google groups.

Cheers, Paweł

Zend Core for Oracle v 2.0.1

2007-05-21T19:30:00.000+02:00

Zend has announced new version of Zend Core for Oracle.

Key features contained in all Zend Core V2.0 releases include:

Improved Windows Performance and Reliability – Up to 200-300%
PHP 5.2.1 enhanced with several key security and bug fixes
Unified "stack" installer - Bundled copies of Apache and MySQL for quick and easy install and configuration
Bundled Zend Framework for fast and easy application development
Universal Database Support - drivers provided for most popular databases.
Mac OS X Support
Zend Core Updater - Automatically download Zend Network updates

Give it a try...

Cheers, Paweł

Moving domain index to another tablespace

2007-05-20T12:19:00.000+02:00

It seems to be very easy task that can be accomplished using rebuild option:

ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS;

We can even do it online for most indexes:

ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS ONLINE;

But trying this for domain index (ex. Spatial Index) causes error:

ALTER INDEX MY_GEOMETRY rebuild tablespace users;
ORA-29871: invalid alter option for a domain index

What is the reson and how to overcome this? Domain index is in fact set of other objects. For spatial index it is table (MDRT_*). Spatial Index can be moved quite easy with

ALTER INDEX MY_GEOMETRY 
REBUILD PARAMETERS('TABLESPACE=NEW_TBS');

Oracle Text CONTEXT index is set of tables:

DR$[index_name]$I
DR$[index_name]$K
DR$[index_name]$N
DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate text index. But first storage parameters have to be defined:

begin
   ctx_ddl.create_preference('MY_TEXT_STORE', 'BASIC_STORAGE');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'K_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'R_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'N_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_INDEX_CLAUSE',
       'tablespace NEW_TBS COMPRESS 2');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'P_TABLE_CLAUSE',
       'tablespace NEW_TBS');
end;
/

and then just drop and create index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column) 
indextype is ctxsys.context parameters(
'storage MY_TEXT_STORE');

For more information go to Oracle Text Application Developer's Guide

Cheers, Paweł

RMAN TIP: Avoiding ORA-27211

2007-04-19T00:05:00.000+02:00

I'm using RMAN to backup only to disk. Usualy when I have to delete obsolete backup I run command:

delete obsolete device type disk;

It is anoying to type device type disk every time, but

delete obsolete;

causes:

RMAN-03002: failure of delete command at 09/12/2005 08:44:27
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

The reason is that some time ago I was testing backups to tape. There is simple solution:

Configure CHANNEL device type 'SBT_TAPE' clear;

It will remove information about tape device configuration, so all commands will use default device: disk.

Cheers, Paweł

CPUApr2007 and Interim patch 3 for 10.2.0.3 on Windows

2007-04-18T13:16:00.000+02:00

On Monday I've downloaded Interim patch 3 (5916257) for 10.2.0.3 on Windows released on 13th April. Today I've downloaded CPUApr2007 (5948242) alias Interim patch 4. I've compared content of these patch and majority of files are identical. Most changes are in patch description files. The only important file that has some changes is oracle.exe while all other *.dll files are identical. Also scripts to apply do database catcpu.sql differs only by 2 lines.
Interim patch 3:

DEFINE CPU_NUMBER  = 5916257
DEFINE CPU_DESC    = 'Patch3'

CPUApr2007:

DEFINE CPU_NUMBER  = 5948242
DEFINE CPU_DESC    = 'CPUApr2007'

I wonder why Oracle published 2 patches just in 4 days? On Windows all interim patches contain also previous interim patch. For me the only explanation is that they didn't what to include other bug description into CPU. What do you think?

Cheers, Paweł

Oracle: Cursors, Bind Variables and Performance

2007-04-06T19:16:00.000+02:00

I've found today very good document on Efficient use of bind variables, cursor_sharing and related cursor parameters dated 2002. Document is related to Oracle 9i, but also applies to Oracle 10g.

Paweł

Monitor progress of long running processes

2007-03-28T23:05:00.000+02:00

This time I want to share how I use v$session_longops to monitor Oracle long running queries but also how to create your own entries in this view. When monitoring long lasting operation I'm interested what is running now, and what was finished lately. For this purposes I use query:

SELECT sid,
  serial#,
  opname,
  target_desc,
  percent,
  sofar,
  totalwork,
  to_char(start_time,   'hh24:mi:ss') start_time,
  to_char(efin,   'hh24:mi:ss') estimate_fin,
  case when sofar <> totalwork and last_update_time < sysdate-1/10000 then '*' else null end broken
FROM
  (SELECT sid,
     serial#,
     opname,
     target_desc,
     sofar,
     totalwork,
     to_char(CASE
             WHEN totalwork = 0 THEN 1
             ELSE sofar / totalwork
             END *100,    '990') percent,
     start_time,
     last_update_time,
     start_time +((elapsed_seconds + time_remaining) / 86400) efin
   FROM v$session_longops
   ORDER BY  CASE
             WHEN sofar = totalwork 
                THEN 1
                ELSE 0 END,
          efin DESC)
WHERE sofar <> totalwork or rownum <= 20;

It lists all currently running operations and up to twenty lately finished. The most important columns are estimate_finish – it predicts time of process end, and broken – if contains star (*) it is very possible that process was terminated or hung. Constant 1/10000 (about 8 seconds) in comparison

efin < sysdate-1/10000

is to avoid false alerts. If single step of yours process takes more then 8 seconds than you should alter this value to meet your needs. You can find more information on v$session_longops view in Oracle documentation.
Sample output:

Lets take a look how to write your own entries to long operations view. As a samle I'll just use simple loop that for each record in ALL_OBJECTS process will sleep for 0.1 second:

declare
  v_rindex pls_integer;
  v_slno   pls_integer;
begin
  v_rindex := dbms_application_info.set_session_longops_nohint;
  for r_qry in (select t.*, rownum rn, count(*) over () cnt 
                  from ALL_OBJECTS t ) loop
    dbms_application_info.set_session_longops
      ( rindex =>  v_rindex,
        slno   =>  v_slno,
        op_name => 'ALL_OBJECTS processing',
        target  =>  0,
        target_desc => 'ALL_OBJECTS',
        context     => 0,
        sofar       => r_qry.rn,
        totalwork   => r_qry.cnt,
        units       => 'loops'
      );
    dbms_lock.sleep(0.1);
  end loop;
end;

So what is important here:

rindex – for first call it must be ste to dbms_application_info.set_session_longops_nohint – it means add new row to v$session_longops view
rownum is used to get row number; it is passed to sofar parameter
analytical function (count(*) over () cnt) is used to calculate all rows (steps) in that process; it is passed to totalwork parameter
rindex, slno – should be assigned always to the same variable; it is needed to pass information about row that should be changed

Additionaly you can add when others handler to set progress to 100%, but you do have to do that. Broken flag described above should work fine, and you know if process ended normally or with errors. Full specification of dbms_application_info.set_session_longops

Paweł

Table that cannot be deleted

2007-03-22T23:45:00.000+01:00

Today I run into interesting situation on one of development machines. I was drooping all tables of one user and after that there was still one table with name SYS_IOT_OVER_54321. Seeing that name I've concluded that it must be an overflow table for some Index Organized Table. But there were no other table in that schema. Of course I was not able to delete it.
Than I've realized that there are some tables in recycle bin. This situation is inconsistent as table is in recycle bin but its overflow area is still in normal table list.

Here is test case:

SQL> create table IOT_TEST
  2  (a_pk number not null primary key
  3  ,a_number number
  4  ,big_string varchar2(4000)
  5  ) organization index
  6  including a_number overflow tablespace users;

Table created.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME                                         
------------------------------ ------------------------------                   
SYS_IOT_OVER_14580             IOT_TEST                                         
IOT_TEST

So lets drop it and see what will happen:

SQL> drop table IOT_TEST;

Table dropped.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME                                         
------------------------------ ------------------------------                   
SYS_IOT_OVER_14580             BIN$rKpJH0NuROKt+Woa00+hMg==$0

As you can see we still have IOT Overflow table that cannot be deleted:

SQL> drop table SYS_IOT_OVER_14580;
drop table SYS_IOT_OVER_14580
           *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

The only way to get rid of that table is to purge recycle bin:

SQL> purge recyclebin;

Recyclebin purged.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

no rows selected

In my opinion it is bug. I haven't yet reported it on metalink, but I'll fill SR soon. I've confirmed it on Oracle 10g R2 (versions: XE, 10.2.0.1, 10.2.0.2, 10.2.0.3)

Paweł

„Do not use Linux, please, pirate our software” - Microsoft

2007-03-16T00:05:00.000+01:00

It seems that Microsoft wants You to pirate it's software. According to The Register, Jeff Raikes, Microsoft business group president, said that if You have to pirate, You should pirate Microsoft software. The idea behind it is that some percentage of pirates become paying customers. Microsoft wants You to:

pay and use MS software,
use MS software not paying for it; might be in future you will pay,
not use other software. Microsoft does not want You to switch to competing software, especially legal royalty-free FLOSS software such as Linux, OpenOffice.org or Mozilla.org

So why they implemented Windows Genuine Advantage Programme, if they do not want to push You to be legal? Paweł

Polish Students won ACM Programming Contest

2007-03-15T21:28:00.000+01:00

Today students from Warsaw University won ACM-International Collegiate Programming Contest. They solved 8 tasks out of 10, see Final results.

Congratulations to my compatriot,
Paweł

Moving/Adding Control file when using SPFile

2007-03-07T18:13:00.000+01:00

Adding controlfile or moving to new location is tricky when your database was started with server parameter file (spfile). It is because You cannot edit spfile when database is not running.
Method 1 – preferred by me:

alter system parameter to point to new (planed) location of controlfiles

alter system set control_files
= '/dir1/CONTROL01.CTL'
, '/dir2/CONTROL02.CTL'
, '/dir3/CONTROL03.CTL'
scope=spfile;

shutdown database,
copy one of controlfile to new locations,
startup database.

Method 2:

shutdown database,
copy one of controlfile to new locations,
copy spfile to pfile:
```
create pfile from spfile;
```
manually edit pfile,
copy pfile back to spfile:
```
create spfile from pfile;
```
startup database.

Cheers, Paweł

Technorati

2007-03-06T22:22:00.000+01:00

I've just created my Technorati Profile, so this post is just to interrelate this blog with my Technorati account.
Paweł

the Month of PHP Bugs

2007-03-05T19:57:00.000+01:00

For all PHP developers, web admins and users this might be very interesting. the Month of PHP Bugs is an initiative to improve the security of PHP. It focuses on PHP Core vulnerabilities that are dangerous for web servers with applications written in PHP. It is done in hope that flaws that are publicly disclosed will get fixed quickly.

Cheers, Paweł

Yet another tokenizer in Oracle

2007-03-03T22:54:00.000+01:00

I was in need to have function that transforms string into table of words. Also words can be separated by many different chars. For this purpose i've created function:

create or replace function tokenizer
  (p_string in varchar2
  ,p_separators in varchar2
  ) return dbms_sql.varchar2s
is
  v_strs dbms_sql.varchar2s;
begin
  with sel_string as 
      (select p_string fullstring 
         from dual)
  select substr(fullstring, beg+1, end_p-beg-1) token
   bulk collect into v_strs
   from (select beg, lead(beg) over (order by beg) end_p, fullstring
           from (select beg, fullstring
                   from (select level beg, fullstring
                           from sel_string
                         connect by level <= length(fullstring))
                   where instr(p_separators ,substr(fullstring,beg,1)) >0
                 union all
                 select 0, fullstring from sel_string
                 union all
                 select length(fullstring)+1, fullstring from sel_string))
    where end_p is not null
      and end_p>beg+1;
  return v_strs;
end;

Usage is very simple. First parameter is string that should be tokenized, second parameter is string with characters that should be treated as word separators. Example:

SQL> set serveroutput on
SQL> declare v_strs dbms_sql.varchar2s;
  2  begin
  3    v_strs := tokenizer('I was in need to have function that transforms string into table of words.'
  4        , ' ,:;.<>[]{}()');
  5    for i in v_strs.first..v_strs.last loop
  6      dbms_output.put_line(v_strs(i));
  7    end loop;
  8  end;
  9  /
I                                                                               
was                                                                             
in                                                                              
need                                                                            
to                                                                              
have                                                                            
function                                                                        
that                                                                            
transforms                                                                      
string                                                                          
into                                                                            
table                                                                           
of                                                                              
words                                                                           

PL/SQL procedure successfully completed.

Let me explain some elements

  with sel_string as 
      (select p_string fullstring 
         from dual)

is just to bind string parameter into SQL just once. Otherwise it will have to be binded in many places.

                 select beg, fullstring
                   from (select level beg, fullstring
                           from sel_string
                         connect by level <= length(fullstring))
                   where instr(p_separators ,substr(fullstring,beg,1)) >0

Internal select generates numbers from 1 to length of string. Outer select chooses only these rows (numbers) that are separators in string.

                 union all
                 select 0, fullstring from sel_string
                 union all
                 select length(fullstring)+1, fullstring from sel_string))

adds separator at beginning and end of string, so first and last work can be recognized.

 select beg, lead(beg) over (order by beg) end_p

gives as positions of two consecutive separators, and finally

  select substr(fullstring, beg+1, end_p-beg-1) token
   bulk collect into v_strs
   ...
    where end_p is not null
      and end_p>beg+1;

select words.

Might be you find it useful,
Paweł

Bug 5705795 - AMAZON-LIKE ?

2007-02-15T18:38:00.000+01:00

Today I've reviewed list of 10.2.0.3 patch 1 for Windows and i've noticed bug with name: 5705795 - MANY CHILD CURSORS CREATED FOR AMAZON-LIKE WORKLOAD IN 10.2.0.3. I was aware of this bug as it was mentioned in note 403616.1 on Metalink. I do not think it's good idea to use customer name in bug description. From this I can assume:

Amazon is using latest Oracle Database,
Amazon is running on Linux or Windows (as this bug is only for these platforms),
probably Amazon is using Linux as patch for linux was released earlier,
it gives some inside view of software design used at Amazon,

I do not think Amazon is happy with these reveals.

At least bug 5705795 is not public on Metalink.

Cheers, Paweł

Empty String IS NOT NULL?

2007-02-07T23:15:00.000+01:00

Today friend of mine show me interesting case:

declare
  var varchar2(3);
  chr char(3);
begin
  var := ''; -- Empty string here
  chr := var;
  if var is null then
    dbms_output.put_line('VAR IS NULL');
  end if;
  if chr is null then
    dbms_output.put_line('CHR IS NULL');
  else
    dbms_output.put_line('CHR IS NOT NULL');
  end if;
end;
/
VAR IS NULL
CHR IS NOT NULL

I must say that I was expecting that both variables will be null. It might be my lack of experience with CHAR datatype as I usually use VARCHAR2 even when it is expected that string will have constant length.

declare
  var varchar2(3);
  chr char(3);
begin
  var := null;
  chr := var;
  if var is null then
    dbms_output.put_line('VAR IS NULL');
  end if;
  if chr is null then
    dbms_output.put_line('CHR IS NULL');
  else
    dbms_output.put_line('CHR IS NOT NULL');
  end if;
end;
/
VAR IS NULL
CHR IS NULL

Just as expected. So it seems that in some cases NULL and empty string is not equivalent. But this difference can be only seen in PL/SQL. When I tried to reproduce this in pure SQL it was always NULL:

SQL> create table a(v1 varchar2(3), v2 varchar2(3), c1 char(3), c2 char(3));
Table created.
SQL> insert into a values (null, '', null, '');
1 row created.
SQL> select dump(v1) v1,dump(v2) v2,dump(c1)c1,dump(c2) c2 from a;
V1    V2    C1    C2
----- ----- ----- -----
NULL  NULL  NULL  NULL
SQL> update a set c1 = v1, c2 = v2;
1 row updated.
SQL> select dump(v1) v1,dump(v2) v2,dump(c1)c1,dump(c2) c2 from a;
V1    V2    C1    C2
----- ----- ----- -----
NULL  NULL  NULL  NULL

Cheers, Paweł

Interesting posts 2007-02-06

2007-02-06T09:17:00.000+01:00

Here are some interesting posts I've read recently:

User Interface Design: Complexity vs. Flexibility - Rob Walling is traing to find aswer to question: can a design be simple and flexible at the same time?
How to scale... Tom Kyte post about scalability of middleware and database. Do not forget to read Tim Hall response Middle-tier diet….
Difference between Jeff Bezos and Bill Gates? by Kathy Sierra - very interesting story about top CEO's attitude to users.

Cheers, Paweł

Restore CONTROLFILE and/or SPFILE from autobackup

2007-02-01T22:43:00.000+01:00

As I mentioned earlier in post about setting up autobackup, its time to describe how to restore CONTROLFILE and SPFILE from autobackups. If your database is still running and what You want is just get historical version of SPFILE or CONTROLFILE then it is easy task. Just start RMAN:

RMAN TARGET=/

And enter command to restore CONTROLFILE:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 01-FEB-07

The until time clause sets time of validity for CONTROLFILE. By default RAM looks for autobackups for seven days into past starting from that time. It can happen that for several days there were no changes, and autobackup was not invoked. In that case add MAXDAYS clause:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;

It will force RMAN to look for copies for more days into past. Similarly You can restore SPFILE:

RMAN> restore until time 'sysdate-3'
2> SPFILE to 'c:\temp\SPFILE' from autobackup maxdays 100;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01-FEB-07

After restoring SPFILE You can convert it to PFILE, so You can easily read settings:

RMAN> sql "create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''";
sql statement: create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''

Now more complex scenario: You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don't it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it'is part of the name of autobackup file. Details in my previous post. So if autobackup file name is 'c-669001291-20070129-03' then DBID is 669001291. Now we can start recovery:

RMAN target=/

At the beginning set Database ID:

RMAN> SET DBID 669001291;

Then startup instance:

RMAN> startup force nomount;

and run recovery of SPFILE

RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE SPFILE FROM AUTOBACKUP; }

I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them.

RMAN> shutdown;
RMAN> startup force nomount;
RMAN> RUN { 
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE CONTROLFILE FROM AUTOBACKUP; }

Now You can continue recovery of datafiles.

Paweł

Autobackup of SPFILE and CONTROLFILE

2007-01-30T12:52:00.000+01:00

Even if you do not use RMAN to take backups of your database, you might find it useful to take snapshots of your SPFILE and CONTROLFILE automatically. You can configure Recovery Manager (RMAN) to automatically create copies of CONTROLFILE and SPFILE when you make changes to database such as:

adding/dropping a new tablespace or datafile
altering the state of a tablespace or datafile (online/offline/read only)
adding a new online redo log,
renaming a file
adding a new redo thread
and many more...

To setup autobackup just start RMAN and connect to database

RMAN target=/

RMAN target=sys/password@db

set directory and format of backup files:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';

In name of autbackup file you have to use %F. It will be expanded to C-XXXXXXXXX-YYYYMMDD-NN, where:

XXXXXXXXX – database id
YYYYMMDD – day, when backuped
NN – change number during day, starts with 00, and represented in hexadecimal

Activate autobackups:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Now autobackups are active, and every time you change database structure controlfile will be automatically backed up.
If you use RMAN for backups it will create copy of SPFILE or CONTROLFILE also after each backup operation.
I've tested it on Oracle 10g (10.2) but it should work in Oracle since version 8i. Next time i'll write how to restore SPFILE or CONTROLFILE from these backup.

Cheers, Paweł

Update: How to restore CONTROLFILE and SPFILE from RMAN autobackups

Oracle XMLType: exctractvalue vs. extract

2007-01-11T22:48:00.000+01:00

I always thought that exctarctvalue(xml, '/Node')
is equivalent to xml.extract('/Node/text()').getstringval()
Usually it is true, but in some cases it is not. When node contains special characters like &, >, < etc... then results are different. Let me give an example:

SQL> set define off
SQL> select aa.a.extract('Node/text()').getStringVal() extr
  2       , extractvalue(aa.a, 'Node') extractval
  3    from (select XMLRoot(
  4              XMLElement("Node", 'test & < and >'),
  5             VERSION '1.0') a from dual) aa;

EXTR
----------------------------------------
EXTRACTVAL
----------------------------------------
test &amp; &lt; and &gt;
test & < and >

First look on documentation even confirms my expectations:
"extractValue – This takes an XPath expression and returns the corresponding leaf node. The XPath expression passed to extractValue should identify a single attribute or an element that has precisely one text node child. The result is returned in the appropriate SQL data type. Function extractValue is essentially a shortcut for extract plus either getStringVal() or getNumberVal()."
XML DB Developer's Guide - b14259.pdf page 1-17.

But there is small note on that:
"Note: Function extractValue and XMLType method getStringVal() differ in their treatment of entity encoding. Function extractValue unescapes any encoded entities; method getStringVal() returns the data with entity encoding intact."
XML DB Developer's Guide - b14259.pdf page 4-10.

Cheers, Paweł

RMAN connection to Catalog - updated

2007-01-10T23:31:00.000+01:00

I must say that I was completely wrong in my post RMAN connection to Catalog. In fact when connection to Catalog fails, RMAN quits and rest of script is ignored. So I've changed this. RMAN connect only to Target Database during backup process, and after that RMAN is run second time. This time it connects to Target Database and Catalog Database, then run command RESYNC CATALOG to force synchronization of control file with Catalog. It should work now.

Cheers, Paweł

ORA-03297 on empty datafile

2007-01-02T23:54:00.000+01:00

Today I've noticed strange behavior of Oracle 10gR2 (10.2.0.3) while shrinking datafile. I had empty datafile (no objects) and i couldn't resize it. I was getting ORA-03297: file contains used data beyond requested RESIZE value while DBA_EXTENTS show no rows for that file. I've discovered that there are objects in Recycle Bin that were located on that file. After purging recycle bin file was shirked successfully.
Let me make an example:

SQL> create tablespace tb_test
  2  datafile 'c:\temp\tb_test.dbf' size 100 K autoextend on;
Tablespace created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
    106496                                                                      
SQL> create table t_big tablespace tb_test as
  2  select a.* from all_objects a, dba_users for_cartesian;
Table created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
        34                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
  19963904

Now we have datafile quite big. Let's drop table and shrink file:

SQL> drop table t_big;
Table dropped.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
alter database datafile 'c:\temp\tb_test.dbf' resize 100 K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

So. even there is nothing reported in DBA_EXTENT, file cannot be shinked. Let's purge recyclebin and try again:

SQL> purge recyclebin;
Recyclebin purged.
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
Database altered.

Now works fine! Don't forget to clear after tests :)

SQL> drop tablespace tb_test including contents and datafiles;
Tablespace dropped.

Cheers, Paweł

Security: getting DBA rights quite easy

2006-12-30T17:43:00.000+01:00

I've stepped today at ORASEC blog by Paul Wright. I've found this issue a big problem. Anybody that is using Oracle client on windows can quite easy get DBA rights by editing client9.dll file. I've to check if this is still problem with latest Oracle 10.2.0.3 release.

Cheers, Paweł

Applying Oracle 10.2.0.3 patch

2006-12-21T13:25:00.000+01:00

Today I've installed patch 10.2.0.3 for Oracle on Windows on top of 10.2.0.2 database. I've noticed that my spool file generated by upgrade script was 33 MB big. It occurred that source of all oracle build-in packages were spooled. It was terrible experience to search for errors in this log file, as searching for 'ORA-' give hundreds results and I had to figure out if it is in comment of package, or real error.
Fortunately patch was installed successfully and everything runs fine.

Cheers, Paweł

UTL_MATCH - String Similarity in Oracle

2006-12-07T23:05:00.000+01:00

You can compare string using equality operator (=), or using similarity operator (LIKE). But there are cases where it is not enough. You Can use UTL_MATCH package to calculate string similarity index. This packages offers 4 functions that take two strings as parameters:

edit_distance - algorithm by Levenshtein - returns number of edits that must be done to change one string into second,
edit_distance_similarity - normalized results of edit_distance in percents - integer values,
jaro_winkler - returns similarity based on Jaro-Winkler distance algorithm,
jaro_winkler_similarity - same as above but presented as integer in range 0-100.

Lets teke a look how it works. First we can create sample table and insert some data:

create table countries(name varchar2(15) not null);
insert into countries values ('Poland');
insert into countries values ('Germany');
insert into countries values ('United States');
insert into countries values ('Portugal');
insert into countries values ('Czech Republic');
insert into countries values ('China');
insert into countries values ('Slovakia');
insert into countries values ('Slovenia');
commit;

Now we can take a look at results. Lets compare to miss-spelled country name: 'Slovnia'

select name
 ,to_char(utl_match.edit_distance(name, 'Slovnia'),'999') edit_dist
 ,to_char(utl_match.edit_distance_similarity(name, 'Slovnia'),'999') edit_dist_sim
 ,to_char(utl_match.jaro_winkler(name, 'Slovnia'),'999d9999') jaro_winkler
 ,to_char(utl_match.jaro_winkler_similarity(name, 'Slovnia'),'999') jaro_winkler_sim
from countries
order by jaro_winkler_sim desc;

NAME                 EDIT EDIT JARO_WINK JARO
-------------------- ---- ---- --------- ----
Slovenia                1   88     .9750   97
Slovakia                2   75     .8881   88
China                   5   29     .5619   56
United States          12    8     .5531   55
Poland                  6   15     .5317   53
Portugal                7   13     .5119   51
Germany                 7    0     .3571   35
Czech Republic         13    8     .0000    0

Above we can observe differences in algorithms.
Lets test it on NULLs:

SQL> select to_char(utl_match.edit_distance('test', NULL),'999')
  2      edit_dist
  3   ,to_char(utl_match.edit_distance_similarity('test', NULL),'999')
  4      edit_dist_sim
  5   ,to_char(utl_match.jaro_winkler('test', NULL),'999d9999')
  6      jaro_winkler
  7   ,to_char(utl_match.jaro_winkler_similarity('test', NULL),'999')
  8      jaro_winkler_sim
  9  from dual;

EDIT EDIT JARO_WINK JARO
---- ---- --------- ----
  -1  125     .0000    0

We can see that using edit_distance on NULLs migth be dengerous.
All samples were run on Oracle 10g XE

Hope You find it usefull.
Paweł

MERGE and SEQUENCE

2006-11-22T23:29:00.000+01:00

Last days I was working on code and I was using merges often. And i've notice some side-effect. When you use sequence in WHEN NOT FOUND clause then next sequence value is gathered for all rows processed in query. I've observed it on 10g R2 EE (10.2.0.2) and on Oracle XE. Let me show test case. First create sequence:

SQL> create sequence a_sq start with 1 increment by 1 nocache;
Sequence created.

This sequence generator starts with 1 with step 1. Lets taka look how many rows we have in USER_OBJECTS view:

SQL> select count(*) from user_objects;
 COUNT(*)
----------
 46

Lets create sample table and populate it with 10 rows:

SQL> create table a_tab as
 2 select object_id o_id, object_name o_name, a_sq.nextval o_sq
 3 from user_objects where rownum <=10;
Table created.

So we have table with 10 rows, and last number generated by sequence is also 10:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 10 10

Now we run merge:

SQL> merge into a_tab
 2 using (select object_id , object_name
 3 from user_objects )
 4 on (object_id = o_id)
 5 when matched then
 6 update set o_name = object_name
 7 when not matched then
 8 insert (o_id, o_name, o_sq)
 9 values (object_id, object_name, a_sq.nextval);
47 rows merged.

47 rows were merged, but 10 rows was updated and 37 inserted. Lets check it:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 47 57

It seems that sequence generated 47 new values, but only 37 rws were inserted. Lets run the same merge one more time. This time rows will be only updated, as all rows are already in a_tab table.

SQL> merge into a_tab
 2 using (select object_id , object_name
 3 from user_objects )
 4 on (object_id = o_id)
 5 when matched then
 6 update set o_name = object_name
 7 when not matched then
 8 insert (o_id, o_name, o_sq)
 9 values (object_id, object_name, a_sq.nextval);
47 rows merged.

Now no new rows where created. But what happen to sequence:

SQL> select a_sq.nextval from dual;
 NEXTVAL
----------
 105

It generated another 47 values, that are useless. It can be proved:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 47 57

This side effect might have impact on performance. Also you loose lot of numbers, when most of rows are updated. Cheers, Paweł

Links 2006-11-20

2006-11-20T17:41:00.000+01:00

Links 2006-11-20:

Cheers, Paweł

Blogger Beta Backup tool

2006-11-19T12:58:00.000+01:00

Finally i found time to prepare solution to backup my blog. I've decided to do that as I had problems to backup my blog using available tools. To archive that I use Blogger Beta Atom feeds, few tables in Oracle, and PL/SQL package to read blog posts, parse them using XML DB features and store in DB tables.

Requirements

Oracle 10g (tested on Oracle XE on Windows and Oracle Enterprise 10.2.0.2 on Windows). Oracle9i should work also, but it's not tested.
Blogs maintained by Beta Blogger and hosted on blogspot.com. You must allow to publish full content of your posts and comments.
Direct connection to Internet

Installation

Download installation package
Create new Oracle schema and grant to it:
1. roles CONNECT and RESOURCE
2. EXECUTE on SYS.UTL_HTTP
run script install.sql

Usage

First You have to register blog you want to backup. Use BLOGSPOT_BACKUP.register_blog function:

begin
 dbms_output.put_line(
 BLOGSPOT_BACKUP.register_blog
 ('Software Engineer Thoughts', 'http://pbarut.blogspot.com/'));
 commit;
end;

To download all your posts with comments andl labels use this PL/SQL block:

begin 
 BLOGSPOT_BACKUP.synchronize_blog(null, false); 
 BLOGSPOT_BACKUP.synchronize_labels(null, null); 
 BLOGSPOT_BACKUP.synchronize_comments(null, null, false); 
 commit; 
end;

For more information take a look into BLOGSPOT_BACKUP specification.

Data storage

Table name	Desription
BLOGS	Blogs registered for backup.
BLOG_POSTS	Blog posts downloaded.
BLOG_LABELS	Labels associated to blog posts.
BLOG_COMMENTS	Comments related to blog posts.
FEED_CACHE	Used speed up tests. In tests phase feeds are read from this table instead of downloading every time.

End notes

I'm publishing this tool, as some of you might find it useful. It should work on all beta blogger blogs, but I do not give ant warranty. In case of any problems fill free to contact me.

Regards, Paweł

The Palm: The Eighth Wonder of World

2006-11-16T22:54:00.000+01:00

You must definitely see The Palm: The Eighth Wonder of World. Incredible...

Paweł

ORA-30625 and XMLType

2006-11-13T23:48:00.000+01:00

I will present some differences in handling XMLType in SQL and PL/SQL by Oracle. Test case was done on Oracle 10.2.0.2. Lets define variable and fill it with sample data. It is important to have empty value in tag <empty>.

SQL> var xm varchar2(100);
SQL> exec :xm := '<root><empty></empty></root>';
PL/SQL procedure successfully completed.

Lets try to extract value of empty tag:

SQL> var xmv varchar2(100);
SQL> exec :xmv := XMLType(:xm).extract('//empty/text()').getstringval();
BEGIN :xmv := XMLType(:xm).extract('//empty/text()').getstringval(); END;
*
ERROR at line 1:
ORA-30625: method dispatch on NULL SELF argument is disallowed 
ORA-06512: at line 1

Lets do the same with SQL:

SQL> select XMLType(:xm).extract('//empty/text()').getstringval() from dual;
XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL()
--------------------------------------------------------------------------------

Now we get NULL. You can confirm this with that query:

SQL> select nvl(XMLType(:xm).extract('//empty/text()').getstringval(),
'(NULL)') from dual;
NVL(XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL(),'(NULL)')
--------------------------------------------------------------------------------
(NULL)

So, be careful when you extract data from XMLType in PL/SQL. If you want NULL to be returned for empty tags then you should use function like this:

CREATE OR REPLACE
FUNCTION XML_EXTRACT_NO_EXCEPTION
( p_xml IN XMLTYPE
, p_xpath IN VARCHAR2
, p_namespace IN VARCHAR2 default NULL
) RETURN VARCHAR2 AS
BEGIN
return case when p_xml.extract(p_xpath, p_namespace) is not null
then p_xml.extract(p_xpath, p_namespace).getstringval()
else NULL
end;
END XML_EXTRACT_NO_EXCEPTION;

And then you can use it safe in PL/SQL:

SQL> exec :xmv := XML_EXTRACT_NO_EXCEPTION(XMLType(:xm), '//empt/text()');
PL/SQL procedure successfully completed.
SQL> print :xmv
XMV
--------------------------------------------------------------------------------

Another reason for ORA-30625 can be that you did not provide namespace in execution of extract. More on this in my post
XMLType and Namespace problem.

Paweł

SQL Developer 1.1 preview

2006-11-12T16:39:00.000+01:00

Today I give a try to new SQL Developer 1.1. Installation on Windows 2000 goes smoothly without any problems. Users interface is almost the same as in previous version. One change that I noticed at one is search plug in, that allows user to search Oracle documentation and also Ask Tom site. It integrated well with my Firefox . There are 2 new features that was missing in previous version:

XMLType support - now XML data is displayed as query results, so you can open pop-up and see all details
Display for Date type - you can define in preferences how dates should be displayed

What I'm still missing is possibility to update more then one row in Index Organized Tables (IOT) in single transaction. At the moment when you change one row you must commit transaction. If you do not do that, whole transaction will be forced to be rolled back.
Changes goes in good directions. Might be in few months i will completely stop to use TOAD...
Paweł

XMLType and Namespace problem

2006-11-07T23:24:00.000+01:00

I've run into some strange problem with XMLType and XML namespaces. I'll try to make a showcase. Lets define variable and simple XML content

SQL> var x varchar2(200);
SQL> exec :x := '<feed
xmlns="http://www.w3.org/2005/Atom"><id>test_id</id></feed>'
PL/SQL procedure successfully completed.

Try to find out what tag is on root:

SQL> select XMLType(:x).getRootElement() from dual;
XMLTYPE(:X).GETROOTELEMENT()
--------------------------------------------------------------------------------
feed

Lets find out the namespace:

SQL> select XMLType(:x).getNamespace() from dual;
XMLTYPE(:X).GETNAMESPACE()
--------------------------------------------------------------------------------

Seems to be no namespace. But there was namespace in XML definition. Lets try extract data:

SQL> select XMLType(:x).extract('/feed').getStringVal() from dual;
XMLTYPE(:X).EXTRACT('/FEED').GETSTRINGVAL()
--------------------------------------------------------------------------------

So nothing was extracted. It's not what I was expecting. Ok, lets try to force namespace:

SQL> select XMLType(:x).extract('/feed',
'xmlns="http://www.w3.org/2005/Atom"').getStringVal() from dual;
XMLTYPE(:X).EXTRACT('/FEED','XMLNS="HTTP://WWW.W3.ORG/2005/ATOM"').GETSTRINGVAL(
--------------------------------------------------------------------------------
<feed
xmlns="http://www.w3.org/2005/Atom"> <id>test_id</id>
</feed>

Now works fine.
For me it is little confusing: there is no namespace, but you must provide namespace if you want to extract data.
Paweł

Search Oracle Blogs

2006-10-25T22:36:00.000+02:00

Google announced release of new tool Google Custom Search Engine. Using this tool You can create our own list of sites you want to search within. There are also many options to customize search result pages, to have search results on your own site. You may also collaborate with other users, to create list of sites on specific topic.
Based o this I’ve created a new search engine that searches only Blogs related to Oracle. You can check if you are already listed. If not you can add your site. You are welcome to take part and propose sites with oracle related blogs. For sure I will include it in this listing.
On top of my page you can find search box. Try it.
If you want this search box on your page just copy following HTML code

<!-- Google CSE Search Box Begins -->
<form id="searchbox_007198821948994293043:dv284dbx17s" action="http://www.google.com/cse">
  <input type="hidden" name="cx" value="007198821948994293043:dv284dbx17s" />
  <input name="q" type="text" size="40" />
  <input type="submit" name="sa" value="Search" />
  <input type="hidden" name="cof" value="FORID:1" />
</form>
<script type="text/javascript" src="http://www.google.com/coop/cse/brand?form=searchbox_007198821948994293043%3Adv284dbx17s"></script>
<!-- Google CSE Search Box Ends -->

Paweł

Oracle 11g new features, part 2

2006-10-25T08:00:00.000+02:00

There are two new interesting sources for 11g new features.
Tim Hall post The Next-Generation Self-Managing Database (11g)
And Lewis Cunningham post OOW: Enhancements in Oracle 11g PL/SQL
Also these features or enhancements seems very promising

PL/SQL Native Compilation. There is no need for external C compiler, so it’s easier to make PL/SQL faster.
New Data Type: simple_integer. Another performance booster.
Intra-unit inlining. - this is feature that I was missing for years. Now I can create complex expressions and reuse it easily. I hope it will work also in SQL
Database Replay and SQL Replay - this is very interesting idea.

I'm looking forward to play with these features.

Cheers, Paweł

Oracle 11g new features

2006-10-24T22:23:00.000+02:00

I have found Laurent post on 11g new features very interesting. Let me comment on some items:

/*+result_cache*/ is very promising. From description it seems that it might be very useful for web applications based on Oracle. But sentence "There is an aggressive cache invalidation, so whatever DML happens on one dependent table, the cache result is invalidated" suggest that it will be useful only in read only environment. Otherwise cache will be constantly invalidated making this feature useless. Question is if query results will be stored in new memory structure, or it will be in Buffer Cache? Anyway there might be need for huge RAM
Using sequence in PL/SQL without DML. This is long time expected feature
Trigger order. This can be also sometimes useful.

All other features seem to be just improvements or enhancements. I do not see any revolutionary solution in this list.

Paweł

Binding list variable

2006-10-21T00:22:00.000+02:00

Today I was working with XMLType, and as a side effect I found interesting solution to transform list of values contained in single string to rows. You can find some solutions on Tom Kyte blog. But my solution is new approach that uses xmlType and xmlSequence

SQL> var list varchar2(200)
SQL> exec :list := '2,4,6,8,10,34,33';

PL/SQL procedure successfully completed.

SQL> select items.extract('/l/text()').getStringVal() item
  2  from table(xmlSequence(
  3    extract(XMLType('<all><l>'||
  4      replace(:list,',','</l><l>')||'</l></all>')
  5          ,'/all/l'))) items;

ITEM                                                                            
--------------------------------------------------------------------------------
2                                                                               
4                                                                               
6                                                                               
8                                                                               
10                                                                              
34                                                                              
33                                                                              

7 rows selected.

Simple! Isn’t it?
Cheers, Paweł

What tool do you use to make blog backup?

2006-10-18T17:27:00.000+02:00

Today a have a question to you: What tool do you use to make backup of your blog?
My blog in hosted on blogspot and is run by beta.blogger.com. I was trying to make backup (mirror) using HTTrack but it run into infinite loop. It was downloading every post many, many, many.... times with little changed name.
Looking forward for your recommendation…
Paweł

A better view? I do not think so.

2006-10-16T22:12:00.000+02:00

I have read today Gojko Adzic article A better view. I must say that it made me thinking about my approach to database and front-end applications. I do not share his opinion.
In my approach PL/SQL API is the only possible way to retrieve and manipulate data in Oracle Database. Especialy when it goes for Web Applications.

Logging user activity. Using triggers is fantasting to logging changes to data (Journaling), but usually is useless for logging user activity. User activity should be logged based on his/her actions and it’s parameters not data modifications. You do not want your batch processes to generate so many log.
Performance Monitoring. With well-written API you can get very detailed statistics for time consumed by individual routine. Based on this statistics You can decide where are your bottlenecks and you can improve that.
Simplicity. For easy, standard operations You should prepare generators that automatically generate API procedures for many simple tables.
Cursors. PL/SQL procedures can return REF CURSORS as an output. With dynamic SQL you can return different variations of data depending on input parameters.
API can be used for many Applications. In many big organizations one database is often accessed by different client applications. You do not have to write the same (similar) SQL’s in PHP, java or whatever other tool You want/plan to use.

Hope You understand my point of view.
If you are disagree or agree, please leave a comment.

Cheers, Paweł

Group Comparison Conditions

2006-10-15T18:21:00.000+02:00

Sometimes there is a need to compare one value to many others. In Oracle SQL or PL/SQL for this purpose you can use Group Comparisons. Syntax for this is:
expr { = | != | < | > | <> | <= | >= } {ANY | SOME | ALL} (expr, …, expr) , for example X = ANY (1,2,3) .
You can use subquery instead of expression list. For full syntax go to Oracle Documentation. Lets see how it works. First create sample table:

SQL> CREATE TABLE T1 (N1 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T1 SELECT LEV, LEV
  2  FROM (SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL < 10);
9 rows created.
SQL> COMMIT;
Commit complete.

Then do some queries

SQL> SELECT * FROM T1 WHERE N1 < ANY (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < SOME (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < ALL (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3

And lets see what will happen if there is NULL in list:

SQL> SELECT * FROM T1 WHERE N1 < ANY (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < SOME (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < ALL (4,6, NULL);
no rows selected

So we can see that ANY is equivalent to SOME. These Grouping conditions are in fact rewritten to simple conditions. ANY is rewritten to many comparisons with logical OR, a < ANY (b,c,d) is rewritten to (a<b OR a<c OR a<d) while ALL is rewritten to AND statement a < ALL(b,c,d) becomes (a<b AND a<c AND a<d).
We can observe that = ANY is equivalent to IN operator.

SQL> SELECT * FROM T1 WHERE N1 = ANY (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         4 4                                                                    
         6 6                                                                    
SQL> SELECT * FROM T1 WHERE N1 IN (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         4 4                                                                    
         6 6

Also observe that != ALL is equivalent to NOT IN operator.

SQL> SELECT * FROM T1 WHERE N1 != ALL (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         5 5                                                                    
         7 7                                                                    
         8 8                                                                    
         9 9                                                                    
7 rows selected.
SQL> SELECT * FROM T1 WHERE N1 NOT IN (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         5 5                                                                    
         7 7                                                                    
         8 8                                                                    
         9 9                                                                    
7 rows selected.
SQL> SELECT * FROM T1 WHERE N1 != ALL (4,6, NULL);
no rows selected
SQL> SELECT * FROM T1 WHERE N1 NOT IN (4,6, NULL);
no rows selected

Take a look at trick with joining many tables based on the same key. Lets create more tables

SQL> CREATE TABLE T2 (N2 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T2 SELECT * FROM T1;
9 rows created.
SQL> CREATE TABLE T3 (N3 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T3 SELECT * FROM T1;
9 rows created.
SQL> COMMIT;
Commit complete.

And simple join:

SQL> SELECT N1, N2, N3 FROM T1, T2, T3 WHERE N1 = ALL (N2,N3);
        N1         N2         N3                                                
---------- ---------- ----------                                                
         1          1          1                                                
         2          2          2                                                
         3          3          3                                                
         4          4          4                                                
         5          5          5                                                
         6          6          6                                                
         7          7          7                                                
         8          8          8                                                
         9          9          9                                                
9 rows selected.

Is it really evaluated in proper way?

SQL> set autotrace traceonly;
SQL> SELECT N1, N2, N3 FROM T1, T2, T3 WHERE N1 = ALL (N2,N3);
9 rows selected.
Execution Plan
----------------------------------------------
Plan hash value: 3348756600 
---------------------------------------------- ...
| Id  | Operation              | Name        | ...
---------------------------------------------- ...                                   
|   0 | SELECT STATEMENT       |             | ...
|   1 |  NESTED LOOPS          |             | ...
|   2 |   NESTED LOOPS         |             | ...
|   3 |    INDEX FAST FULL SCAN| SYS_C004041 | ...
|*  4 |    INDEX UNIQUE SCAN   | SYS_C004042 | ...
|*  5 |   INDEX UNIQUE SCAN    | SYS_C004043 | ...
---------------------------------------------- ...                                         
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   4 - access("N1"="N2") 
   5 - access("N1"="N3")

So we can observe that it is really rewritten and oracle uses indexes in optimal way.
Hope you find it useful

Paweł

GUI for FireBird DB

2006-10-15T12:30:00.000+02:00

Friend of mine, that is still working on xBase (Clipper), asked me to recommend him a good GUI tool for SQL database. At first I didn't know what he exactly wanted. In fact he wanted a tool to retrive and manipulate data in FireBird DB. After trying few tools I recommended IB_SQL. It's realy light weighted and intuitive.

It was my first experience with FireBird. I must say that for me it seems insecure. Whole database is stored in one file, and if you have this file, you can easily open it without need for any password.

Paweł

RMAN connection to Catalog

2006-10-06T18:47:00.000+02:00

Today my nightly backups were not taken. I had an OS script that started backup using this command:

rman target=sysop/password@targetdb catalog=rman/password@catdb 
      log=rman.log append @rman.txt

The reason was that network was down and there was no connection to catdb. It leaded to error and RMAN did not start at all. So I’ve changed my command line to:

rman target=sysop/password@targetdb 
      log=rman.log append @rman.txt

And added a line in rman.txt

connect catalog rman/password@catdb;

Next time there will be no connection to catdb backup will be done anyway. It will be not registered in catalog only. Catalog will be synchronized on next backup procedure or next time you connect to target and catalog an issue any command that operate on catalog.

Cheers, Paweł

More on Flashback

2006-10-05T22:52:00.000+02:00

If you are instrested in more materials on Oracle Flashback you should take a look at Tom Kyte presentation TomKyte_FlashBackQ_Mar-05.zip found on Calgary Oracle Users Group.
Polish speeking readers can have a look at my article PLOUG'tki nr 31 (IX 2004) - Podróż w czasie, czyli Oracle Flashback that I wrote for Polish Oracle Users Group.

Cheers, Paweł

UNDO_SQL in FLASHBACK_TRANSACTION_QUERY

2006-10-04T23:33:00.000+02:00

When You read my post on Flashback and Materialized View You might think about other workaround. In FLASHBACK_TRANSACTION_QUERY View there is column with SQL Statement to Undo operation on table. Lets do simple test:

SQL> insert into a_fb values (1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> delete from a_fb;
1 row deleted.
SQL> commit;

And now see whats in FLASHBACK_TRANSACTION_QUERY.

SQL> select commit_scn, operation, row_id, undo_sql
  2  from FLASHBACK_TRANSACTION_QUERY
  3  where table_name = 'A_FB'
  4  order by commit_scn;
COMMIT_SCN OPERATION                        ROW_ID                              
---------- -------------------------------- -------------------                 
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
    641488 INSERT                           AAAM2dAAEAAAAG9AAA                  
delete from "BARUT"."A_FB" where ROWID = 'AAAM2dAAEAAAAG9AAA';                  
                                                                                
    641571 DELETE                           AAAM2dAAEAAAAG9AAA                  
insert into "BARUT"."A_FB"("ID","TEXT") values ('1','1');

To run above query You need SELECT ANY TRANSACTION privilege
So now instead of issuing FLASHBACK TABLE A_FB to SCN 641400 You may run statements from UNDO_SQL:

insert into "BARUT"."A_FB"("ID","TEXT") values ('1','1');
delete from "BARUT"."A_FB" where ROWID = 'AAAM2dAAEAAAAG9AAA';

But it will not work! Because when you insert new row usually it will get new row id

SQL> select rowid, id from a_fb;

ROWID                      ID                                                   
------------------ ----------                                                   
AAAM2dAAEAAAAG9AAB          1

So when issuing Delete statement you must map old rowid to that new one. When you do Flashback on Table oracle manages it internally. But when you use UNDO_SQL you must take care for all details. So be careful with that…

Cheers, Paweł

13 – Happy or Unhappy Number?

2006-09-30T15:35:00.000+02:00

This is my 13 post. So I’ve decided to make some digression. Usually number 13 is considered as unhappy. It seems not a rule for me. This year 13 is my happy number. On 13th January I’ve signed new job contract. It was just about 13:15 (or might be it was exactly at 13:13). What is more interesting, sum of digits in that date (13-01-2006) is equal to 13 (1+3+0+1+2+0+0+6=13). Number of building I’m now working in is also 13. As for now I’m quite happy in my new job.
Also according to Walter Schneider 13 is definitely happy number. I’ve observed that for me the unhappiest day in month is 26th. In fact it is 2*13. When I use Walters method to it gives me: 26 => 40 (2*2+6*6) => 16 => 37 => 58 => 89 => 145 => 42 => 20 => 4. So 26 is unhappy number and it confirms my observations :).
More on Happy Numbers in Wikipedia.

Cheers, Paweł

Flashback Table and Materialized View – not working together

2006-09-30T00:11:00.000+02:00

I must say that I like Flashback features very much. This is very useful option and should be used more often. But sometimes you might hit some obstacles. One of them is Materialized view. When You want to issue FLASHBACK TABLE statement on table that has materialized view it does not work.
Lets try:

SQL> create table a_fb 
  2  (id number primary key, text char(200)) enable row movement;
Table created.
SQL> create materialized view log on a_fb with rowid;
Materialized view log created.

Now insert some data:

SQL> insert into a_fb select object_id, object_name
  2  from all_objects where rownum <= 100;
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568668        258       1069        100

ok. Lets insert more data and then try to flashback to above SCN.

SQL> insert into a_fb select object_id, object_name
  2  from all_objects where rownum <= 100
  3  and object_id not in(select id from a_fb);
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568704        258       1257        200

And now flashback:

SQL> flashback table a_fb to scn 568668;
Flashback complete.

It seems that Materialized view log is not a problem. Moreover when FLASHBACK TABLE is executed new rows are inserted to Materialized view log. You can check it issuing this select:

select count(*) from MLOG$_A_FB;

Now lets try to create materialized view

SQL> create materialized view a_fb_mv
  2  REFRESH FAST ON COMMIT with rowid
  3  as
  4  select id, text, rowid a_rowid from a_fb;
Materialized view created.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568852        258       1069        100

Lets add some data and roll back to that SCN

SQL> insert into a_fb select object_id, object_name
  2  from all_objects where rownum <= 100
  3  and object_id not in(select id from a_fb);
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568893        258       1257        200

Lets see what we have in Materialized view:

SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb_mv;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568895        258       1257        200

Problem

Try to flashback:

SQL> flashback table a_fb to scn  568852;
flashback table a_fb to scn  568852
                *
ERROR at line 1:
ORA-08194: Flashback Table operation is not allowed on materialized views

You cannot flashback table if there is materialized view defined on it. But materialized view log is not a problem. I must say that I do not see any reason for that. If materialized view log is generated during flashback operation why materialized view is not updated according to that log?

Workaround

This is quite simple. You just have to drop materialized view and issue flashback table statement:

SQL> drop materialized view a_fb_mv;
Materialized view dropped.
SQL> flashback table a_fb to scn  568852;
Flashback complete.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  569009        258       1069        100

Now you need only to recreate dropped materialized view.

ORA_ROWSCN continuation…

2006-09-21T00:02:00.000+02:00

In my previous post I was wondering how SCN is assigned to ROW or Block. I couldn’t find explanation in Oracle Documentation. But I’ve found this document written by Jonathan Lewis. You should read this! I’m little afraid about 'best guess' technology.
By the way you can also read article about undocumented userenv('commitscn') written in May 1999. When I first saw it I thought it might be replaced by ORA_ROWSCN.
So I’ve try this:

SQL> create table t (n1 number, text varchar2(10)) ROWDEPENDENCIES;

Table created.

SQL> insert into t values (userenv('commitscn'), 'T1');

1 row created.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
             4621851 T1                                                       

SQL> declare
 2    pragma autonomous_transaction;
 3  begin
 4    insert into t values (userenv('commitscn'), 'TAuto');
 5    commit;
 6  end;
 7  /

PL/SQL procedure successfully completed.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
             4621851 T1                                                       
  4621852    4621851 TAuto                                                    

SQL> commit;

Commit complete.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
  4621854    4621853 T1                                                       
  4621852    4621851 TAuto

So for sure ORA_ROWSCN and userenv('commitscn') is not the same. I must say that I’m even more confused.

If You have some spare time, I recommend to read this post: Is it crazy to feel guilty about success? and from Eddie Awad blog: SYS_OP_MAP_NONNULL, REVERSE, LNNVL, NVL, NVL2, COALESCE

Cheers, Paweł

ORA_ROWSCN behavior: bug or feature?

2006-09-17T15:21:00.000+02:00

ORA_ROWSCN is pseudo column that gives SCN (System Change Number) for transaction that was modifying record. Lets take a closer look. First create table:

 CREATE TABLE A_TABLE
   (ID NUMBER, TEXT CHAR(2000));

Then feed table with some data:

SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into a_table select rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

Procedure auto_commit is used to force change of SCN. Take a look what we have in table:

SQL> select ora_rowscn, count(*) from a_table
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4589308         24                                                           
   4589313         16                                                           
   4589293         15                                                           
   4589297         24                                                           
   4589302         21

We ran INSERT statement 10 times and we get 5 different SCN’s in table. None of them has exactly 10 rows. This is because table was created with default setting: NOROWDEPENDENCIES, and SCN is kept on block level. Every and each row in block has the same SCN. Lets commit this transaction:

SQL> commit;

Commit complete.

SQL> select ora_rowscn, count(*) from a_table
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4589363        100

Now all rows haveexactly the same SCN.
Now lets take a look what will happen for table with ROWDEPENDENCIES on.

 CREATE TABLE A_TABLE_ROW
   (ID NUMBER, TEXT CHAR(2000)) ROWDEPENDENCIES;

Insert some data:

SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into A_TABLE_ROW select rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

Query table:

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
                  100

Now ORA_ROWSCN is NULL for uncommitted transaction. This is very interesting.

SQL> commit;

Commit complete.

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4590773        100

What will happen when updating:

SQL> update A_TABLE_ROW set id = id where rownum <20;

19 rows updated.

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)
---------- ----------
                   19
   4590773         81

We can conclude that data in active transaction have NULL ORA_ROWSCN for tables with ROWDEPENDENCY.
What about Index Organized Tables:

 CREATE TABLE A_TABLE_IOT
   (ID NUMBER primary key, TEXT CHAR(2000)) organization index;

Generate data:

 SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into A_TABLE_IOT select i*10+rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select ora_rowscn, count(*) from A_TABLE_IOT
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)
---------- ----------
                  100

For IOT with NOROWDEPENDENCIES we still get NULLs for ORA_ROWSCN.

Questions:

1. Why we have such differences in implementation of ORA_ROWSCN?
2. We can see that SCN is assigned at commit. Does commit tackles all blocks modified by transaction? For short transaction it is not a problem, as blocks probably are still in buffer. But large transactions that generate lot of redo, and there are few check points during transaction time should not change this blocks. That means that block should have only transaction id, and oracle translates this transaction id to SCN.

Cheers, Paweł

Avoiding Deadlocks: SKIP LOCKED

2006-09-14T22:24:00.000+02:00

After reading Tim's article on Deadlocks, I've recalled solution that can help avoid deadlocks in some situations.
Assume that in our application we have to choose few (say 5) documents from pool to assign to users for processing. Usually we can use just update statement like that:

UPDATE documents
SET doc_state = 'ASSIGNED',
  doc_user = USER
WHERE doc_state = 'TO PROCESS'
 AND rownum <= 5;

But this can cause some waits in highly loaded environments where this statement is run often. To overcome that we can use SKIP LOCKED clause:

BEGIN
  FOR r IN
    (SELECT id FROM documents
     WHERE doc_state = 'TO PROCESS'
     AND rownum <= 5 FOR UPDATE SKIP LOCKED) LOOP
    UPDATE documents
    SET doc_state = 'ASSIGNED',
      doc_user = USER
    WHERE id = r.id;
  END LOOP;
END;

In this statement we do not suffer from waits, and we get up to 5 rows assigned to current user.

Cheers, Paweł

Binary Days

2006-09-13T23:59:00.000+02:00

As addition to may previous post lets see other Binary days, where day number is power of 2.

SQL> select rownum-1 power_of2
  2  , to_char(trunc(sysdate,'YYYY')-1+power(2,rownum-1),
  3    'DD-MON-YYYY DAY')  magic_day
  4  from dual connect by level <=9;

 POWER_OF2 MAGIC_DAY
---------- ---------------------------------------------------------
         0 01-JAN-2006 SUNDAY
         1 02-JAN-2006 MONDAY
         2 04-JAN-2006 WEDNESDAY
         3 08-JAN-2006 SUNDAY
         4 16-JAN-2006 MONDAY
         5 01-FEB-2006 WEDNESDAY
         6 05-MAR-2006 SUNDAY
         7 08-MAY-2006 MONDAY
         8 13-SEP-2006 WEDNESDAY

9 rows selected.

Interesting coincidence - there are only 3 days of week: SUNDAY, MONDAY and WEDNESDAY.

BTW. Does anybody know if is it possible to turn off these automatic <br /> tags in beta blogger?

Paweł

Programmers Day

2006-09-13T19:54:00.000+02:00

Today is 256th day of year. It is considered as Programmers Day. Let's celebrate...

Cheers, Paweł

Row Generators

2006-09-12T22:44:00.000+02:00

You probably now all these methods to generate rows. I've decided to choose the best method. My criterias are:

Maximum Speed
Without limitations
Easy of use

To measure time I use PL/SQL block:

declare
v_start_time  timestamp;
v_end_time    timestamp;
v_max_number  integer;
v_max_gen     integer;
begin
v_max_number := 1000;
v_max_gen := 0;
v_start_time := systimestamp;
for r in (QUERY_HERE) loop
  v_max_gen := r.r;
end loop;
v_end_time := systimestamp;
dbms_output.put_line('Max gen = '||v_max_gen);
dbms_output.put_line('Time = '||(v_end_time-v_start_time));
end;

Where QUERY_HERE was one of:

ALL_OBJECTS	select rownum r from all_objects where rownum <= v_max_number
2*ALL_OBJECTS	select rownum r from all_objects, all_objects where rownum <= v_max_number
group by cube	select rownum r from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14)) where rownum <= v_max_number
level	select level r from dual connect by level <= v_max_number
PL/SQL Function	select rownum r from table(rowgen(v_max_number))
	where function was created using: CREATE OR REPLACE TYPE numtab_type IS TABLE OF NUMBER; CREATE or replace FUNCTION rowgen(pin_nor IN NUMBER) RETURN numtab_type DETERMINISTIC PIPELINED AS BEGIN FOR i IN 1..pin_nor LOOP PIPE ROW(i); END LOOP; RETURN; END rowgen; /

My Results, all times in seconds

Method/Rows	1000 rows	10000 rows	1000000 rows	Comment
ALL_OBJECTS	0.1	(*)	(*)	(*) only 5292 rows generated. We cannot generate big number of rows
2*ALL_OBJECTS	0.2	0.7	66.2
group by cube	0	0.1	> 5 min	Number of elements in cube clause vary on how big numbers we want get. For big numbers it is slow and not flexible in setting.
level	0	0.1	2.8
PL/SQL Function	0	0.1	2.2	We have to create Type and Function

Conclusion

If we consider speed, we should use one of two methods: level or PL/SQL Function. When we also consider the Easy of use we have only one solution select level r from dual connect by level <= v_max_number

Cheers, Paweł