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 '...you will need to reset the passwords.  

CREATE PROFILE prof LIMIT
 FAILED_LOGIN_ATTEMPTS 4
 PASSWORD_LOCK_TIME 30
 PASSWORD_LIFE_TIME 90
 PASSWORD_GRACE_TIME 3;
ALTER USER johndoe PROFILE prof;

 (source: Oracle Documentation)

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

ALTER PROFILE DEFAULT LIMIT ;

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

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' thing...it 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 key...so...you rebuild the table so it is in a certain order...you 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 key...no myth...just the way it works.


...by 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 operation...download it today from www.DanHotka.com .

Dan Says:
I STRONGLY push index rebuilds to fix the index block splits on DML...using a low PCTFREE loads the index leaf blocks up...so...when you RANGE SCAN operations on them...you do fewer physical I/O.  No myth here...you do fewer physical I/O...and things run a bit faster...you 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 PCTFREE...you will get a lot of leaf block splits (when there isn't room for the entry...Oracle splits the leaf pointers across 2 leaf blocks...giving you 2 half full leaf blocks...), which causes more physical I/O when range scanning...more DML...more splits...so...you do reorgs to keep performance at its peak.

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

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

Dan,

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.

Regards,
Tim

Skills Update

Hi!

I'm going to be better at posting to my blog.  Watch for useful APEX and OBIEE information. 

I'll also post some useful information and scripts for starting and stopping Oracle SQL Trace (the 10046 trace).

Dan