Annual my favorites updates

During 2008 there were a lot of new Oracle blogs I tried to follow, nowadays it is so hard to catch up with all the information around us. Within our limited time resource it is very important to choose which blogs is your own favorites.

Until this morning I couldn’t manage to update my favorites, so if you want to take a look here are my favorites as of today.

Andrew Barry’s DBA Queries at

I loved the idea behind and wanted to share; handy scripts that a DBA may need daily are categorized by subject here, you can find and consume them easily.

Very similar to psoug quick test case idea I strongly believe this kind of a code library is also a basic and important need, after years we all have our own libraries.

So I think this idea can make an important difference for Oracle’s wiki if we all participate?

Improvements with 11.1 for the datatypes and table structures supported for asynchronous change data capture option

I was studying the Asynchronous Autolog CDC Cookbook, I am working on the S_AUDIT_READ Siebel source table, but the AUDIT_LOG column with CLOB datatype failed during the setup with ORA-31513: unsupported column type specified in change table error on 10.2.

As I found out that this limitation for LOBs is not a problem after 11.1 anymore, I wanted to share this information.

Oracle® Database Data Warehousing Guide 10g Release 2 (10.2)
Chapter 16 Change Data Capture: Datatypes and Table Structures Supported for Asynchronous Change Data Capture

Oracle® Database Data Warehousing Guide 11g Release 1 (11.1)
Chapter 16 Change Data Capture: Datatypes and Table Structures Supported for Asynchronous Change Data Capture

ps: if you are interested in CDC option of Oracle please check this previous post of mine for an introduction and a Hotlog option implementation example.

How to give positive feedback as an Oracle customer?

Any company who value their profit must be after their customers’ feedbacks. So we as Oracle users, how do we give our feedback about things we experience? This experience can be a bug, an improvement need or a new feature request and after this feedback we need to receive a response back of course. Metalink is the place for official support, if you purchase of course, usually buggy/nasty situations are topics here. So can we say OTN forums, Oracle related blogs are the places to give our valuable feedback to Oracle? Since I believe so, I will be doing this with this post :)

Each individual is unique and as a result during our daily Oracle quests in our environments we all face different, interesting experiences. But think about somebody coming and requesting from you this kind of a feedback one day, you can get nervous or excited I guess, after all those fights what to say now? :)

I am a part of a telco organization who owns one of the most prominent data warehouses among all Oracle data warehouse customers, also a highly loaded Billing/CRM Oracle OLTP database. Within last ~10 years there were very interesting experiences we had as a team, some we could speak out loud and share, some we can not of course, some I can not remember now even they felt very scary and unforgettable once upon a time. So I tried to use my blog to produce this kind of a feedback.

WordPress has a very nice feature, the random post option at the top right menu, by clicking it several times and reviewing my own posts for the last two years within some minutes my list was ready to go.

In this list some items can be not so valid, some can be very interesting I know, but what I try to point out here is in order to come out with a list like this you need to document your daily experience, in a light fashion of course, blogging like a dairy can be very helpful for this kind of a need or “what did I do last year?” kind of a question’s answers.

So now I hope here comes your items, come on don’t be shy and comment, sky can be our limit here who cares right :)

Turkish Oracle Community Related Updates

Just to prove I am still alive, I wanted to write a post :)

This May our Turkish Oracle Users group forum will be 10 years old. ~1500 memberships and ~5000 messages until now.

I always tried to support OracleTurk since there is a very limited amount of Turkish resources related to Oracle, also CeTurk is another forum I try to support in Turkish. Recently there was an announcement of a new Turkish forum which is founded by the members of the local Oracle University team.

Türkiye has young and very talented software engineers, parallel to this fact as far as I can follow the Turkish Oracle Users community is also growing quietly. I am 32 and I feel like a dinosaur within these guys. So here is a small list that you may want to check which consists of Turkish Oracle Bloggers writing in English who are near 25, yes 25 :)

Yasin, Coşkan and Kubilay more popular Turkish bloggers who are older ones like me, near 30 lets say :)

Oracle had always tons of critiques in my country why they can not touch young poeple compared to IBM and Microsoft, so nowadays we may say things are changing and forums/blogs are the main drivers behind this change I guess.

