The Tom Kyte Blog

Upcoming Events...

2014-01-27T15:54:00.000-05:00

I took some time off from the road at the beginning of 2014 - getting ready to get back on the road again, lots of trips scheduled from February till the end of June. This is just looking at public events for the next three months, hope to see you at one of them!

Virtual Developer Day Feb 4th

I'll be speaking, along with Jonathan Lewis, about the last 20 years or so of technology and where we think it is all going as part of the Virtual Developer Day - an entirely online event with many technical speakers. Something to definitely look into!

RMOUG Feb 6th-7th

At the beginning of February, I'll be at RMOUG Feb 6th and 7th. Truth be told, this won't be a trip for me - I'm located in Denver, CO now (since the beginning of the year). I will just be returning from Toronto at the beginning of the week (I know all of the best places to go in February!).

Ohio Feb 11th-13th

Of course, if it is February - it must be Ohio. That is the best place to fly into and then drive around in during that month :) I'll be coming down from Ottawa (another 'must see' location in February) and spending Feb 11th in Dayton, Feb 12th in Cincinnati and the 13th in Columbus. Hitting all of the user groups across the state.

Real World Performance Netherlands, Germany and Bulgaria Feb 18th-21st

Then I'm off to continue the Real World Performance tour with Andrew Holdsworth and Graham Wood. This month we are hitting the town of Breda in the Netherlands on the 17th, Munich in Germany on the 18th and finally Sofia in Bulgria on the 21st.

Hotsos Symposium Mar 3-6th

If it is March, then it is definitely time for the annual Hotsos Symposium. I'll be returning this year with a few sessions. It should be nice and warm in Dallas in March!

Ireland March 11th-12th

I'll be in Ireland on March 11th for the Ireland OUG conference. I'll be speaking on performance and the new In-Memory capabilities coming in Oracle Database 12.1.0.2 soon. On the 12th - I'll be delivering a one day Optimizer Master Class.

Real World Performance London and Latvia

The tour continues with a date of March 26th in London and March 28th in Riga Latvia.

Belgrade Serbia, April 1st-2nd

I'll post more details when I have them, but I'll be doing a two day seminar on the optimizer and developer related topics at this time. It will be in conjunction with Oracle University so you can monitor their event website for information soon too.

IOUG Collaborate April 7th-11th

I'll be doing a one day optimizer seminar and four other sessions during the week. April in Las Vegas - perfect time to be there! And Admin Savage will be speaking too - love the mythbusters!

Oracle Conference on the James, April 24th

I'll be doing a keynote and a technical session in the morning of the 24th for the VOUG and HROUG. Lots of great speakers here - definitely check this out if you are in the Virginia area!

Manila, Philippines - week of April 28th

I'll be doing some events in Manila this week - details to follow soon!

All day optimizer event....

2013-11-01T12:26:00.000-04:00

I've recently taken over some of the responsibilities of Maria Colgan (also known as the "optimizer lady") so she can move onto supporting our new In Memory Database features (note her new twitter handle for that: https://twitter.com/db_inmemory ).

To that end, I have two one day Optimizer classes scheduled this year (and more to follow next year!). The first one will be Wednesday November 20th in Northern California. You can find details for that here: http://www.nocoug.org/ .

The next one will be 5,500 miles (about 8,800 km) away in the UK - in Manchester. That'll take place immediately following the UKOUG technical conference taking place the first week of December on December 5th. You can see all of the details for that here: http://www.ukoug.org/events/tom-kyte-seminar-2013/

I know I'll be doing one in Belgrade early next year, probably the first week in April. Stay tuned for details on that and for more to come.

Checking out Adaptive Execution Plans in 12c

2013-09-16T11:48:00.002-04:00

Just a short note pointing out a new video on how/when adaptive execution plans happen on Oracle Database 12c: http://www.youtube.com/watch?v=9o9iuxNBciQ&feature=youtu.be

Oracle OpenWorld 2013, where I'll be...

2013-09-15T04:36:00.000-04:00

I have a fairly busy schedule next week at Oracle OpenWorld 2013.

It all starts on Sunday with a day long seminar. I'll be doing a series of "five things about" - in the areas of SQL, PL/SQL, Performance and more. You can check out and register for those sessions (and many others) here: http://www.oracle.com/openworld/oracle-university/index.html

Next, on Monday I'll be delivering:

Session ID: GEN8579
Session Title: General Session: What’s New in Oracle Database Application Development
Venue / Room: Marriott Marquis - Salon 8
Date and Time: 9/23/13, 12:15 - 13:15

I've done a session like this every year for the last 8 or so years, It'll cover what new Oracle Database Application development techniques have become available over the last year (and will include a few Oracle Database 12c repeats from last year where relevant).

Next, on Tuesday I'll be doing:

Session ID: CON8426
Session Title: The Five Best Things to Happen to SQL
Venue / Room: Moscone South - 103
Date and Time: 9/24/13, 10:30 - 11:30

This is not just the five best things to happen to SQL in Oracle Database 12c - but rather - five of the coolest things to happen to the SQL language over the years. You might be surprised at how many you did not know about....

And lastly - on Wednesday - the last slot before the appreciation event - I'll be presenting on:

Session ID: CON11637
Session Title: What’s New in Oracle Database 12c
Venue / Room: Moscone South - 103
Date and Time: 9/25/13, 17:00 - 18:00

This is not a repeat of last years "Top 12 Things about Oracle Database 12c", this is all new content - things discovered and found useful in the last year of using Oracle Database 12c. So, if you have seen my "12 things" talk - or videos - or read the articles in Oracle Magazine, this will be all new and different.

I hope to see you around - I'll be on site all week from Saturday evening until Wednesday night (flying back home on a redeye Wednesday).

Enjoy the show!

12c - Code Based Access Control (CBAC) part 1

2013-07-18T10:12:00.003-04:00

One frequently asked question posed by stored procedure developers since version 7.0 was released in 1992 has been "why do I get an ora-1031 in PL/SQL but not in SQL*Plus directly". I get this question on asktom a lot, I've written articles about it in Oracle magazine, I must have explained this thousands of times by now.

And now, it all has to change - roles and stored procedures are no longer like matter and anti-matter. Staring in Oracle Database 12c a role may be granted to a bit of code, and that role will be active only while that procedure is executing. Think about what that means - you can have a schema chock full of code - hundreds of packages - but have only one package that has a certain privilege at run time. This will allow you to implement the concept of "least privileges" fully. What could be more least privileges than granting a privilege to a specific bit of code?

Think about this from a SQL injection protection point of view. You could either grant privilege X directly to the schema - meaning this privilege would be available for every single stored unit in that schema to use at anytime, or you can grant this privilege to a role and then grant that role to that unit. That unit and only that unit would be able to use privilege X at runtime. If some other units in that schema had a SQL injection bug - they would not be able to utilize that privilege.

We'll take a look at this new capability from two perspectives - from that of a definers rights routine (the default) and from that of an invokers rights routine. In the case of the definers rights routine, this new capability will only make sense when you use dynamic SQL. In the case of the invokers rights routine, this new capability has a much larger impact and makes the use of invokers rights routines much wider than it was in the past. I'll defer talking more about invokers rights routines until next time and we'll concentrate on definers rights routines for now.

