Oracle Beginnings - Where to Start?

Scalli0n asks: "I'm a programmer with a solid computer science background, but I'd like to know where I should start with the behemoth that is Oracle - my bosses tell me that I need to learn it to store geodatabases (since I work with geospatial intelligence) and I have no clue where to start since nobody can even tell me of a good beginners book - any suggestions?"

Comments

oracle classes

[Bluntzilla]

i would suggest seeing if your companmy will send you to a user confrence or some classes for a couple g's

Re:Oracle is overrated

[mrobinso]

If he wants to be taken seriously and he has a solid CompSci background, why is he asking slashdot?

RTFM.

MySQL speaks OpenGIS

[jamie]

Just checking, your bosses know MySQL supports OpenGIS, right?

Re:MySQL speaks OpenGIS

[treerex]

Just checking, but does it really matter? He asked a question, and I doubt he has the ability to come in and say, "Hey, use this open source database instead." Sometimes people need to use closed source software, and have to learn how to use it. Answer his question or keep your mouth shut.

Re:MySQL speaks OpenGIS

[jbplou]

He asked for help with Oracle, because apparently that is what his organization wants him to use. He didn't ask for OSS alternatives to Oracle. Which by the way Oracle still is far and away a better rdms than MySQL.

Re:MySQL speaks OpenGIS

[io-waiter]

Because the main player in the GIS arena has oracle as its main platform.

Re:MySQL speaks OpenGIS

[treerex]

I'm not intolerant to free thinking. I'm simply sick and tired of people responding to a valid question with the MySQL_is_the_answer_to_all_problems mantra. It's a useless response. FWIW, I use MySQL and PostgreSQL on a daily basis, and haven't touched Oracle in years. When you can develop a system from scratch you have a choice in the tools you use. When you join an existing team, you don't.

Tried Amazon?

[mrobinso]

Here you go:

Oracle Beginner's Guides

Where to start? Where are you starting?

[Praetorian42]

This is another one of those classic "We need more Info" Ask Slashdot's.

Do you mean that you don't even know SQL, and you need to start from the ground up for everything? Do you know SQL and have a decent background in MySQL? How about Postgres? I'm going to assume that you have some SQL knowledge, it's just you need to know about Oracle itself.

Learning Oracle is no different than any other technology, really. When I started my current job a little over a year ago, I had no clue about Oracle, I just discovered new features as I needed them. I was in a shared office with 2 oracle DBAs, and so there was quite a bit of communal learning there. Anyways, one of our DBAs left last month, and I've since been "promoted" to programmer/DBA. Oracle isn't really so much a behemoth (I learned enough to become a half-DBA in about a year), but it does have quite a few quirks that can leave your head scratching if you're used to some other RDBMS's.

First, if you're going to do anything interesting, you are (unfortunately) going to have to learn PL/SQL. PL/SQL is a big pain in the ass. Dynamic SQL is a pretty powerful tool (Feature?), but the way it is implemented with PL/SQL and Oracle is clumsy at best, and debugging is nearly impossible by any conventional means. Implementation for triggers and stored procedures is a bit more straightforward once you get used to the syntax.

