Database Reference
In-Depth Information
Features and Functions
A natural extension of the argument that you shouldn't necessarily strive for database independence is the idea that
you should understand exactly what your specific database has to offer and make full use of it. This is not a section
on all of the features that Oracle 12
c
has to offer—that would be an extremely large book in itself. The new features
of Oracle 9
i
, 10
g
, 11
g
, and 12
c
themselves fill a book in the Oracle documentation set. With over 10,000 pages of
documentation provided by Oracle, covering every feature and function would be quite an undertaking. Rather, this
section explores the benefits of gaining at least a cursory knowledge of what is provided.
As I've said before, I answer questions about Oracle on the Web. I'd say that 80 percent of my answers are simply
URLs to the documentation (for every question you see that I've published—many of which are pointers into the
documentation—there are two more questions I choose not to publish, almost all of which are “read this” answers).
People ask how they might go about writing some complex piece of functionality in the database (or outside of it), and
I just point them to the place in the documentation that tells them how Oracle has already implemented the feature
they need and how to use it. Replication comes up frequently. Here's a typical example of what I am asked:
Is there a view that will show the literal SQL run? What I mean is that when I select from V$SQL,
the SQL_TEXT looks like this: INSERT INTO TABLE1 (COL1,COL2) VALUES (:1,:2). I need to see
the actual data submitted. e.g. INSERT INTO TABLE1 (COL1,COL2) VALUES ('FirstVal',12). What
I am trying to get is a list of insert, update, or delete statements run against one schema and run
those same SQL statements against a second schema in the same order of execution. I am hopeful
to be able to write something like this:
Select SQL_FULLTEXT from V$SQL where FIRST_LOAD_TIME > SYSDATE-(1/24) AND
➥
(SQL_TEXT like 'INSERT%'...) order by FIRST_LOAD_TIME
This record set would be sent via a web service to schema2, which would process the statements.
Is this possible?
Here is someone trying to reinvent replication! He can't get the literal SQL (and thank goodness for that!), but
even if he could, this approach would never work. You can't just take a concurrently executed set of SQL statements
(what happens on a multi-CPU machine where two SQL statements are executed at exactly the same time?) and
execute them serially (you may end up with different answers!). You'd need to replay them using the degree of
concurrency you used on the originating system.
For example, if you and I both execute
INSERT INTO A_TABLE SELECT * FROM A_TABLE;
at about the same time,
we'd end up with
A_TABLE
having three times as many rows as it did when we started. For example, if
A_TABLE
started
with 100 rows and I did that insert, it would now have 200 rows. If you did the insert right after me (before I commit),
you would not see my 200 rows and you'd insert 100 more rows into
A_TABLE
, which would end up with 300 rows.
Now, if we change things so that a web service performs my insert (
A_TABLE
grows from 100 to 200 rows) and then
your insert (
A_TABLE
grows from 200 to 400 rows)—you can see the problem here. Replication is not trivial, it is, in fact,
quite difficult. Oracle (and other databases) has been doing replication for over two decades now; it takes a lot of effort
to implement and maintain.
It's true you can write your own replication, and it might even be fun to do so, but at the end of the day, it's not the
smartest thing to do. The database does a lot of stuff. In general, it can do it better than we can ourselves. Replication,
for example, is internalized in the kernel, written in C. It's fast, it's fairly easy, and it's robust. It works across versions
and across platforms. It is supported, so if you hit a problem, Oracle's support team will be there to help. If you
upgrade, replication will be supported there as well, probably with some new features. Now, consider if you were
to develop your own. You'd have to provide support for all of the versions you wanted to support. Interoperability
between old and new releases? That'd be your job. If it “broke,” you wouldn't be calling support. At least, not until
you could get a test case small enough to demonstrate your basic issue. When the new release of Oracle comes out,
it would be up to you to migrate your replication code to that release.
