New Features for Administrators - Question 097

Which two are true regarding a list-partitioned table? (Choose two.)

A. It is possible to partition an index-organized table using the LIST method.

B. It is NOT possible to have multiple columns as the partition key of a list partitioned table.

C. The keyword NULL can be specified as a partition literal value for one partition of a list-partitioned table.

D. The keyword MAXVALUE can be specified as a partition literal value for one partition of a list-partitioned table.

Answer: B, C

Explanation:

It is NOT possible to have multiple columns as the partition key of a list-partitioned table. It’s possible to specify the keyword NULL as a partition literal value for one partition of a list-partitioned table: the set of values that specifies a partition cannot be empty; it must contain at least one value.

Incorrect Answers

A: List partitioning is supported for heap tables only. IOTs do not support this feature.

D: You cannot specify MAXVALUE. You can specify a NULL value.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 134-136 Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-107 Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

New Features for Administrators - Question 096

What is required for the Character Set Scanner to operate?

A. Oracle Enterprise Manager

B. A special schema installed with csminst.sql

C. A separate tablespace called csscan to store the results

D. A GUI capable workstation, because it uses a Java interface

Answer: D

Explanation:

A GUI capable workstation is required for the Character Set Scanner to operate.

Incorrect Answers

A: Oracle Enterprise Manager is not required for the Character Set Scanner utility.

B: The Character Set Scanner does not require a special schema installed with csminst.sql.

C: The Character Set Scanner does not require a separate tablespace called csscan to store the results.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 273-275 Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 141 Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 095

What is true regarding a shared, server-side parameter file for a Real Application Clusters database?

A. It can contain parameters with distinct values for each instance.

B. It can contain only parameters with identical values for each instance.

C. It must contain an IFILE parameter for each instance's individual parameter file.

D. It must be located in the default location for the primary instance's parameter file.

Answer: A

Explanation:

The server-side parameter file for a Real Application Clusters database can contain parameters with distinct values for each instance.

Incorrect Answers

B: It can contain not only parameters with identical values for each instance, but distinct values for each instance.

C: It is not required that it must contain an IFILE parameter for each instance's individual parameter file. It needs to be done when the DBA stores the common parameters in one file and references that common file in each instance by using the IFILE initialization parameter.

D: It can be not located in the default location for the primary instance's parameter file.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 227-230 Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 188-192 Chapter 7: Oracle9i Real Application Clusters

New Features for Administrators - Question 094

What does the Character Set Scanner command line utility do?

A. Reports only on any Unicode character conversions required

B. Performs character set conversions to the new specified character set

C. Scans on any character set conversion required, then converts the data

D. Checks for any character definitions that will fail conversion to the new character set

Answer: D

Explanation:

Oracle9i provides you with the Character Set Scanner (csscan) utility that scans the data to discover potential problems with character set migrations. The scanner verifies the following: that the data in the target database can fit into the column’s data size, that the data in the source can be mapped without being replaced in the target database, that the data can be correctly converted particularly when dealing with differences in byte sizes and fixed/variable byte encoding systems.

Incorrect Answers

A: It does not report only on any Unicode character conversions required.

B: The Character Set Scanner does not perform character set conversions to the new specified character set.

C: It does not convert the data, only scans it.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 273-275 Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 141 Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 093

An index-organized table (IOT) called SPARSE has had many records deleted.

Which command reorganizes the IOT so that the empty space is removed, while minimizing the impact for users?

A. EXPORT with TABLES and IMPORT with the INDEX and IGNORE options.

B. ALTER TABLE _ COALESCE ;

C. ALTER INDEX _ REBUILD ONLINE ;

D. CREATE TABLE _ AS SELECT _ ONLINE;

Answer: B

Explanation:

The ALTER TABLE table_name COALESCE statement will reorganize the IOT. The rebuild operation creates a new tree to defragment the index, as opposed to the coalesce operation that coalesces the leaf blocks within the same branch of the tree.

Incorrect Answers