Definers rights routines compile with the set of privileges granted directly to the owner of the procedure - roles are never enabled during the compilation of a compiled stored object. This is true in Oracle Database 12c still - and is the reason this new capability only makes sense with dynamic SQL in a definers rights routine. In order for the unit to compile, all of the privileges necessary for the static SQL and PL/SQL in the unit must be granted directly to the owner of the unit. Therefore - any privileges granted via roles cannot be used for static SQL or PL/SQL. The compilation would fail without the direct privilege. However, any dynamically executed code would not be security checked until runtime, the compiler would not "see" this code. And with CBAC - the set of privileges the dynamic SQL will be checked with will be all of the privileges granted directly to the owner of the unit and any privileges associated with roles granted to the unit.

So, if we start with a simple user and role:

ops$tkyte%ORA12CR1> create user a identified by a
  2  default tablespace users
  3  quota unlimited on users;
User created.

ops$tkyte%ORA12CR1> create role create_table_role;
Role created.

and then we grant some privileges to the user and the role:

ops$tkyte%ORA12CR1> grant create table to create_table_role;
Grant succeeded.

ops$tkyte%ORA12CR1> grant create session, create procedure to a;
Grant succeeded.

ops$tkyte%ORA12CR1> grant create_table_role to a with admin option;
Grant succeeded.

ops$tkyte%ORA12CR1> alter user a default role all except create_table_role;
User altered.

we are ready to start. Note that the user A has only the create session and create procedure privilege granted to the directly. They do have the CREATE TABLE privilege, but that privilege is granted via a role to the user - it will not be available to that user during the compilation of a stored unit, nor would it be available at runtime (until we grant it to the code itself).

So, let's create a procedure in this account:

ops$tkyte%ORA12CR1> connect a/a
Connected.

a%ORA12CR1> create or replace procedure p
  2  as
  3  begin
  4          execute immediate
  5          'create table t ( x int )';
  6  end;
  7  /
Procedure created.

Now, the procedure created successfully since we had the create procedure privilege, but if you try to run it you would receive:

a%ORA12CR1> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "A.P", line 4
ORA-06512: at line 1

and in fact in 11g and before - that would be the only thing you would ever receive. You would have to grant CREATE TABLE to the schema A - making it available to every single stored unit in that schema. But in Oracle Database 12c - we can grant the CREATE_TABLE_ROLE to the procedure:

a%ORA12CR1> set role create_table_role;
Role set.

a%ORA12CR1> grant create_table_role to procedure p;
Grant succeeded.

a%ORA12CR1>
a%ORA12CR1> exec p

PL/SQL procedure successfully completed.

a%ORA12CR1> set linesize 40
a%ORA12CR1> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                          NUMBER(38)

the CREATE TABLE privilege is now available to the stored procedure P and only that stored procedure. No other bits of code in this schema would be able to create a table.

So, in short, dynamic SQL and PL/SQL executed within a definers rights routine can now take advantage of privileges granted to roles. This will allow you to implement the concept of "least privileges" (and to use roles in definers rights routines).

In the next article, we'll look at this from the perspective of an invokers rights routine. That is where this new capability gets really interesting!

12c - Whitelists...

2013-07-17T15:17:00.001-04:00

Starting in Oracle Database 12c - you can limit down to the package/procedure or function level what bits of code may invoke other bits of code in the database. This process is called "white listing" and can be used to implement the concept of least privileges in your database.

In the past - if a given schema A had packages P1, P2, P3, ... Pn - then any of P1 .. Pn could invoke any function or procedure exposed in the specification of any of P1 .. Pn. There would be no way to stop one bit of code from invoking any other bit of code. This could have implications in the area of SQL Injection. If one of the packages was subject to a SQL Injection bug - then that package could be used to execute ANY of the existing bits of code in that schema. Additionally - even if none of the packages in that schema had a SQL Injection bug - but the application connected to the database itself did, an attacker could use that bug to execute any bit of code in that schema.

With the white list approach, the only way to execute a given piece of code would be to run it from a specific set of compiled units. You cannot execute a white listed unit from the top level, it must be called by some specific set of units. Now a SQL injection bug in the application cannot execute this code (it would have to call it as a top level call - but a white listed unit cannot be called that way). And even further - a SQL injection bug in the code stored in the database will not be able to execute this white listed code (unless of course it was on the white list).

This is all accomplished with the new "accessible by" clause. The use of this clause on a unit will restrict the calling set of units to be those in the accessible by clause and the unit itself (a units code is always accessible to itself).

For example, I'll create a package that is to be used only by procedure P1 (and itself) in some schema:

ops$tkyte%ORA12CR1> create or replace package my_pkg
  2  accessible by (p1)
  3  as
  4      procedure p;
  5      function f return number;
  6  end;
  7  /
Package created.

ops$tkyte%ORA12CR1> create or replace package body my_pkg
  2  as
  3
  4  procedure p
  5  is
  6  begin
  7      dbms_output.put_line( 'hello world' );
  8  end;
  9
 10  function f return number
 11  is
 12  begin
 13          p;
 14          return 42;
 15  end;
 16
 17  end;
 18  /
Package body created.

as you can see - I've used the accessible by clause in the package specification. This will restrict this package to be invoked only by procedure P1 or the package MY_PKG in this same schema. We can see that MY_PKG can invoke itself since function F calls procedure P in that same package. Additionally - we can see that only procedure P1 outside of MY_PKG can invoke the functionality of this package. For example:

ops$tkyte%ORA12CR1> create or replace procedure p1
  2  as
  3  begin
  4      my_pkg.p;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA12CR1> create or replace procedure p2
  2  as
  3  begin
  4      my_pkg.p;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

ops$tkyte%ORA12CR1> show errors
Errors for PROCEDURE P2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PL/SQL: Statement ignored
4/5      PLS-00904: insufficient privilege to access object MY_PKG

Procedure P1 successfully compiles and would be able to invoke MY_PKG.P but P2 cannot. Furthermore, an attempt to execute MY_PKG as a top level call will fail:

ops$tkyte%ORA12CR1> exec my_pkg.p
BEGIN my_pkg.p; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object MY_PKG
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

It should be noted that the acccessible by clause list is not evaluated at compile time for the unit being protected. In the above example - we stated MY_PKG would be accessible by P1, before P1 was created. That means you can put just about anything you want in the accessible by clause without raising an error (so be careful).

The accessible clause can be used across schemas as well. If we recreate the package specification as:

ops$tkyte%ORA12CR1> create or replace package my_pkg
  2  accessible by (p1,scott.p)
  3  as
  4      procedure p;
  5      function f return number;
  6  end;
  7  /

Package created.

ops$tkyte%ORA12CR1> grant execute on my_pkg to scott;
Grant succeeded.

we'll be able to successfully compile and execute the code from SCOTT.P:

ops$tkyte%ORA12CR1> connect scott/tiger
Connected.

scott%ORA12CR1> create or replace procedure p
  2  as
  3  begin
  4          ops$tkyte.my_pkg.p;
  5  end;
  6  /
