New Features for Administrators - Question 047

Consider the following statement:

SQL> EXECUTE DBMS _STATS.GATHER_SCHEMA_STATS (-
2> ownname => 'OE', -
3> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
4> method opt => 'for all columns size AUTO');

What is the effect of 'for all columns size AUTO' of the METHOD_OPT option?

A. The Oracle server creates a new histogram based on existing histogram definitions for all table, column, and index statistics for the OE schema.

B. The Oracle server creates a histogram based on data distribution regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.

C. The Oracle server creates a histogram based on data distribution and application usage of the column/s for all table, column, and index statistics for the OE schema.

D. The Oracle server creates a histogram based on application usage, regardless of data distribution, for all table, column, and index statistics for the OE schema.

Answer: C

Explanation:

The Oracle server creates a histogram based on data distribution and application usage of the column/s for all table, column, and index statistics for the OE schema.

Incorrect Answers

A: The Oracle server does not create a new histogram based on existing histogram.

B: Histogram creation is not regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.

D: The Oracle server creates a histogram not only based on application usage, but based on data distribution also.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 220-222

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 180-181

Chapter 6: Oracle9i SQL, PL/SQL New Features

New Features for Administrators - Question 046

Examine the list of variables and their data types:

Name Data Type

TS, TS1 TIMESTAMP
TSZ TIMESTAMP WITH TIME ZONE
TLZ TIMESTAMP WITH LOCAL TIME ZONE
IYM INTERVAL YEAR TO MONTH
IDS INTERVAL DAY TO SECOND

Which three expressions using the new date and time data types are valid? (Choose three.)

A. IDS* 2

B. TS + IYM

C. TS -TS1

D. IDS - TS

E. IDS + IYM

Answer: A, B, E

Explanation:

IDS*2, TS+IYM and IDS+IYM are valid new date and time data types.

Incorrect Answers

C: You cannot subtract timestamps.

D: It’s not possible to subtract timestamp from the interval day to second.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 266-271

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-135

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 045

What is true if you want to switch undo tablespaces from the current one, called UNDO1, to a new one called UNDO2?

A. It is NOT possible to switch unless no active transaction exists in UNDO1.

B. It is possible to switch to UNDO2, but current active transactions will abort.

C. It is possible to switch to UNDO2; current active transactions will be automatically migrated to UNDO2.

D. It is possible to switch to UNDO2; only current active transactions will continue to execute inside UNDO1

Answer: D

Explanation:

You can switch undo tablespaces from the current one, called UNDO1, to a new one called UNDO2. Only current active transactions will continue to execute inside UNDO1, all new transactions will be assigned to the new undo tablespace.

Incorrect Answers

A: You can switch undo tablespaces while active transactions will run in the old undo tablespace. All new transactions will be assigned to the new undo tablespace.

B: Current active transactions will abort if you switched undo tablespaces.

C: Current active transactions will continue to execute inside UNDO1 till they commit or roll back. They will not be automatically migrated to UNDO2.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-166

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 044

Which statement correctly describes the function of the Oracle9i Cache Fusion feature?

A. It provides each session with its own view of the database at a different point in the past.

B. It enables you to execute scalable applications on a clustered database without having to partition the users or the database tables.

C. It lets you dynamically reassign memory in your database buffer cache to different block buffer sizes.

D. It allows you to add new sites to multimaster replication environment without quiescing the master definition site.

Answer: B

Explanation:

The Oracle9i Cache Fusion allows you to execute scalable applications on a clustered database without having to partition the users or the database tables.

Incorrect Answers

A: It does not provide each session with its own view of the database at a different point in the past.

C: The Oracle9i Cache Fusion feature does not dynamically reassign memory in your database buffer cache to different block buffer sizes.

D: It does not provide you ability to add new sites to multimaster replication environment without quiescing the master definition site.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 223-230

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 193-206

Chapter 7: Oracle9i Real Application Clusters

New Features for Administrators - Question 043

Which three attributes of the LOG_ARCHIVE_DEST_n initialization parameter control the data availability mode of a Data Guard standby database? (Choose three.)

A. SYNC or ASYNC to identify the network transmission mode

B. LGWR or ARCH to identify the primary database process responsible for sending redo information to the standby

C. AFFIRM or NOAFFIRM to control whether log archiving disk write operations are to be performed synchronously or asynchronously

D. PROTECTED or UNPROTECTED to control the degree of divergence and data loss at the standby database

