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

New Features for Administrators - Question 065

Which two procedures or functions are part of the DBMS_METADATA package? (Choose two.)

A. GET_DDL

B. GET_XML

C. GET_TYPE

D. GET_VIEW

E. GET_TABLE

Answer: A, B

Explanation:

The GET_DDL and GET_XML are two functions in the DBMS_METADATA package.

Incorrect Answers

C: GET_TYPE is not a part of the DBMS_METADATA package.

D: There is no GET_VIEW procedure of function in the DBMS_METADATA package.

E: GET_TABLE is not a part of the DBMS_METADATA package.

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 064

Which command can you use to alter the active size of the SGA dynamically, where the entered value differs from the current one?

A. ALTER SYSTEM SET SGA_MAX_SIZE=500M ;

B. ALTER SYSTEM DROP DB_2K_CACHE_SIZE ;

C. ALTER SYSTEM SET DB_CACHE_SIZE=AUTO ;

D. ALTER SYSTEM SET SHARED_POOL_SIZE=40M;

Answer: D

Explanation:

This command can be used to alter the active size of the SGA dynamically.

Incorrect Answers

A: This command is incorrect, because the SGA_MAX_SIZE cannot be changed dynamically. And question was about SGA size dynamic change, not SGA_MAX_SIZE.

B: This command is incorrect. You cannot drop the DB_CACHE_SIZE parameter.

C: This command is incorrect. You cannot set the DB_CACHE_SIZE parameter as AUTO.

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 063

In a Data Guard switchover operation, which command must you execute on the database that is being switched from the standby to the primary role?

A. ALTER DATABASE MOUNT STANDBY DATABASE;

B. ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

C. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;

D. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Answer: C

Explanation:

This command will switch the database from the standby to the primary role. This statement ensures that all redo log files have been received and that they have been applied up to the end-of-redo marker. It also converts the standby control file to the current control file.

Incorrect Answers

A: This command just mounts the database as a standby database.

B: This command backs up the control file.

D: This command closes the primary database, completes the archive task, inserts an end-of-redo marker, and backs up the current control file to the SQL session trace file for conversion to a standby control file.

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 062

Examine the statement:

ALTER TABLE sales
EXCHANGE PARTITION q1_2000
WITH TABLE sales q1_2000
UPDATE GLOBAL INDEXES
PARALLEL (DEGREE 4);

What is the result of the statement?

A. All valid global indexes on the SALES table will retain their USABLE status after the statement completes.

B. Nonpartitioned indexes on the SALES table will be marked UNUSABLE, while valid partitioned global and local indexes will retain their USABLE status.

C. Valid indexes on the exchanged table SALES _Q1_2000 will remain in the USABLE state, while all indexes associated with the new partition Q12000, will be marked UNUSABLE, including any global indexes on SALES.

D. Global indexes on the SALES table will be maintained concurrently with the exchange operation if the statement executes with the desired degree of parallelism (four) otherwise it will execute serially and mark the global indexes UNUSABLE.

Answer: A

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

B: Nonpartitioned indexes on the SALES table will not be marked UNUSABLE.

C: All valid global indexes on the SALES table will retain their USABLE status after the statement completes.

D: Global indexes on the SALES table will be maintained concurrently with the exchange operation disregarding if the statement executes with the desired degree of parallelism.

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 061

To assist you in migrating your existing LONG columns to LOB data types, the ALTER TABLE syntax has been enhanced to support LONG to CLOB migration and LONG RAW to BLOB migration.

During this migration, if redo logging is enabled for the table or for the LOB being created, the ALTER TABLE ... MODIFY command temporarily _______ the space requirements.

A. Halves

B. Doubles

C. Triples

D. Quadruples

Answer: B

Explanation:

The ALTER TABLE ... MODIFY command temporarily doubles the space requirements to support LONG to CLOB or LONG RAW to BLOB migration. It happened because of need to keep temporary converted data.

Incorrect Answers

A: It doubles the space requirements, not halves them.

C: The ALTER TABLE ... MODIFY command temporarily doubles, not triples, the space requirements to support LONG to CLOB or LONG RAW to BLOB migration.

D: It doubles the space requirements, not quadruples them.

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 060

