While working on our datapump workaround, I stumbled upon dbms_pclxutil.build_part_index. This is a handy little bugger that has been around since at least 8.1.5, and has some interesting limitations. For starters, the said index has to be partitioned and unusable. Not a biggie, but why? I also found out (the hard way) that not only do you have to run the procedure as the table owner, but also the index owner as well. Or to put it in other words, you have to create the index in the same schema as the table, and run the procedure from that schema only. That rather confounds me; when I asked Oracle Support about this, I was given some schtick about how they can't have users running around building partitioned indexes on other people's tables. If you ask me, I am thinking some developer back in the early '90s wrote this handy piece of code, but made some assumptions, and those assumptions were never corrected going forward. Strange how after all these releases, it is still not very well documented.
ah well....
Subscribe to:
Post Comments (Atom)
1 comment:
Hi Charles,
I completely agree with how ridiculous the restrictions on the package are. For large domain indexes, this is a must-have in Oracle...so I went around the problem. Check out my blog at:
http://otipstricks.blogspot.com/2011/07/fast-domain-index-rebuild-on-exadata.html
Post a Comment