A: You cannot do this using EXPORT and IMPORT with specified options. There is no INDEX option in EXPORT utility. Only INDEXES option exists.

C: This command can be used to rebuild the secondary index of IOT only.

D: This command is incorrect: you cannot use the ONLINE option that way.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 76-78 Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53 Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 092

What type of protection is implemented when the DBA issues the ALTER DATABASE SET STANDBY DATABASE PROTECTED; command?

A. The standby database is protected against write operations

B. The primary database is protected against write operations

C. The primary database is protected against data loss and data divergence

D. The standby database is protected against data loss and data divergence

Answer: C

Explanation:

The failure resolution policy specifies what should happen on the primary database if all the standby databases ion the configuration are unable to archive the redo logs. To specify guaranteed protection, you must establish the PROTECTED mode for the primary database. The PROTECTED mode will establish a zero divergence and zero data loss configuration.

Incorrect Answers

A: The standby database is not protected against write operations by this operation. The standby database can be mounted for recovery or open as read-only database.

B: The primary database is not protected against write operations.

D: The primary, not standby, database is protected against data loss and data divergence.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 64-72 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 091

Oracle9i provides a database package called dbms_redefinition to perform an online rebuild of a table.

Which two steps are you recommended to do prior to issuing the dbms_redefinition_start_redef table procedure call? (Choose two).

A. Grant privileges on the interim table

B. Invoke the dbms_redefinition.can_redef_table procedure

C. Invoke the dbms_redefinition.sync_interim_table procedure

D. Create any triggers, indexes, or constraints on the interim table

E. Create an empty interim table with all the desired characteristics

Answer: B, E

Explanation:

Before the issuing the dbms_redefinition_start_redef table procedure call it’s recommended to invoke the dbms_redefinition.can_redef_table procedure and create an empty interim table with all the desired characteristics. Dbms_redefinition_start_redef procedure verifies that the table can be redefined online.

Incorrect Answers

A: You don’t need to grant privileges on the interim table.

C: It is recommended to periodically synchronize the interim table with the source one when a large amount of DML is executed on the table while the re-organization is taking place by calling the dbms_redefinition.sync_interim_table() procedure. But this step is not required.

D: You don’t need to create any triggers, indexes, or constraints on the interim table.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 78-80 Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 46-53 Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 090

Which four are true regarding the Workspace Manager in Oracle9i? (Choose four.)

A. Automatically versions all tables

B. Automatically installed with Oracle9i

C. Merges changes with parent rows or discards changes

D. Provides mechanism to identify and resolve conflicts

E. Allows for version enabling tables by use of a packaged procedure

Answer: B, C, D, E

Explanation:

The Workspace Manager in Oracle9i automatically installed with Oracle9i. It merges changes with parent rows or discards changes, provides mechanism to identify and resolve conflicts and allows for version enabling tables by use of a packaged procedure.

Incorrect Answers

A: It does not automatically versions all tables.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 166-176 Chapter 3: Manageability Enhancements

New Features for Administrators - Question 089

Oracle9i offers the ability to monitor the dynamic use of the SGA through the DB_CACHE_ADVICE parameter. When this parameter is set to READY, what is its effect?

A. The Advisory is on and will capture both CPU and memory overhead.

B. The Advisory is on but the memory for the Advisory is not allocated.

C. The Advisory is off and the memory for the Advisory is not allocated.

D. The Advisory is off but the memory for the Advisory remains allocated.

Answer: D

Explanation:

You must instruct Oracle to gather statistics on the buffer cache by setting the value of the DB_CACHE_ADVICE parameter to ON. The default value for this parameter is OFF. DB_CACHE_ADVICE can take on a third value, READY, which specifies that memory is allocated, but it does not gather statistics. The activity of collecting statistics affects the performance of the system. DB_CACHE_ADVICE is a dynamic parameter and can be changed to ON using ALTER SYSTEM command.

Incorrect Answers

A: The Advisory is off.

B: The Advisory is off.

C: The memory for the Advisory remains allocated.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 180-182 Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 16-17 Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 088