Procedure created.

scott%ORA12CR1> exec p
hello world

PL/SQL procedure successfully completed.

scott%ORA12CR1> exec ops$tkyte.my_pkg.p
BEGIN ops$tkyte.my_pkg.p; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object MY_PKG
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

but note that once again - SCOTT cannot invoke this package from the top level either - meaning if the SCOTT schema has some SQL injection issues - we've removed the ability for an attacker to invoke OPS$TKYTE.MY_PKG from that schema.

Next time I'll be taking a look at another new PL/SQL security feature - code based access control, the ability to grant ROLES to code...

12c - Implicit Result Sets...

2013-07-11T04:56:00.000-04:00

Since version 7.2 of Oracle we've been able to return result sets from stored procedures to clients. The way we've accomplished this in the past is via a REF CURSOR. I've always liked this approach since it makes it so that the signature of a stored procedure/function makes it clear that a procedure/function a) actually returns a result set and b) can be done such that the 'shape' of the result set is known at compile time.

What that means is - the REF CURSOR would be a formal named parameter for the procedure or the return value of a function. If you describe the procedure - you'll see it. You'll know exactly how many result sets the procedure returns and you have the capability to know the number of columns, names of columns and datatypes if the developer chose to use strongly typed ref cursors. In other words - you'll know what you are getting, the REF CURSORS are explicitly there, staring you in the face.

Other databases adopted an implicit approach. If you describe their procedures - you will have no idea how many or what kind of result sets they return. You have to run them and see what they decide to send back - and each time you run them, they could return a different result. In other words, you had to read the code to see what results might be coming back to you. I'm not a huge fan of this approach - it is much less self documenting, more error prone (in my opinion).

That said - this difference (explicit versus implicit result sets) can make migrating an application from these databases to Oracle difficult. You have to change the inputs/outputs of your stored procedures - adding the ref cursors and you have to modify the client code. You cannot just change the stored procedure, you have to change the client. When using implicit result sets - the client code would look similar to this psuedo code:

execute stored procedure( param1, param2, .. paramN )
while more result-sets loop
while more-data-from-that-result-set loop
process data
end while more-data-from-that-result-set
end while result-sets

while the code for explict result sets would resemble:

execute stored procedure
( param1, param2, ... paramN,
result-set1, result-set2, ... result-setN)

while more-data-in-result-set1 loop
process data
end while more-data-in-result-set1
...
while more-data-in-result-setN loop
process data
end while more-data-in-result-setN