ps: I am looking for anyone who has been ~10 years of Oracle DBA/Developer, liked to play with Oracle and than chose to continue their career in decision making side, to have their comments on this kind of a migration, are they cool or missing the feeling of being a doer?

Oracle’s Warehouse Builder for any kind of your Extraction-Transformation-Loading needs within Oracle database

Warehouse Builder is a cost-free(part of the database license) ETL tool which produces PL/SQL when its mappings are deployed. The components you use to develop your data flows are based on Oracle’s SQL and PL/SQL capabilities.

In my opinion you really don’t need to own a warehouse to use OWB, with 11g now it is even coming installed with your database. So if you are also an Oracle database minded developer you better plan to check OWB 11g, similar to Apex I can guarantee that you will be having lots of fun and most probably some opportunity to develop your knowledge :)

Here is a hint showing how it can be possible to automate generation from your standard SQLs to OWB mappings.

And here you will find some important OWB hints.

Using Pipelined Table Functions for Siebel’s Audit_Read Data Transformation

It has been a while, I thought it is time that I should write on something? :)

25-12-2008 was my second anniversary on this blog: 233 posts on 18 categories resulted 383 approved comments and ~330,000 page views and lots of good friendships until now. So many thanks to all who contributed!

I was very surprised that I didn’t write on one of my best features of Oracle until today, the Pipelined Table Functions. Beginning with the 9i days I benefited from Pipelined Functions for lots of complex transformations needs.

Here with this post I will share a recent example need to demonstrate this feature. Siebel as a source system for our warehouse has a logging table called S_AUDIT_READ, this table has a CLOB column AUDIT_LOG which holds a string to be parsed with the rules mentioned in the below link.

How to parse audit log value (Doc ID 555553.1)

It can be possible to parse the string in several methods as usual, with even a single SQL of course. But PL/SQL is more readable/maintainable compared to SQL, so Pipelined Table Fuctions is always a good alternative to transform a massive data, in terms of performance also. In the below demonstration I also got rid of the algorithm mentioned above because of the cases in our data.

Siebel’s Audit_Read Data Transformation with Oracle’s Pipelined Table Functions

With the help of Pipelined Table Functions while reading the source data in parallel, it is possible to transform and insert to target table through pipes. For more examples please visit:

Just before closing, I wish a very happy new year to all of us! :)

Two Days Oracle Index Internals Seminar by Richard Foote

I have been following Richard’s blog for some time. So it was good to meet him in personal and join his seminar in Istanbul last week. But compared to my previous seminar notes I honestly need to say that I have been lazy this time, sorry.

When you hear the word Internals it is scary for some of us and not so practical for some others. Since I was familiar with the stuff from Richard’s blog the name was not so important for me, but I must mention that this seminar is much more beneficial for a starter to intermediate Oracle DBA profile so the naming can be perceived wrong.

It was long two days, 720 slides of pure Index topics within 10 sessions. But Richard is really a good presenter, with his jokes, up and down waving voice and body language he never let the class to go to a little sleep, even after a Turkish lunch. :)

I also wrote several Index related posts before, it was a good chance to ask Richard the question: Why Indexes? Not Tables, Clusters or Streams etc. His answer was, no specific reason. :) Marketing guys call this kind of a strategy as positioning, I like this very much: Volvo>Security, Lewis>Optimizer, Google>Simplicity, Dyke>RAC, Apple>Flexibility, Millsap>10046 etc. We may say if you have a seat on your mind for Oracle Indexes Richard wants to sit there, I guess.

Within two days Richard discussed on below topics:
– Introduction to Oracle Indexes,
– B-Tree Index Internals,
– Indexes and Statistics,
– Indexes and Constraints,
– Rebuilding Indexes(ironic chapter name for Richard right? :)
– Indexes and the Cost Based Optimizer,
– Indexing Tips, Tricks and Traps,
– Linguistic Indexes and Case-Insensitive Search,
– Other Index Structures like: Reverse Key, Function Based, IOT, Secondary Indexes on IOT, Invisable and Fake Indexes,
– Partitioned Indexes,
– Bitmap and Bitmap-Join Indexes

And here are my highlights from this seminar.