Triggers are pretty important- if you're used to auto_increment fields in MySQL, in order to do it with Oracle (at least 9i.. I don't know much about 10g), you need to create a sequence, and then a trigger which will pull the next value out of the sequence and put it into the field (if it's null). While you have much more flexibility this way, it takes about 5 minutes to do something which would take a half-second in MySQL. That's pretty much the essence of Oracle, really- it's very flexible and powerful, but kind of a pain in the ass to use.

But once you get used to it, it's not all that bad. Oracle has some pretty impressive query/table optimization tools, and if you spend the time on it, you can get most everything running incredibly fast.

Oh, and if you run windows, get TOAD (or TOra for *nix), and it will help you cheat with most of this stuff. For TOAD at least, if you do anything the WYSIWYG way, you can always look at the SQL to see what the hell it is actually doing, which is a pretty decent way to learn.

Re:Where to start? Where are you starting?

[Anonymous Coward]

The parent post exemplifies the problem with asking for technical advice on slashdot. It's mostly on target but contains just enough misinformation (for example, the part about using triggers to emulate MySQL's auto_increment [you definitely don't want to use a trigger for that]) to be dangerous. Instead of listening to people on slashdot, pick up some of the better Oracle books:

Effective Oracle by Design

Expert Oracle

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

If, as the parent poster inquired, you don't even know SQL or basic relational theory, you'll need a much more fundamental textbook--but since you mentioned Oracle specifically, I'm assuming you're smart enough to know the difference between asking about relational databases in general and a specific implementation of one.

Re:Where to start? Where are you starting?

[MagicM]

Instead of Toad, I would recommend PL/SQL Developer.

Re:Where to start? Where are you starting?

[java.bean]

Triggers are pretty important- if you're used to auto_increment fields in MySQL, in order to do it with Oracle (at least 9i.. I don't know much about 10g), you need to create a sequence, and then a trigger which will pull the next value out of the sequence and put it into the field (if it's null). While you have much more flexibility this way, it takes about 5 minutes to do something which would take a half-second in MySQL.

You do not need to use a trigger for this, although you do need to use a sequence. The proper way of doing this, if you have a table with for example an employee id and name, is:

insert into employees (id, name) values (sequence.nextval, 'the name')

Re:Where to start? Where are you starting?

[abulafia]

That's pretty much the essence of Oracle, really- it's very flexible and powerful, but kind of a pain in the ass to use.

This is the best summary of Oracle ever. Very true.

I've been working with Oracle on and off since v.7.3 (ca. 1996 or so). Which is sort of a worst-case - if you use it quite a bit, you start to feel comfortable, then leave it alone for a while and forget all the dumb little workarounds and tricks.

For whatever it is worth, my advice is:

• Start keeping a directory of files with snippets of plsql/sql/series of steps of how you did something. If you have OCD like me, you can even make these executable scripts and everything. If you're a child of the modern world, you might instead use a wiki or something. The important thing is to keep a log of some sort - you will want to refer back to it.
• Remember to google. Whenever you run in to a problem, I promise you someone else has been there before, and talked about it online. Get used to searching around.
• Become comfortable with the error codes. They suck, but in their own demented way, do actually help. It takes time to learn what they're actually telling you, which is usually different than what they say
• Whether or not you're concerned with optimization (and you will be, at some point), get comfortable with explain and tkprof. Wrapping your head around how the optimizer works gives a fair amount of insight that is generally useful.
• Don't be afraid of plsql. It sometimes feels as if you're building a house in the dark or something, but there's a lot of value in it. I know several folks who draw a hard line here - "well, I can use it like a DB, but programming in it is not what I do". They miss out on a lot of the value of Oracle.
• Make sure you're comfortable with sqlplus. The Quest tools are great, and vastly improve productivity, but the time will come when you need to muck around with the v$ and dba_whatever tables, or have to manually reset a sequence, or something, and making sure you can handle sqlplus (and selecting out the twisted joins required to get anything useful out of said tables) is invaluble. I am not making this up: last time I had a full time job, I got a promotion out of the fact that I was the only one in engineering who was able to use it to diagnose a weird logic problem. (Well, there were other factors, but a high-pressure situation when you can ask and get answers from the DB and nobody else can looks impressive. I have not yet tried this technique with the ladies.)
• And all the general database tips: if you aren't already good at design, learn, learn, learn. Don't ignore the theory: if you don't know the difference between third-normal and Boyce-Codd, go learn it. Don't just read: make test DBs in each form, and build test cases in each one. Make sure you understand at least the basics of set theory. I'm not kidding - folks will tell you it doesn't matter, but the difference between a competent DB developer and a great one is frequently a deep understanding of theory. Being in a line of work that exposes me to designs done by a lot of other people, I can tell a surprising amount about their knowledge and work habits by looking at how they build DBs.

Anyway. That ended up being kind of a random brain dump. Hope something was useful.

Re:Where to start? Where are you starting?

[Praetorian42]

Well heck, if you want to go that way, you don't even need a sequence:

insert into employees (id, name) values ((select max(id) from employees), 'the name')

My point was if you want to implement the same logic that you had for MySQL's auto_increment property, where you could leave out the ID on an insert and have the database itself handle it, then you'd need a trigger and a sequence. As a developer, I prefer to do it that way, because I don't have to remember what stupid name the DBA came up with for the sequence.

Re:Where to start? Where are you starting?

[Praetorian42]

Wait, make that max(id)+1

Duh.

Re:Where to start? Where are you starting?

[heinousjay]

Just so no one takes this idea seriously, if there is ever a concurrent update then there will be two inserts that get the same value, and the second one will fail (assuming proper primary key semantics)

Even with max(id)+1 this is wrong. There are reasons databases provide ID generation primitives. One of the biggest is guaranteeing that an id given to a client is unique for that primitive.

Re:Where to start? Where are you starting?

[java.bean]

I'll reiterate, the way I posted is the proper way. As the previous poster said your way is not safe for concurrent inserts. I work at Oracle in the apps development group.

Re:Where to start? Where are you starting?

[Bush Pig]

Actually, I quite like PL/SQL - it's an _awful_ lot like Ada. (Admittedly getting it to give you output can be a pain.) To come to grips with its idiosyncrasies, read anything by Stephen Feuerstein.

Re:Where to start? Where are you starting?

[Daniel Dvorkin]

And all the general database tips: if you aren't already good at design, learn, learn, learn. Don't ignore the theory: if you don't know the difference between third-normal and Boyce-Codd, go learn it. Don't just read: make test DBs in each form, and build test cases in each one. Make sure you understand at least the basics of set theory. I'm not kidding - folks will tell you it doesn't matter, but the difference between a competent DB developer and a great one is frequently a deep understanding of theory. Being in a line of work that exposes me to designs done by a lot of other people, I can tell a surprising amount about their knowledge and work habits by looking at how they build DBs.

This one cannot be emphasized enough. One of my major jobs as a DBA was teaching some set theory to the other members of my dev team -- smart people, with lots of practical experience, but they didn't have the practice thinking that way that I did.

This is one of many advantages I gained, IMO, from having a math major and CS minor rather than the more usual other way 'round, and one of the best answers to people who whine, "why do I have to take so much math to get a degree in CS?" People don't think of DB programming as inherently mathematical like, say, games programming, but it is -- not in the "crunching numbers" sense, but in the sense that it ties in quite directly with the roots of mathematics. Learn the theory, and the code will follow; don't learn the theory, and your code may be acceptable, but it will never be really good.

Re:Where to start? Where are you starting?

[UnapprovedThought]

This is another one of those classic "We need more Info" Ask Slashdot's.

No, this is nowhere near the classic "we need more info" ask slashdot. If you want to win the classic crown, it has to read more like this:

Hi. (As will soon become obvious) I don't know anything about the subject matter, I haven't done any investigation (not even a google search...), I'm not sure (1) exactly what I need it for or (2) how large scale it needs to be, or (3) how fast it needs to be, or (4) what it will be hooked up to, or (5) where it will be installed, or (6) why anyone would want such a thing... but, could you call out every expert on the planet in all fields to help me find the optimal solution and lay out a complete project plan as part of a detailed rollout strategy that leads to a leveraged vendor selection solution synergized with our quarterly goals, marketing plan, corporate vision and political partnerships? Thanks. By the way my sis says she is also curious. I have to go now my dog is barking.

Connect scott/tiger

[SpaceLifeForm]

Perhaps you should hire an experienced DBA.

Re:Connect scott/tiger

[areguly]

You should read Oracle's free e-book entitled

Oracle 10 day DBA. Every major release of Oracle has a version. It is a very good starting point.

Re:Connect scott/tiger

[areguly]

maybe it is 2 day DBA, it gets even easier :o)

Ask Tom

[the eric conspiracy]

Tom Kyte is a sort of help Guru for the Oracle community. His book Effective Oracle by Design is excellent can contains a lot references to other material.

He also runs a web site that has been very useful to me as well.

Re:Ask Tom

[dshannon]

Tom's website is at http://asktom.oracle.com/

I've found it extremely useful for dealing with specific as well as general problems with Oracle.

But in general, if you know one RDBMS, then until you need specific features it's not too hard - I taught myself with the aid of the manuals and some experimentation. HTH

Dan

for what it's worth

[ackdesha]

About 8 years ago I was in a similar spot. Fresh out of college with a physics degree, I was lucky enough to be hired into a good situation using Oracle everyday to manage a large pharm. corp's marketing DB. I had little programming experience, and no unix or DB experience, and had to get up to speed quickly. I haven't touched Oracle in several years, but my advice is to not only read as much of the Oracle manuals as you can stomach, but also pick a language like perl or python to cozy up to. A lot of the tasks you'll need to do are best handled with gnu unix utilities and scripting. Ask other people to review your schema designs. Avoid application programming at the DB level (PL/SQL). Take advantage of subqueries and hints and find a good system to analyze the query cost and tune your SQL (some used toad, i didn't). Looking at my bookshelf it looks like I wore out "Oracle Performance Tuning and Optimization" by Edward Whalen, and "Oracle8 HOW-TO" by Honour, Dalberth, Kaplan. These are probably really outdated by now, but look for something like a cookbook approach to suppliment the official manual. Hope that helps some. Cheers.

