Database Reference
In-Depth Information
Solving Problems Simply
There are always two ways to solve everything: the easy way and the hard way. Time and time again, I see people
choosing the hard way. It is not always done consciously. More often, it is done out of ignorance. They never expected
the database to be able to do “that.” I, on the other hand, expect the database to be capable of anything and only do it
the hard way (by writing it myself ) when I discover it can't do something.
For example, I am frequently asked, “How can I make sure the end user has only one session in the database?”
(There are hundreds of other examples I could have used here). This must be a requirement of many applications, but
none I've ever worked on—I've not found a good reason for limiting people in this way. However, people want to do it
and when they do, they usually do it the hard way. For example, they will have a batch job run by the operating system
that will look at the V$SESSION table and arbitrarily kill sessions of users who have more than one session. Alternatively,
they will create their own tables and have the application insert a row when a user logs in and remove the row when they
log out. This implementation invariably leads to lots of calls to the help desk because when the application crashes, the
row never gets removed. I've seen lots of other “creative” ways to do this, but none is as easy as this:
EODA@ORA12CR1> create profile one_session limit sessions_per_user 1;
Profile created.
EODA@ORA12CR1> alter user scott profile one_session;
User altered.
EODA@ORA12CR1> alter system set resource_limit=true;
System altered.
Now we'll try to connect to SCOTT twice; the second attempt should fail:
EODA@ORA12CR1> connect scott/tiger
Connected.
SCOTT@ORA12CR1> host sqlplus scott/tiger
SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 14 11:12:04 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
That's it—now any user with the ONE_SESSION profile can log on only once. When I bring up this solution, I can
usually hear the smacking of a hand on the forehead followed by the statement “I never knew it could do that.” Taking
the time to familiarize yourself with what the tools you have to work with are capable of doing can save you lots of time
and energy in your development efforts.
The same “keep it simple” argument applies at the broader architecture level. I would urge people to think
carefully before adopting very complex implementations. The more moving parts you have in your system, the more
things you have that can go wrong, and tracking down exactly where that error is occurring in an overly complex
architecture is not easy. It may be really “cool” to implement using umpteen tiers, but it's not the right choice if a
simple stored procedure can do it better, faster, and with less resources.
I've seen projects where application development has been going on for months, with no end in sight. The
developers are using the latest and greatest technologies and languages, but development is not going very fast.
It wasn't that big of an application—and perhaps that was the problem. If you are building a doghouse (a small
woodworking job), you wouldn't bring in the heavy machinery. You'd use a few small power tools, but you wouldn't
have any use for the “big stuff.” On the other hand, if you were building an apartment complex, you'd have a cast of
hundreds working on the project, you'd have the big machines—you'd use totally different tools to approach this
problem. The same is true of application development. There is not a single “perfect architecture.” There is not a
single “perfect language.” There is not one single “perfect approach.”
 
Search WWH ::




Custom Search