Oracloid Blog

Back in May 2006, I have started my blog using the Blogger platform and one month later moved it to my own website using Wordpress. Couple month later, I joined Pythian and, since then, the vast majority of my blogging activities has been on the Pythian Group Blog. The Pythian blog has grown significantly since then and many more excellent authors started blogging there. While the Pythian blog was mostly focused on Oracle database just a couple years ago, it's has got very broad coverage now and is including MySQL, SQL Server and Oracle databases as well as Oracle Application Server, Oracle eBusiness Suite and other enterprise software. While I think this is a great opportunity to entend your area of interests, it might be just too much for some as few people already complained and unsubscribed to avoid being overwhelmed with information. That was painful to hear! First of all, I should say that there is a way to subscribe only to a selected category or a single author -- just add /feed/ at the end of pretty much any page. For example, all my blog posts can be seen using URL http://www.pythian.com/blogs/author/alex and RSS feed URL would be http://www.pythian.com/blogs/author/alex/feed/. Likewise, the Oracle category RSS feed is http://www.pythian.com/blogs/category/oracle/feed/. For my personal RSS feed I have come up with another alternative that let me also keep my personal web site more up to date -- I've setup an aggregation of my blog posts from the Pythian Group Blog to my personal web site at www.oracloid.com. If I happen to blog at any other places like the BAAG Party web site, my posts can be aggregated and subscribed using a single feed on my personal web site. The feed has the direct links to the source blog posts so you are sent directly to the source blog from your RSS reader so no additional hops involved. The comments on the aggregated posts are disabled and only the fragments are posted in the syndicated post linking to the source for the full version and commenting. This way all readers end up on the source blog anyway. I should mention that I had to "break" my personal blog feed during that process so that the subscribers of my blog are not spammed with 2 years of my old Pythian posts. Some of you might have noticed that. I had to write couple two-liner Wordpress plug-ins to assist in this process -- I should post them one day in case someone else finds them useful as well. What does it mean to you? If you have this message in your RSS feed then, probably, nothing.

• You are subscribed to the Pythian Group Blog -- stay this way and chances are you won't miss any of my blog posts and will keep getting other good stuff from our blog.
• You are subscribed to my personal Pythian blog feed -- stay this way or re-subscribe to my aggregated feed.
• You are subscribed to my personal website feed -- stay this way or consider subscribing to the Pythian blog feed instead for more great stuff.
• You are subscribed to the Pythian Group feed and my personal feed so you've got this message twice -- unsubscribe from my personal feed and keep Pythian blog in your RSS reader; this will avoid duplicate messages.
• You are subscribed to my personal feed on the Pythian blog and my personal web site so you've got this message twice -- unsubscribe from my personal Pythian feed to avoid duplicates.

That's it folks. Thanks for reading! Read more...

We hit an ORA-01450 error today trying to do online rebuild for an index in an unusable state. This was a non-unique index on a fairly large column — VARCHAR2(800 CHAR).

SQL> alter index i1 rebuild online;
alter index i1 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

It rang a bell. I remembered that I encountered this issue a while ago, but I couldn’t recall the details. I know that it has nothing to do with the actual data size — it’s an error that can occur during index creation. A single index block must be able to fit at least two index entries, so the maximum index key size defined by the block size and overhead.

The key length is calculated as:

key length = sum of all column lengths
           + number of columns
           + 2 (key length)
           + 6 (restricted ROWID)
           + 1 (ROWID field length)

If I didn’t miss anything, the key length for my index should be 800 + 1 + 2 + 6 + 1 = 810. Hold on . . . we have character length semantic here — let’s check the database character set:

(more…)

If you have created a new Oracle 11g database using DBCA and opted to use by default 11g’s enhanced security settings or, at least, the audit setting, then you risk the unlimited growth of the SYSAUS tablespace that hosts the audit trail table SYS.AUD$. I realized that while reviewing the slides of my presentation on 11g’s new features, a few of which covered security enhancements.

During my presentation at the TOUG meeting later that day, I mentioned that concern, and Mohamed El-Shafie from Oracle quickly noticed that there is no auto-purge. I promised to have another look at the maintenance tasks in 11g to confirm that, and indeed, the audit trail is not purged automatically when auditing is enabled by default.

Here is a quick remedy — scheduling an audit trail maintenance job.

First create a PL/SQL procedure that will accept a number of days to keep. It rounds down to the beginning of the day. I like to dump a few diagnostic messages to alert.log when my maintenance procedures are running, so I included that here as well. (There was a typo in purge data calculation — thanks to Nial for catching it.

create or replace procedure purge_audit_trail (days in number) as
  purge_date date;
begin
  purge_date := trunc(sysdate-days);
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' started');
  delete from aud$ where ntimestamp# < purge_date;
  commit;
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' has completed');
end;
/

Then let’s schedule a new maintenance job using Job Scheduler, which appeared first in 10g. Oracle 11g includes the default maintenance windows group, MAINTENANCE_WINDOW_GROUP, and we will use that to run the purge.

(more…)