Thanksgiving Thoughts

Today is Thanksgiving Day in the US. For those who are not familiar with American holidays and traditions, Thanksgiving is a really tribute to cooperation and camaraderie. There are many theories and folklore surrounding the concept; but I think one trumps over the rest. More than four scores ago when the pilgrims from England set foot in North America and decided to call it their home, they didn't encounter a Welcome to America sign at JFK airport. Harsh New England weather was just one of the many sobering reminders of the fate of things to come for the visitors. The pilgrims didn't have a Walmart and a credit card to swipe for potatoes and cereal; they had to grow their own food. Well, they did that in their own country; but they had no idea how to do that in this strange land.

Presentations at Philadelphia Area Oracle User Group 2012

Thank you all those who came to my sessions at PHLOUG in November. You can download the presentations and the demo scripts I used here.

OOW12: Beginning Performance Tuning

Thank you very much for coming to my session "Beginning Performance Tuning" on the #IOUG track at #OOW12 Oracle Open World 2012. It makes the day for any speaker to see the room filled to capacity even at 9 AM on a Sunday morning. Much, much appreciated.

Here are the slides and the scripts I used in the demos. Please feel free to reuse the slides and scripts for for any purpose. All I ask is to give due credit to me. I imply no warranty and support for the materials. Use your discretion while using.

Quiz: Mystery of Create Table Statement

Happy Friday! I thought I would jumpstart your creative juices with this little, really simple quiz. While it's trivial, it may not be that obvious to many. See if you can catch it. Time yourself exactly 1 minute to get the answer. Tweet answer to me @arupnanda

Here it goes. Database is 11.2.0.3. Tool is SQL*Plus.

The user ARUP owns a procedure that accepts an input string and executes it. Here is the procedure.

create or replace procedure manipulate_arup_schema
(
        p_input_string  varchar2
)
is
begin
        execute immediate p_input_string;
end;
/

The user ARUP have granted EXECUTE privileges on this to user SCOTT. The idea is simple: SCOTT can create and drop tables and other objects in ARUP's schema without requiring the dangerous create any table system privilege.

With this, SCOTT tries to create a table in the ARUP schema:

SQL> exec arup.manipulate_arup_schema ('create table abc (col1 number)')

PL/SQL procedure successfully completed.

The table creation was successul. Now SCOTT tries to create the table in a slightly different manner:

SQL> exec arup.manipulate_arup_schema ('create table abc1 as select * from dual');

It fails with an error:

BEGIN bus_schema.manipulate_bus_schema ('create table abc as select * from dual'); END;

*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at "ARUP.MANIPULATE_ARUP_SCHEMA", line 18
ORA-06512: at line 1

Huh? After checking you did confirm that the user indeed doesn't have the quota on tablespace USERS, so the error is genuine; but how did the first table creation command go through successfully?

Tweet me the answer @arupnanda. Aren't on Twitter? Just post the answer here as a comment. I will post the answer right here in the evening. Let's see who posts the first answer. It shouldn't take more than 5 minutes to get the answer.

Have fun.

Update at the end of the Day. Here is the answer:

What to Learn from LinkedIn Password Hack as an Oracle DBA

One of the major news today was the hacking and resultant publishing of passwords in LinkedIn. Didn't hear about it? Well, read it here. In summary, someone smart but with head screwed a little askew decided to pull passwords from LinkedIn account using a little known flaw in the LinkedIn iOS app. LinkedIn later confirmed that leak and asked users to change the password. This created a major ripple effect all over the world. The news competed for attention with others such as Spain's economic reforms; but in the end it managed to rise to the top since many professionals and executives are members of the LinkedIn site and were affected.

Well, what is that to do with being an Oracle DBA - you may ask. Fair question. You see, there is a very important lesson to be learned here from this incident - a lesson commonly ignored by many DBAs, developers, architects and pretty much all users of an Oracle database. Let's see what that is.

Collaborate 2012 Sessions and Select Article

Thank you all who came to my sessions at #IOUG Collaborate 2012 #C12LV on April 22-24 in Las Vegas. I had four full sessions, two panels and one bootcamp. Quite a busy schedule, as you can see. I also worked on some urgent performance issues at work during the week.

You can download the the slides and scripts here. They are available from the IOUG site but I thought I would put them for download here as well.

• Rise of the Machines slides
• Secure Your Database in a Single Day slides scripts
• Beginning Oracle Performance Tuning slides scripts
• How Oracle Locking Works slides

My article Cache Fusion Demystified on SELECT Journal won the Editor's Choice award for 2011. This article is available for free download here.

I hope you like them. As always, feedback - good, bad and something in between - will be appreciated.

Update as of April 29th, 2012

I usually add more stuff to my slides after the session is over. This may result from a direct attendee feedback that some content was not clear, inadequate, misleading or even incorrect. This time is no exception. I added some more content to make the concepts clearer. Also many thanks to Charles Hooper for pointing out an Oracle documentation bug which makes two scripts in my session incorrect. I have since corrected the slides and re-uploaded. Please redownload the slides for How Oracle Locking Works and specifically look at the slides 14, 18, 24, 25 and 26. Also please download the scripts for Beginning Oracle Performance Tuning. More specifically, the script lock1.sql has changed.

The Arup Nanda Blog: AIOUG Webcast:Methodical Performance Tuning Part 2

The Arup Nanda Blog: AIOUG Webcast:Methodical Performance Tuning Part 2

AIOUG Webcast:Methodical Performance Tuning Part 2

Thank you all for attending the Part 2 of the Methodical Performance Tuning series. I hope you got something out of the 1 hour long session. You can download the slides and the scripts I used during the demo here.

As always, I will appreciate any feedback which helps me in designing future content.