Answer: A, B, C

Explanation:

The LOG_ARCHIVE_DEST_n initialization parameter control the data availability mode of a Data Guard standby database with three attributes: SYNC or ASYNC for the network transmission mode, LGWR or ARCH to identify the primary database process responsible for sending redo information to the standby and AFFIRM or NOAFFIRM to control whether log archiving disk write operations are to be performed synchronously or asynchronously.

Incorrect Answers

D: There is no PROTECTED or UNPROTECTED attribute for the LOG_ARCHIVE_DEST_n initialization parameter.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 51-74

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 042

There is more than one way to set the server to detect and affect long running operations automatically. What is the best choice if you want to reduce the impact of long running operations on other users without aborting the long running operations?

A. Define user profiles and set the CPU_PER_CALL limit.

B. Define a SWITCH_TIME for a plan in the Resource Manager.

C. Create a batch job that checks V$SESSION_LONGOPS; the batch job alters the session priority of the long running operations.

D. Create a user defined event in the Oracle Enterprise Manager, which monitors V$SESSION_LONGOPS.

Answer: B

Explanation:

You can define a SWITCH_TIME for a plan in the Resource Manager to reduce the impact of long running operations on other users without aborting the long running operations.

Incorrect Answers

A: You cannot do this with CPU_PER_CALL limit.

C: You don’t need to use a batch job to check V$SESSION_LONGOPS for this purpose.

D: It can be done with a SWITCH_TIME for a plan in the Resource Manager, you don’t need to monitor V$SESSION_LONGOPS.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129

Chapter 3: Manageability Enhancements

New Features for Administrators - Question 041

Online index rebuild functionality has been extended to include which four index structures? (Choose four.)

A. Bitmap indexes

B. Reverse key indexes

C. Function-based indexes

D. Key-compressed indexes on regular tables

E. Key-compressed indexes on IOT (including secondary indexes)

Answer: B, C, D, E

Explanation:

It’s possible to rebuild reverse key, function-based, key-compressed indexes on regular tables and on IOT (including secondary indexes).

Incorrect Answers

A: Oracle9i does not currently support online index rebuilding for bitmap or partitioned local and global indexes.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 74-83

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 040

Which three table transformations can be done using online redefinition of tables?

(Choose three.)

A. Delete rows

B. Drop a column

C. Change a heap table to an index-organized table

D. Change the data type of a column from LONG to BLOB

E. Change a range-partitioned table to a list-partitioned table

Answer: B, C, E

Explanation:

You can drop a non-primary key column, change a heap table to an index-organized table and vice versa or change a partitioning method for the table online.

Incorrect Answers

A: Rows deleting is a DML operation. It’s not a table transformation.

D: It’s not possible to redefine the table online with a LONG column.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 74-83

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 039

The multithreaded agent architecture for heterogeneous services is similar to the Oracle multithreaded server architecture. The principal difference is that it uses threads instead of processes.

What are three kinds of threads that it uses? (Choose three.)

A. TCP

B. Task

C. Monitor

D. Dispatcher

E. Shared Server

F. Shutdown Address

Answer: B, C, D

Explanation:

The multithreaded Heterogeneous Service (HS) Agents architecture has three kinds of threads: a single monitor thread, several dispatcher threads and several task threads.

Incorrect Answers

A: The multithreaded HS Agents do not use TCP thread.

E: They do not use Shared Server thread.

F: The multithreaded HS Agents do not use Shutdown Address thread.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 148-150

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 59-60

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 038

Which operation causes an index to be considered "used" while monitoring is turned on for that particular index?

A. Only when the index is specified in a hint

B. When data is fetched by an execution plan that uses that index

C. When a statement is executed and the execution plan contains a reference to the index

D. When a statement is parsed and the resulting execution plan contains a reference to the index

Answer: D

Explanation:

When a statement is parsed and the resulting execution plan contains a reference to the index an index will be considered "used".

Incorrect Answers

A: It will be considered as “used” when a statement is parsed and the resulting execution plan contains a reference to the index, not only when the index is specified in a hint.

B: An index will be considered "used" when a statement is parsed and the resulting execution plan contains a reference to the index, not when data is fetched by an execution plan that uses that index.

C: It will be considered as “used” when a statement is parsed and the resulting execution plan contains a reference to the index, not when a statement is executed and the execution plan contains a reference to the index.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 56-59

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 037

