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