Sunday, November 11, 2012

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

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

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

Monday, November 21, 2011

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

Wednesday, June 1, 2011

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!

Tuesday, March 15, 2011

PL/SQL Conditional Compilation

Starting with Oracle10.2, Oracle now allows for PL/SQL to be coded including only certain lines of code:
· Different code for different releases of the database (selection directives)
· Checking INIT.ORA settings at compile time (inquiry directives)
· Checking for various parameter error conditions at compile time (error directives)

This coding technique was implemented in Oracle10r2 but has backward support back to Oracle9.2. Contact Oracle Support to learn how to include this technique for these earlier releases.

This coding technique can make procedures and functions database release independent! You can now have one version of the code that supports the legacy and latest features, compile with only certain INIT.ORA settings, or create error conditions based on parameters and code reminders at compile time.




Selection directives works with $IF $THEN $ELSE $ELSIF $END. At compile time, Oracle will only compile the code that tests true between these $IF directives. The DBMS_DB_VERSION package is very useful for release-specific code. This package allows you to interrogate the Oracle major and minor versions of the database at compile time. DBMS_DB_VERSION.VERSION gives the major database release (such as 10) and the DBMS_DB_VERSION.RELEASE gives the minor database release number such as 2 (…as in Oracle10r2). There are also bullions that will be set true for the release that is currently being used.


Inquiry Directives works with the $IF logic and is used to interrogate compiler settings.



Inquiry Directives example

Notice that the PLSQL_CCFLAGS can be set using any variables!




Standard PL/SQL errors can be invoked with a mix of these techniques. This might be useful to document that code has not been completed, reminders for programmers who are doing modular programming, etc. Notice the code at lines 14 thru 18.

These examples came from Oracle Magazine July / Aug 2006.

Summary

This technique will allow programmers and DBA’s to develop 1 script that will work on multiple Oracle platforms, particularly using that syntax that changes between releases of Oracle!

My 3-day Advanced PL/SQL Tips and Techniques has lab exercises using this new coding style.