A switchover to a standby database differs from a failover to the standby in a number of ways.

What are two of the key differences that characterize a switchover? (Choose two.)

A. The operation is planned and causes no data loss.

B. The standby database must be running in a no data divergence mode.

C. The old primary database can take on the standby role and be available for a future switchover.

D. The primary and standby database must be using exactly the same release and patch level of Oracle9i.

E. Either the primary database's online or archived log files, but not necessarily both, must be available.

Answer: A, C

Explanation:

Switchover is planned operation and should not cause any data losses. During the switchover the old primary database can take on the standby role and be available for a future switchover.

Incorrect Answers

B: The standby database has not to run in a no data divergence mode.

D: It is not required that the primary and standby database must be using exactly the same release and patch level of Oracle9i.

E: Online and archived log files of the primary database need to be available for the standby database.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 51-74

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 036

When enabling Flashback using a wall-clock time, within what period does the database choose a System Commit Number (SCN)?

A. One minute of the time specified

B. One second of the time specified

C. Five minutes of the time specified

D. Five seconds of the time specified

Answer: C

Explanation:

The database chooses a System Commit Number within a five minutes of the time specified.

Incorrect Answers

A: It chooses a SCN within a five, not one minute, of the time specified.

B: The database does not choose a System Commit Number within an one second of the time specified.

D: The database chooses a System Commit Number within a five minutes of the time specified, not 5 seconds.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 45-47

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 63-68

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 035

User defined events in Oracle Enterprise Manager return status, and possibly values, to the Oracle Enterprise Manager Event Monitor. What are user defined events?

A. Database based scripts (PL/SQL, Java) run by the Agent

B. Database Event Triggers, which issue Alerts to the Agent

C. Scripts run by the Agent in any language on the host server

D. User written PL/SQL programs in the database, which the OEM console can execute and display the results

Answer: C

Explanation:

Scripts run by the Agent in any language on the host server are user defined events. You have the capability to create custom scripts via the Diagnostic Pack that monitor conditions that you specify.

Incorrect Answers

A: Database based scripts (PL/SQL, Java) run by the Agent are not user defined events.

B: Database Event Triggers, which issue Alerts to the Agent, are not considered as user defined events.

D: Scripts run by the Agent in any language on the host server, not user written PL/SQL programs in the database, are user defined events.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 189-201

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 219-220

Chapter 7: Oracle9i Real Application Clusters

New Features for Administrators - Question 034

Oracle9i offers security improvements in the middle tier. One of these improvements is the Secure Application role.

What are three key benefits of this improvement? (Choose three).

A. The role is enabled through a package.

B. The password is hidden within the application itself.

C. It uses the SYS_CONTEXT mechanism to authenticate user access.

D. It removes the requirement to embed a password in the application itself.

Answer: A, C, D

Explanation:

Oracle9i allows you to enable the role through a package. It uses the SYS_CONTEXT mechanism to authenticate user access. The Secure Application role removes the requirement to embed a password in the application itself. When your application starts up, you should enable the roles for application by using the SET ROLE statement. Prior to Oracle9i, your application would have to try to hide this password by encrypting it. The application itself became the weakest link in the chain. If someone discovered the password, any application could enable the role and gain access to the information. Any user that has access to the application code could in fact discover the embedded password. In Oracle9i, the SET ROLE does not use a password to enable a role; instead, it invokes the associated procedure that authenticates the role. This procedure can also use SYS_CONTEXT to access session information and set up fine-grained access control.

Incorrect Answers

B: The application does not have to store the password itself.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 10-24

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-135

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 033

What does Block Media Recovery (BMR) do?

A. Does only a recovery of the specified blocks

B. Replaces bad blocks by substituting good blocks

C. Uses the DBMS_REPAIR package to fix block corruptions

D. Does a restore and a recovery of the specified blocks

Answer: D

Explanation:

Block Media Recovery restores and recovers the specified blocks. You can use the new BLOCKRECOVER statement to perform BMR.

Incorrect Answers

A: It does not only recover the specific blocks but also restores blocks.

B: It does not replace bad blocks by substituting good blocks.

C: Block Media Recovery does not use the DBMS_REPAIR package to fix block corruptions.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 186

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 84-86

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 032

What are three characteristics of the Data Guard Broker? (Choose three.)

A. It runs as a process called DMON

B. It can be controlled through the Data Guard Manager GUI and command-line interface

C. It is started by setting the DRS_START parameter to TRUE in your initialization file

