Forgot your password?
typodupeerror
Programming

FortKnox's Journal: The Oracle Experience 11

Journal by FortKnox
I'm no DBA. Actually, I'm very far from being a DBA (it wasn't until after I graduated college before I had even SEEN table relationships (primary keys/foreign keys) in a database before).
Anyway, as I had mentioned before, I'm writing a mud from scratch in Java (based on GURPS), and I'm storing everything in a DB. I am not a fan of mySQL (still immateur in my opinion), and was steering towards Postgres when my local DBA said "Just get oracle!"
I didn't want a pirated copy, but I found out oracle has a 'personal edition' that's free (go to oracle.com->downloads->oracle9i->Personal Edition). So I spent yesterday downloading the hoss (yeah, its about 1.5GB download). After downloading, I had to unzip and install it. It took FOR-EV-ER to install. But, in comparison, it wasn't difficult to install at all.

Now, I got this big-ol DB hanging around, and I need a little help. I know my SELECTs and UPDATEs, but that's the extent. I know the concepts of DB's (relationships, etc...), but I need some extra help to figure out the inner workings of oracle. And I do not want to spend any money, if possible.
Anyone have a favorite tutorial/reference guide for Oracle? I'm running Oracle9i.
Thanks.
This discussion has been archived. No new comments can be posted.

The Oracle Experience

Comments Filter:
  • I don't know much about the product at hand, but I just thought I'd mention an open source project which is compatible with Oracle. SAPDB. http://www.sapdb.org/, maybe you can compare and contrast them at some point if you're bored :)
    --
  • by glh (14273)
    Good question. I usually resort to a google search on what I'm looking for, but even there it is hard to find good resources-- a lot of them are pay-per-view. Anyway, I'd also be interested in getting a good reference book. There are a few Oracle DBA's here that are good, I could ask them and get back with you.

    For the record, I used to use Oracle quite a bit when I first started developing with DB's. I did a lot of it command line style for the administration, which wasn't bad, but kind of cryptic. At my new job we use Oracle for everything, and I have come to love it again. I'm using a tool called SQL Navigator by Quest Software which beats the pants off any other tool I've ever used with Oracle (especially SQL Plus). It's good for administrating, doing PL/SQL, you name it. I'm not sure how much it costs but it's probably not cheap. Anyway, it's got some good help files in it.
  • In my experience the W3C school has really good tutorials. Here [w3schools.com] is the section about SQL. Yes, it first starts about SELECT and stuff like that, but you should check out the advanced section where the creation and deletion of tables is covered. Honestly if you know basic SQL, any database should do. The only thing that seems to miss in the tutorial is transaction management (I just browsed briefly). Now, for basic usage, just start using your SELECT/UPDATE/DELETE and then type COMMIT to commit or ROLLBACK to go back from where you came since the last COMMIT.
    If you master all this, you can go on to the next level which are stored procedures which are well supported in Oracle.

    However, since you are a beginner, you should not have dissed MySQL. In your opinion MySQL is "not mature", well except the lack of transactions (which can be added by patches, or is now perhaps even in the main tree, I don't know) MySQL is really a good way to get your experience with databases. What would have been useful is that MySQL is only around 20Megs, which would have caused you to have less pain downloading it.
    From my experience knowing basic SQL is enough to handle any database. Yes, you miss out some stuff but it is simply nonstandard, which means that the day you need to use Sybase or MSSQL you might feel like a newbie again.

  • If you're an ACM member, they have all those funky professional development courses that they're offering for free now, and Oracle is one of them (it is Oracle8, but still) So if you've already spent the money on ACM, it's there for the taking.
  • Tablespaces (Score:3, Informative)

    by Cliff (4114) on Wednesday October 09, 2002 @04:09PM (#4419127) Homepage Journal
    If you don't create your own tablespaces, then you use Oracle's USER tablespace by default, which may or may not be sufficient for your needs, so make sure you create decently sized TABLESPACEs or are at least familiar with the concept.

    If you are not quite sure what a TABLESPACE is, then you might want to read up on it. Basically, a TABLESPACE is where your data objects will live. You can create static TABLESPACEs or TABLESPACEs that grow to a certain size. It's been a while since I've done Oracle and PL/SQL work, but this was one of the more tricky concepts to get down.

    Now if you run out of space in your TABLESPACEs, don't fret. You can use the Oracle 'exp' tool to dump your database to a file and then the 'imp' tool to reload the database from the file after you've done whatever TABLESPACE maintenance that needs to be done.

    I think that the simple form of the tablespace creation command goes something like this:

    CREATE TABLESPACE <tablespacename> DATAFILE <filename> SIZE <n>M
    Where n is a number and M means megs, of course. To create your database, use the generic SQL, but add the TABLESPACE clause:
    CREATE DATABASE .... TABLESPACE <tablespacename>
    Now any tables created in that database will default to the mentioned TABLESPACE. However there may be some instances when you want specific tables in specific TABLESPACEs, and I think CREATE TABLE also allows a TABLESPACE clause, and ALTER TABLE may have a few allowed TABLESPACE operations, as well.

    It's been a while since I've done this stuff. Hope this minor bit of hints and pointers helps you out. More good tablespace hints can be found, here [relia.net] and here [216.239.51.100], although if you've installed the whole Oracle, you have the proper PDF manuals available as well. Those will be crucial resources if you do have them.

    Oh! And another good thing to know is how to look at TABLESPACE usage. It's not an obvious command [vsbabu.org], I tell ya.

    So there you go, a crash course in What I Can Remember About TABLESPACEs. There's more. Oh, much more, but this should get you started.

    • Cliff, have you ever personally seen Slash running on Oracle? I know it comes with a schema setup for Oracle, but I wonder if there's any hardcore support for it. Does it run any "better" than it does on MySQL? Better can mean any number of things, like speed, reliabillity, scalability, etc.
      • No, I have never personally seen Slash run with an Oracle backend. There are Modules that have basic Slash support, but I think those were current for Slash 2.1 and haven't been updated since and there have been a looot of changes since then.

        This is not a trivial thing, but I wouldn't mind taking the time to download and install Oracle and help with such a project if there is one in progress.

  • Oracle is a pain in the butt to work with! Trust me, I worked with it for a few months this past summer. It can do some amazing things, and in fact my app would have dead-ended without its capabilities, but Oracle really needs an astute DBA behind the hood. For tweaking the algorithms, y'know. There is also space allocation issues like setting your MAXEXTENTS and NEXT values to the correct numbers. Configuring Oracle properly is akin to using hdparm on your Linux box a thousand-fold. Don't forget to ANALYZE TABLE CREATE STATISTICS; and all that good stuff.

    The coolest thing about Oracle is the ROLLBACK buffer. E.g.,
    SQL> DELETE FROM USER_BANK_ACCOUNTS WHERE MONEY > 0;
    14379183 rows deleted.
    *ack!*
    SQL> ROLLBACK;

    Money's still there! :D

    I don't think My SQL and the other pansy databases feature this, but don't quote me on that, I'm no DBA either.

    Might want to pick up one of those red $50 Oracle books if you want to learn any of the details; I recommend it.
  • You might want to check out OTN [oracle.com] and click on 'documentation' in the left navbar. It's searchable and all that.

    Some basic/not-so-basic questions can be answered at orafaq [orafaq.com].

"Whoever undertakes to set himself up as a judge of Truth and Knowledge is shipwrecked by the laughter of the gods." -- Albert Einstein

Working...