so, the structure of the client code must be modified in addition to the stored procedures signature (it's inputs and outputs).

In order to ease migrations from implicit result set databases to Oracle, Oracle database 12c introduced support for implicit result sets - result sets that can be returned to a client but do not necessitate a formal named parameter. The client code to process such a result set is identical now - the client code needs not change, the stored procedure signature need not change - the body of the procedure just needs to be implemented in PL/SQL.

We accomplish this magic in PL/SQL via two new API calls in the DBMS_SQL package - one for returning REF CURSORS and one for returning DBMS_SQL cursors. They are:

PROCEDURE RETURN_RESULT(rc IN OUT SYS_REFCURSOR, to_client IN BOOLEAN DEFAULT TRUE); 
PROCEDURE RETURN_RESULT(rc IN OUT INTEGER, to_client IN BOOLEAN DEFAULT TRUE);

TO_CLIENT => true, return to client layer
TO_CLIENT=> false, return to invoker, immediate caller - could be another plsql routine

to demonstrate this, we can use SQL*Plus as the client and show an implicit result set being returned to the client and auto-magically printed (this will only work with a 12c SQL*Plus!! it is the first SQL*Plus to recognize that there might be result sets to print)

ops$tkyte%ORA12CR1> declare
  2      c1 sys_refcursor;
  3      c2 sys_refcursor;
  4  begin
  5      open c1 for select * from dept;
  6      dbms_sql.return_result(c1);
  7
  8      open c2 for select * from dual;
  9      dbms_sql.return_result(c2);
 10  end;
 11  /

PL/SQL procedure successfully completed.

ResultSet #1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ResultSet #2

D
-
X

ops$tkyte%ORA12CR1>

as you can see - there were no inputs/outputs to this block of code - we implicitlly returned the two result sets using the new DBMS_SQL API and SQL*Plus used a method that would result the second set of psuedo code above to discover what results might be available and then print them out.

We can process these implicit result sets in PL/SQL if we want (they don't have to be returned to a client - the ref cursor/dbms_sql cursor can be implicitly returned to a PL/SQL function/procedure) using the two new API calls:

PROCEDURE GET_NEXT_RESULT(c IN INTEGER, rc OUT SYS_REFCURSOR);
PROCEDURE GET_NEXT_RESULT(c IN INTEGER, rc OUT INTEGER);

(see http://docs.oracle.com/cd/E16655_01/appdev.121/e17622/dynamic.htm#LNPLS2176 for a description and example of this API).

But in general, it'll be a client bit of code that processes these result sets and a client java program might resemble:

Connection conn = DriverManager.getConnection(jdbcURL, user, password);
try 
{
    Statement stmt = conn.createStatement (); 
    stmt.executeQuery ( “begin foo; end;” );
 
    while (stmt.getMoreResults())
    {
         ResultSet rs = stmt.getResultSet();
         System.out.println("ResultSet");
         while (rs.next())
         {   
               /* get results */
         }
     }
}

I see Tim Hall has written about this recently as well - you can see his writeup here: http://www.oracle-base.com/articles/12c/implicit-statement-results-12cr1.php

12c - SQL Plus new things....

2013-07-08T10:42:00.000-04:00

SQL Plus is still my typical tool of choice to "talk" to Oracle - 25+ years and going... Oracle Database 12c has introduced a few new things in this venerable old tool

Last Login Time

$ sqlplus /

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 7 13:53:15 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Wed Jul 03 2013 14:30:14 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ops$tkyte%ORA12CR1>

By default, SQL Plus will display your last login time - if you don't want that, just use -nologintime.

More on invisible columns

I recently wrote about invisible columns and how they wouldn't be displayed via a DESCRIBE command. That is - SQL Plus won't show them by default. However, there is a SET command that will display them:

ops$tkyte%ORA12CR1> create table t
  2  ( x int,
  3    y int invisible
  4  );

Table created.

ops$tkyte%ORA12CR1> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)

ops$tkyte%ORA12CR1> show colinvisible
colinvisible OFF
ops$tkyte%ORA12CR1> set colinvisible ON
ops$tkyte%ORA12CR1> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 Y (INVISIBLE)                                     NUMBER(38)

Support for Pluggable Databases

There is support for starting pluggable databases from a container database, as well as three new SHOW commands to see what pluggable databases there are and information about the current pluggable database you are connected to:

sys%ORCL> startup pluggable database pdborcl;
Pluggable Database opened.

sys%ORCL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO



sys%ORCL> connect scott/tiger@pdborcl
Connected.

scott%PDBORCL> show con_id

CON_ID
------------------------------
3

scott%PDBORCL> show con_name

CON_NAME
------------------------------
PDBORCL

For more details...

For more details - check out the SQL Plus guide. It is always good to look through the existing commands anyway - to remind you of something you forgot you already knew (I do that all a lot - forget things I knew once upon a time. Just looking at the table of contents for SQL Plus could remind you of many forgotten SHOW and SET commands!)

In Israel...

2013-07-05T11:23:00.001-04:00

I'm in Israel now getting ready for the http://www.iloug.org.il/ conference in Jerusalem next week. Looks to be a good one with Mark Rittman, Joel Kallman and many others speaking!

See http://www.iloug.org.il/TechDays/?page=BigData_BI#track for the agenda...

The weather is beyond phenomenal here - see https://twitter.com/OracleAskTom/status/353171810784772098 for a quick picture.

12c - Multiple same column indexes...

2013-07-05T11:15:00.000-04:00

Another new capability of Oracle Database 12c is the ability to create more than one index on the same set of attributes. That is - you can have two or more indexes on the column "X", or the set of columns "A,B,C".

So, that would beg the question "why - why would you want to do that?". The answer is twofold - testing and availability. The testing part is obvious - you might want to test how the performance of a bitmap index would be in your reporting system as compared to a b*tree index on the same set of columns. You can now create that bitmap index even though the b*tree exists, test it out in your session, and then decide to get rid of the b*tree index in favor of the bitmap (or get rid of the bitmap of course).

The availability part isn't as obvious perhaps. If you wanted to roll out a change to production that would replace a normal b*tree index with a reverse key index - that would have called for an outage in the past. You would have to drop the original index and then create the new one. During the period of time the index was initially dropped and the new one completed work, it is likely the applications that depended on that index for data retrieval would have to be offlined. If they were not - their performance, in light of the missing index, could be disastrous - in effect, a denial of service attack on the database.

In Oracle Database 11g and before - this is what we would expect if we attempted to create more than one index on the same set of attributes:

ops$tkyte%ORA11GR2> create table t ( x int, y int, z int );
Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(x,y);
Index created.

ops$tkyte%ORA11GR2> create bitmap index t_idx2 on t(x,y);
create bitmap index t_idx2 on t(x,y)
                                *
ERROR at line 1:
ORA-01408: such column list already indexed

ops$tkyte%ORA11GR2> create bitmap index t_idx2 on t(x,y) invisible;
create bitmap index t_idx2 on t(x,y) invisible
                                *
ERROR at line 1:
ORA-01408: such column list already indexed

as you can see - it just wasn't going to happen. But now in 12c - as long as only one index is "visible" - we can create multiple indexes:

ops$tkyte%ORA12CR1> create table t ( x int, y int, z int );
Table created.

ops$tkyte%ORA12CR1> create index t_idx on t(x,y);
Index created.

ops$tkyte%ORA12CR1> create bitmap index t_idx2 on t(x,y) invisible;
Index created.

Now that the second index is there, we can test it - to evaluate the performance for example - easily:

ops$tkyte%ORA12CR1> alter session set optimizer_use_invisible_indexes=true;

Session altered.

ops$tkyte%ORA12CR1> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> set autotrace traceonly explain
ops$tkyte%ORA12CR1> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1106681275

---------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |     0   (0)|
|   1 |  SORT AGGREGATE               |        |     1 |            |
|   2 |   BITMAP CONVERSION COUNT     |        |  1000K|            |
|   3 |    BITMAP INDEX FAST FULL SCAN| T_IDX2 |       |            |
---------------------------------------------------------------------

ops$tkyte%ORA12CR1> set autotrace off

if we deemed that index to be what we really wanted - we would just drop the old index and make this one visible, or set the old one invisible and set this one visible - publish it in effect.

See http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#FEATURENO09740 and especially http://richardfoote.wordpress.com/2013/07/02/12c-intro-to-multiple-indexes-on-same-column-list-repetition/ for more info. (and subscribe to Richard's blog if you aren't already - best index information out there)

12c - flashforward, flashback or see it as of now...

2013-07-03T07:04:00.001-04:00

Oracle 9i exposed flashback query to developers for the first time. The ability to flashback query dates back to version 4 however (it just wasn't exposed). Every time you run a query in Oracle it is in fact a flashback query - it is what multi-versioning is all about.

However, there was never a flashforward query (well, ok, the workspace manager has this capability - but with lots of extra baggage). We've never been able to ask a table "what will you look like tomorrow" - but now we do.

The capability is called Temporal Validity. If you have a table with data that is effective dated - has a "start date" and "end date" column in it - we can now query it using flashback query like syntax. The twist is - the date we "flashback" to can be in the future. It works by rewriting the query to transparently the necessary where clause and filter out the right rows for the right period of time - and since you can have records whose start date is in the future - you can query a table and see what it would look like at some future time.

Here is a quick example, we'll start with a table:

ops$tkyte%ORA12CR1> create table addresses
2 ( empno number,
3 addr_data varchar2(30),
4 start_date date,
5 end_date date,
6 period for valid(start_date,end_date)
7 )
8 /

Table created.

the new bit is on line 6 (it can be altered into an existing table - so any table you have with a start/end date column will be a candidate). The keyword is PERIOD, valid is an identifier I chose - it could have been foobar, valid just sounds nice in the query later. You identify the columns in your table - or we can create them for you if they don't exist. Then you just create some data:

ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )

2 values ( 1234, '123 Main Street', trunc(sysdate-5), trunc(sysdate-2) );

1 row created.

ops$tkyte%ORA12CR1>

ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )

2 values ( 1234, '456 Fleet Street', trunc(sysdate-1), trunc(sysdate+1) );

1 row created.

ops$tkyte%ORA12CR1>

ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )

2 values ( 1234, '789 1st Ave', trunc(sysdate+2), null );

1 row created.

and you can either see all of the data:

ops$tkyte%ORA12CR1> select * from addresses;

EMPNO ADDR_DATA START_DAT END_DATE

---------- ------------------------------ --------- ---------

1234 123 Main Street 27-JUN-13 30-JUN-13

1234 456 Fleet Street 01-JUL-13 03-JUL-13

1234 789 1st Ave 04-JUL-13

or query "as of" some point in time - as you can see in the predicate section - it is just doing a query rewrite to automate the "where" filters:

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate-3;

EMPNO ADDR_DATA START_DAT END_DATE

---------- ------------------------------ --------- ---------

1234 123 Main Street 27-JUN-13 30-JUN-13

ops$tkyte%ORA12CR1> @plan

ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID cthtvvm0dxvva, child number 0

-------------------------------------

select * from addresses as of period for valid sysdate-3

Plan hash value: 3184888728

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

|* 1 | TABLE ACCESS FULL| ADDRESSES | 1 | 48 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter((("T"."START_DATE" IS NULL OR

"T"."START_DATE"<=SYSDATE@!-3) AND ("T"."END_DATE" IS NULL OR

"T"."END_DATE">SYSDATE@!-3)))

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

24 rows selected.

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate;

EMPNO ADDR_DATA START_DAT END_DATE

---------- ------------------------------ --------- ---------