D. It uses a configuration file stored on the primary database server and, optionally, on one of your standby database servers

Answer: A, B, C

Explanation:

The Data Guard Broker has next characteristics: it runs as a process called DMON, it can be controlled through the Data Guard Manager GUI and command-line interface and it is started by setting the DRS_START parameter to TRUE in your initialization file. DRS_START enables Oracle to determine whether or not the DRMON (Disaster Recovery Monitor) process should be started. DRMON is a non-fatal Oracle background process and exists as long as the instance exists.

Incorrect Answers

D: It does not use a configuration file stored on the primary database server and, optionally, on one of your standby database servers

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 51-74

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 031

You use the TABLESPACES option of the Export command to export, not transport, the contents of a set of tablespaces. What are the only contents of the dump file?

A. 1. Non-partitioned tables in the tablespace set
2. Partitioned tables that have all their partitions in the tablespace set
3. Indexes on all the exported tables

B. 1. Non-partitioned tables in the tablespace set
2. Partitioned tables that have at least one partition in the tablespace set.
3. Indexes on all the exported tables

C. 1. Non-partitioned tables in the tablespace set
2. Partitioned tables that have all their partitions in the tablespace set
3. Indexes on the exported tables if the indexes are in the tablespace set or have at least one partition in the tablespace set

D. 1. Non-partitioned tables in the tablespace set
2. Partitioned tables that have at least one partition in the tablespace set
3. Tables (including all partitions, if they exist) that have an index in the tablespace set

4. Indexes on all the exported tables

Answer: B

Explanation:

If you use the TABLESPACES option of the Export command to export the contents of a set of tablespaces, content of dump file will be: non-partitioned tables in the tablespace set, partitioned tables that have at least one partition in the tablespace set, indexes on all the exported tables.

Incorrect Answers

A: It’s not required that partitioned tables have to have ALL their partitions in the tablespace set them to be exported with this option.

C: It’s not required that partitioned tables have to have ALL their partitions in the tablespace set them to be exported with this option. Also ALL indexes on all the exported tables will be exported.

D: Tables that have an index in the tablespace set will not be exported.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 49-51

Chapter 2: Availability Enhancements

New Features for Administrators - Question 030

Which three table transformations can be done by using online redefinition of tables? (Choose three.)

A. Delete rows

B. Rename a column

C. Change a heap table to an index-organized table

D. Change the data type of a column from LONG to BLOB

E. Change the data type of a column from DATE to CHAR

Answer: B, C, E

Explanation:

You can add or rename columns, change a heap table to an index-organized table and change the data type of a column from DATE to CHAR by using online redefinition of tables.

Incorrect Answers

A: Rows deleting is DML operation. It’s not a table transformation operation.

D: It’s not possible to redefine the table online with a LONG column.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 74-83

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 029

Examine this statement, which creates a Cartesian product of the COUNTRIES and REGION tables:

SQL> SELECT c.country_name,
2> r.region name
3> FROM countries c, region r;

Oracle9i SQL: 1999 syntax supports the same functionality with which join type?

A. Equijoin

B. Cross join

C. Merge join

D. Natural join

Answer: B

Explanation:

The CROSS JOIN operation creates a Cartesian product of two tables. It’s the same operation performed when you specify two or more comma-separated tables in the FROM clause without specifying a join condition.

Incorrect Answers

A: The EQUIJOIN operation requires usage of equity operation.

C: The MERGE JOIN operation is used to either update existing rows or insert new rows into a destination table. You update the row in the destination table if there is a matching row in the destination table; otherwise, you insert a new row into the destination table.

D: The NATURAL JOIN matches rows that have equal values in all columns that have the same name.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 242-249

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 158-161

Chapter 6: Oracle9i SQL, PL/SQL New Features

New Features for Administrators - Question 028

For the best performance, what should be done with blocks transferred using the Cache Fusion algorithm in a Real Application Clusters database?

A. They should be transferred across a high-speed cluster interconnect.

B. They should be written to disk by the sending instance and read from disk by the receiving instance.

C. They should be granted, by the Global Cache Service, an exclusive mode resource for each instance involved.

D. They should be transferred through the cache area of the Global Resource Directory on the instance where the block is mastered.

Answer: A

Explanation:

The blocks transferred using the Cache Fusion algorithm in a Real Application Clusters database should be transferred across a high-speed cluster interconnect to increase the performance.