Two areas to look at

[richg74]

You didn't actually tell us whether you have any other data base experience. In general, when you're setting out to use an RDBMS (whether it's Oracle, DB/2, Sybase, PostgreSQL, whatever), there are two areas to tackle:

1. A general understanding of how and why relational databases work.
2. The specifics of how to use your particular platform.

(To put it into a more purely programming context, you might think of the first as like what you might find in Knuth's Art of Computer Programming, and the second as like Kernighan & Ritchie's The C Programming Language.)

It's been a while since I used Oracle, so I can't really give you any current recommendations for no. 2. (O'Reilly has some offerings in this category that, in their early editions, were pretty decent.) For no. 1, though, I suggest you pick up a copy of A Guide to the SQL Standard, by Chris Date and Hugh Darwen. Date is one of the "pioneers" of relational data bases, and this book is a good reference to the "why" of how they work.

here

[Anonymous Coward]

Start here.

Wimp...

[Anonymous Coward]

I learned Oracle when I was 14 years old. It's a free download, after all, and there are literally gigabytes of reference documentation available on http://otn.oracle.com./ Plus, large Barnes and Noble stores have an dizzying array of books on Oracle, usually a whole shelf full.

What exactly did you learn while receiving your "solid computer science background"? Certainly not how to learn something that isn't force-fed to you, it seems.

Re:Wimp...

[narcolepticjim]

Yes, but not all of us are 14, with limitless free time. Some of us have full-time jobs with families that require our time outside our work, and we're looking for the most efficient path to learning, not mucking through gigabytes of documentation and a dizzying array of books.

Re:Wimp...

[Anonymous Coward]

L..A..M..E..R

Re:Wimp...

[Anonymous Coward]

Apparently, you learned Oracle this year.

Re:The best place to start ...

[Naikrovek]

the best place to stay? not hardly. there are plenty of situations where MySQL is the worst place to stay. In those situations, Oracle is worth every penny.

Maybe the best place for you, but to assume that MySQL is the best DB for everyone is just plain short-sighted.

Re:Oracle is overrated

[imgumbydammit]

Come on, the manual in Oracle is 584 Megs of pdfs. You could give him at least a little clue.

Like "start with the one titled 'Database Concepts'", e.g.

Learn standard SQL first

[sobiloff]

Oracle is, for the most part, a SQL-compliant database at its core. So, you'll want to get a good handle on basic SQL first, then you can sign up for the Oracle Technical Network to get access to the Oracle-specific documentation at <http://www.oracle.com/technology/documentation/da tabase10gr2.html>. There are probably Oracle-specific commands that you will find useful for your specific geospatial data.

When I learned SQL I used the book "The Practical SQL Handbook" by Bowman, Emerson and Darnovsky. It was very readable, used meaningful examples, and presented the topics in an appealing order. I've recommended it to two or three others during my career and they liked the book, too.

RBDMS

[bleaknik]

There are several different options for relational databases, and despite what the slashdot community thinks, MySQL isn't necessarily the right one.

Let's first assume that you're devoted to the Oracle platform. Oracle has some nice advantages compared to some of the other RBDMS. It handles triggers very well, it supports a java based client for end user applications (programmed via PL/SQL), and it's damned fast (when it's setup up very well). Notably, you will need a dedicated Oracle server with pretty robust specs fi you want it to run well.

Now, a brief comparison of other products in case you are not devoted to Oracle.

Access is relatively cheap and easy to use if you're not going to be doing a lot concurrent operations on the DB.

MySQL is one of the most efficient databases I've worked with in terms of speed, assuming you've got a well normalized database. You can also run MySQL from a nondedicated machine for quite some time before you need a dedicated box (your results may vary). MySQL does have some limitations, though.

As best as I can tell, MS SQL Server runs pretty well, but seems like an oversized version of Access. This can be a good or a bad thing, depending on how you look at it. It does offer decent performance, an easy user interface, and it's a Microsoft product, which of course comes with positives and negatives.

There's also IBM's DB2 platform which (last I used it) had a fast db engine, and a horribly slow front end.

Wikipedia has a nice article that distinguishes between the different RDBMS out there. If you haven't yet picked a platform, I would suggest that you start there.

There are several things you can do, once you've picked a RDBMS, however, your best approach is to learn SQL. SQL is pretty standard amongst the DB engines, and if you can use it well, you'll be golden. This has been my favorite reference manual, although I must it admit if you're first learning SQL, the reading is quite dry. I would also recommend that you find a good resource for whichever database engine you end up using, because each of them has specific functions and keywords that you might need to look up from time to time.

Finally, one last question, if you don't mind me asking... If you've got a computer science background, why don't you have a good basic understanding of RDBMS? I mean, when it comes down to it they're all basically the same. Maybe I'm wrong, but it makes me wonder...

Consequentially, if you're boss is willing to hire me, I might just be looking for a job. :)

oreilly has a large catalog of oracle titles

[atomic-penguin]

Here is a link to Learning Oracle. Here is a link to all of their oracle titles.

Serious Question for "ackdesha".

[mosel-saar-ruwer]

This is a serious question for ackdesha - not a spam or a troll or whatever.

ackdesha, you wrote: Fresh out of college with a physics degree, I was lucky enough to be hired into a good situation using Oracle everyday to manage a large pharm. corp's marketing DB.

Again, not a troll, but a serious question: Wasn't this extraordinarily depressing - to have been a physics major and then immediately turn around and get dumped in a 9-5 doing DB work for a marketing department?

I've had some incredibly depressing jobs in my life - but what you're describing sounds like it could make a guy positively suicidal.

Free Online Oracle 2 Day DBA Course

[DisasterDoctor]

http://www.oracle.com/technology/obe/2day_dba/inde x.html

Re:Serious Question for "ackdesha".

[ackdesha]

Fair enough. Actually, the chain of events went Physics degree -> playing bass in a band and sleeping on strange people's floors -> working at http://www.acxiom.com/ -> then moving on. So, I guess I needed some stability at the time. I considered continuing on with physics, but I really don't think I was cut out for it. I'm guessing most people don't stumble into a degree in physics, but I did somehow. At Acxiom I managed the marketing database for Schering-Plough for roughly 2 years and then moved into application programming, and then quit. I actually really enjoyed the work while at acxiom. The large corp thing was pretty depressing. But, heh, you can always quit...and I did.

A good read after dealing with oracle...

[fornaxsw]

In my experience, you may find this book helpful.

Re:The best place to start ...

[theNote]

Is MySQL.
And it's the best place to stay.

Only on slashdot would you see such a ridiculous comment...

Having worked with just about every RDBMS out there (including Oracle 7 - 10g) I can tell you that the reason Oracle has a well deserved reputation for being a behemoth is not so much its diversion from other platforms but because of its complete lack of decent management tools compared to other vendors. Every RDBMS is a behemoth if you are trying to manage it from a command line but pretty much all other vendors have managed to provide an intuitive wrapper around the inner core. Not oracle. Unless something chaged recently they couldn't be bothered to provide anything but an incredibly lame and slow Java app which you may or may not be able to get working.

If you're job is 100% Oracle DBA than this isn't such a big deal. If (like most us) you have other things on your mind than trying to find the most efficient table space and log file settings you will be burning Larry Ellison in effigy pretty soon.

That being said its not much different from any other RDBMS. The function names may be a little different but you may also find some new shortcuts (CONNECT BY comes to mind) but its just SQL.

As far as where to get started pick up the "Complete Reference" title for whichever version you are using. Combine that with whatever the latest PL/SQL book from Oracle and you've got everything you'll need sans the detailed spatial implementation.

Whew - you had me scared there for a moment...

[mosel-saar-ruwer]

Actually, the chain of events went Physics degree -> playing bass in a band and sleeping on strange people's floors -> working at http://www.acxiom.com/ -> then moving on.

Okay, the "playing bass in a band and sleeping on strange people's floors" provides much better context.

And I had wondered why a physics major would have been "lucky" to get a job as a database gearhead in 1997, at the very nascence of the great dot-com bubble.

But now it all makes sense.

Re:Whew - you had me scared there for a moment...

[ackdesha]

Also, note that all of this happened in Arkansas. Not much of a dotcom boom there believe it or not. cheers. http://www.helpmehelpnola.com/

Re:Oracle is overrated

[mrobinso]

Well, the question starts off with "I'm a programmer with a solid computer science background...", so I'm assuming we're dealing with a reasonably intelligent creature with a university education, since computer science starts there.

One can safely assume then that the user is reasonable intelligent and could discern from the manual titles or chapters where to start. For example, I would think that a beginner would want to start with Chapter 1, or a manual titled "Getting Started". Something like that.

If a beginner starts off with "Triggers, Stored Procedures, & Advanced Normalization Theory", well then, going back to the solid computer science background, I assume he or she is familiar with terms like "chomp", "bitten squarely on the ass", "pebkac", and "you're fired".

I was certainly tempted to point the questioner to both mysql and postgresql (ahem, in no particular order), and also ask if his bosses were qualified to single out Oracle to exclusion of other less costly and better documented products, and whether or not the logic behind that decision was questioned. My first duty as the point person in my outfit's IT strategy is help them refrain from making stupid decisions, not read manuals to learn how to cope with them.

Frankly, if he's going to hit the ground running with Oracle, with little or no experience, the manual I would be most inclined to point him to is "How To Write An Effective Resume". All hell is certainly about to break loose.

Re:The best place to start ...

[Anonymous Coward]

You must be smoking crack. Emctl makes 10g's management quite a breeze and even with 8i/9i, oemapp and other tools make Oracle mgt. much easier than others, including MySQL and PostgreSQL. With free tools like TOAD/TORA, even a clueless newbie will be able to create an instance, do some magic and get everything done.

The whole point of RDBMS is you manage the engine using SQL, hence the command line tools but no one stops you using the fancy stuff.

My experience with MySQL has been purely command line and I can't see anyone bitching about MySQL's lame command line management tools (suddenly the horror called mysql_setpermission comes to my mind).

Re:The best place to start ...

[M1FCJ]

awww, please tell me that you are kidding.

Relational database theory

[joib]

If you have trouble with concepts, rather than the specifics of how Oracle implements those, you might want to look at e.g. "C.J. Date, An Introduction to Database Systems". It's the "classic" in its field.

oracle academy

[bigalsenior]

its free or cheap and can probably be found at most collages dirt cheap in the us (there not allowed to make a profit)

Pro Oracle Spatial

[dicey]

For the spatial extension in Oracle I'd recommend http://www.amazon.com/exec/obidos/tg/detail/-/1590 593839/102-1378503-5410527?v=glance

For the normal Oracle stuff, I'm sure there's 101 books out there that you could find useful depending on what angle you want to approach it from. Check out the documentation on the oracle site - also might be worth getting a login for Oracle's OTN and checkout the forums.

Hope this is helpful...

[tchuladdiass]

Most of the Oracle programming books I've found start off either too heavy (assuming you already know a lot of oracle dba stuff), or too basic, where you still don't get the info you need.
I came across one back a while ago that struck a perfect balance, at least for me, in that I was able to go from 0 Oracle experience to setting up tables and writting the app I needed for a work project within about a week. The book is at work, but I think it's "Oracle 9i Programming: A Primer" by Rajshekhar Sunderraman (at least, that's the one I found on Amazon, I think it's the same book I used, I'll double check on Monday at work and get back to you)