The new time and date data types support greater precision for capturing fractional seconds and time zone information. This gives the Oracle9i database greater flexibility in supporting locality preferences.

What is the default degree of precision of fractional seconds when specifying the TIMESTAMP data type?

A. 6 digits

B. 9 digits

C. 12 digits

D. 22 digits

Answer: A

Explanation:

Default degree of precision of fractional seconds for TIMESTAMP data type is 6 digits.

Incorrect Answers

B: Default degree of precision of fractional seconds for TIMESTAMP data type is not 9 digits. It’s 6 digits.

C: Default degree of precision is 6 digits, not 12 digits.

D: It is not 22 digits.

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

Chapter 5: Language Enhancements

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

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 059

Consider this RMAN command:

RMAN> CONFIGURE RETENTION POLICY
2> TO RECOVERY WINDOW OF 7 DAYS;

What does the command accomplish?

A. Establishes the fixed number of backups that must be kept for media recovery

B. Establishes the fixed number of backups required to perform media recovery within 7 days

C. Sets the fixed number of days that backups are held for media recovery

D. Ensures that sufficient backups and logs will be kept so that a point-in-time recovery to any time in the last 7 days is possible

Answer: C

Explanation:

This command sets the fixed number of days that backups are held for media recovery. You can use the CONFIGURE command to specify a recovery window policy that establishes the number of days the backups are to be retained.

Incorrect Answers

A: You can use the CONFIGURE RETENTION POLICY TO REDUNANCY number command to specify a redundancy policy that establishes the number of backups files and control files you want to retain.

B: It does not establish the fixed number of backups required to perform media recovery within 7 days. This command sets the fixed number of days that backups are held for media recovery.

D: This command does not ensure that sufficient backups and logs will be kept so that a point-in-time recovery to any time in the last 7 days is possible.

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

Chapter 3: Manageability Enhancements

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

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 058

Which two statements are true regarding the use of UTF-16 encoding? (Choose two.)

A. Enables easier loading of multinational data

B. Uses a fixed-width multibyte encoding sequence

C. Asian characters are represented in three characters

D. Uses a variable-width multibyte encoding sequence

E. European characters are represented in one or two bytes

Answer: A, B

Explanation:

AL16UTF16 is a 2-byte, fixed-width Unicode character set, which is also referred to as UTF16 or UCS2. The ASCII English character set is assigned the first 128 values from 0 (0X00) through 127 (oX7F) in Unicode, which translates to 1 byte. Even though AL16UTF16 uses one more byte than UTF8 for ASCII character representation, it is still faster because it uses fixed-width encoding as opposed to UTF8, which uses variable-width encoding. UTF-16 encoding enables easier loading of multinational data. It uses a fixed-width multibyte encoding sequence.

Incorrect Answers

C: Asian characters are represented in two characters because UTF16 is a 2-byte, fixedwidth Unicode character set.

D: It uses a fixed-width multibyte encoding sequence.

E: European characters are represented in two bytes because UTF16 is a 2-byte, fixedwidth Unicode character set.

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

Chapter 5: Language Enhancements

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

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 057

You want to drop the TBS1 tablespace from your database. You also want to delete the corresponding data files automatically, and not have to do it manually. What should you do?

A. Use the DROP DATAFILE command

B. Use the DROP TABLESPACE command

C. Ensure that all database files are Oracle Managed Files before using the DROP TABLESPACE command

D. Ensure that the DB_FILE_CREATE_DEST initialization parameter is set before using the DROP TABLESPACE command

Answer: B

Explanation:

You can drop the tablespace and the segments, and also delete the OS datafiles with the following command: DROP TABLESPACE tablespace_name INCLUDING CONTENTS and DATAFILES.

Incorrect Answers

A: You cannot achieve this purpose with the DROP DATAFILE command.

C: You can delete not-OMF datafiles also using the DROP TABLESPACE command.

D: To delete not-OMF datafiles you don’t need to be ensure that the DB_FILE_CREATE_DEST initialization parameter is set before using the DROP TABLESPACE command.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 157-158

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 056

The direct handoff connection method to an Oracle9i Shared Server involves fewer messages than in previous releases of the multithreaded architecture. Which type of message has been eliminated?

A. The initial request from the client to the listener

B. Messages between the dispatcher and the shared server background processes

