Subscribe: Software Engineer Thoughts
http://pbarut.blogspot.com/feeds/posts/default
Added By: Feedage Forager Feedage Grade B rated
Language: Malay
Tags:
articles paweł  barut blog  day  oow day  oow  oracle  paweł barut  paweł  related articles  sql  written paweł  xml   
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Software Engineer Thoughts

Software Engineer Thoughts



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: 2017-08-26T14:14:14.375+02:00

 



What a news! Oracle Buys Sun !

2009-04-20T22:39:28.592+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:

Keep reading,
Paweł



Caution: FOR loop and CONTINUE in Oracle 11g

2009-04-06T12:01:11.376+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 w[...]



Book Review: Processing XML documents with Oracle JDeveloper 11g

2009-03-29T13:01:53.388+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:40:15.984+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-11-25T22:14:10.881+01: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 DrivesVery 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 TechniquesSession 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 SupportThis 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: Oracle OpenWorld 2008 Preparing for Oracle OpenWorld 2008 Arriving San Francisco – OOW2008 day 1. OOW2008 day 2 – Oracle Beehive OOW2008 day 3 OOW2008 day 4 – HP-Oracle Exadata Server Announcement = Extreme Performance OOW2008 day 5 – It[...]



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

    2008-11-25T22:13:51.676+01: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: Oracle OpenWorld 2008 Preparing for Oracle OpenWorld 2008 Arriving San Francisco – OOW2008 day 1. OOW2008 day 2 – Oracle Beehive OOW2008 day 3 OOW2008 day 4 – HP-Oracle Exadata Server Announcement = Extreme Performance OOW2008 day 5 – It’s A Wrap [...]



    OOW2008 day 3

    2008-11-25T22:13:27.675+01: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:
    (image)

    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-11-25T22:12:04.932+01:00

    Written by Paweł Barut
    (image) 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:
    (image)
    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-11-25T22:11:17.383+01: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-13T12:38:58.454+02:00

    (image) 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:
    src="http://spreadsheets.google.com/pub?key=pHTrQsufqUNW-oAmOJdh6Lw" width="700" frameborder="0" height="300">
    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-14T10:59:21.228+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-12-10T23:58:15.886+01: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:(image)
    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:46:16.630+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:




      ORA-00904: "XMLROOT": invalid identifier

      2008-05-17T11:57:12.421+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'))   2  from dual; gives error: select XMLRoot(xmltype('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'), version '1.0', standalone yes)   2  from dual; XMLROOT(XMLTYPE('A'),VERSION'1.0',STANDALONEYES) ------------------------------------------------------------------------ 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: Useful links on Oracle XML DB Solving ORA-22905: cannot access rows from a non-nested table item Oracle XMLType: exctractvalue vs. extract Binding list variable Empty String IS NOT NULL? [...]



      Oracle CPUApr2008 for DB: 10.2.0.4 patch set and first install

      2008-04-20T15:45:59.298+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:53:57.591+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-12-10T23:58:16.089+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:(image)
      • 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:



      Horrible practice of storing users passwords

      2008-03-09T19:26:21.551+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:18:44.164+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.

      Cheers Paweł

      --
      Related Articles on Paweł Barut blog:



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

      2008-02-11T22:55:58.233+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:47:56.868+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:I've got spammed (in comments)"Do not use Linux, please, pirate our software" - Microsoft 13 – Happy or Unhappy Number? I've been tagged 3 times; 8 things about me [...]



      How to shrink Oracle Context Index

      2008-01-23T21:52:06.623+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$$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:22:09.598+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:
      1. By Eddie Awad
      2. By H.Tonguç YILMAZ
      3. By Jorrit Nijssen (Jornica)
      Thanks You for tagging.
      Now I feel that I really need to write something about me:
      1. I was born and grow up in Krosno in south-east Poland (see satellite picture),
      2. 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,
      3. I'm Thawte Web of Trust Notary,
      4. 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 :(
      5. 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,
      6. My first program was written in Basic and was run on PSP-80 (page in Polish)
      7. I'm married and we have 2 daughters (6 and 2 years old),
      8. 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

      2008-12-10T23:58:16.271+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: (image)
      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:49:59.289+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ł