Re:Hope this is helpful...

[tchuladdiass]

Ok, now that I've had a chance to retrieve this book, I can give you a bit more info on it.
It starts off talking about the relational data model, then goes into using SQL Plus (Oracle's command line tool for interacting with the database). The 3rd chapter is all about embedded SQL, specifically putting Oracle SQL statements in C. The process here is you run your C code (with embedded SQL) through Oracle's ProC compiler (basically acts like a pre-processor), which outputs another C file that you then run through your regular C compiler. Note that other languages may have different methods of interfacing with Oracle (depending on what database pre-processors are available for them).
Chapter 4 and 5 go over PL/SQL and Oracle JDBC, and the final chapter gives a bunch of sample projects.
Overall, this isn't a very comprehensive book, but it has enough information in it (and is small enough) that makes it very easy to get through.

Some useful sites & info

[Anonymous Coward]

Assuming you already know something about databases:

http://asktom.oracle.com/
http://tahiti.oracle.com/
http://metalink.oracle.com/

Tom Kyte's books
Cary Millsap's books
Oracle Press books
The Oracle Concepts Guide from tahiti above for your version
comp.databases.oracle.server (watch out, there are some bigheads)

And you can download and run a full-blown Oracle setup on your own for free, as long as it's for learning.

There are lots of crap books and bad advice out there; concentrate on understanding from the above first so you can spot those.

Do backups. Use RMAN. Test backups.

Oracle's own reference books...

[Anonymous Coward]

Are some of the best docs for a product I've ever used. Well written, thorough, with lots of good examples. And the whole system is documented.

http://www.amazon.com/exec/obidos/tg/detail/-/0072 253517/qid=1126980407/sr=8-1/ref=pd_bbs_1/103-4713 948-1537460?v=glance&s=books&n=507846

Just 1 example, this is a HUGE book, and you get them all for free on CD-ROM when you buy Oracle.

When you install the books, a search utility is installed as well. You can then browse them all online.

If you find a book really handy, you can buy a copy at Amazon.

OP: Some guidance

[Glonoinha]

Contact the professor that is teaching the Boston University MET* CS_579 class L2 Database Management. His name is Ellis Cohen and he is a hard-core Oracle guy, has a full semester worth of studies in everything that is database and is very Oracle 10g centric. All his stuff is delivered in PowerPoint (yea, yea - but it works) and he has excellent homework exercises that walk you through every aspect of the engine, including all the fun freaky stuff.

The class was one of the more difficult classes I have taken, but I came out of there on par with most of our DBAs (and more fluent in Oracle 10g than a few of them.)

I honestly don't know how he will respond to you contacting him, but if you want to be an Oracle heavy, he can show you the way.

* Metropolitan College

Re:OP: Some guidance

[Forbman]

Does he know the history behind empty strings and NULL's in Oracle?

My theory is it slipped into the system, and it's unfortunately stuck. Talk about legacy code...

Re:Serious Question for "ackdesha".

[molnarcs]

I'm in a similar situation. I'm doing my PhD in literature and philosophy - wrote my MA thesis on Freud and the Uncanny, and my dissertation would be on alternative economic models in utopian/science fiction and in the high tech industry (gift-economy for the geeks in the humanities - someone must tell them what's going on in their language).

Anyhow, with my MA and doctorate (which I'll hopefully get in two years) I will be qualified to teach at a university. Only problem is that they don't throw tenures at you these days. I calculate 5% chance of getting a job in my area of expertise, which - and I don't mean to brag - covers a fairly broad area compared to other students: classic literature (from the Bronte sisters through Balzac to Thomas Pynchon), philosophy (from Kant and Hegel to Jacques Derrida and Michel Foucault), feminism and gender studies, and whatnot. So, it was this year that I began to face reality and recognize that I will probably have a (much) greater chance to find job as an *nix administrator.

So here is where I stand now: I switched to linux 4 years ago and to FreeBSD two years ago. I gradueally learned how to secure a FreeBSD system (using pf/ipfw), some basic networking stuff, some very basic scripting, ~ 10 commands I need to set up mysql and manage its accounts - just enough so I can use some CMS. I have a part time job working for some folks at the university, maintaining their site (which includes everything: the underlying os - FreeBSD, translating geeklog into hungarian, writing documentation for non technical stuff to handle moderation, well, every apsect of running this site, and another hobby site. I recognize that this knowledge is almost nothing for a serious job application in the field. So in the upcoming two years, I must set aside time from working on my dissertation to learn more. I just bought a book on introduction to php and mysql, with lots of examples. Knowing php and mysql is a must. Javascript comes next, along with a good working knowlede of a scripting language (most likely python or pearl). Along with these I need to learn setting up more complicated (not just NAT and basic load balancing via pf) networks, vlans, etc.

This is both a fortunate and unfortunate. If I manage to get a job that has something to do with *nix, I would be happy. But then, there are those 7 years I spent learning and thinking about literature and philosophy. I don't regret it, I enjoyed it, and would enjoy working with it, but if those years were spent learning programming, then I would be better off now. On the other hand, I know that I would not be the same person, and I would not know what I have missed. And there is a chance that I will be able to return to my "official" field. There are so many things I want to do and write (I have already something for another book if I finish my dissertation) - so maybe, I will be able to do it in my free time, while earning my living working with unix. Anyhow, the next 3 years will be probably the most difficult years in my life, for I'll have a double workload: finishing my dissertation in two years (that basically means writing a book that will be accepted for publication) + acquiring an in-depth knowledge of everything that a good system administrator needs. And I have no choice in this: no matter how good my dissertation will be, it won't be enough for a tenure, because the staff at our univ. is young and talented, and there won't be any job openings in the forseeable future. And I can't think of anything else that I might somewhat enjoy than working with linux/unix.

Wanted: SQL techniques and reference

[Tablizer]

I've been looking for a general Oracle SQL (not pl/sql) reference and intermediate and advanced SQL techniques. Maybe that would be 2 different books, but I like books that have thorough examples under each language or command topic. Any suggestions?

Re:Wanted: SQL techniques and reference

[nigelo]

Yep, Effective Oracle by Design by Kyte as mentioned earlier - check out his website as mentioned earlier: asktom.com

Re:Wanted: SQL techniques and reference

[Tablizer]

It sounds like it has a lot of discussion on schema design and performance tuning. Usually for Oracle systems I don't have much say in the schema design and just want to know more SQL techniques and have a handy example-heavy reference.

Re:Wanted: SQL techniques and reference

[Forbman]

Joe Celko's books would be a good place to start ("SQL For Smarties") for expanding your mind regarding SQL.

But he writes all his queries using ANSI-standard syntax where possible. Oracle 10g (9.2) just seemed to join the modern ages to support ANSI join syntax as well.

--typical Oracle left outer join syntax:
Select a.name, b.*, c.*
from table_a a, table_b b, table_c c
where a.id = b.id (+) and b.id2 = c.id2 (+)

--ANSI 89 left outer join syntax:
Select a.name, b.*
from table_a a
LEFT OUTER JOIN table_b b (
    LEFT OUTER JOIN table_c c on b.id2 = c.id2)
on a.id = b.id

At least since 9i though it supports the CASE statement, as well as the non-standard (but traditional) DECODE() function.

The hierarchical tree support in Oracle is...well...it's there (I'm not a fan of it).

Some of the newer options for doing running sums, etc. since 9i are quite useful, and beat writing a bunch of subqueries or using cursors to do it the traditional way.

Java for stored procs? Knock yourself out. I'd stick with PL/SQL unless you have to use/write Java SPs.

I haven't checked for sure, but I don't think Oracle supports full outer joins, so you have to union a right outer join and a left outer join:

instead of:

select a.field1, b.field2
from table_a a FULL OUTER JOIN table_b b

you have to do:

select a.field1, b.field2
from table_a a, table_b b
where a.id = b.id (+)
UNION
select a.field1, b.field2
from table_a a, table_b b
where a.id (+) = b.id

Oh... and in oracle, the empty string is equivalent to NULL...

select decode(NULL, 'is not null', 'is null')
from dual
union
select decode('', 'is not null', 'is null')
from dual ...should return you two records that say, "is null"

Re:Wanted: SQL techniques and reference

[ahmusch]

Oracle 9i supports full outer joins using the FULL OUTER JOIN syntax.

I find the Oracle's heirarchical structures significantly simpler than the ANSI standard, which reminds me a lot of the MODEL stuff that Oracle introduced in 9i.

Where to start:

[sakusha]

At the beginning.

I spent many years doing computer tutoring. I was quite successful with one strategy, sitting down with the client and saying, "Let's start on page 1 of the manual."

Your Oracle software came with a manual, right?

Re:Where to start:

[Anonymous Coward]

You have got to be kidding. Do you even know what Oracle is? I'm not even on the same continent as whatever the Oracle install I use "came with".

Re:Where to start:

[sakusha]

Yes, Mr. Anonymous Coward, I know what Oracle is. Every Oracle site I ever worked at had a whole shelf full of manuals. Go to their website, Oracle University courses will bury you in manuals.

PostgreSQL

[commanderfoxtrot]

I thought PostgreSQL's OpenGIS was far better than MySQL's; this certainly used to be the case - has it changed?

Just to get more back on topic, PostgreSQL has excellent Oracle SQL compliance, so it's probably a good DB to play with to bring you up to speed. I know I always felt a little bit concerned learning DB2 on the actual mainframe...

Caution - O'Reilly's tuning advice has ... issues

[rebill]

The tuning advice given by the O'Reilly books is quite useful if and only if you are going to have a single user accessing the data.

However, if you intend to allow multiple people to access the data at one time (i.e. a web site), then burn those books and start reading about performance tuning on Tom Kyte's website.

The main problem in the O'Reilly books is that they completely ignore the effect of the queries on resources that are shared globally by all users on the database. In a perfect world, there wouldn't be any shared resources, but we aren't in a perfect world.

The idea here is that one person's tasks are processed sequentially simple because of the nature of what they are doing. However, when you have 15 people on the database, you really want their tasks to be running in parallel with each other. You end up sacrificing some of the "best possible" performance that a single user could gat in order to give everyone reasonably good performance.

A good analogy is automotive traffic. It would be really nice for you if every traffic light you hit was always green in your direction ... but everyone else on the road would hate you for it.

Without any doubt...

[Jippy T Flounder]

the best place to start is to find a job where Oracle is NOT a requirement.

I've been working with Oracle for the past year, and suffering every minute of it.

They've constructed an RDBMS with AWESOME potential, but not only is the documentation generally aweful, the system itself is so unnecessarily complicated and, to quote Neal Stephenson, "crufty", that it takes a certain mentality to deal with the myriad little things that need tweaking and tuning (read Oracle SQL Statement Tuning if you don't believe me)... a certain "I don't mind wasting my life on trivialities" mentality.

Now don't get me wrong, I've seen Oracle DBA's do some mind-blowing things, but I've also seen the amount of work they have to put into the little, this-was-supposed-to-take-a-minute things, and quite frankly, it's one hell of a trade-off.

If you're satisfied with dedicating a large portion of your sanity and time, then good luck! Otherwise, keep away. It's for your own good.

I was asked the other day if I would say the same things if I was suddenly offered a job at Oracle, for an unbelievably large sum of money. I thought carefully about it, and came up with the only possible conclusion.
"So long, and thanks for all the fish."

Re:The best place to start ...

[GNUALMAFUERTE]

Oracle is Proprietary. Period.

If you must be a dick about it...

[Corngood]

MySQL is a toy. Period.

Re:If you must be a dick about it...

[GNUALMAFUERTE]

1 - It's NOT.
2 - My Freedom is more important than anything else. Argue with that.

Re:If you must be a dick about it...

[Anonymous Coward]

If you want freedom, go postgresql. BSD license has greater freedom than a dodgy gpl+proprietry license.

Re:If you must be a dick about it...

[gentlewizard]

If your freedom is more important than anything else to you, that's fine.

To many other people, however, freedom (in the sense of what? being able to read the source code?) is not the only, or even the most valid business criterion for selecting a database platform.

Feature set, scalability, high availability, performance on large data sets, and availability of both vendor support and a pool of employees experienced in the software are other things that go into making a platform decision.

Re:If you must be a dick about it...

[Anonymous Coward]

My food is more important than your freedom. So there!

Re:If you must be a dick about it...

[GNUALMAFUERTE]

No it's not. If in order to be really free, i must kill you, or make you starve to death, i certainly will.

OTAH, is YOUR food more important than YOUR freedom?, that's sad.

Re:The best place to start ...

[GNUALMAFUERTE]

Oracle is Proprietary. Period.
MySQL is functional enough. You think Oracle is better?, improve MySQL. My Freedom is more valuable than some technical benefits.

SQL for Web Nerds

[dhwang]

A good introduction to SQL databases with lots of Oracle-related examples is SQL for Web Nerds.

Mastering Oracle SQL

[heffel]

I like Mastering Oracle SQL from O'Reilly. It assumes no previous familiarity with SQL.

If you are looking to do stored procedures, it touches briefly on PL/SQL (Procedural Language/Structured Query Language), which an Oracle proprietary language, but does not explain it in depth.

For SQL, and specifically explaining Oracle's quirks and proprietary functions, it is very good.

start with the fundamentals

[Anonymous Coward]

Wow, nobody here has even suggested the most logical place to start: by learning the relational model, the only complete theoretical model for data manipulation and retrieval. (The suggestions to "use MySQL" are frightening, to say the least).

Or maybe they just assume since you are CS, you understand the RM? I've been burned several times making that assumption. So here's my advice, buy this book and master the content within. I choose that one because 1) Chris Date is one of the few people who actually has a deep understanding of the RM 2) it's short (240 pages) 3) it's well-written (as is all of Date's stuff) and 4) it's on O'Reilly which is comforting to people who are afraid of theory books (in fact I was surprised to see O'reilly pick up a book that wasn't about "hacks" or a "cookbook", kudos, we need more books that teach principles that can be applied to any product).

Only then should you proceed to learning any particular product (and yes, Oracle is just a product, not a "technology", or a religion for that matter).

GIS is it?

[Strixy]

Your applications may be different from those I've worked with (being that I am a geographer, not a programmer). When you mention geodatabases the first thing that popped into my head was GIS ( http://www.gis.com/ ). And my first solution was to recommend taking a night class twice a week and get your company to reimburse you upon succesful completion of the course. If GIS is what you are interested in interfacing with there should be a university in your area that will cover what you need to know. If not, look into http://www.opengeospatial.org/ Assuming it is GIS that you are working with. Good luck.

Kyte, Gruber, manuals

[^Z]

Get one of Tom Kyte's books. While none of them is exactly for beginners, you're also not fresh from college, are you?

The above applies when you have some understanding of basic SQL already. If not, get Martin Gruber's classic book 'Understanding SQL'; it is so venerable that you'll have no trouble getting it via p2p or irc, too ;)

