Oracle9i dba new features for Oracle7.3 and Oracle9 ocps Sample Questions:

1. Examine this fragment from a SQL*Plus session:
SQL> SELECT name, value FROM v$sysstat 2> WHERE name LIKE 'work area executions%';
NAME VALUE
work area executions - optimal 1544 work area executions - onepass 11 work area executions - multipass 1038
What can you conclude about the setting of the PGA_AGGREGATE_TARGET initialization parameter?

A) You cannot conclude anything because these statistics are not related to the PGA_AGGREGATE_TARGET parameter.
B) It may be set too high because there are many more multipass work area executions than one pass executions.
C) It may be set too low because there are many more multipass work area executions than one pass executions.
D) It is probably at its optimal setting because the ratio of one pass to multipass work area executions is below 0.10 (ten percent).

2. What criterion does Oracle9i use to determine whether a database file is an Oracle Managed File?

A) information stored inside the corresponding initialization parameter file for the instance
B) information stored inside a data dictionary table
C) information stored in the ALERT.LOG file for the corresponding instance
D) the filename format

3. What must you do to enable Automated SQL Execution Memory Management?

A) Set the instance parameters PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY to appropriate values.
B) Execute the DBMS_STATS.GATHER_SYSTEM_STATS procedure with appropriate values
C) Set the instance parameters SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, HASH_AREA_SIZE, SORT_AREA_SIZE, and SORT_AREA_RETAINED_SIZE all to AUTO.
D) Unset the instance parameters SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, HASH_AREA_SIZE, SORT_AREA_SIZE, and SORT_AREA_RETAINED_SIZE.

4. A switchover to a standby database differs from a failover to the standby in a number of ways. What are two of the key differences that characterize a switchover? (Choose two.)

A) The old primary database can take on the standby role and be available for a future switchover.
B) The operation is planned and causes no data loss.
C) The standby database must be running in a no data divergence mode.
D) The primary and standby database must be using exactly the same release and patch level of Oracle9i.
E) Either the primary database's online or archived log files, but not necessarily both, must be available.

5. Consider the following statement:
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_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 histogram based on application usage, regardless of data distribution, 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 distribution and application usage of the column/s for all table, column, and index statistics for the OE schema.
D) The Oracle server creates a new histogram based on existing histogram definitions for all table, column, and index statistics for the OE schema.

Solutions: