Re: Oracle question from a SQL Server DBA

AA,

I'm an ORACLE DBA just learning Sphinx, so maybe we can help each other. Anyway, here goes:

aa wrote:

> I am an experienced SQL Server DBA starting to learn Oracle. I am comparing
> the two systems and would like comments on the following points.
>
> Table update locks:
> With SQL Server, if I run a query that updates every row in a table the
> entire table will be locked. If I simaultaneouly run a select query against
> the table the query will hold until the update either commits or rolls back.
> If I do the same in Oracle, the select query will return the old snapshot of
> the table ( from rollback segments) before the update is committed. The user
> is unaware of the fact someone else is updating the table and the Select
> results will be out of date as soon as the other update commits.
> The SQL Server user will know someone is running an update (due to the
> hourglass) and will try again later. He is always sure that any Select
> results he obtains are committed ones.
> What are the pros and cons of these methods?
>

Two points here:

Both users know that all results they get are committed transactions, their results differ because the queries are run at different times.

If the intent is to provide a consistent dataset within the table, a work block can be created via a 'LOCK TABLE ... IN EXCLUSIVE MODE' prefix which will guarantee that a query will see none of the changes made to your data until the next commit/rollback. Then, the user sees either the old data( prior to your work block completion), or all of the new data.

Given the ability to provide data consistency via workblocks, I don't see an advantage to preventing a nonblocking query( ORACLE parlance for a query which only reads).

> Cold sever backups:
> With Sql Server, I can copy *.DAT in MSSQL\DATA to another machine with
> SQL Server loaded and everything will be up instantly. All database
> configuration are stored in Master.dat.
> Can I do the same with Oracle? Would I have to re-create the database
> instances in the Registry? Please comment.

The equivalent ORACLE operation is a database-level export, with all objects exported.

This is one of those places where ORACLE and NT grind each other's rough spots. You will need to re-create the tablespaces from your source machine on your target machine before importing the database. This is a major argument for scripting all DDL, and keeping the scripts!

>
>
> Integrate Login:
> Can I authenticate an Oracle user with the NT login?

Yes.

The syntax is 'CREATE USER username IDENTIFIED EXTERNALLY'.

>
>
> Performance monitoring:
> Where can I get Oracle counters for perfomance monitor? SQL Server has a
> whole bunch of them and that facilitates tuning a great deal.

ORACLE (v8.03 for me) has a set of Perfmon counters predefined. Pick the 'Oracle8 Performance Monitor v8.0' option from the 'Oracle for Windows NT' submenu of the 'Start' menu.

That said, look to third party tools from Platinum, Sylvan Faust and others for tuning help. The Oracle Enterprise Manager does work, especially with the optional Performance Pack, but it is not as well integrated. None of these, so far as I know, are MMC snap-ins.

Oracle demands more from the DBA, both in design and in operation. It pays back in tunability, scalability, and portability.

Best of luck,
Don Humberson Received on Thu Mar 05 1998 - 00:00:00 CST