Then, Oracle's own documentation supplied with the database server is well enough written, if somehow voluminous. Don't shy to look there.

Re:Kyte, Gruber, manuals

[Tupper]

Parent poster knows about what he speaks. Kyte, Gruber, manuals.

sqlplus is annoying. One free substitute that works much better (at least on unix) is Henplus.

Make them train you

[theonetruekeebler]

If the company needs you trained up on Oracle, point out to them that Oracle has an excellent training program that will get you up to speed (and certified!) in just a few weeks. Their training has many paths and areas of focus---DBA, developer, etc. They even do specialty training on stuff like Oracle Spatial (their GIS specialty module)

As you have pointed out, Oracle is a big 'un. If your company wants to entrust its data to it, they should entrust its care to an employee they've trained to use it.

If they try to be ominous to you, all "Well, maybe instead of training you we should just hire somebody who's already trained," point out to them that in the long run it would cost them a lot more to train an Oracle guy to replace you than it will to train an existing employee to do Oracle. This of course assumes you're good at your job, that the skills you exercise for them are reasonably non-generic, and that you have been with them for some time.

Once you're trained, you'll have some idea of what to look for in a book. That is when you start buying books.

Oh, and install a copy of Oracle on your RHEL box at home, and client stuff on your XP box. You do have a RHEL box and an XP box at home, don't you? All that's available for download from Oracle.

