Friday, October 4, 2013

See Dan's Blogs on TOAD World

Hi!  Just an FYI...I blog somewhat frequently on  Check my blog there for useful information.

Work has commenced on the Isetta then I got much work has commenced yet.

Thank you!

Dan :)

Sunday, March 10, 2013

The Isetta is Painted!!!

The Isetta is painted!  I'll get the BMW color codes/names for another post but this is a color pattern these cars were painted.  This one was originally a more turquois color but I liked this one better...I was shooting for a bit darker blue but it sure came out nice!

Jerry (pictured) did most of the prep work...dedented it...refreshed some putty from a prior accident it appeard to have been in...etc.  Marv (not pictured) did the actual painting...he does a great job and had painted both of my bugs.

Saturday, February 23, 2013


Just got home from a speaking opportunity at the NCOAUG group.  They meet twice a year in the Chicago area.

I did a new topic:  OBIEE Tips and Techniques.  The session was well-attended.  I used to do these with Discoverer all the time. 

I discussed how to get the free Sample environment from Oracle/OTN...discussed how to run it and use it. 

Then I build a pivot table report and a report with a narative response in it.

I will be publishing a small book on these various tips.  Watch for 'OBIEE Tips and Techniques by Dan Hotka'.  I want the book to be affordable because I will publish a newer version as I do these presentations.  I will also include various tips I run across about the product.  It will be on Amazon.  Watch my website/newsletter for announcements on its availability.

Sunday, November 11, 2012

Expiring Passwords

Did you know that the default for passwords in Oracle11 is 180 days?

You can disable this feature, adjust this feature, and even assign a grace period for people to change their passwords after the password has expired.

IF the limit was hit...and you get the warning 'password to expire in ' will need to reset the passwords.  

ALTER USER johndoe PROFILE prof;
 (source: Oracle Documentation)

You can also alter a profile and set any of these parmeters:


alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;

Good luck out there!

Password Complexity

These are the rules for Oracle Passwords:
  • The password contains no fewer than 8 characters and does not exceed 30 characters.
  • The password is not the same as the user name, nor is it the user name spelled backward or with the numbers 1–100 appended.
  • The password is not the same as the server name or the server name with the numbers 1–100 appended.
  • The password is not too simple, for example, welcome1, database1, account1, user1234, password1, oracle,oracle123, computer1, abcdefg1, or change_on_install.
  • The password is not oracle or oracle with the numbers 1–100 appended.
  • The password includes at least 1 numeric and 1 alphabetic character.
  • The password differs from the previous password by at least 3 letters.

    You can make changes to this default behavior by editing the UTLPWDMG.SQL script

    Check my prior blogs on how to disable this feature altogether.  I do this for my training environments.  I wish to use the same password as the user ID and also turn off case-sensitivity...

Thursday, August 2, 2012

Index Rebuild Tips...why???

This is an answer to an email I thought I'd share...the question is doing index reorgs to fix poor clustering factor...

partial email: "Dear Dan
I very much enjoyed your explain plan/indexing seminar earlier this year.

I am now in a situation where I need to apply some of this for real. I have run your INDEX_INFO.sql and found that half of our indexes are 'Fair' or even 'Poor'. On another slide, you recommend regular Index Rebuilds, even weekly in case of 'lots of DML'.

There are many places on the internet where Index Rebuilding is called a myth. They say it does not improve the clustering factor. Our DBAs chime in on that.

Can you point me to an final answer?

As I discussed...clustering factor is not a 'fixable' is the relationship between the leaf blocks and how many table blocks they point to.  It is TOTALLY dependent on the order of the table and the index rebuild the table so it is in a certain will fix the intended index but what about the other indexes on that table?  IF one or two indexes had a good CF...they won't now!  So...CF is strictly the order of the table vs the indexing myth...just the way it works. the way...the Index_Info.sql script is yours for asking :)  It shows the clustering factor...which Oracle uses to help decide to use this index on a 'range scan' execution plan it today from .

Dan Says:
I STRONGLY push index rebuilds to fix the index block splits on DML...using a low PCTFREE loads the index leaf blocks you RANGE SCAN operations on do fewer physical I/O.  No myth do fewer physical I/O...and things run a bit rebuild indexes because you WANT to use a low pct free so you do fewer physical I/O operations when using the index...and you rebuild because with a low will get a lot of leaf block splits (when there isn't room for the entry...Oracle splits the leaf pointers across 2 leaf you 2 half full leaf blocks...), which causes more physical I/O when range scanning...more DML...more do reorgs to keep performance at its peak.

This is no myth :)  Doing fewer physical I/O operations improves SQL performance.  Hope this helps.

Thursday, July 26, 2012

Large Exports out of APEX

Having problems creating large export files out of APEX?  Try these tips...

The question was APEX 4.0 would only create a 60mb .csv file...


A lot of factors here. My best guess would be the web server, but it depends on how it is hosted.

I know we would hang our CSV generation if we used interactive reports due to size, but had a lot to do with our DB. It would create a session that would run for quite a while and then get lost by the web server.

We ended up doing a process that does a straight dump. My question would be, does the 60MB file correspond with a "time to generate". There may be something else going on.