1234 456 Fleet Street 01-JUL-13 03-JUL-13

ops$tkyte%ORA12CR1> @plan

ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID 26ubyhw9hgk7z, child number 0

-------------------------------------

select * from addresses as of period for valid sysdate

Plan hash value: 3184888728

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

|* 1 | TABLE ACCESS FULL| ADDRESSES | 1 | 48 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter((("T"."START_DATE" IS NULL OR

"T"."START_DATE"<=SYSDATE@!) AND ("T"."END_DATE" IS NULL OR

"T"."END_DATE">SYSDATE@!)))

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

24 rows selected.

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate+3;

EMPNO ADDR_DATA START_DAT END_DATE

---------- ------------------------------ --------- ---------

1234 789 1st Ave 04-JUL-13

ops$tkyte%ORA12CR1> @plan

ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID 36bq7shnhc888, child number 0

-------------------------------------

select * from addresses as of period for valid sysdate+3

Plan hash value: 3184888728

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

|* 1 | TABLE ACCESS FULL| ADDRESSES | 1 | 48 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter((("T"."START_DATE" IS NULL OR

"T"."START_DATE"<=SYSDATE@!+3) AND ("T"."END_DATE" IS NULL OR

"T"."END_DATE">SYSDATE@!+3)))

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

24 rows selected.

All in all a nice, easy way to query effective dated information as of a point in time without a complex where clause. You need to maintain the data - it isn't that a delete will turn into an update the end dates a record or anything - but if you have tables with start/end dates, this will make it much easier to query them.

*Note added 4-jul-2013: this feature currently is not supported/working with the pluggable database infrastructure. This is a temporary limitation.

12c - Silly little trick with invisibility...

2013-07-02T14:03:00.004-04:00

This is interesting, if you hide and then unhide a column - it will end up at the "end" of the table. Consider:

ops$tkyte%ORA12CR1> create table t ( a int, b int, c int );
Table created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> desc t;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
A NUMBER(38)
B NUMBER(38)
C NUMBER(38)