C. A redirect message from the listener to the client during client connections

D. Round-trip messages between the listener and the shared server background processes

Answer: C

Explanation:

When a client machine initiates a shared server connection to the database in Oracle9i, the listener uses the direct handoff whenever possible. This connection process requires fewer network calls and, accordingly, less overhead.

When the listener receives the request from the client for a shared server connection, it locates the address of the least loaded dispatcher process. In previous versions, the next step would have been to pass this address back to the client for the client to contact the dispatcher again over the network. Instead, in Oracle9i’s direct handoff method, the listener hands the connection request to the dispatcher. The dispatcher communicates directly with the client to establish a connection without the need for the additional network calls that were required in previous versions.

Incorrect Answers

A: The initial request from the client to the listener is not eliminated.

B: Messages between the dispatcher and the shared server background processes are used in Oracle9i also as in previous versions.

D: Round-trip messages between the listener and the shared server background processes are used in Oracle9i as in previous versions of Oracle.

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 055

Examine the statement:

SQL> CREATE TABLESPACE user_ data
2> EXTENT MANAGEMENT LOCAL
3> SEGMENT SPACE MANAGEMENT AUTO;

Which two assumptions must be true for this statement to execute successfully? (Choose two.)

A. Oracle Managed Files are used for this instance.

B. The USER_DATA tablespace is managed using FET$/UET$ tables.

C. The COMPATIBLE initialization parameter must be 9.0.0 or higher.

D. Space within segments in the USER_DATA tablespace is managed with freelists.

Answer: A, C

Explanation:

Oracle Managed Files are used for this instance. To use automatic space management segment the COMPATIBLE initialization parameter in the init.ora file must be 9.0.0 or higher.

Incorrect Answers

B: The USER_DATA tablespace is not managed using FET$/UET$ tables.

D: Because of automatic space management segment usage the USER_DATA tablespace cannot be managed with freelists.

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 054

Examine the following parameter settings from an initialization (init.ora) file:

DB_CREATE_FILE_DEST = '/u01/oradata/'
DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata/'
DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata/'

If you create an Oracle Managed Files (OMF) database using these settings, what is the result?

A. The data files, temp files, and control file will be on device /u01; one online redo

log group will be on device /u02; another redo log group will be on device /u03.

B. The data files and temp files will be on device /u01; one copy of the control file and one online redo log group will be on device /u02; another copy of the control file and a second redo log group will be on device /u03.

C. The data files, temp files, online redo log files, and control file will be on device /u01; multiplexed copies of the archive log files will be created: one set on device /u02, and another set on device /u03.

D. The data files and temp files will be on device /u01; one copy of the control file and the first member in each online redo log group will be on device /u02; another copy of the control file and a second member of each redo log group will be on device /u03.

Answer: D

Explanation:

The data files and temp files directory name is set by the DB_CREATE_FILE_DEST parameter. If you use Oracle Managed Files Locations for the control files are set by the DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2 parameters.

Incorrect Answers

A: Control file will not be located on device /u01. One copy of the control file and the first member in each online redo log group will be on device /u02; another copy of the control file and a second member of each redo log group will be on device /u03.

B: Each redo log copy will have the first member on device /u02 and a second member will be on device /u03.

C: Online redo log files, and control file will not be on device /u01.

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 053

The database was started up using a text parameter file. What will be the default scope of changes made by using the SET clause of the ALTER SYSTEM statement?

A. Only MEMORY is updated

B. Only SPFILE values are updated

C. Only init.ora parameters are updated.

D. Both SPFILE and MEMORY memory values are updated.

E. Both init.ora parameters and SPFILE values are updated.

Answer: A

Explanation:

The default scope of changes made by using the SET clause of the ALTER SYSTEM statement is only MEMORY scope. The MEMORY scope only makes changes in memory for the current instance and not the SPFILE; it does not persist.

Incorrect Answers

B: The SPFILE is not a default scope of changes made by using the SET clause of the ALTER SYSTEM statement. It makes changes only in the SPFILE and not in the instance.

C: The ALTER SYSTEM command does not change the init.ora file.

D: Only MEMORY scope is updated by default.

E: The init.ora file is not updated by the ALTER SYSTEM command.

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

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 32-37

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 052