The EMPLOYEES table is stored in the SAMPLE tablespace. The corresponding IPK_EMP index for the EMPLOYEES table's primary key is stored in the INDX tablespace.

Out of 12 partitions, only partition P1 of the SALES table is stored in the SAMPLE tablespace.

Which objects will be exported by this command?

exp system/manager tablespaces=SAMPLE

A. Only the EMPLOYEES table

B. Only the EMPLOYEES table and its corresponding primary key index

C. The EMPLOYEES table and partition P1

D. The EMPLOYEES table, IPK_EMP index, and the SALES table

Answer: D

Explanation:

The EMPLOYEES table, IPK_EMP index, and the SALES table will be exported by this command, because Export utility export objects in the SAMPLE tablespace and ALL other objects corresponding to objects in the SAMPLE tablespace: other partitions for table from SAMPLE tablespace, primary key, other corresponding indexes and so on.

Incorrect Answers

A: Not only the EMPLOYEES table will be exported, but all objects corresponding to it also.

B: All other partitions of the EMPLOYEE table will be exported also.

C: Its corresponding primary key index will be exported also.

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

New Features for Administrators - Question 087

What is the new Oracle Shared Server?

A. An improved version of multithreaded server configuration

B. A connection pooling configuration where several clients are connected to the same server process.

C. Two or more database servers, which share data by means of database links; the client software is unaware to which server it is connected

D. A configuration of Real Application Clusters where the client connection is routed to the least busy instance.

Answer: A

Explanation:

Oracle Shared Server is an improved version of multithreaded server configuration.

Incorrect Answers

B: Oracle Shared Server does not provide configuration where several clients are connected to the same server process.

C: Oracle Shared Server does not include two or more database servers, which share data by means of database links.

D: Oracle Shared Server is not a configuration of Real Application Clusters where the client connection is routed to the least busy instance.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 144-152 Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 59-60 Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 086

A bitmap join index is defined as __________.

A. An index used to join two bitmap indexes on a table

B. A bitmap index created for the join of two or more tables

C. A bitmap index created on the join of two or more indexes

D. A bitmap index created on the join of two or more indexed-organized tables

Answer: B

Explanation:

Prior to Oracle9i, you could create bitmap indexes on a single table. Bitmap indexes are very useful in data warehousing environment for low-cardinality columns. Oracle9i extents this concept to a bitmap join index that optimizes the joining of two or more tables. A bitmap join index is a space- and performance-efficient technique. By predetermining associations, bitmap join indexes eliminate the large volumes of data generated with Cartesian products performed in a join operation.

Incorrect Answers

A: A bitmap join index is not a join of two bitmap indexes on a table.

C: A bitmap index created on the join of two or more tables, not indexes.

D: You cannot reference an IOT or a temporary table.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 142-143 Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 54-56 Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 085

What should you look at first to compute the number of undo blocks written per second to disk?

A. V$UNDOSTAT

B. V$ROLLSTAT

C. V$TRANSACTION

D. DBA_UNDO_EXTENTS

E. DBA_ROLLBACK_SEGS

Answer: A

Explanation:

This view displays a histogram of statistical data to show how well the system is working. Each row in the view keeps statistics collected in the instance for a 10-minute interval.You can use this view to estimate the amount of undo space required for the current workload. The database uses this view to tune undo usage in the system. This view is available in both SMU (system managed undo) mode and RBU (rollback segment undo) mode.

Incorrect Answers

B: This dynamic view contains rollback segment statistics.

C: This dynamic view lists the active transactions in the system

D: The DBA_UNDO_EXTENTS data dictionary view is used to show information about extents in the UNDO segments.

E: The DBA_ROLLBACK_SEGS data dictionary view contains information about rollback segments.

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

Oracle 9i New Features, Robert Freeman, p. 24-25 Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 084

What does the Metadata Application Programming Interface (API) allow you to do?

A. Repair damaged data dictionary entries

B. Delete data dictionary information about database objects you no longer need

C. Extract data definition commands from the data dictionary in a variety of formats