Incorrect Answers

B: They should not be written to disk by the sending instance and read from disk by the receiving instance. Cache Fusion algorithm does not use disk read/write operations to transfer blocks.

C: It’s not possible to grant by the Global Cache Service an exclusive mode resource for EACH instance involved. Only one instance can have exclusive mode.

D: It’s not required that these blocks should be transferred through the cache area of the Global Resource Directory on the instance where the block is mastered.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 223-230

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 193-206

Chapter 7: Oracle9i Real Application Clusters

New Features for Administrators - Question 027

Which two are true regarding the Automatic Undo Management feature? (Choose two).

A. PMON is responsible for shrinking undo segments as needed.

B. SMON is responsible for shrinking undo segments as needed.

C. To use this feature, you must create undo segments into an undo tablespace.

D. Oracle9i automatically creates a fixed number of undo segments per undo tablespace.

E. In a Real Application Clusters environment, you must create one undo tablespace per opened instance.

Answer: B, D

Explanation:

If you are using the Automatic Undo Management feature, SMON is responsible for shrinking undo segments as needed and Oracle9i automatically creates a fixed number of undo segments per undo tablespace.

Incorrect Answers

A: SMON, not PMON background process, is responsible for shrinking undo segments as needed.

C: You have not to create undo segments into an undo tablespace: it will be done by Oracle automatically.

E: You don’t have to create one undo tablespace per open instance in a Real Application Clusters environment.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-164

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 026

Examine this fragment from a SQL*Plus session:

SQL> SELECT name, value FROM v$sysstat
2> WHERE name LIKE 'work area executions%';

NAME VALUE

-------------------------------- -------

work area executions - optimal 1544
work area executions - onepass 11
work area executions - multipass 1038

What can you conclude about the setting of the PGA_AGGREGATE_TARGET initialization parameter?

A. You cannot conclude anything because these statistics are not related to the PGA_AGGREGATE_TARGET parameter.

B. It may be set too low because there are many more multipass work area executions than one pass executions.

C. It may be set too high because there are many more multipass work area executions than one pass executions.

D. It is probably at its optimal setting because the ratio of one pass to multipass work area executions is below 0.10 (ten percent).

Answer: B

Explanation:

If the PGA_AGGREGATE_TARGET initialization parameter is set too low there will be more multipass work area executions than one pass executions.

Incorrect Answers

A: These statistics are related to the PGA_AGGREGATE_TARGET parameter.

C: If you set the PGA_AGGREGATE_TARGET initialization parameter is set too high there will be more one pass work area executions than multipass executions.

D: It’s not optimal setting because there are many more multipass work area executions than one pass executions.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 176-182

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 025

What are two key features of Automatic Undo Management? (Choose two.)

A. Oracle manages all undo space automatically.

B. The SYSTEM tablespace is never used to store automatic undo segments.

C. Undo tablespaces contain all undo information not stored in the SYSTEM rollback segment.

D. Rollback segments are gradually replaced with automatic undo segments as the instance gathers statistics about rollback requirements.

E. The number of automatic undo segments available to an instance is determined by the number of undo tablespaces defined in its initialization parameter file.

Answer: A, C

Explanation:

There are two key features of Automatic Undo Management: Oracle manages all undo space automatically and undo tablespaces contain all undo information not stored in the SYSTEM rollback segment.

Incorrect Answers

B: The SYSTEM rollback segment is created automatically during database creation and is managed automatically just as it was in previous versions.

D: AUM creates automatic undo segments itself based on gathered statistics about rollback requirements. Regular rollback segments are not gradually replaced with automatic undo segments, because these types of segments cannot coexist in one database instance.

E: The number of automatic undo segments available to an instance is not determined by the number of undo tablespaces defined in its initialization parameter file: AUM performs this task.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-164

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 024

Examine this startup script called startmydb.sql:

CONNECT myid/mypwd AS SYSDBA
STARTUP
EXIT

In Oracle8i, you could have executed this script by invoking Server Manager with the command:

svrmgrl @startmydb.sql

Which alternative command could you use to run the startmydb.sql script with SQL*Plus to start your Oracle9i database?

A. sqlplus @startmydb.sql

B. sqlplus -Sstartmydb.sql

C. sqlplus -S @startmydb.sql

D. sqlplus /NOLOG @startmydb.sql

Answer: D

Explanation:

