A System for Oracle Users and Privileges with Automatic Expiry Dates

Tired of tracking down all the users in the database to deactivate them when they cease to exist, or change roles, or fulfill their temporary need to the database? Or, tracking down privileges you granted to existing users at the end of their requested period? The solution is to think out of the box - developing a system that allows you to create a database user account with an expiration date. This fire-and-forget method allows you to create users with the assurance that they will be expired (locked or dropped) at the expiration date automatically, without your intervention. Interested? Read on how I developed such a system--along with source code for you to try.

My #OOW13 Session: Are Indexes Unnecessary in Exadata

Thanks to all those who came to my session "Are Indexes Unnecessary in Exadata" at Oracle Open World 2013. Considering it was late afternoon of the last day of the conference, especially after the appreciation concert, it was a pleasure to see a packed room. Judging by the interaction I had with the attendees afterwards, it was clear that the crowd was pretty serious about this topic. Thanks you very much. There is nothing more a speaker can ask for, at #oow13 or anywhere.

Here is the presentation material, if you want, in PDF format. Please feel to download and as always, your comments will be highly valuable. I will make it a blog post later.

Last Successful Login Time in SQL*Plus in Oracle 12c

If you have been working with Oracle 12c, you may have missed a little something that appeared without mush fanfare but has some powerful implications. Let's see it with a small example--connecting with SQL*Plus.

C:\> sqlplus arup/arup

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 19 14:17:45 2013

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

Last Successful login time: Mon Aug 19 2013 14:13:33 -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

SQL>

Did you note the line in red above?

Last Successful login time: Mon Aug 19 2013 14:13:33 -04:00

That line shows you when you last logged in successfully. The purpose of that little output is to alert you about the last time you (the user ARUP) logged in, very similar to the message you get after logging in to a unix session. If you didn't login earlier, this message will alert you for possible compromise of your account.

Suppression

What if you don't want to show this timestamp?

C:\> sqlplus -nologintime arup/arup

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 19 14:23:25 2013

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

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

The login time has been suppressed, going back to the old behavior.

Scanned Copy of Oracle Magazine Article on Oracle 12c Multitenant

My article on Oracle Database 12c Multitenant feature (aka Pluggable Database) has been finally published, after being in the cold storage for about 6 months. You can read it on Oracle Magazine online, which is optimized for a web-presentation.

Here is a scanned copy of the article from the actual magazine. It may be easier to read. Hope you like it.

New York Oracle User Group Fall Conference Materials

Thank you all who attended my sessions at NYOUG Fall Conference this morning. I appreciate spending you most precious commodity - your time - with me. I sincerely hope you found both the presentations enlightening as well as entertaining.

Please see the details of the sessions below along with the download links.

Keynote: Oracle 12c Gee Whiz Features

Yet another Oracle version is out and so are about 1500 new features in a variety of areas. Some are well marketed (e.g. pluggable database or the multitenant option) and some shine by their sheer usefulness. And there are some that do not get a whole lot of coverage but are are hidden gems. In this session you learned 12 broad areas of Oracle Database 12c I feel are worth learning about to make your job as a DBA or developer better, easier, smoother and, in some cases, even make it possible what was hitherto impossible or impractical.

Download slides here and scripts here.

Big Data for Oracle DBAs

Have you ever considered the impact of Big Data on your career as database professionals? Or do you feel frustrated by the lack of a coherent set of explanations of various concepts like Hadoop, Hive, HDFS and Pig Latin - the essential vocabulary of  Big Data? If you did, then session was for you. In this, I explained the various concepts of Big Data - Hadoop, Hive, HDFS, etc. and explained how they relate to and contrast with modern relational database concepts.

Download the slides here.

OSWOUG Conference

Thank you very much who all attended my day long seminars in Portland, OR and Seattle, WA for Oregon and Southern Washington Oracle User Groups (OSWOUG). Listening to one speaker for 5 hours definitely was not easy. I understand that and appreciate your gesture.

Attached please find the various slides and scripts I used in the demo (Remember: this is a 2 MB file).

http://www.proligence.com/pres/oswoug13/oswoug13.zip

In addition, please read my following blog posts I referenced during my talks.

