Pawel Barut

Subscribe to Pawel Barut feed
My thoughts about developing software. Problems descriptions and solutions, tips, tricks, links to useful resources. Mostly on Oracle, but also PHP, Delphi and other languages & tools.
Updated: 14 hours 40 min ago

What a news! Oracle Buys Sun !

Mon, 2009-04-20 15:36
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:

Keep reading,
Paweł
Categories: Development

Caution: FOR loop and CONTINUE in Oracle 11g

Mon, 2009-04-06 04:59
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:
    Categories: Development

    Book Review: Processing XML documents with Oracle JDeveloper 11g

    Sun, 2009-03-29 05:55
    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:
      Categories: Development

      Multiplying texts and null effect

      Sun, 2009-03-15 16:39
      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:
      Categories: Development

      OOW2008 day 5 – It’s A Wrap

      Thu, 2008-09-25 23:52
      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:
      Categories: Development

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

      Wed, 2008-09-24 18:25
      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:
      Categories: Development

      OOW2008 day 3

      Tue, 2008-09-23 23:08
      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:
      Categories: Development

      OOW2008 day 2 – Oracle Beehive

      Tue, 2008-09-23 10:23
      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:
      Categories: Development

      Arriving San Francisco – OOW2008 day 1.

      Sun, 2008-09-21 19:41
      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:
      Categories: Development

      Preparing for Oracle OpenWorld 2008

      Sat, 2008-09-13 04:41
      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:
      Categories: Development

      DB Link to Oracle 11g

      Wed, 2008-08-13 13:59
      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:
      Categories: Development

      Lost Pictures - Recovered Pictures

      Mon, 2008-08-04 16:09
      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:
        Categories: Development

        Oracle OpenWorld 2008

        Thu, 2008-07-17 15:44
        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:

        Categories: Development

        ORA-00904: "XMLROOT": invalid identifier

        Sat, 2008-05-17 04:55
        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:
        1. SQL function; see documentation
        2. 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:
        Categories: Development

        Oracle CPUApr2008 for DB: 10.2.0.4 patch set and first install

        Sun, 2008-04-20 08:40
        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:
        Categories: Development

        Oracle Database 11g Release 2 is coming

        Tue, 2008-04-15 15:52
        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:
        Categories: Development

        Which Temporary Tablespace is used for sorts?

        Mon, 2008-03-24 12:34
        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:
        1. User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
        2. 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:
        Categories: Development

        Horrible practice of storing users passwords

        Sun, 2008-03-09 13:24
        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:
        Categories: Development

        Useful links on Oracle XML DB

        Mon, 2008-02-18 16:17
        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.

        Cheers Paweł

        --
        Related Articles on Paweł Barut blog:
        Categories: Development

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

        Mon, 2008-02-11 15:54
        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:
        Categories: Development

        Pages