Search Oracle Blogs

List of Blogs maintained by Paweł Barut.

Friday, September 26, 2008

OOW2008 day 5 – It’s A Wrap

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 ( 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.

Related Articles on Paweł Barut blog:

Thursday, September 25, 2008

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

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.


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

Wednesday, September 24, 2008

OOW2008 day 3

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.

Related Articles on Paweł Barut blog:

Tuesday, September 23, 2008

OOW2008 day 2 – Oracle Beehive

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.


Related Articles on Paweł Barut blog:

Monday, September 22, 2008

Arriving San Francisco – OOW2008 day 1.

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 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.

Related Articles on Paweł Barut blog:

Saturday, September 13, 2008

Preparing for Oracle OpenWorld 2008

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,

Related Articles on Paweł Barut blog:

Wednesday, August 13, 2008

DB Link to Oracle 11g

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:

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.


More on Creating Database Links.

Related Articles on Paweł Barut blog:

Monday, August 04, 2008

Lost Pictures - Recovered Pictures

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.


Related Articles on Paweł Barut blog:

    Thursday, July 17, 2008

    Oracle OpenWorld 2008

    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 !


    Related Articles on Paweł Barut blog:

    Saturday, May 17, 2008

    ORA-00904: "XMLROOT": invalid identifier

    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

    my original code should look like that:
    SQL> select XMLRoot(xmltype('<a>a</a>'), version '1.0', standalone yes)
      2  from dual;


    <?xml version="1.0" standalone="yes"?>
    This can run without XMLROOT function in XDB schema.

    Hope this will help someone to save some time.

    Related Articles on Paweł Barut blog:

    Sunday, April 20, 2008

    Oracle CPUApr2008 for DB: patch set and first install

    Written by Paweł Barut
    To my surprise CPUApr2008 is not available for latest patch-set of Oracle 10gR2 ( 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 patch-set. In note 552248.1 you will find then:

    1.3 Database Patch Set

    The Database Patch Set includes the CPUApr2008 content.

    So the CPUApt2008 was available to all at least month before official release and was included in 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
    ORA-06508: PL/SQL: could not find program unit being called:
    ORA-06512: at line 1

    Hmm... After reconnecting, this commend run without errors:
    SQL> conn / as sysdba
    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:

    Tuesday, April 15, 2008

    Oracle Database 11g Release 2 is coming

    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:

    Monday, March 24, 2008

    Which Temporary Tablespace is used for sorts?

    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:
    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:

    Sunday, March 09, 2008

    Horrible practice of storing users passwords

    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:

    Monday, February 18, 2008

    Useful links on Oracle XML DB

    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:

    Monday, February 11, 2008

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

    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 or later

    • Databases was created with any release (for example,,, or 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:

    Sunday, February 03, 2008

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

    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:
    1. 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.
    2. 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.
    3. 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.
    4. 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:

    Wednesday, January 23, 2008

    How to shrink Oracle Context Index

    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 (,
    • 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ł

    Saturday, January 12, 2008

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

    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.


    Copyright © Paweł Barut
    Printing from DOS to USB Printer