http://arup.blogspot.com/2011/01/how-oracle-locking-works.html
http://arup.blogspot.com/2010/12/100-things-you-probably-didnt-know.html
http://arup.blogspot.com/2011/07/who-manages-exadata-machine.html

Hope you found the sessions worthwhile, educational and entertaining.

Primary Keys Guarantee Uniqueness? Think Again.

When you create a table with a primary key or a unique constraint, Oracle automatically creates a unique index, to ensure that the column does not contain a duplicate value on that column; or so you have been told. It must be true because that is the fundamental tenet of an Oracle database, or for that matter, any database.

Well, the other day I was checking a table. There is a primary key on the column PriKey. Here are the rows:

Select PriKey from TableName;

PriKey
------
1
1

I got two rows with the same value. The table does have a primary key on this column and it is enforced. I can test it by inserting another record with the same value - “1”:

SQL> insert into TableName values (1,…)

It errors with ORA-00001: unique constraint violated error. The question is: why there are two rows with duplicate values in a column that has an enforced primary key, and it refuses to accept the very value that is already violated the primary key? It could be a great interview question, test your mettle; or just entertaining. Read on for the answer.

Demystifying Big Data for Oracle Professionals

Ever wonder about Big Data and what exactly it means, especially if you are already an Oracle Database professional? Or, do you get lost in the jargon warfare that spews out terms like Hadoop, Map/Reduce and HDFS? In this post I will attempt to explain these terms from the perspective of a traditional database practitioner but getting wet on the Big Data issues by being thrown in the water. I was inspired to write this from the recent incident involving NSA scooping up Verizon call data invading privacy of the citizens.

Streams Pool is only for Streams? Think Again!

If you don’t use the automatic SGA (i.e. set the sga_target=0) - something I frequently do - and don’t use Streams, you probably have set the parameter streams_pool_size to 0 or not set it at all, since you reckon that the pool is used for Streams alone and therefore would be irrelevant in your environment wasting memory.

But did you know that the Streams Pool is not just for Streams and it is used for other tools some of which are frequently used in almost any database environment? Take for instance, Data Pump. It uses Streams Pool, contrary to conventional wisdom. If Streams Pool is not defined, it is dynamically allocated by stealing that much memory from the buffer cache. And the size is not reset back to zero after the demand for the pool is over. You should be aware of this lesser known fact as it reduces the buffer cache you had allocated to the instance earlier.

Application Design is the only Reason for Deadlocks? Think Again

[Updated on 4/20/2013 after feedback from Charles Hooper, Jonathan Lewis, Laurent Schneider and Mohamed Houri and with some minor cosmetic enhancements of outputs]

Have you ever seen a message “ORA-00060: Deadlock detected” and automatically assumed that it was an application coding issue? Well, it may not be. There are DBA-related issues and you may be surprised to find out that INSERTs may cause deadlock. Learn all the conditions that precipitate this error, how to read the "deadlock graph" to determine the cause, and most important: how to avoid it.

Exadata Article as NYOUG's Article of the Year 2012

The Editors of New York Oracle User Group (NYOUG) publication - TechJournal - chose my article Exadata Demystified as the Article of the Year. Here is the snippet from the Editorial:

Switching Back to Regular Listener Log Format

Did you ever miss the older listener log file format and want to turn off the ADR-style log introduced in 11g? Well, it's really very simple.

My Sessions in RMOUG 2013

Many thanks for attending my sessions (I had a whopping four of them!) ar Rocky Mountain Oracle User Group Training Days 2013. I was pleasantly surprised to find some attending all four. Thank you. You all made my day.

Here you can download the slides and the demo scripts I used. Sorry about the delay. I didn't get a chance to post these earlier.

Boston DBA SIG Feb 6, 2013 Meeting Materials

Thank you all for coming to my session - Exadata from Beginner to Advanced in 3 Hours - on an evening in the dead of winter and staying for 4 hours. It was an amazing experience for me to see the sheer volume of interaction, which can only mean the depth of engagement of the attendees - a dream for any speaker. Thank you.

Here is the deck for the session which contains the scripts I used in the demo. Hope you enjoy them. As always, I will appreciate any feedback - good, ugly and anything in between.

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.