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

QUESTION NO: 6

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

A. It provides each session with its own view of the database at a different point in the past.
B. It enables you to execute scalable applications on a clustered database without having to partition the users or the database tables.
C. It lets you dynamically reassign memory in your database buffer cache to different block buffer sizes.
D. It allows you to add new sites to multimaster replication environment without quiescing the master definition site.

Answer: B

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

Incorrect Answers

A: It does not provide each session with its own view of the database at a different point in the past.
C: The Oracle9i Cache Fusion feature does not dynamically reassign memory in your database buffer cache to different block buffer sizes.
D: It does not provide you ability to add new sites to multimaster replication environment without quiescing the master definition site.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 223-230
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 193-206
Chapter 7: Oracle9i Real Application Clusters

QUESTION NO: 7

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

A. It is NOT possible to switch unless no active transaction exist in UNDO1.
B. It is possible to switch to UNDO2; but current active transactions will abort.
C. It is possible to switch to UNDO2; current active transactions will be automatically migrated to UNDO2.
D. It is possible to switch to UNDO2; only current active transactions will continue to execute inside UNDO1.

Answer: D

Explanation:

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

Incorrect Answers

A: You can switch undo tablespaces while active transactions will run in the old undo tablespace. All new transactions will be assigned to the new undo tablespace.
B: Current active transactions will abort if you switched undo tablespaces.
C: Current active transactions will continue to execute inside UNDO1 till they commit or rollback. They will not be automatically migrated to UNDO2.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-166
Chapter 3: Manageability Enhancements
Oracle 9i New Features, Robert Freeman, p. 19-25
Chapter 1: Oracle9i Database Administration and Management Features

QUESTION NO: 8

Examine the list of variables and their data types:
NAME DATA Type
TS, TS1 TIMESTAMP
TSZ TIMESTAMP WITH TIME ZONE
TLZ TIMESTAMP WITH LOCAL TIME ZONE
IYM INTERVAL YEAR TO MONTH
IDS, IDSI INTERVAL YEAR To SECOND

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

A. IDS* 2
B. TS + IYM
C. TS –TSI
D. IDS – TS
E. IDS + IYM

Answer: A, B, E

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

Incorrect Answers

C: You cannot subtract timestamps.
D: It’s not possible to subtract timestamp from the interval day to second.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 266-271
Chapter 5: Language Enhancements
Oracle 9i New Features, Robert Freeman, p. 132-135
Chapter 5: Miscellaneous Oracle9i Features and Enhancements

QUESTION NO: 9

Consider the following statement:
SQL> EXECUTE DBMS_STATS.GATHER_SHEMA_STATS (-2> ownname => ‘OE’, -
3> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
4> method opt => ‘for all columns size AUTO’);

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

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

Answer: C

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

Incorrect Answers

A: The Oracle server does not create a new histogram based on existing histogram.
B: Histogram creation is not regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.
D: The Oracle server creates a histogram not only based on application usage, but based on data distribution also.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 220-222
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 180-181
Chapter 6: Oracle9i SQL, PL/SQL New Features

QUESTION NO: 10

Which two are true regarding external tables? (Choose two)

A. External tables can be updated.
B. External tables are read-only tables.
C. Indexes can be created on external tables.
D. Indexes cannot be created on external tables.

Answer: B, D

Explanation:
External tables are read-only tables whose data resides in an external OS flat file, and whose definition is stored inside the database. Indexes cannot be created on external tables.

Incorrect Answers
A: External tables cannot be updated. They are read-only tables.
C: Indexes cannot be created on external tables.

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