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

Oracle10g+ Plan Table

Hi!

Oracle is now maintaining a PLAN_TABLE for us. This table is used extensively in the SQL tuning process. It is also used by Oracle's autotrace and other tools. I show various uses for it in my SQL Tuning course. Oracle's version is owned by SYS and is named PLAN_TABLE$. It does have public access.

To see if it is working correctly for you:

• Simply DROP your PLAN_TABLE.
• in sql*plus...'set autotrace on'
• execute ANY sql (select * from emp; for example...).
  

IF sql*plus complains about a missing Plan Table, have your administrator create a public synonym for SYS.PLAN_TABLE$. This is easier than using PLAN_TABLE$ in each of your scripts/TOAD/etc. Some tools such as SQL Developer, the PLAN_TABLE name is not adjustable.

Good luck!

Dan

Oracle11g Case Sensitive Passwords

A Change for Oracle11

Some of my best material comes to me in interesting ways. I usually use all lower case syntax with Oracle. I recently taught a class using an 11g database and some folks had issues logging in.

Oracle11g IS Case Sensitive on Passwords

Oracle11g is now case sensitive with passwords. When new accounts are created, the password is indeed case sensitive.

You can see the case sensitive setting in SQL+ by using ‘SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON’. People with DBA privileges can see the password status using ‘SELECT USERNAME, PASSWORD_VERSIONS from DBA_USERS;’. This list will show both migrated users and new users password status.

Migrating from older releases…

When migrating from older releases, the passwords are preserved and they are NOT case sensitive until they are changed, and they are only case sensitive only if the above SEC_CASE_SENSITIVE_LOGON is still set to TRUE.

Changing the Settings

Because I do training and I’m not interested in case-sensitive passwords, I ran the following 2 commands to disable this feature on my Oracle11R1 database.

Command prompt: orapwd file=orapwDB11Gb entries=100 ignorecase=y password=’’;

Connect as SYSDBA and enter:

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

Summary

Be prepared for this subtle change in Oracle11 logon behavior!