D. Prepare pseudocode modules for conversion to Java or PL/SQL programs with a Metadata code generator

Answer: C

Explanation:

Oracle9i has introduced a new package called DBMS_METADATE that enables you to retrieve all the attributes of the database object from the data dictionary with one invocation. Also you can use other methods, available in earlier versions of Oracle for documenting the objects in an existing database: query the various tables in the data dictionary to obtain information about an object, export/import utilities, OCIDescribeAny interface.

Incorrect Answers

A: The Metadata Application Programming Interface (API) does not allow you to repair damaged data dictionary entries.

B: It is not used to delete data dictionary information about database objects you no longer need.

D: The Metadata Application Programming Interface (API) does not prepare pseudocode modules for conversion to Java or PL/SQL programs.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 136-138 Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 44-46 Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 083

Examine the command:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

What does the command accomplish?

A. Creates an additional copy of the database online redo log files

B. Stores the primary key column values of each row involved in a DML operation in the online redo log files

C. Stores the primary key column values of each row involved in a DML operation in the supplemental log files

D. Stores the old and new primary key column values of each row involved in a DML operation only when the primary key is modified in the online redo log files

Answer: B

Explanation:

Database supplemental logging allows you to specify logging of primary keys, unique indexes or both. With this enabled, whenever a DML is performed, the columns involved in the primary key or unique index are always logged even if they were not involved in the DML. This only takes effect for statements which have not yet been parsed. It also invalidates all DML cursors in the cursor cache and therefore has an effect on performance until the cache is repopulated.

Incorrect Answers

A: This statement does not create an additional copy of the database online redo log files

C: Database supplemental logging stores the primary key column values of each row involved in a DML operation in the regular, not supplemental, online redo log files.

D: It stores the primary key column values of each row involved in a DML operation in the online redo log files.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 100-110 Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 68-72 Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 082

In the Oracle9i Data Guard architecture, what is the purpose of the Log Transport Services?

A. To transfer redo log information to one or more destinations

B. To apply redo log records sent from the primary database to a standby database at the receiving location

C. To synchronize changes to the control files on all standby databases with changes on the primary database when a log switch occurs

D. To batch archived log files on the primary database until a defined number of checkpoints have been processed and then to distribute the archives to each standby database

Answer: A

Explanation:

The Log Transport Service is comprised of several processes. On the primary database site, the Log Writer updates the online redo logs with the transactions. It can also update the local archived redo logs and send online redo logs transactions to the standby databases. The Archiver saves the online redo log transactions on either local or standby archive logs. The Fetch Archive Log (FAL) client gets redo log transactions from the primary database. When it detects an archive log gap on the standby database, it initiates a request to the FAL server to automatically send and archive the primary database’s redo log transactions. The FAL server exists on the primary database server, and it services requests from FAL clients.

Incorrect Answers

B: The Log Application Service applies the archived redo logs to the standby database.

C: Log Transport Services cannot synchronize changes to the control files on all standby databases with changes on the primary database when a log switch occurs

D: They do not batch archived log files on the primary 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 081

What are three benefits of performing data definition language (DDL) statements against a partitioned table with the UPDATE GLOBAL INDEXES clause? (Choose three.)

A. Global indexes are rebuilt automatically at the end of the DDL operation thereby avoiding problems with the UNUSABLE status.

B. You do not have to search for invalid global indexes after the DDL command completes and rebuild them individually

C. Global indexes are maintained during the operation of the DDL command and therefore can be used by any concurrent query

D. Global indexes remain intact and available for use by data manipulation language (DML) statements even for sessions that have not enabled the skipping of unusable indexes.

Answer: A, B, D

Explanation:

Oracle9i overcomes the problem of rebuilding the global index by giving you the option to update global indexes as Oracle performs the partition DDL. This feature is not applicable to local indexes, domain indexes, index-organized tables (IOTs), or to indexes that were UNUSABLE prior to start of the partition DML. You can invoke this capability by using the optional clause UPDATE GLOBAL INDEX of the ALTER TABLE command. You can use this clause with the ADD, COALESCE, DROP, EXCHANGE, MERGE, MOVE, SPLIT, and TRUNCATE partition DDL commands.