Which two are true regarding automatic space management segments? (Choose two.)

A. You can create an automatic space management segment in a dictionary-managed tablespace.

B. You can create an automatic space management segment only in a locally managed tablespace.

C. You can successfully invoke the DBMS_SPACE.FREE_BLOCKS procedure on an automatic space management segment.

D. You CANNOT successfully invoke the DBMS_SPACE.FREE_BLOCKS procedure on an automatic space management segment.

Answer: B, D

Explanation:

You can create an automatic space management segment only in a locally managed tablespace. The FREE_BLOCKS procedure refers to the blocks in a segment below the high water mark whose number of rows falls below the PCTUSED attribute, and therefore are candidate for new inserted rows. An automatic space management segment usage eliminates the need to specify the PCTUSED, FREELISTS, and FREELIST GROUPS parameters. So you CANNOT successfully invoke the DBMS_SPACE.FREE_BLOCKS procedure on an automatic space management segment.

Incorrect Answers

A: You can create an automatic space management segment only in a locally managed tablespace, not in a dictionary-managed tablespace..

C: You CANNOT successfully invoke the DBMS_SPACE.FREE_BLOCKS procedure on an automatic space management segment because of eliminating the need to specify the PCTUSED, FREELISTS, and FREELIST GROUPS parameters in an 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 051

The EMPLOYEES table has six indexes and DML operations are slow. Which command begins monitoring the EMPLOYEE_IDX_FK index to determine whether it has been used by an execution plan?

A. ALTER TABLE employees monitor index employee_idx_fk;

B. ALTER INDEX employee_idx_fk monitoring on;

C. ALTER TABLE employees monitor all indexes;

D. ALTER INDEX employee_idx_fk monitoring usage;

Answer: D

Explanation:

This statement provide correct syntax to start monitoring the EMPLOYEE_IDX_FK index to determine whether it has been used by an execution plan.

Incorrect Answers

A: This command requires MONITORING USAGE clause with ALTER INDEX command to be used, not MONITOR INDEX clause with ALTER TABLE command.

B: This command requires MONITORING USAGE clause to be used, not MONITORING ON clause.

C: This command requires MONITORING USAGE clause with ALTER INDEX command to be used, not MONITOR ALL INDEXES clause with ALTER TABLE command.

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 050

Using a bitmap structure to manage database object space has several benefits over using freelist structures.

Which three are benefits of managing space using automatic segment-space managed objects? (Choose three)

A. It readily responds to dynamic changes for concurrent access.

B. It improves performance and space utilization in a multi-instance environment.

C. It requires fewer space related options than freelist managed structures.

D. It improves performance for SYSTEM tablespaces where a high degree of concurrency is required

Answer: A, C, D

Explanation:

Managing space with automatic segment-space managed objects can respond to dynamic changes for concurrent access, improves performance and space utilization in a multiinstance environment and improves performance for SYSTEM tablespaces where a high degree of concurrency is required.

Incorrect Answers

B: It requires more space related options than freelist managed structures.

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 049

Examine the statement:

DROP TABLESPACE IND2
INCLUDING CONTENTS
AND DATAFILES;

What is the result of the statement?

A. The contents and data files belonging to the IND2 tablespace are dropped.

B. The statement will only succeed if the IND2 tablespace was built using Oracle-Managed Files (OMF) data files.

C. The statement will only succeed if the data files belonging to the IND2 tablespace are Oracle-Managed Files (OMF) or if the database was created using OMF.

D. The contents of the tablespace are dropped along with all its data files except those identified with the Oracle-Managed Files (OMF) naming convention.

Answer: A

Explanation:

After executing this statement the content and data files belonging to the IND2 tablespace will be dropped.

Incorrect Answers

B: The statement will only succeed in any case if the tablespace is offline. There is no requirement that IND2 tablespace had to be built using Oracle-Managed Files (OMF) data files.

C: The statement will only succeed in any case if the tablespace is offline. There is no requirement that IND2 tablespace had to be built using Oracle-Managed Files (OMF) data files or the database was created using OMF.

D: The contents of the tablespace are dropped along with all its data files.

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

Chapter 3: Manageability Enhancements

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

Chapter 1: Oracle9i Database Administration and Management Features