2. A DDL (Data Definition Language) statement is used to manipulate or
define the storage in the database. These statements are used to create and destroy
tables, tablespaces, datafiles, and so on.
3. A DML (Data Manipulation Language) statement is used to manipulate the
actual data that is stored in the database. These statements are used to input, change,
delete, or retrieve data that is being stored in the database.
4. An OLTP (Online Transaction Processing) system is used to service online
users who are inputting, changing, or deleting data from the database. An OLTP system
is typically characterized by large numbers of online users.
5. A DSS, or Decision Support System, is used to retrieve valuable decision-making
information from a database. The DSS is characterized primarily by read-only queries.
The DSS typically has very few users but long-running operations.
6. Duties of a DBA might include software and hardware installation, configuration
of new hardware and software, security, performance tuning, backup and recovery,
routine maintenance, Troubleshooting, and failure recovery.
7. The most important duty of an Oracle DBA is to maintain the integrity and reliability of the user's data. This means keeping the system running and properly backed up.
2. Intelligent agents allow the Oracle Enterprise Manager to communicate
with the Oracle RDBMS.
3. Oracle utilities include the SQL*Loader, the Export utility, and the
4. TCP/IP, SPX/IPX, named pipes, and DECNet are all protocols supported by SQL*Net.
2. The individuals you need at a sizing effort are the database designer,
the application designer, and a management-staff representative.
3. A good database block size for an OLTP application would be 2,048 bytes.
4. Any cache-hit percentage above 90%. A cache-hit percentage above 90%
means that 90% or more of the reads are found in memory. Logical (memory) reads are
much faster than physical (disk) reads.
5. RAID 1 would use a factor of two times the number of writes and RAID
5 would use a factor of two times the number of writes to calculate the additional
reads and two times the number of writes to calculate writes.
6. You need to know the database size, the I/Os per second, and the RAID
type that you will be using.
7. None. There will always be some page faulting, but you can minimize
8. The maximum CPU utilization in a steady-state situation is 75%.
9. The maximum disk utilization in a steady-state situation is 85%.
10. You should allocate 5.5MB for the Oracle8 executable.
2. Installing software; creating databases, tablespaces, tables, views,
indexes, and accounts; monitoring space and performance; and maintaining the integrity
of the data in the database are all duties of the DBA.
3. Implementing a sound backup and recovery strategy.
4. OSDBA and OSOPER are two OS roles created when the
database is created.
5. This is the instance that is created under NT with the NT Instance Manager
or with ORADIM80.EXE. This service allows you to start and stop the Oracle
instance via the Oracle tools.
6. The TNSNAMES.ORA file is where information is kept about what
Oracle services you can connect to. It also allows aliasing to occur.
7. The TNSNAMES.ORA file can be administered through the Oracle
Network Configuration wizard.
8. The system state can be modified via the ALTER SYSTEM command.
2. The SID is the system identifier. This environment variable is used
to determine which database to connect to.
3. No, you can create or alter a data file to autoextend. This is a new
feature in Oracle.
4. Yes, the Oracle RDBMS supports more than one instance running on a server.
5. Each SID uniquely identifies one database; therefore you can only create
one database per SID. With the Oracle Parallel Server option, you can have multiple
instances (and SIDs) accessing the same database, but for each system it appears
as though there is a one-to-one relationship between the SID and the database.
6. There is no firm limit to the number of databases that can be created on one system. The system resources that each database consumes will be the limiting factor.
2. The limits on the tablespace are OS dependent. A tablespace can consist
of 1,022 datafiles. Under Windows NT, a datafile can be 8GB with a 2KB block size
or 32GB with a 8KB block size, making the maximum size of a tablespace 32TB.
3. You can create more datafiles or alter a datafile to autoextend. This
is a new feature in Oracle.
4. A tablespace can be online or offline.
5. You can have any number of tablespaces in a database. Tablespaces are
usually split by function. Accounting might have a different tablespace from finance,
and so on.
6. The four types of segments are data, index, rollback, and temporary.
2. A log file group is a set of identical redo log files. This provides
redundancy to protect against media failure.
3. The control file is used to keep information about the structure of
the database. When the instance is started, the control file is used to identify
the database files.
4. Yes. In fact, it is a good idea to have more than one control file in
case of media failure.
5. Without the control file, Oracle will not know which datafiles to open.
Oracle cannot start without a control file.
6. The ALTER DATABASE database BACKUP CONTROLFILE command
is used to back up the control file. Using the TO TRACE qualifier generates
the SQL statements necessary to reproduce the control file.
7. Yes, a log switch forces a checkpoint.
8. No, a checkpoint does not cause a log switch.
9. Just as the name indicates, a rollback segment is used to store change
information that can be used in the event of a rollback. It is also used for read
10. The rollback segment dynamically allocates and deallocates space. If the OPTIMAL value is set, the rollback segment will try to stay that size.
2. Import is used to take data from an export file and use that data to
re-create the database's content and structure.
3. SQL*Loader is a very flexible tool that is used to load ASCII or flat-file
data into an Oracle database.
4. Only the Import utility can load export files.
5. Export/Import can be used to back up and reorganize the database, and
to transfer data between systems.
6. The Windows NT version of the Export program is called EXP80.
7. The Windows NT version of the Import program is called IMP80.
8. The Windows NT version of the SQL*Loader program is called SQLLDR80.
9. The fixed record load file has data in columns where the same column
in each record has the same length. The variable record datafile has different records
where the same column may be a different length.
10. The parameter file is a file that contains additional command-line parameters.
2. Each user should have only one account. In cases where a user is serving
dual duties, you might want to assign individual accounts for each duty.
3. System resources are assigned through profiles. You would set the CPU
per session through a profile.
4. Permissions to access certain utilities or tables are assigned through
system privileges. This may be assigned through a role also.
5. The Admin option allows you (as the grantee) to grant a role
or system privilege to other users or roles.
6. Roles are removed via Enterprise Manager, Security Manager, or the DROP
7. Privileges are removed from roles via either Security Manager or the
8. No. Quotas are assigned to a user directly. A quota is given to a user
based on tablespaces.
9. Temporary tablespaces as well as default tablespaces are assigned to
a user. These values can be modified via Enterprise Manager, Security Manager, or
the ALTER USER command.
10. CREATE LIKE is a shortcut that copies a user's properties to a blank user. This lets you copy users as templates. It is a very nice feature to use.
2. The LGWR (log writer) process is used to handle all the log writes.
The server processes log write information into the log buffer, and the log writer
reads from the log buffer and writes to disk.
3. The CKPT (checkpoint) process is used to help the DBWR process when
4. If you are running with the Oracle Parallel Server option, you'll have
LCKn processes running in your system.
5. A three-tier system includes a middleware piece. This middleware piece
is typically a transaction monitor (TM).
6. The I/O subsystem is typically the limiting factor in your system.
7. The order of precedence is as follows: Hints are used first, then table
definitions, and finally, if none of those are used, the parameter file is used.
8. No, even if you have only one CPU, you'll see a benefit from using the
Parallel Query option. Without the parallel query, your CPU might be mostly idle,
waiting for I/Os to complete.
9. Under Windows NT, the Oracle server uses threads. In most of this book,
however, the term process refers to both processes and threads.
10. The multithreaded server takes most of its memory from the shared pool.
2. A row in a table is an individual record. A row is the specific data
that has been added to the database.
3. A row piece consists of a row header and the row data.
4. An object consists of an attribute and a method.
5. A nested table is a table that appears as a column in another table.
6. A partitioned table is a table where the data is divided into smaller
pieces based on the data itself.
7. Oracle8 supports range partitioning. The data is divided into various
chunks based on ranges of data in one or more columns.
8. A view is a logical representation of a subset or superset of information
in schema objects.
9. When creating a table, the STORAGE clause is used to define
how the table will grow. Because tables are made of extents, the STORAGE
clause defines the extent growth parameters.
10. A synonym is simply an alias to another object in the database. This object can be a table, view, sequence, or program unit.
2. Yes, the parallel-index creation facility can greatly enhance the performance
of the index-creation operation.
3. A B*-tree index is a binary tree structure that can be used
to find data quickly. A binary comparison is made and the tree is traversed based
on that selection.
4. Yes. In fact, there are two ways to partition an index. A local partitioned
index is an individual index that resides on a partition and indexes the data on
that partition, whereas a global partitioned index is a single index over all of
the partitioned data. A global partitioned index can in turn be partitioned itself.
5. An index-only table is an index where the column data resides in the
leaf block of the index. This schema can be very useful under certain conditions.
6. A bitmap index is an index where the index values are kept in a bitmap.
A 1 indicates that the key value is present; a 0 indicates that
the key value is not present.
7. A sequence can be used to provide new account numbers, order numbers,
invoice numbers, and so on. Any number that must be unique is a good candidate for
8. A sequence can be as many as 38 digits in length--bigger than you will
9. An index does not need to be unique, but it can be.
10. An index can be used to enforce uniqueness on a column or set of columns.
2. Criterion 1: The cluster key value is unique. Criterion 2: The majority
of queries are equality queries on the cluster key.
3. By using a cluster on a set of tables that are primarily accessed via
a join operation, the data from all the tables that will be used together is read
into the SGA together, thus reducing I/O and improving performance.
4. Clustering is not a good idea if you are doing a lot of inserts. Because
the data is stored together inserts cause more overhead than an insert to a standard
table. Also, if you do not usually access the data with joins on the cluster key
in the cluster, you will see no benefit from clustering.
5. A stored procedure is a set of SQL statements that are pre-parsed and
stored in the database. When the stored procedure is invoked, only the input and
output data is passed; the SQL statements are not transferred or parsed.
6. By having the SQL pre-parsed and stored in the database, less data needs
to be transmitted, less work is done by Oracle, and a better library cache hit rate
is achieved, all resulting in better performance.
7. Stored procedures typically perform more than just database operations;
conditionals and logical operations are usually a big part of stored procedures.
8. Database links are used to simplify access to a remote database and to hide network details from the user. When you have provided a link, users can easily select data from a database on another server elsewhere in the network.
2. Various operations such as backups, exports, imports, and SQL statements
can be scheduled.
3. OS operations can also be scheduled using the job queue.
4. It is not necessary for a new account to be created, but it is not a
5. The NT user right "login as a batch job" must be enabled for
the user who will be running the batch queue.
6. Yes, queued jobs can run on any system that is properly configured for
the Enterprise Manager.
7. Jobs can be scheduled to run once, or at a regular interval that can
be daily, weekly, or monthly.
8. You need to make sure that the preferred credentials (account information)
are properly set up in the Enterprise Manager in order for job queuing to work.
9. Auditing might need to be enabled if you suspect some suspicious behavior
on your system.
10. Auditing can consume a lot of system resources. Be careful what you audit and for how long.
2. Log file losses cannot be recovered from but do not cause system failure.
If a log file is damaged and the datafiles are okay, you should shut down your system
and perform an immediate backup.
3. Yes, if a datafile is damaged it can be restored from a backup, and
the changes can be restored from the redo log files and archive log files.
4. If you are not running in ARCHIVELOG mode, you can perform
an instance recovery but you cannot recover any changes that are older than the online
redo log files. If a datafile is damaged, you cannot recover to this point, only
to the last full backup.
5. The different types of backups that can be done are full backups, tablespace
backups, archive log backups, control file backups, and datafile backups.
6. To completely back up your system, you must back up the datafiles and
the control files. If you are running in ARCHIVELOG mode, you should also
back up the archive log files.
7. RAID stands for Redundant Array of Inexpensive Disks. This hardware
or software allows you to stripe, mirror, or provide other fault-tolerant striping
to your disks.
8. No, the NT Backup Manager can only back up the local system.
9. The archive log files are essentially a copy of the redo log file after
a log switch. When a log switch occurs, the old redo log file is copied to an archive
10. An offline backup occurs when the Oracle instance is shut down. An online backup occurs when the instance is running.
2. All committed transactions can be recovered from an instance failure.
3. All committed transactions can be recovered from a media failure if
you are running in ARCHIVELOG mode.
4. ARCHIVELOG mode causes all redo log files to be saved whenever
a log switch occurs. These archived log files can be used to restore the database
5. A dirty buffer is a buffer in the SGA that has been modified but has
not been written to disk.
6. A checkpoint is a process that periodically causes all dirty buffers
to be written to disk.
7. The database and online redo log files are necessary for instance recovery.
8. The database, the online redo log files, the backup files, and the archive
log files are all necessary for media recovery.
9. A point-in-time recovery operation recovers only up to a specified point
in time. This time can be specified as a time or a system change number (SCN).
10. A point-in-time recovery is used to recover up to a time just before a software or operator problem corrupted the database. This prevents the recovery process from running the command that corrupted the database in the first place.
2. An updateable snapshot is a replication method where both the master
and the replication systems are updateable. The replicated systems synchronize themselves
on a regular basis.
3. Realtime data replication causes all systems to be updated immediately
after the change is made. This replication method needs all systems to be up and
running for it to work effectively.
4. The master site is the system from which the data is replicated. Because
it is updateable and holds the original data, it is considered the master.
5. The snapshot site is the system that receives the snapshots. If the
snapshot site is read-only, no updates are allowed and the site is considered a slave
6. Read-only table snapshots can be used in a variety of applications:
retail price lists, lookup tables, and so on.
7. Advanced replication can be used for sites that need remote updateable
databases and as a failover system.
8. With a complete refresh, the entire snapshot is updated. With a fast
refresh, only the changes are updated.
9. A standby database is a database that is constantly in recovery mode,
recovering archive log files from the primary database. In the event of a failure,
the standby database can immediately substitute for the primary database. The standby
database is used to provide immediate restoration of service in the event of a primary
system failure. In a matter of minutes or even seconds, this system can be operational.
10. A read-only tablespace can be used to prevent users from updating critical data as well as to reduce recovery time, because no instance recovery is needed on a read-only tablespace.
2. The server interconnect provides two functions: to communicate locking
information and to act as a system heartbeat.
3. The shared-disk subsystem is what allows OPS to work. Each member of
the cluster must be able to access all datafiles and redo log files at all times.
Thus, the disk subsystem is shared.
4. DLM stands for Distributed Lock Manager. The DLM passes lock
information between members of the cluster.
5. PCM stands for Parallel Cache Management. This term is used primarily
to describe the Parallel Cache Management (PCM) locks. These locks are used to keep
the various nodes in the cluster from overwriting each other by locking data that
is being modified.
6. The Parallel Query option is used to parallelize certain SQL operations
in order to improve performance.
7. The Parallel Query option improves performance by allowing multiple
threads of execution to perform one task. Because the operation is split, the CPU(s)
can continue processing while the system waits for I/Os to complete.
8. The best way to set the degree of parallelism is via a hint in the SQL
9. The secondary way to set the degree of parallelism is to set the parallelism
on the table with the ALTER TABLE command.
10. Parallelizable operations include table scans, joins, and the recovery operation.
2. You can either query the V$ tables directly or use the UTLBSTAT
and UTLESTAT tools to determine the shared pool cache-hit ratio.
3. I recommend that a disk drive doing random I/Os should not be pushed
harder than 60-70 I/Os per second.
4. I recommend that a disk drive doing sequential I/Os should not be pushed
harder than 90-100 I/Os per second.
5. Latency refers to the length of the response time. Disk latency refers
to how long it takes for an I/O to complete. Remember the knee of the curve theory
from Day 4.
6. The buffer cache-hit ratio is determined from this equation:
Cache hit ratio = 1 - (PHYSICAL READS / (DB BLOCKB GETS + CONSISTENT GETS))
7. You can determine whether the shared pool is large enough by examining
both the data dictionary cache-hit ratio and the library cache-hit ratio.
8. The effectiveness of an index depends on the index being present and
the application forming SQL statements that can take advantage of that index.
9. Always run UTLBSTAT.SQL first. This script sets the system
up to calculate data with UTLESTAT.SQL.
10. If you have a system that is performing DSS-type queries and large table scans, you could benefit from a large block size. Also, if you have large rows, you might benefit from a larger block size.
2. CORBA stands for the Common Object Request Broker Architecture and is
a specification for object communications.
3. HTTP, or Hypertext Transport Protocol, is the protocol used in order
for Web browsers to communicate with Web servers.
4. HTML, or Hypertext Markup Language, is the language used to define Web
5. A cartridge is a plug-in object that performs an application function.
Cartridges usually serve one function but work together with other cartridges to
serve many functions.
6. A static Web page is one that does not dynamically change. It only changes
when it is replaced.
7. The Web Publishing Assistant can create only static Web pages.
8. NC stands for Network Computer. This computer is designed to
access the World Wide Web and run network applications.
9. The Oracle Universal server is designed to handle all traditional data
types and new data types such as video, audio, text, and spatial data.
10. Any type of application that requires access to a central server would be suitable for running on an NC because no local data storage is required.
© Copyright, Macmillan Computer Publishing. All rights reserved.