Tips for using Oracle effectively on Windows NT
With its 53 percent share of the market, Oracle is the worldwide leader in relational database management systems (RDBMSs). Microsoft's SQL Server is more widely used in the Windows NT market--not surprising, because SQL Server is part of BackOffice and Microsoft gives away five-license developer versions of SQL Server with the enterprise editions of Visual Studio 97 and Visual Basic 5.0. But Oracle7 for Windows NT is gaining fast. Oracle's Web site devotes a subsection (http://www.oracle. com/NT) to NT, where, among other things, you can order Oracle on an NT CD-ROM, as 50,000 others have before you. The CD-ROM includes evaluation versions of a variety of Oracle NT products,
including the Oracle7 Workgroup Server. (For a comparison of Oracle7 Server for
NT and Oracle7 Workgroup Server, see my article "Exploring Oracle7 Server
for Windows NT," December 1996.)
Although the new Oracle7 products are easy to install, many users are
discovering that Oracle isn't as easy to use as SQL Server. A reasonably savvy
user with some Microsoft Access experience can export Access databases or create
new SQL Server databases without taking any formal SQL Server training (although
I don't recommend doing so).
Don't expect the same level of install-and-go simplicity with Oracle, even
with the Oracle7 Workgroup Server. Oracle has spent considerable time making
sure that Oracle is consistent across platforms. This consistency means that NT
users face the same hundreds of tuning parameters that database administrators
(DBAs) face on other platforms. In other words, you probably can't be a
weekend-warrior-style Oracle DBA. Good Oracle DBAs command a lot of respect--and
a lot of money. If you need to manage an Oracle database, plan to get some
training. One obvious avenue for training is Oracle Education (800-633-0575 or
http://education.oracle.com), which offers an array of choices that include
training by instructors, via satellite, on the Web, and through computer-based
tutorials.
I've been gathering reader questions and monitoring Oracle discussion lists
and newsgroups, and I have assembled a list of commonly asked questions about
using Oracle with NT. Some deal with installation, some with tuning, some with
general product information. Consider this article a starting point for
discussion about Oracle and NT, and feel free to submit more questions to me
directly or to the SQL Server section at http://www.winntmag.com/forums. If
readers demonstrate enough interest, perhaps Windows NT Magazine
will set up an Oracle forum on its Web site.
Q: How should I organize my NT accounts to work with Oracle?
A useful approach is to create an NT user account called ORACLE to install
and administer all databases; grant NT Administrator privileges to this account.
Create a local NT group called ORAadmin, for example, into which you add the
ORACLE account and the personal accounts of any NT users who will be
administering the NT databases. Use the ORAadmin group to assign NT file
permissions for all Oracle-related files.
An alternative to setting up a local Oracle DBAs group (which can be quite
restrictive and cumbersome for user account management) is to create a global
group called OraGlobalAdmin. This way, members can administer Oracle databases
across trusted domains without needing to replicate the individual user accounts
from domain to domain.
Q: Besides password and privilege management, what else should I worry
about when I create a new user?
Make sure to explicitly define a user's default Tablespace and temporary
Tablespace (both are the SYSTEM Tablespace by default) to avoid filling up the
SYSTEM Tablespace (in SQL Server, you don't want people storing data in the
Master database). Tablespace is Oracle's term to describe the set of
files that store Oracle data. Tablespaces can contain many entities, including
Tables, indexes, and clusters. Clusters let you tell Oracle to store related
Tables close together.
Also consider assigning profiles to enforce resource limits--to prevent
runaway queries, for example. To assign profiles, you need to include a
RESOURCE_LIMIT=TRUE statement in the database instance's initialization
parameter file. You can set resource limits for connect time, idle time, the
number of sessions, and so forth.
Q: How can I keep track of logon attempts?
Oracle supports auditing of logon attempts, database actions, or specific
database objects (such as salary Tables). The first step in enabling auditing is
to run Oracle's CATAUDIT.SQL script (found in ORANT/RDBMS73/ADMIN, with scores
of other useful scripts). Run the script as the user SYS, and set the
AUDIT_TRAIL parameter in INIT.ORA. Oracle keeps configuration parameters in
INIT.ORA. You'll need to create your parameters, probably by modifying a copy of
the sample template file because that method is generally easier than creating
configuration parameters from scratch. As Screen 1 shows, the sample template
file is INITORCL.ORA, which you can find in the ORANT/DATABASE directory.
AUDIT_TRAIL=DB stores audit information in the database; AUDIT_TRAIL=OS
stores it as an NT file. To enable logon auditing, execute the SQL command AUDIT
SESSION.
Q: How do I load SQL data into my Oracle database?
Use SQL*Loader. It is similar to SQL Server's bulk copy program (bcp); both
let you load data from fixed- or variable-length files into Oracle Tables.
Q: What database block size should I use?
Oracle recommends that your database block size match or be multiples of
your operating system block size. You can make your database block size smallerdown
to a minimum of 4096 bytes under NT 3.x or 4.xbut the performance cost is
significant. Your choice will depend on the type of application you're running.
If you have lots of small transactions, as you do with OLTP, use a small block
size. With fewer but larger transactions, such as in a decision support system
(DSS) application, use a large block size.
Q: What are packages?
A package is a set of related procedures, functions, and other PL/SQL code
that's stored in an Oracle database and that client applications can invoke. You
(as SYS) can find out which packages are available for a given database, by
issuing the SQL statement
SELECT object_name FROM sys.dba_objects WHERE object_type = 'PACKAGE'
To get a list of almost 1500 objects (including Tables, views, indexes,
packages, procedures, triggers, and synonyms), issue the command
SELECT * FROM sys.dba_objects