Incorrect Answers

C: Global indexes are not maintained during the operation of the DDL command.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 130-131 Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-107 Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

New Features for Administrators - Question 080

How can you make a shared server-side initialization parameter file available to all instances of a Real Application Clusters database?

A. Include an SPFILE parameter in each instance-specific initialization file.

B. Create an SPFILE copy on each client from which the instance will be started.

C. Store the SPFILE in a raw partition with a vendor-specific location and name.

D. Use a single client to start the instances and create the SPFILE on this client.

Answer: A

Explanation:

You can make a shared server-side initialization parameter file available to all instances of a Real Application Clusters database by including an SPFILE parameter in each instance-specific initialization file. You still have to store a copy of the initialization parameter file on each node to facilitate startup of the instances. You can avoid distributing copies by converting this initialization parameter file into a Server Parameter File (SPFILE). Then you put the one parameter, as shown here, in the initialization parameter file for each instance:

spfile = oracle/home:\config\spfile

Incorrect Answers

B: You don’t need to create an SPFILE copy on each client from which the instance will be started.

C: It is not required to store the SPFILE in a raw partition with a vendor-specific location and name.

D: You cannot create the SPFILE on the client.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 227-230 Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 188-192 Chapter 7: Oracle9i Real Application Clusters

New Features for Administrators - Question 079

Consider this syntax:

MERGE INTO tl
USING t2 ON (join predicate).....

What does the MERGE syntax do?

A. It performs a merge join of the row from T2 only if it doesn't exist in the T1 table.

B. It creates a natural join of tables T1 and T2 for all columns that have the same name.

C. It creates a Cartesian product of table T1 and table T2 for all columns that have the same name.

D. For each row from T2, it updates the row if it exists within table T1, otherwise it inserts the row into T1.

Answer: D

Explanation:

Oracle9i introduces the MERGE statement to enable you 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. You can specify the basis for the match with a condition in the ON clause. You specify the UPDATE in the WHEN MATCHED clause and you specify the INSERT in the WHEN NOT MATCHED clause.

Incorrect Answers

A: For each row from T2, it updates the row if it exists within table T1; otherwise it inserts the row into T1.

B: It does not create a natural join of tables T1 and T2.

C: It does not create a Cartesian product of table T1 and table T2.

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 DDS and Data-Warehouse Features

New Features for Administrators - Question 078

Which is a restriction on a list partitioned table?

A. You cannot create global range partitioned indexes on the table.

B. The optimizer will not execute partition wise joins on the table's partitions.

C. You must include at least one element in the value list of each partition of the table, even if that element is the keyword NULL.

D. Partition pruning will not occur during query optimization if a range of values is included in the query predicate.

Answer: C

Explanation:

At least one element needs to be defined for each partition of the table. The set of values that specifies a partition cannot be empty; it must contain at least one value.

Incorrect Answers

A: You can create global range partitioned indexes on the table.

B: The optimizer will execute partition wise joins on the table's partitions.

D: Partition pruning will occur during query optimization if a range of values is included in the query predicate.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 134-136 Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-107 Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

New Features for Administrators - Question 077

Your company has four DBAs. You need to know which DBA added a column to the JOB_HISTORY table. The database cannot be offline during work hours. Which LogMiner command can you use to find out which user made the table change?

A. SELECT username,operation,sql_redo from V$logmnr_contents where operation='DDL';

B. SELECT username,operation,sql_redo from V$logmnr_logs where operation='DDL';

C. You must wait until after hours, shut the database down, and issue the command EXECUTE DBMS_LOGMNR_D.BUILD to extract the information from the database.

D. The task cannot be done. LogMiner does not track DDL commands in Oracle9i.

Answer: A

Explanation:

