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!

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.