Oracle 1z0-032: Oracle 9i Database Fundamentals-II #1

QUESTION NO: 1
What criterion does Oracle9i use to determine whether a database file is an Oracle Managed File?
A. The filename format.
B. Information stored inside a data dictionary table.
C. Information stored in the ALERT.LOG file for the corresponding instance.
D. Information stored inside the corresponding initialization parameter file for the instance.

Answer: A
Explanation:
Oracle will use naming conventions when it creates the OMF files. In this naming convention, %t represents the tablespace name, %u is a unique 8-character string, and %g stands for the redo log group number.

Incorrect Answers
B: Oracle will create a locally managed tablespaces, so information about a datafile will not be stored inside a data dictionary table.
C: Oracle does not use ALERT.LOG file to store information about datafiles. It just keep log of database events and database structure changes.
D: Initialization parameter file will not be used to store this information. Oracle reads this file to set initialization parameters to start the instance. The structure of the database is stored inside control files. Oracle uses them to mount and open the database.

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

QUESTION NO: 2

Oracle9i extends the cursor sharing functionality with the new value of SIMILAR for the CURSOR_SHARING parameter. With CURSOR_SHARING = SIMILAR, cursors are shared for safe literals only. What is meant by ‘safe literals only’?
A. No literal value is substituted for a shared cursor.
B. Different execution plans are generated for substituted literal values.
C. The substitution of a literal value will produce different execution plans.
D. The substitution of any literal value will produce exactly the same execution plan.

Answer: D
Explanation:
Oracle9i has enhanced cursor sharing mode. It can use additional value, SIMILAR, in addition to the EXACT and FORCE cursor sharing modes. When you specify SIMILAR, Oracle only uses the execution plan if is certain that the execution plan does not have any association with the specific literal value. You can enable similar statements to share the same SQL execution plan by setting CURSOR_SHARING to either FORCE or SIMILAR.

Incorrect Answers
A: Literal value is substituted for a shared cursor. The substitution of any literal value will produce exactly the same execution plan.
B: Exactly the same execution plans will be generated for substituted literal values.
C: The substitution of any literal value will produce exactly the same execution plan.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 57-59
Chapter 2: Oracle9i Architecture Changes

QUESTION NO: 3

The Dynamic SGA feature allows the SGA to grow and shrink dynamically according to an ALTER SYSTEM command. This avoids the previous need of shutting down the instance in order to modify the components of the SGA, namely the buffer cache and shared pool components.
Which three statements are true for the Dynamic SGA feature? (Choose three)
A. The maximum granule size is 4 MB.
B. The minimum SGA configuration is three granules.
C. SGA memory is based on granules by SGA components.
D. The size of the SGA components is set by the SGA_MAX_SIZE parameter.
E. The size of the SGA components is limited by the setting of SGA_MAX_SIZE parameter.

Answer: B, C, E

Explanation:
The minimum SGA configuration is three granules. One is for fixed SGA, one for database buffer, one for shared pool. Oracle9i has enhanced the nature of SGA parameters; they are now dynamic. You can change the values of the shared pool and the buffer cache without restarting the database instance. The Oracle9i dynamic SGA concept enables you to take memory from one area of the SGA and allocate it to another area as needed while the database instance is up and running. Additionally, the unit of memory allocation fr SGA is a granule in Oracle9i. Oracle9i also introduces SGA_MAX_SIZE, a new static parameter that enables the DBA to start with a smaller SGA and dynamically increase it to the maximum value specified by SGA_MAX_SIZE. If you do not set SGA_MAX_SIZE or if you set it to a value less than initial SGA size, you cannot increase the SGA size later.

Incorrect Answers
A: The size of a granule is 4 MB if the SGA at startup is less than 128 MB; it will be 16 MB otherwise.
D: The size of the SGA components is not set by the SGA_MAX_SIZE parameter. This parameter is just maximum limit for sum of the SGA components.

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

QUESTION NO: 4

There is more than one way to set the server to detect and affect long running operations automatically. What is the best choice of you want to reduce the impact of long running operations on other users without aborting the long running operations?
A. Define user profiles and set the CPU_PER_CALL limit.
B. Define a SWITCH_TIME for a plan in the Resource Manager.
C. Create a batch job that checks V$SESSION_LONGOPS; the batch job alters the session priority of the long running operations.
D. Create a user defined event in the Oracle Enterprise Manager, which monitors V$SESSION_LONGOPS.

Answer: B

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

Incorrect Answers
A: You cannot do this with CPU_PER_CALL limit.
C: You don’t need to use a batch job to check V$SESSION_LONGOPS for this purpose.
D: It can be done with a SWITCH_TIME for a plan in the Resource Manager, you don’t need to monitor V$SESSION_LONGOPS.

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

QUESTION NO: 5

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





*This is published for helping newbies in Oracle/MySql DBA field. Please excuse, If in any sense violating the rules/laws.