Prior to Oracle9i, DDL statements were recorded as a set of DML statements on internal tables. It was no simple task to review these DML statements and realize that a DDL operation had been performed. You could almost forget this when trying to determine the actual DDL statement that sourced these DML statements. Oracle 9i records the original DDL statement in the redo logs, and LogMiner lists this DDL statement followed by the set of generated DML statements. When you query V$LOGMNR_CONTENTS, you can see DDL under the OPERATION column, and the DDL statement itself under the SQL_REDO column.

Incorrect Answers

B: This view does not provide the requested information. There are no USERNAME, OPERATION, SQL_REDO and OPERATION columns in this view.

C: You don’t need to wait until after hours, shutdown database to retrieve this information. The V$LOGMNR_CONTENTS dynamic view can be used for this purpose.

D: This task can be done with the V$LOGMNR_CONTENTS dynamic view.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 100-110 Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 68-72 Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 076

Which two data types can be converted to LOBs using an ALTER TABLE... MODIFY command? (Choose two.)

A. RAW

B. LONG

C. VARCHAR

D. LONG RAW

Answer: B, D

Explanation:

Oracle9i simplifies the process of converting a LONG or LONG RAW column into a CLOB or BLOB column respectively. You use the ALTER TABLE … MODIFY statement to change a LONG or LONG RAW column into a CLOB or BLOB column respectively.

Incorrect Answers

A: The ALTER TABLE … MODIFY statement can only convert a LONG or LONG RAW to a CLOB or BLOB column respectively. It will not change a VARCHAR or a RAW column into a LOB column.

D: The ALTER TABLE … MODIFY statement will not change a VARCHAR or a RAW column into a LOB column.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 262-263 Chapter 5: Language Enhancements

Oracle 9i New Features, Robert Freeman, p. 147 Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 075

Users complain that SQL statements using a particular index fail. Using DBVERIFY, you find that two separate blocks in the index have become corrupt. The database data files for user data and index are very large. What is the least disruptive recovery strategy available in Oracle9i?

A. Rebuild the index using the online option.

B. Take the individual data file offline (not the whole tablespace), restore and recover the data file from backup with RMAN, then set the data file online again.

C. Without setting the individual data file offline, use RMAN with Block Media Recovery to restore and recover only those blocks.

D. Take the individual data file (not the whole tablespace) offline, use RMAN with Block Media Recovery to restore and recover only those blocks, then set the data file online again.

Answer: C

Explanation:

You can use RMAN with Block Media Recovery to restore and recover only those blocks. It is not required to set the individual data file offline. BMR enables you to back up specific blocks in a datafile without taking the database offline. The default backup method is datafile media recovery. You use the new BLOCKRECOVER statement to perform BMR.

Incorrect Answers

A: With corrupted blocks you cannot rebuild the index. You need restore and recover these blocks.

B: It is not required to take the individual data file offline to perform the blocks restore and recovery.

D: It is not required to take the individual data file offline to perform the blocks restore and recovery.

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 074

You created a database by using the Database Configuration Assistant. What must you do to access a user account in the sample schemas?

A. Create the user account and unlock the account.

B. Create the user account and define a password.

C. Unlock the user account and define a password.

D. Set the 07_DICTIONARY_ACCESSIBILITY parameter to TRUE.

Answer: D

Explanation:

To access a user account in the sample schemas you need to set the 07_DICTIONARY_ACCESSIBILITY parameter to TRUE. This enables anyone who had the ANY privilege to use this privilege on the Data Dictionary.

Incorrect Answers

A: You don’t need to create the user account and unlock the account.

B: You don’t need to create the user account and define a password.

C: You don’t need to unlock the user account and define a password.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 8-9

Chapter 1: Security Enhancements

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

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 073

The enterprise data warehouse routinely receives data from various production OLTP databases. The database block size on the data warehouse is 8192, and in the OLTP databases it may be 2048, 4096 or 8192. The current SGA is 112 MB. Oracle9i has the ability to transport tablespaces with different block sizes. Which two parameters are required in order to "plug in" the OLTP tablespaces into the warehouse database? (Choose two.)

A. DB_2K_CACHE_SIZE

B. DB_4K_CACHE_SIZE