ops$tkyte%ORA12CR1> alter table t modify (a invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (a visible);
Table altered.

ops$tkyte%ORA12CR1> desc t;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
B NUMBER(38)
C NUMBER(38)
A NUMBER(38)

Now, that means you can add a column or shuffle them around. What if we had just added A to the table and really really wanted A to be first. My first approach would be "that is what editioning views are great at". If I couldn't use an editioning view for whatever reason - we could shuffle the columns:

ops$tkyte%ORA12CR1> alter table t modify (b invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (c invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (b visible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (c visible);
Table altered.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> desc t;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
A NUMBER(38)
B NUMBER(38)
C NUMBER(38)

Note: that could cause some serious invalidations in your database - so make sure you are a) aware of that b) willing to pay that penalty and c) really really really want A to be first in the table!

12c - Invisible Columns...

2013-07-02T10:50:00.000-04:00

Remember when 11g first came out and we had "invisible indexes"? It seemed like a confusing feature - indexes that would be maintained by modifications (hence slowing them down), but would not be used by queries (hence never speeding them up). But - after you looked at them a while, you could see how they can be useful. For example - to add an index in a running production system, an index used by the next version of the code to be introduced later that week - but not tested against the queries in version one of the application in place now. We all know that when you add an index - one of three things can happen - a given query will go much faster, it won't affect a given query at all, or... It will make some untested query go much much slower than it used to. So - invisible indexes allowed us to modify the schema in a 'safe' manner - hiding the change until we were ready for it.

Invisible columns accomplish the same thing - the ability to introduce a change while minimizing any negative side effects of that change. Normally when you add a column to a table - any program with a SELECT * would start seeing that column, and programs with an INSERT INTO T VALUES (...) would pretty much immediately break (an INSERT without a list of columns in it). Now we can add a column to a table in an invisible fashion, the column will not show up in a DESCRIBE command in SQL*Plus, it will not be returned with a SELECT *, it will not be considered in an INSERT INTO T VALUES statement. It can be accessed by any query that asks for it, it can be populated by an INSERT statement that references it, but you won't see it otherwise.

For example, let's start with a simple two column table:

ops$tkyte%ORA12CR1> create table t
2 ( x int,
3 y int
4 )
5 /
Table created.

ops$tkyte%ORA12CR1> insert into t values ( 1, 2 );
1 row created.

Now, we will add an invisible column to it:

ops$tkyte%ORA12CR1> alter table t add

( z int INVISIBLE );

Table altered.

Notice that a DESCRIBE will not show us this column:

ops$tkyte%ORA12CR1> desc t

Name Null? Type

----------------- -------- ------------

X NUMBER(38)

Y NUMBER(38)

and existing inserts are unaffected by it:

ops$tkyte%ORA12CR1> insert into t values ( 3, 4 );

1 row created.

A SELECT * won't see it either:

ops$tkyte%ORA12CR1> select * from t;

X Y

---------- ----------

1 2

3 4

But we have full access to it (in well written programs! The ones that use a column list in the insert and select - never relying on "defaults":

ops$tkyte%ORA12CR1> insert into t (x,y,z)

values ( 5,6,7 );

1 row created.

ops$tkyte%ORA12CR1> select x, y, z from t;

X Y Z

---------- ---------- ----------

1 2

3 4

5 6 7

and when we are sure that we are ready to go with this column, we can just modify it:

ops$tkyte%ORA12CR1> alter table t modify z visible;

Table altered.

ops$tkyte%ORA12CR1> select * from t;

X Y Z

---------- ---------- ----------

1 2

3 4

5 6 7

I will say that a better approach to this - one that is available in 11gR2 and above - would be to use editioning views (part of Edition Based Redefinition - EBR ). I would rather use EBR over this approach, but in an environment where EBR is not being used, or the editioning views are not in place, this will achieve much the same.

Read these for information on EBR:

http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o10asktom-172777.html

http://www.oracle.com/technetwork/issue-archive/2010/10-mar/o20asktom-098897.html

http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30asktom-082672.html

12c - SQL Text Expansion

2013-07-01T09:27:00.000-04:00

Here is another small but very useful new feature in Oracle Database 12c - SQL Text Expansion. It will come in handy in two cases:

You are asked to tune what looks like a simple query - maybe a two table join with simple predicates. But it turns out the two tables are each views of views of views and so on... In other words, you've been asked to 'tune' a 15 page query, not a two liner.
You are asked to take a look at a query against tables with VPD (virtual private database) policies. In order words, you have no idea what you are trying to 'tune'.

A new function, EXPAND_SQL_TEXT, in the DBMS_UTILITY package makes seeing what the "real" SQL is quite easy. For example - take the common view ALL_USERS - we can now:

ops$tkyte%ORA12CR1> variable x clob

ops$tkyte%ORA12CR1> begin

2 dbms_utility.expand_sql_text

3 ( input_sql_text => 'select * from all_users',

4 output_sql_text => :x );

5 end;

6 /

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> print x

--------------------------------------------------------------------------------

SELECT "A1"."USERNAME" "USERNAME","A1"."USER_ID" "USER_ID","A1"."CREATED" "CREAT

ED","A1"."COMMON" "COMMON" FROM (SELECT "A4"."NAME" "USERNAME","A4"."USER#" "US

ER_ID","A4"."CTIME" "CREATED",DECODE(BITAND("A4"."SPARE1",128),128,'YES','NO') "

COMMON" FROM "SYS"."USER$" "A4","SYS"."TS$" "A3","SYS"."TS$" "A2" WHERE "A4"."DA

TATS#"="A3"."TS#" AND "A4"."TEMPTS#"="A2"."TS#" AND "A4"."TYPE#"=1) "A1"

Now it is easy to see what query is really being executed at runtime - regardless of how many views of views you might have. You can see the expanded text - and that will probably lead you to the conclusion that maybe that 27 table join to 25 tables you don't even care about might better be written as a two table join.

Further, if you've ever tried to figure out what a VPD policy might be doing to your SQL, you know it was hard to do at best. Christian Antognini wrote up a way to sort of see it - but you never get to see the entire SQL statement: http://www.antognini.ch/2010/02/tracing-vpd-predicates/. But now with this function - it becomes rather trivial to see the expanded SQL - after the VPD has been applied. We can see this by setting up a small table with a VPD policy

ops$tkyte%ORA12CR1> create table my_table

2 ( data varchar2(30),

3 OWNER varchar2(30) default USER

4 )

5 /

Table created.

ops$tkyte%ORA12CR1> create or replace

2 function my_security_function( p_schema in varchar2,

3 p_object in varchar2 )

4 return varchar2

5 as

6 begin

7 return 'owner = USER';

8 end;

9 /

Function created.

ops$tkyte%ORA12CR1> begin

2 dbms_rls.add_policy

3 ( object_schema => user,

4 object_name => 'MY_TABLE',

5 policy_name => 'MY_POLICY',

6 function_schema => user,

7 policy_function => 'My_Security_Function',

8 statement_types => 'select, insert, update, delete' ,

9 update_check => TRUE );

10 end;

11 /

PL/SQL procedure successfully completed.

And then expanding a query against it:

ops$tkyte%ORA12CR1> begin

2 dbms_utility.expand_sql_text

3 ( input_sql_text => 'select * from my_table',

4 output_sql_text => :x );

5 end;

6 /

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> print x

--------------------------------------------------------------------------------

SELECT "A1"."DATA" "DATA","A1"."OWNER" "OWNER" FROM (SELECT "A2"."DATA" "DATA",

"A2"."OWNER" "OWNER" FROM "OPS$TKYTE"."MY_TABLE" "A2" WHERE "A2"."OWNER"=USER@!)

"A1"

Not an earth shattering new feature - but extremely useful in certain cases. I know I'll be using it when someone asks me to look at a query that looks simple but has a twenty page plan associated with it!

12c - Utl_Call_Stack...

2013-06-28T11:37:00.002-04:00

Over the next couple of months, I'll be writing about some cool new little features of Oracle Database 12c - things that might not make the front page of Oracle.com. I'm going to start with a new package - UTL_CALL_STACK.

In the past, developers have had access to three functions to try to figure out "where the heck am I in my code", they were:

Now these routines, while useful, were of somewhat limited use. Let's look at the format_call_stack routine for a reason why. Here is a procedure that will just print out the current call stack for us:

ops$tkyte%ORA12CR1> create or replace

2 procedure Print_Call_Stack

3 is

4 begin

5 DBMS_Output.Put_Line(DBMS_Utility.Format_Call_Stack());

6 end;

7 /

Procedure created.

Now, if we have a package - with nested functions and even duplicated function names:

ops$tkyte%ORA12CR1> create or replace

2 package body Pkg is

3 procedure p

4 is

5 procedure q

6 is

7 procedure r

8 is

9 procedure p is

10 begin

11 Print_Call_Stack();

12 raise program_error;

13 end p;

14 begin

15 p();

16 end r;

17 begin

18 r();

19 end q;

20 begin

21 q();

22 end p;

23 end Pkg;

24 /

Package body created.

When we execute the procedure PKG.P - we'll see as a result:

ops$tkyte%ORA12CR1> exec pkg.p

----- PL/SQL Call Stack -----

object line object

handle number name

0x6e891528 4 procedure OPS$TKYTE.PRINT_CALL_STACK

0x6ec4a7c0 10 package body OPS$TKYTE.PKG

0x6ec4a7c0 14 package body OPS$TKYTE.PKG

0x6ec4a7c0 17 package body OPS$TKYTE.PKG

0x6ec4a7c0 20 package body OPS$TKYTE.PKG

0x76439070 1 anonymous block

BEGIN pkg.p; END;

ERROR at line 1:

ORA-06501: PL/SQL: program error

ORA-06512: at "OPS$TKYTE.PKG", line 11

ORA-06512: at "OPS$TKYTE.PKG", line 14

ORA-06512: at "OPS$TKYTE.PKG", line 17

ORA-06512: at "OPS$TKYTE.PKG", line 20

ORA-06512: at line 1

The bit in red above is the output from format_call_stack whereas the bit in black is the error message returned to the client application (it would also be available to you via the format_error_backtrace API call). As you can see - it contains useful information but to use it you would need to parse it - and that can be trickier than it seems. The format of those strings is not set in stone, they have changed over the years (I wrote the "who_am_i", "who_called_me" functions, I did that by parsing these strings - trust me, they change over time!).

Starting in 12c - we'll have structured access to the call stack and a series of API calls to interrogate this structure. I'm going to rewrite the print_call_stack function as follows:

ops$tkyte%ORA12CR1> create or replace

2 procedure Print_Call_Stack

3 as

4 Depth pls_integer := UTL_Call_Stack.Dynamic_Depth();

6 procedure headers

7 is

8 begin

9 dbms_output.put_line( 'Lexical Depth Line Name' );

10 dbms_output.put_line( 'Depth Number ' );

11 dbms_output.put_line( '------- ----- ---- ----' );

12 end headers;

13 procedure print

14 is

15 begin

16 headers;

17 for j in reverse 1..Depth loop

18 DBMS_Output.Put_Line(

19 rpad( utl_call_stack.lexical_depth(j), 10 ) ||

20 rpad( j, 7) ||

21 rpad( To_Char(UTL_Call_Stack.Unit_Line(j), '99'), 9 ) ||

22 UTL_Call_Stack.Concatenate_Subprogram

23 (UTL_Call_Stack.Subprogram(j)));

24 end loop;

25 end;

26 begin

27 print;

28 end;

29 /

Here we are able to figure out what 'depth' we are in the code (utl_call_stack.dynamic_depth) and then walk up the stack using a loop. We will print out the lexical_depth, along with the line number within the unit we were executing plus - the unit name. And not just any unit name, but the fully qualified, all of the way down to the subprogram name within a package. Not only that - but down to the subprogram name within a subprogram name within a subprogram name. For example - running the PKG.P procedure again results in:

ops$tkyte%ORA12CR1> exec pkg.p

Lexical Depth Line Name

Depth Number

------- ----- ---- ----

1 6 20 PKG.P

2 5 17 PKG.P.Q

3 4 14 PKG.P.Q.R

4 3 10 PKG.P.Q.R.P

0 2 26 PRINT_CALL_STACK

1 1 17 PRINT_CALL_STACK.PRINT

BEGIN pkg.p; END;

ERROR at line 1:

ORA-06501: PL/SQL: program error

ORA-06512: at "OPS$TKYTE.PKG", line 11

ORA-06512: at "OPS$TKYTE.PKG", line 14

ORA-06512: at "OPS$TKYTE.PKG", line 17

ORA-06512: at "OPS$TKYTE.PKG", line 20

ORA-06512: at line 1

This time - we get much more than just a line number and a package name as we did previously with format_call_stack. We not only got the line number and package (unit) name - we got the names of the subprograms - we can see that P called Q called R called P as nested subprograms. Also note that we can see a 'truer' calling level with the lexical depth, we can see we "stepped" out of the package to call print_call_stack and that in turn called another nested subprogram.

This new package will be a nice addition to everyone's error logging packages. Of course there are other functions in there to get owner names, the edition in effect when the code was executed and more. See UTL_CALL_STACK for all of the details.

Raw Devices...

2013-05-08T14:26:00.002-04:00

A quick request...

If you are using RAW devices for your database (no ASM, no filesystem - just 'raw') - please drop me a line and let me know. Include the size of your database as well please.

thanks!

You can't trust everything you see...

2013-01-18T10:28:00.001-05:00

A few years ago, I wrote a book - Effective Oracle by Design. Oracle Press really wanted my photo on the book and I really didn't want it. So they went around me and asked Oracle Magazine for the head shot they had and were using for the print column back then. Unbeknownst to Oracle Press - that head shot was a photoshop job. It was my head, someone else's body. I never did find out whose body it was - but it was definitely not me. If you have the book and look really close - you can see the line on my neck where they slid my head on.

I just got the latest issue of Oracle Magazine and lo and behold - they've done it again. My column has a new picture of me on the intro page, one that I've never seen before - but only because it never happened in real lift. Again - it is my head from a shot I recognize, but on someone else's body - not just neck and shoulders this time - but the full body!

This time - I know where the body came from. In an effort to make people as unproductive as possible - I'm going to offer up a challenge. If you can point me to the source of the body shot on the internet, I'll send you a copy of Expert Oracle Database Architecture - 3rd edition when it comes out (which will be a while, sometime in 2013 - I'm not starting the edits on it until the next release of the database is official).

In order to play - supply a link to the source body here in the comments. Make sure to use an identifiable username (do not use anonymous) or identify yourself in some way in the comment. If you are the first to get it right - I'll post a comment myself stating so and ask you to email me so I can get your contact information (so you'll have to check back every now and then). When the book is printed, I'll send you a signed copy. table 42 :))))))

The keys to Oracle…

2012-08-17T17:35:00.003-04:00

This is a question I get on asktom frequently – what are the things I need to know, what do I have to do to become expert, where is the list of key things I need to do with regards to Oracle. It is a hard sounding question that has an easy answer.

If you are still wet behind the ears – or even just damp – then the definitive place to start is the Oracle Server Concepts Guide. Not only is this free - but it is your guide to understanding how the database works. If you understand something - you can use that thing. If you don't, well, simply put - you won't be able to effectively use it. Getting a solid knowledge of how a transaction is done, what locking and concurrency controls are and how they work, how the database makes the data durable on disk (redo, undo management) - all of it is key to effectively using the database.

If you don't know who can see what data, which version of data, at what points in time - confusion will abound and data integrity will be lost. Getting a good basic knowledge of how the database manages data is the only way for you to know how to code a correct program.

Once you've mastered the material in that document - I would suggest taking a look at the 2-Day guides. They are a good way to get spun up on the necessary knowledge for either a Developer or DBA track. There is the 2-Day Developers Guide and the 2-Day DBA Guide. These documents are designed to get you going - you won't be an expert after reading them, but you'll know the gist of what it is you need to know, to learn. You'll have a good idea of what is available feature wise at the very least.

After these 2-Day guides (you can read either one or both), you might want to move onto some of the other overview guides - the 2-Day Performance would be a must read for anyone (followed by the Performance Guide itself eventually).

While all of this is going on - you should also be communicating - a lot. Constantly. Get on the forums - http://otn.oracle.com/forums, start writing - start asking questions (after reading the guidelines!!), start participating. It is the way I learned much of what I know now myself. But communicate - a lot. You won't be able to progress forward in a vacuum. Share everything you have in your head - and find others that will share back with you. You'll find you not only get to learn (and to teach eventually) from people all over the world - but you may end up calling some of them your really good friends.

And then add time, a lot of time. Maybe years of time. You will not become really good at what you are doing in six months, not in a year, probably not in five years. It will take a while - you need to have lots of different experiences, encounter many different situations, attempt and fail to solve many problems - before you'll become really good at what you do. You might feel like you are expert in a year - but trust me - you aren't. And four years later you'll realize how little you knew and how much more you have to learn.

I learn something new everyday about Oracle, just the other day something about Total Recall but many things in general, technical and otherwise - and I've been doing Oracle things for a long time.
:)))))))))))))

