I recently stumbled across a sample chapter of Expert Oracle PL/SQL where the authors (Ron Hardman and Michael McLaughlin) mentioned that OraFAQ.com is a good place to post Oracle questions.
LOBs, or Large OBjects, are Oracle's preferred way of handling and storing non-character data, such as mp3s, videos, pictures, etc., and long character data. Binary large objects, or BLOBs, and character large objects, or CLOBs, can store up to terabytes of data - much more than the paltry 4000 bytes permitted in a varchar2 column. LOBs and CLOBs offer DBAs and developers great flexibility and storage space; the tradeoff is that they're a bit clunkier to handle.
The first thing to know about LOBs is that there are two basic types: external LOBs, which are stored outside the database, and internal LOBs, which are stored in the database. External LOBs are of the BFILE datatype; essentially, the database stores a pointer to the LOB's location in the file system. As such, they can't participate in transactions, and access is read-only. This article will deal with internal LOBs.
Flashback Recovery is a new enhancement to the 10g database for the DBA's toolkit. Effectively, it's an "Oh shit!" protection mechanism for DBAs as it reduces recovery time from hours to minutes. Ask any DBA about the main cause of application outage - other than hardware failure - and the answer will be "human error". Such errors can result in logical data corruption and can bring down the complete system. Part of being human is making mistakes. Without advance planning, these errors are extremely difficult to avoid, and can be very difficult to recover from. Typical user-errors may include the accidental deletion of valuable data, deleting the wrong data, or dropping the wrong table.
Some DBAs complain that Oracle's pre-10g job queue interface is lacking. Unlike cron or Windows Scheduler, pre-10g Oracle doesn't provide a mechanism to schedule jobs to run twice a week, on the first of the month, etc.
Oracle has been available on Windows Server since Windows NT shipped in 1994. In that time there have been many changes to Oracle as features have been added and improved. One of the most significant improvement to Oracle on Windows has just been introduced (this was a year ago they supported 64 bit AMD/EM64T) without much fanfare or publicity. This is the introduction of Oracle 10g on Microsoft Windows Server x64.
PL/SQL is an excellent language for Oracle. Integrated with the database, highly useful extensions to SQL, extremely powerful when exploiting Oracle Objects, and in spite of all this, it is still way easy to learn. But like any procedural programming language, one can get lazy with it, tending to accept the first solution arrived at. Looping constructs in particular seem to be used as crutches rather than necessary components of a solution. Bad looping causing performance issues is a problem that liters the PL/SQL landscape. But it is easy to spot and fix.
In 10gR1, Oracle introduced the Scheduler, a new way to schedule jobs to run from within Oracle. The old DBMS_JOB mechanism is still there, but the Scheduler has several advantages over its predecessor. This article will take a quick look at those advantages and discuss transitioning from DBMS_JOB/ DBA_JOBS to the Scheduler. My next article will take a more in-depth look at some of the Scheduler's features.
OUTER-JOIN is a very handy feature of SQL. But its value at solving certain classes of SQL query problems aside, it is also one of the most error prone Oracle SQL extensions we can put to use. Even advanced developers can make these mistakes. So let us discuss the error prone nature of this feature, and how to fix it.
This article discusses the various types of primary keys and list the advantages and disadvantages they provide.
If you've ever gotten a phone call from an annoyed user whose transaction just won't go through, or from a developer who can't understand why her application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked. Even better, you can identify the exact row that a session is waiting to lock.