Not for geospatial data, it isn't

[gentlewizard]

You can get similar performance out of a properly tweaked MySQL database...

Actually, no. He said in his post that he was primarily interested in geospatial data. Oracle's Spatial option is designed specifically for data like this.

Plus, geospatial data often means huge databases. Oracle's ability to scale is much higher than MySQL's.

www.LazyDBA.com

[Anonymous Coward]

www.LazyDBA.com

This is where everybody starts !

Some links for you

[Captain Numerica]

The Oracle Technology Network
http://otn.oracle.com/ - LOTS of FREE online books and tutorials.

Ask 'Tom'
http://asktom.oracle.com/ - a place to go to ask questions and read FAQs ... but mostly the latter.

Re:The best place to start ...

[Captain Numerica]

i was writing up a response nearly identical to yours but you beat me to it.

Re:The best place to start ...

[Captain Numerica]

with all respect, your idealism exposes your youth.

i'm down w/ OSS but there is a time and a place for everything... and the truth is, most often (note: i didn't say always), there is not a place for OSS in the business world.

businesses want something that (1) works and (2) has a phone number they can call and bitch people out when that POS software doesn't work like that SOB liar salesmen said it would.

Re:The best place to start ...

[GNUALMAFUERTE]

I Work for a Phone company here in Argentina, we work on Public Telephony (Phone Boots, Calling Cards, etc.)
We are 50% VoIP, 50% FWTs (Cellphone adapters). I Build our PBX and our Billing plataform.
Our Software PBX is based on Slackware + Asterisk + GnuGK + MySQL. Our Billing plataform is MySQL + Perl.

We are currently managing arround 1.750.000 minutes per month.

My Idealism is not just idealism, i put it in practice, and it works.

from nowhere to leader?

[chivo243]

RUN!

I'm guessing....

[Chazmyrr]

I'm guessing that your real question isn't how to use Oracle, but how to create a geodatabase. You might try "Designing Geodatabases: Case Studies in GIS Data Modeling"
http://gis.esri.com/esripress/display/index.cfm?fu seaction=display&websiteID=85&moduleID=0/

A google search on geodatabases turns up a number of similar resources.

Totally off-topic

[Anonymous Coward]

But nice Violent Femmes sig.

First of all

[ahmusch]

Go to technet.oracle.com and register. That will enable you to download Oracle's documentation.

Read and understand the Concepts guide. It's important.

Then read the Oracle Spatial User's Guide and Reference.

After that, you should have some semblance of a clue. If you want good Oracle books, any of Tom Kyte's or Jonathan Lewis' would be a good place to start.

Oracle certification sequences

[jbolden]

I think Oracle has put together pretty good lists of basic topics organized well in their certification sequences. Pick either the DBA or the developer one and start working the books (maybe even try and pass the exams). You will learn a lot and understand Oracle by the time you are certified.