Coming soon to Ireland and the Netherlands...

2012-08-13T14:15:00.000-04:00

In September I'll be delivering a free Seminar in Dublin Ireland from 9am till 2:30pm on September 19th at the Gibson Hotel. It is being delivered in conjunction with the Ireland OUG and anyone (even non-members) may attend. Food is involved so it is a real deal :) I'll be talking about big data, statistics and managing large sets of data. We'll close up with a Q&A session before lunch. Full details on registering and the agenda may be found here. Also see the Ireland Oracle User Group page.

After that, on September 20th, I'll be delivering a full day seminar with Oracle University in Utrecht in the Netherlands. Full details - including the agenda - may be found here.

Hope to see you at one of the events!
:))))))

What I learned new about Total Recall...

2012-08-09T08:26:00.003-04:00

Today I learned something new about the Oracle Total Recall option from a good friend (they were very happy to have told me something about Oracle I didn't know :) ) - it doesn't exist anymore! The Total Recall option has been made part of the Advanced Compression option!!

Check this out http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#CJACCDBA to see what you get with the Advanced Compression option which now includes the Total Recall option as well - the Flashback Data Archive capability.
:)))))))))))

Latin America OTN Tour....

2012-08-06T14:15:00.002-04:00

Just starting the Latin American OTN tour for the week here in Uruguay.

This is my first time in Uruguay and I spent the weekend in Montevideo. It was a wet, damp weekend - very foggy too! But on the plus side - the food is excellent and plentiful. The local user group leaders here made sure we were well fed and warm for lunch every day :)

Today is the OTN conference - Dimitri Gielis is presenting right now using a "slide free" presentation. He is building an APEX application to the specification of the audience, in real time. A pretty neat concept, I like it, very good way to show the ability of the tool:

Tomorrow Andrew Holdsworth, Graham Wood and I are doing a Real World Performance day here before we are off to Argentina for a repeat. To get to Argentina, we are going to a three hour ferry ride. It is supposed to rain - so that will be a damp, wet trip for sure.

