Fundamentals I Ch1- New Features for Administrators

Chapter 1 - Oracle9i New Features for Administrators
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla


Review Questions ( 14 total)


1. Which new feature of Oracle9i allows users to view the contents of a table at some point in the past?


A. LogMiner
B. Import
C. Metadata Viewer
D. Oracle Flashback
---

Ans D. The package DBMS_FLASHBACK allows the user to view the contents of a table or tables at a specified time in the past.


2. Choose the statement below that is true regarding enhancements to shared SQL statements in the shared pool.


A. The cursor sharing feature can re-use a SQL statement even if the columns in the statement are in a different order or the GROUP BY clause is different.

B. The new columns CPU_COST, IO_COST, and TEMP_SPACE in PLAN_ TABLE help the rule-based optimizer (RBO) to be more accurate.

C. Even if the only difference in SQL statements is in the literal values, the SQL statement may not be re-used if the histogram statistics are skewed for a column in the WHERE clause.

D. The CURSOR_SHARING parameter now supports the SIMILAR and DERIVED values.

-----
Ans C. If the execution plan is independent of the literal values used in the query,
it is likely that the query can be re-used. The new columns in the PLAN_TABLE assist the cost-based optimizer,
not the rule-based optimizer, and the CURSOR_SHARING parameter does not have DERIVED as a possible value.


3. Given the table declaration below, identify invalid use of timestamp datatypes in an expression or function. (Choose two.)

CREATE TABLE TRANSACTIONS
(TRANS_ID NUMBER,
AMOUNT NUMBER(10,2),
TRANS_START TIMESTAMP,
TRANS_END TIMESTAMP,
SHIP_DATE DATE,
EXPIRE_DATE INTERVAL DAY(0) TO SECOND(0));


A. TRANS_START - TRANS_END

B. TO_TIMESTAMP(AMOUNT, ‘YY-MM-DD HH:MI:SS’)

C. TRANS_START + INTERVAL ‘4’ DAY

D. TRANS_START + SHIP_DATE
----

Any reasonable combination of date and time data types is allowed. However, date fields cannot be added together, and dollar amounts are not valid arguments to date conversion functions.

Ans: B, D

4. Which of the following operations cannot be performed online without any disruption to ongoing online transactions?


A. Dropping a user-defined column
B. Rebuilding secondary IOT indexes
C. Adding new columns to a heap-based table
D. Rebuilding a primary IOT index
-----
Primary keys cannot be dropped nor can columns with user- defined data types be dropped without making a table unavailable.
Ans: A.


5. Which of the following types of joins are now allowed in the FROM clause of a SQL statement? (Choose all that apply.)



A. cross joins
B. inner joins
C. full outer joins
D. left outer joins
----
Ans: A, B, C, D. All the above joins are now specified in the WHERE clause. Previous versions of Oracle supported all these join types other than the full outer join in the WHERE clause.


6. How many panes exist in the new version of Oracle Enterprise Manager (OEM)?


A. One, with pop-up windows

B. Four, as in previous versions

C. Two, in a master/detail format

D. Two, with DBA tools in the right-hand pane
---
The new OEM not only uses a cleaner, easier-to-use two-pane layout, it integrates all the tools previously available through DBA Studio.
Ans: C


7. The DBA is importing a table and an index from a dump file that was exported from another Oracle9i database. Which options does the DBA have when using the statistics from this dump file? (Choose all that apply.)


A. Explicitly accept all statistics
B. Explicitly reject all statistics
C. Let IMPORT decide if the statistics are safe; otherwise recalculate
D. Accept statistics only for non-partitioned tables
E. Explicitly re-calculate statistics, regardless of whether the original statistics are good or bad
---
Ans A, B, C, E. IMPORT cannot reject statistics based on whether the table is partitioned.


8. The Secure Application Role feature in Oracle9i allows a user to authenticate role privileges by doing which of the following?


A. Calling a stored procedure

B. Using OS authentication

C. Using PWFILE authentication

D. Using an encrypted role password

-----
The stored procedure can restrict access to the role in a number of ways, such as by date and time or by the IP address of the user requesting access to the role.
Ans: A.


9. Chad normally runs queries against very small tables, but has informed the DBA that he will soon be running some queries against the data warehouse tables for the operations manager. What can the DBA do to make sure that these new queries won’t slow down OLTP operations? (Choose the best answer.)


A. The DBA can use the Active Session Pool feature to put Chad’s session on hold until another user in the same consumer group finishes their session.

B. The DBA can use the Automatic Consumer Group Switching feature to switch Chad’s consumer group to the same group as the OLTP users.

C. The DBA can use the Active Session Pool feature to suspend the session if there are too many active OLTP sessions.

D. The DBA can use the Automatic Consumer Group Switching feature to switch Chad’s consumer group to a secondary group that has a lower priority.
----
Ans D. Switching to another consumer group with a lower priority will allow the query to finish while minimizing the impact on the ongoing OLTP transactions.
The Active Session Pool feature controls resource usage within the same consumer group and will not necessarily reduce the contention with OLTP transactions.


10. Which of the following is not an advantage of having the data dictionary in the redo logs when using LogMiner for DML and DDL activity?


A. The LogMiner activity will not impact other users’ activity against the data dictionary.

B. The LogMiner reports will be more accurate against a snapshot of the data dictionary rather than a constantly changing live data dictionary.

C. Bad blocks in one of the redo logs will not stop the LogMiner analysis with a static data dictionary.

D. The database does not need to be open to use LogMiner, since all needed information is in the redo logs.

----
Bad blocks can be ignored in LogMiner; however this feature is independent of where LogMiner retrieves the data dictionary information.
Ans: C.



11. Which of the new RMAN options can the DBA use to save time when a backup does not complete successfully?


A. Restart the backup with the NOT BACKED UP option.

B. Use mirrored backups to send the backup to two different device types.

C. Include the archive logs in the backup.

D. There is no alternative to a failed backup other than to restart the backup.
-----
Running RMAN with the NOT BACKED UP option backs up only the missing or incomplete files.
Ans: A.


12. Identify the true statement regarding binary SPFILEs.


A. All changes to an SPFILE are implemented only after the instance is restarted.

B. Changes made to an SPFILE with the ALTER SYSTEM command can be made simultaneously with the change to the memory copy of the parameter.

C. An SPFILE can exist on the client side.

D. SPFILEs can be used in conjunction with a text-format PFILE.
----
Ans B. The changes to an SPFILE may be made at the same time the change is made to the memory copy of the parameter. SPFILEs only exist on the server side and are created using a PFILE. Once the SPFILE is activated, the PFILE is no longer needed.



13. Place the following block read options in order of access time, shortest to longest.


A. Block is read from remote cache without Cache Fusion

B. Block is read from a local cache

C. Block is read from a remote cache with Cache Fusion

D. Block is read from a shared disk
---
Blocks read from a remote cache without Cache Fusion must be written to the shared disk by the remote instance before the blocks can be retrieved by the local instance.
Ans: B,C, D, A


14. PL/SQL execution is significantly more efficient at runtime for which of the following reasons? (Choose two.)


A. Native C code is generated for PL/SQL procedures.

B. The compiled code resides in the SGA.

C. Byte code is generated by the compiler and therefore can easily be re-used by different transactions.

D. The compiled code resides in the PGA.

---
The compiled code is moved to the PGA to reduce contention on the SGA; interpreted byte code is inherently less efficient to execute than native compiled C code.
Ans: A, D.