This command is alternate command to start your Oracle9i database. The Server Manager is not supported in Oarcle9i any more, so you need to use SQL*Plus for administration tasks, as start or stop of database, also.

Incorrect Answers

A: The NOLOG option must be used to run this script because the instance is not started yet.

B: Script that need to be executed can be started with @ symbol, not with –S.

C: The S option cannot be used to run the startmydb.sql script.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 4-10

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-139

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 023

The DBMS_STATS.GATHER_SYSTEM_STATS routine collects system statistics in a user-defined time frame.

Which four statements are true of the GATHER_SYSTEM_STATS routine? (Choose four.)

A. You can use the GATHERING_MODE => 'END' option to end system statistics collection.

B. You can use the INTERVAL option to specify a fixed interval to collect statistics.

C. You can use the GATHERING_MODE => 'START' option to enable manual statistics collection.

D. The routine allows the optimizer to consider a system's CPU and I/O utilization and performance.

E. The routine allows the optimizer to invalidate existing cached plans.

Answer: A, B, C, D

Explanation:

You can use the GATHERING_MODE => 'END' option to end system statistics collection, the GATHERING_MODE => 'START' option to enable manual statistics collection. The INTERVAL option is used to specify a fixed interval to collect statistics. The routine allows the optimizer to consider a system's CPU and I/O utilization and performance.

Incorrect Answers

E: The routine does not allow the optimizer to invalidate existing cached plans.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 220-222

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 180-185

Chapter 6: Oracle9i SQL, PL/SQL New Features

New Features for Administrators - Question 022

To increase the availability of an Advanced Replication environment, you can add a new master to a replication group without quiescing the group. This is achieved ____________.

A. Only when the new master site already has existing replication groups

B. By issuing a single command to create, populate, and activate the new master

C. When the master definition site is not the same for all of the master groups

D. Without impacting end users who are executing data manipulation language (DML) commands on the replicated tables

Answer: D

Explanation:

It’s possible to add a new master to a replication group without quiescing the group without impacting end users who are executing data manipulation language (DML) commands on the replicated tables.

Incorrect Answers

A: The new master site already may not have existing replication groups to achieve this result.

B: You cannot add a new master to a replication group without quiescing the group by issuing a single command to create, populate, and activate the new master.

C: When the master definition site is not the same for all of the master groups you cannot add a new master to a replication group without quiescing the group.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 83-93

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 146-147

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 021

The CURSOR_SHARING parameter is set to SIMILAR at the instance level and you issue these SELECT statements in the order shown below:

SELECT * FROM employees WHERE department _id=50;

SELECT * FROM employees WHERE department_id=70;

There is an index on the DEPARTMENT ID column of the 1,000,000 row EMPLOYEES table. Ninety percent of the employees are part of DEPARTMENT_ID 50.

In which two scenarios will Oracle9i share the corresponding cursors for the two statements? (Choose two.)

A. You are using the rule-based optimizer.

B. You are using the cost-based optimizer with up-to-date statistics but with no histograms.

C. You are using the cost-based optimizer with up-to-date statistics and histograms computed for the DEPARTMENT_ID column.

D. You are using the cost-based optimizer with up-to-date statistics and histograms computed for the DEPARTMENT_ID column and system statistics gathered.

Answer: A, B

Explanation:

Oracle9i will share the corresponding cursors for the two statements if you are using the rule-based optimizer or the cost-based optimizer with up-to-date statistics but with no histograms.

Incorrect Answers

C: Oracle9i will not share the corresponding cursors if you are using the cost-based optimizer with up-to-date statistics and histograms computed for the DEPARTMENT_ID column.

D: The corresponded cursors will not be shared by Oracle if you use the cost-based optimizer with up-to-date statistics and histograms computed for the DEPARTMENT_ID column and system statistics gathered.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 57-59

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 020

Why can setting too low a value for the FAST_START_MTTR_TARGET parameter reduce your overall database performance?

A. Data blocks have to be written more frequently by DBWR.

B. Redo blocks have to be written more frequently by LGWR.

C. You need to substantially increase the size of your database buffer cache to hold the additional checkpoint records.

D. You need to substantially increase the size of your redo buffer cache to hold the additional checkpoint records.

Answer: A

Explanation:

If you set a value for the FAST_START_MTTR_TARGET too low DBWR will write the data blocks more frequently to reduce recovery time in accordance with the FAST_START_MTTR_TARGET parameter. It can cause the overall database performance degradation.

Incorrect Answers