After Argentina we'll be off for Sao Paulo Brazil for a repeat on Saturday (yes, Saturday!).

If you are in the area, you can find the details here: http://www.oracle.com/technetwork/es/community/user-groups/otn-latinoamerica-tour-2012-1634120-esa.html or on http://asktom.oracle.com. Hope to see you here!

Now I'm off to answer some questions that I have in my queue...
:)))))))))))

East Coast Oracle Users Group Conference...

2012-06-16T03:39:00.001-04:00

I'll be speaking at the East Coast Oracle Users Group Conference held in North Carolina on October 16th-18th. It is always nice to be able to stay in my own timezone for a change!

There are opportunities to not only attend the conference but also to speak - they are still accepting abstracts for presentations until June 21st. If you have done anything interesting (you have), please consider speaking out about it. Others will be interested. You never know how it will go unless you try!

Hope to see you there!

Doctors...

2012-05-27T11:38:00.001-04:00

This recent blog post by Seth Godin reminded me a lot of my introduction to Effective Oracle by Design of a few years ago. What was true then is still so true today...

Here is an excerpt from my book that mirrors what he just wrote:

I will use yet another analogy to describe how this book will present information. Pretend for a moment that the developer is instead a medical doctor and the application is the patient. There are many types of MD’s:

The emergency room (ER) doctor. They do “triage” – separating the hopeless from the ones that can be helped. Performing quick fixes to keep patients alive for as long as possible. They strive for short term band-aids to fix up the patient. They will take a patient with a heart attack induced by smoking, bad diet and no exercise and get them stabilized.

The operating room (OR) doctor. They get the patient after the ER doctor has triaged them and patched them up. They strive for long term fixes to keep the patient not only alive but as fully functioning as possible. They perform the by-pass operation on that heart attack attempting to clear the arteries.

The physical therapist (PT). They get the patient after the operating room doctor is finished and begin a long and painful (not to mention expensive) process of rehabilitation.

The preventative medicine doctor. They strive to avoid the above three doctors at all costs. They counsel the patient to quit smoking, eat a healthy diet, and exercise – developing a phased plan to get them in shape. If they do their job right – with the exception of unfortunate accidents (like a car accident), the patient will never see the ER, OR or PT doctors.

Now, the world needs all types of doctors – accidents do happen after all. But one of the most important types of doctors is that last one, the preventative medicine doctor. The one that tries hard to avoid having their patient need the other three.

It is my belief (experience) that most people and books approach tuning using the mindset of the first three doctor types above. They are in support of the hero developer; e.g. the ER or OR doctor. Perhaps that is partially due to my observation that pre-emptive good design and implementation is mostly a thankless exercise. These developers seem to get all of the fame as they snatch the patient from the grasp of death (save the system by doing something miraculous). They get called in at the last moment; work horribly hard for an extended period of time trying to keep the patient alive (and get paid handsomely as well). The physical therapists are the unlucky souls that get the system after the ER/OR doctor has patched it up. They are the ones responsible for keeping this system going.

I feel I am well equipped to speak from that perspective. I am in fact one of those “heroes”. I am called in to “lay hands on” systems and make them better. I could write that book, I’ve been told I should write that book – but I won’t.

What is missing is the comprehensive approach that includes the preventative medicine doctor training. There are some out there – my favorites being Guy Harrison’s developer book, as well as Jonathan Lewis’s DBA book. These books, including my own “Expert One on One Oracle” work to remove the need for the hero. Remember – firefighters are heroes when they do their job, we all just hope wenever need them personally!

When is a foreign key not a foreign key...

2012-05-23T16:57:00.003-04:00

I learn or relearn something new every day about Oracle. Just about every day really!

Last week I was in Belgrade Serbia delivering a seminar and an attendee reminded me of something I knew once but had totally forgotten about. It had to do with foreign keys and the dreaded NULL value.

Many of you might think the following to be not possible, we'll start with the tables:

ops$tkyte%ORA11GR2> create table p
2 ( x int,
3 y int,
4 z int,
5 constraint p_pk primary key(x,y)
6 )
7 /
Table created.

ops$tkyte%ORA11GR2> create table c
2 ( x int,
3 y int,
4 z int,
5 constraint c_fk_p foreign key (x,y) references p(x,y)
6 )
7 /
Table created.

Looks like a normal parent child relationship - a row may exist in C if and only if a parent row exists in P. If that is true - then how can this happen:

ops$tkyte%ORA11GR2> select count( x||y ) from p;

COUNT(X||Y)

-----------

ops$tkyte%ORA11GR2> select count( x||y ) from c;

COUNT(X||Y)

-----------

There are zero records in P - none. There is at least one record in C and that record has a non-null foreign key. What is happening?

It has to do with NULLs and foreign keys and the default "MATCH NONE" rule in place. If your foreign key allows NULLs and your foreign key is a composite key - then you must be careful of the condition where by only SOME of the foreign key attributes are not null. For example - to achieve the above magic, I inserted:

ops$tkyte%ORA11GR2> insert into c values ( 1, null, 0 );

1 row created.

The database cannot validate a foreign key when it is partially null. In order to enforce the "MATCH FULL" option of a foreign key - you would want to add a constraint to your table:

ops$tkyte%ORA11GR2> alter table c add constraint check_nullness

2 check ( ( x is not null and y is not null ) or

3 ( x is null and y is null ) )

4 /

Table altered.

That will ensure either:

All of the columns are NULL in the foreign key
None of the columns are NULL in the foreign key

As long as that constraint is in place - your foreign key will work as you probably think it should work.

UKOUG 2012...

2012-05-15T12:14:00.000-04:00

The call for papers for the UKOUG 2012 conference ends in less than three short weeks! If you were planning on going to the conference (and even if not) - you should consider submitting a paper.

I've been a long time supporter of all of the user groups and their conferences and I can attest to the quality of the UKOUG event. The conference is chock full of technical talks with hundreds of sessions to choose from. There is something for everyone there.

If you've never presented before, don't let that deter you from submitting a paper. No one knows the anxiety that public speaking can bring better than I - I've written about it before. You'll find the conference to be an entirely different experience on the other side of the podium. In addition to the experience of presenting, the networking and exposure that comes with being a speaker won't hurt you at all. Whether you are a DBA or developer - having good public speaking skills is a necessity today - and using the conference as a way to build those skills is a great way to start.

Additionally - what you have to say is important and relevant to the user community as a whole. A good conference needs a lot of speakers, from many diverse disciplines, with diverse backgrounds - the more speakers the merrier. Don't think you don't have anything to offer - everyone does. And don't feel that your topic wouldn't be interesting to someone else - it will be. There are a lot of people out there trying to do some of the same things you've done and they'd love to hear how you did it.

That is one of the things about user groups I really like - they bring together a lot of people doing similar things - but in a different way. You'll learn something new - and they will too.

The UKOUG is one of the larger and well run conferences out there - don't be afraid to talk. Challenge yourself to get up there and just do it. You won't be sorry (ok, maybe in the minutes leading up to it you will be - but you'll get over that :) )

Hope to see you there - and don't chicken out!