C. DB_8K_CACHE_SIZE

D. DB_2K_BLOCK_BUFFERS

E. DB_4K_BLOCK_BUFFERS

F. DB_8K_BLOCK_BUFFERS

Answer: A, B

Explanation:

DB_2K_CACHE_SIZE and DB_4K_CACHE_SIZE parameters are required in order to “plug in” the OLTP tablespaces into the warehouse database. DB_CACHE_SIZE specifies the database buffer cache sized to the standard block size. Oracle 9i can also support four additional block sizes; you can specify the buffer cache size corresponding to each of these block sizes with the DB_nK_CACHE_SIZE parameter. It this parameter, nK stands for the nonstandard block size; n can take the following values: 2, 4, 8, 16, or 32.

Incorrect Answers

C: The database block size on the data warehouse is 8192, so DB_8K_CACHE_SIZE does not need to be specified.

D: There is no DB_2K_BLOCK_BUFFERS parameter in Oracle. You can specify the buffer cache size with the DB_nK_CACHE_SIZE parameter.

E: There is no DB_4K_BLOCK_BUFFERS parameter in Oracle. You can specify the buffer cache size with the DB_nK_CACHE_SIZE parameter.

F: There is no DB_8K_BLOCK_BUFFERS parameter in Oracle. You can specify the buffer cache size with the DB_nK_CACHE_SIZE parameter.

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

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 14-16

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 072

You are using automatic space-managed segments, and you wish to change the value of PCTFREE for a given table. What can you do to ensure the change is made in the bitmap structure?

A. Use the ALTER command, then execute the dbms_space package.

B. Use the ALTER command, then execute the dbms_repair.segment_fix_status procedure.

C. Use the ALTER command to change the PCTFREE value of automatic space managed segments.

D. It is not possible to change the PCTFREE value of automatic space-managed segments.

Answer: B

Explanation:

You use the ALTER TABLE statement to change the PCTFREE value for both FREELIST segments (manual space-managed) and ASM segments. In the case of automatic space-managed tables, the ALTER TABLE command does not update the BMBs. Hence, these blocks may not track the true current status of the datablocks. Oracle9i has added a new procedure called SEGMENT_FIX_STATUS to the DBMS_REPAIR package to fix this problem.

Incorrect Answers

A: Oracle9i has enhanced the DBMS_SPACE package by adding the SPACE_USAGE procedure to obtain information about the free blocks in automatic space-managed (ASM) segments. But you cannot change the PCTFREE value for the table with this package.

C: You cannot change the PCTFREE value of automatic space-managed segments with ALTER command. It can be done with DBMS_REPAIR package.

D: It is possible to change the PCTFREE value of automatic space-managed segments with DBMS_REPAIR package.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-142

Chapter 3: Manageability Enhancements

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

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 071

The TYPE clause used in the creation of an external table defines which access driver is used to convert the data when needed by SQL statements inside the database. Which type of access driver is supported for external tables?

A. Import

B. ORACLE_LOADER

C. Metadata API

D. Direct path export

Answer: B

Explanation:

ORACLE_LOADER is the default access driver for external tables.

Incorrect Answers

A: Import is not the default access driver for external tables.

C: Metadata API is not used to convert the data as an access driver.

D: External tables are not processed with direct path export.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 131-134

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 111-116

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

New Features for Administrators - Question 070

When querying the new v$spparameter view, the column ISSPECIFIED displays the value TRUE for a number of parameters. What does this mean?

A. The value for the parameters is TRUE.

B. The parameters are specified in the PFILE parameter file

C. The parameters are specified in the SPFILE parameter file.

D. Changes to the parameter are only in the currently running instance.

Answer: C

Explanation:

The column ISSPECIFIED displays the value TRUE for the parameters specified in the SPFILE parameter file.

Incorrect Answers

A: This column does not show the value of the parameter itself.

B: The parameters are specified in the SPFILE parameter file, not in the PFILE parameter file.

D: These changes will be saved in the SPFILE parameter file, so they will be active after instance restart also.

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

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 35-36

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 069