B: It will not make LGWR to write redo blocks more frequently: data blocks will be written more frequently by DBWR.

C: You don’t need to substantially increase the size of your database buffer cache to hold the additional checkpoint records.

D: You don’t need to substantially increase the size of your redo buffer cache to hold the additional checkpoint records.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 42-45

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 73-75

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 019

You are attempting to create an Oracle-Managed Files (OMF) tablespace in a production database with the following statement and receive the following error message:

CREATE TABLESPACE tbsl;

ORA-02199: missing DATAFILE/TEMPFILE clause

oerr ora 2199

02199, 00000, "missing DATAFILE/TEMPFILE clause"

*Cause: A CREATE TABLESPACE statement has no DATAFILE/TEMPFTLE clause.

*Action: Specify DATAFILE/TEMPFILE clause.

What is the corrective action to create the OMF based tablespace?

A. Issue the CREATE TABLESPACE tbsl DATAFILE SIZE 10M; command.

B. Issue the CREATE TABLESPACE tbsl EXTENT MANAGEMENT ORACLE; command.

C. Set the db_create_file_dest parameter using the ALTER SESSION command and re-issue the statement.

D. An OMF tablespace is not allowed; only control files and/or redo log files can be created by OMF.

Answer: C

Explanation:

You can set the DB_CREATE_FILE_DEST parameter using the ALTER SESSION command and re-issue the statement. You should define directories for OMF datafiles, redo log files and control files. Oracle9i gives you two new initialization parameters, DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n, to specify the location where Oracle will create and manage OMFs; n can take a value from 1 to 5.

Incorrect Answers

A: This command will not fix the problem.

B: There is no EXTENT MANAGEMENT ORACLE option in the CREATE TABLESPACE command.

D: An OMF tablespace is allowed; datafiles, control files and redo log files can be created by OMF.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 018

Which three resource plan parameters are used to enable Automatic Consumer Group Switching to estimate job execution times automatically, and to perform a switch to a low priority consumer group before they are initiated? (Choose three.)

A. SWITCH_TIME

B. SWITCH_GROUP

C. MAX_SESS_POOL

D. SWITCH_ESTIMATE

Answer: A, B, D

Explanation:

You can specify the maximum estimated time to complete (in seconds) for an operation by assigning a value the MAX_EST_EXEC_TIME parameter; the default value for this parameter is UNLIMITED. Oracle will estimate the time the operation with table will take. It the SWITH_ESTIMATE parameter is set to TRUE, and if Oracle’s estimate is greater than the value specified by MAX_EST_EXEC_TIME, Oracle will return an error and the operation will not be started. If the SWITCH_ESTIMATE parameter is FALSE, Oracle will start the operation even if Oracle’s estimate is greater than the value specified by MAX_EST_EXEC_TIME; Oracle will only switch groups based on other criteria being met. The SWITCH_GROUP parameter specifies the group to which the session will be switched. The SWITCH_TIME parameter specifies the amount of time a session can execute before it must be switched to another group. The default for the SWITCH_GROUP parameter is NULL; the default for the SWITCH_TIME parameter is UNLIMITED.

Incorrect Answers

C: There is no MAX_SESS_POOL resource plan parameter in Oracle.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129

Chapter 3: Manageability Enhancements

New Features for Administrators - Question 017

You are responsible for a data warehouse application that uses records from an external table to update one of the dimension tables periodically. The records in the external table may contain data for new rows in the dimension table, or for updates to its existing rows.

Which type of SQL command would you use to transfer the data from the external table to the dimension table as efficiently as possible?

A. MERGE

B. SELECT ... CROSS JOIN

C. INSERT ALL ... SELECT

D. CREATE VIEW ... CONSTRAINT

Answer: A

Explanation:

To transfer the data from the external table to the dimension table as efficiently as possible you need to use the MERGE command. The MERGE command helps to retrieve rows from a source table and either update existing rows or insert new rows into a destination table. You update the row in the destination table if there is a matching row in the destination table; otherwise, you insert a new row into the destination table.

Incorrect Answers

B: The SELECT … CROSS JOIN command can be used to create a Cartesian product.

C: The INSERT ALL ... SELECT command will not help you in this case.

D: The CREATE VIEW ... CONSTRAINT cannot be used for purpose to insert new records or to update existing records in the dimension table.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 253

Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 122-123

Chapter 4: New Oracle9i Database DSS and Data-Warehouse Features