What is the benefit of the Cached Execution Plan feature?

A. SQL statement performance is improved.

B. The SQL statement no longer needs to be cached in the library cache.

C. Improved diagnosability, as the execution plan of a cached SQL statement in memory is preserved.

D. The execution plan is available for diagnosis after the SQL statement is aged out of the library cache.

Answer: C

Explanation:

Oracle stores the execution plan of a SQL statement to enable a database administrator (DBA) to analyze the execution plan of a poorly performing SQL statement without having to rerun the query. Oracle9i has introduced the V$SQL_PLAN view to display the cached execution plan, the compilation environment, and the execution statistics of each variable. It also helps the database administrators to determine the actual plan of a query at the time of reported performance problem.

Incorrect Answers

A: The Cached Execution Plan feature helps to diagnose the execution plan, not to improve SQL statement performance itself.

B: The SQL statement still needs to be cached in the library cache. The Cached Execution Plan does not eliminate this need.

D: The execution plan is available for diagnosis when the SQL statement is in the library cache.

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

Chapter 4: Performance and Scalability Enhancements

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

Chapter 6: Oracle9i SQL, PL/SQL New Features

New Features for Administrators - Question 068

Which three parameters are ignored when creating an automatic space management segment? (Choose three.)

A. PCTFREE

B. PCTUSED

C. INITRANS

D. MAXTRANS

E. FREELISTS

F. FREELIST GROUPS

Answer: B, E, F

Explanation:

PCTUSED, FREELISTS and FREELIST GROUPS parameters are ignored when creating an automatic space management segment. Oracle9i automatic segment space management maintains information about datablock space utilization in bitmaps, as opposed to freelists.

Incorrect Answers

A: Usage of automatic space management segment will not eliminate the need to specify the PCTFREE parameter.

C: The INITRANS parameter is also used for automatic space management segment.

D: The MAXTRANS parameter is also used for automatic space management segment.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 138-140

Chapter 3: Manageability Enhancements

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

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 067

You start a multithreaded heterogeneous service agent with this initialization parameter:

MAX_DISPATCHERS = 10

What is the result?

A. A maximum of ten dispatcher threads can be active in the agent.

B. A maximum of ten sessions can be connected to the agent simultaneously.

C. A maximum of ten concurrent sessions can be connected to each dispatcher.

D. A maximum of ten external dispatcher processes can register with the listener thread.

Answer: A

Explanation:

The result of this setting is: a maximum of ten dispatcher threads can be active in the agent.

Incorrect Answers

B: A maximum of ten dispatcher threads can be active in the agent, not maximum of ten sessions can be connected to the agent simultaneously.

C: This initialization parameter does not limit a maximum of concurrent sessions connected to each dispatcher.

D: Setting of MAX_DISPATCHERS parameters will not limit a maximum of external dispatcher processes can register with the listener thread.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 144-152

Chapter 3: Manageability Enhancements

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

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 066

Which statement describes the distinction between Fine-Grained Audit (FGA) and Fine-Grained Access Control (FGAC)?

A. FGA is an extension of FGAC.

B. FGAC prohibits access by row; FGA enables access by row.

C. Both use Application Context, but only FGAC can use the Secure Application role.

D. FGA tracks when sensitive rows have been accessed; FGAC prevents access to sensitive rows.

Answer: D

Explanation:

Prior to Oracle9i, value-based auditing was possible with before and after triggers for the INSERT, UPDATE, and DELETE operations. Oracle9i extended the concept of the finegrained access to the auditing functions to enable you to audit SELECT operations. FGA can audit attempts to access information even if the information itself was not accessed.

Incorrect Answers

A: FGA is not an extension of FGAC.

B: FGAC and FGA do not prohibits or enables access by row. FGA specifically audit just the successful operations or the unsuccessful operations.

C: FGA can use the Secure Application role also.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 21-22

Chapter 1: Security Enhancements

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

Chapter 5: Miscellaneous Oracle9i Features and Enhancements