Oracle 10g New Features - Ch4 General Storage Management

Chapter 4 - General Storage Management
Review Questions

1. Which of the following ALTER commands is supported for hash-partitioned indexes?
A. ALTER INDEX REBUILD
B. ALTER INDEX MODIFY PARTITION
C. ALTER TABLE SPLIT INDEX PARTITION
D. ALTER TABLE MERGE INDEX PARTITIONS
E. None of the above

---
Ans: B.
None of the choices contain an ALTER command that is allowed for hash-partitioned indexes, except for the ALTER INDEX MODIFY PARTITION command with the UNUSABLE option—in other words, marking a partition of a hash-partitioned index as unusable.

2. Which of the following recommended practices should a DBA implement to take advantage of bitmap index storage enhancements? (Choose all that apply.)
A. Rebuilding all bitmap indexes manually after adjusting the COMPATIBLE parameter
B. Raising the COMPATIBLE parameter to at least 10.0.0.0
C. Considering rebuilding bitmap indexes when large volumes of single-row DML operations occur on a table
D. Rebuilding bitmap indexes that exhibit a slowdown after adjusting the COMPATIBLE parameter
E. Raising the COMPATIBLE parameter to at least 9.2.0.0

---
Ans: B, D.
Not all bitmap indexes need to be rebuilt after the COMPATIBLE parameter is adjusted, unless they still exhibit a slowdown or get worse. The COMPATIBLE parameter should be set to 10.0.0.0 to take advantage of all enhancements.

3. What is the maximum number of bytes that can be stored in a bigfile tablespace with a database block size of 16KB?
A. 8 exabytes
B. 8,000,000 terabytes
C. 8 petabytes
D. 64 terabytes

----
Ans: D.
Bigfile tablespaces increase the maximum size of a tablespace to 128TB with a block size of 32KB; therefore, with a block size of 16KB, a bigfile tablespace can be 64TB.

4. Which of the following commands creates a temporary tablespace group TMPGRP1 and adds a temporary tablespace named TMPMEMB1? (Choose two.)

A

CREATE TEMPORARY TABLESPACE GROUP TMPGRP1 MEMBERS (TMPMEMB1);
B

ALTER TEMPORARY TABLESPACE GROUP TMPGRP1
ADD TEMPORARY TABLESPACE TMPMEMB1
TEMPFILE 'tmpmem1.dbf ' SIZE 100M;

C

ALTER TABLESPACE TMPMEMB1 TABLESPACE GROUP TMPGRP1;
D

CREATE TEMPORARY TABLESPACE TMPMEMB1
TEMPFILE 'tmpmem1.dbf' SIZE 100M
TABLESPACE GROUP TMPGRP1;

E

None of the above
-----
Ans: C, D.
A temporary tablespace group is created when the first temporary tablespace member is added and is deleted when the last member is removed from the group. If a temporary tablespace already exists, it can be added to an existing group with the ALTER TABLESPACE command.


5. With a non-SYSTEM default permanent tablespace, which users still have SYSTEM as their default permanent tablespace? (Choose all that apply.)
A. SYSMAN
B. SYS
C. OUTLN
D. SYSTEM
E. DBSNMP
F. SCOTT

---
Ans: B, C, D.
Only the system users SYS, SYSTEM, and OUTLN still use the SYSTEM tablespace as their default permanent tablespace.

6. Which of the following statement(s) are not true about default permanent tablespaces?
A. The default permanent tablespace cannot be dropped until another tablespace is defined as the default permanent tablespace.
B. EM Database Control can be used to change the default permanent tablespace.
C. The Database Configuration Assistant defines the USERS tablespace as the default permanent tablespace.
D. In the CREATE DATABASE command, you use the DEFAULT PERMANENT TABLESPACE to assign the default permanent tablespace for users that are not otherwise assigned a default tablespace.
E. The data dictionary view DATABASE_PROPERTIES can be used to retrieve the name of the default permanent tablespace.

---
Ans: D.
In the CREATE DATABASE command, you can only specify DEFAULT TABLESPACE; the PERMANENT keyword is not required nor allowed in the command.

7. Which of the following applications can be moved out of the SYSAUX tablespace?
A. Automatic Workload Repository
B. Oracle Streams
C. StatsPack
D. Job Scheduler
E. LogMiner

----
Ans: E.
Of the available answers, only LogMiner can be relocated out of the SYSAUX tablespace.


8. Which data dictionary or dynamic performance view(s) indicates whether a tablespace is a big-file or smallfile tablespace? (Choose all that apply.)
A. V$TABLESPACE
B. V$DATABASE
C. V$DATAFILE
D. DBA_TABLESPACES
E. DATABASE_PROPERTIES

---
Ans: A, D.
Both V$TABLESPACE and DBA_TABLESPACES contain a new column called BIGFILE to indicate if the tablespace is a bigfile tablespace. V$DATABASE has no tablespace-specific information; V$DATAFILE contains only information relevant to the datafiles of the tablespace; and DATABASE_PROPERTIES has a row indicating the default tablespace type for the database.

9. Which of the following is not true about the initialization parameter SKIP_UNUSABLE_INDEXES?
A. SKIP_UNUSABLE_INDEXES is a dynamic parameter.
B. Even if set to TRUE, a user may still get ORA-01502 messages if UPDATE INDEXES was not specified in partition maintenance.
C. The default value is TRUE at the session and system level.
D. Even if set to TRUE, the optimizer may choose a suboptimal execution plan.
E. The data dictionary view DBA_IND_PARTITIONS can be monitored to see if a local index partition has become invalid.

----
Ans: B.
By setting SKIP_UNUSABLE_INDEXES to TRUE either at the system level or the session level, the optimizer may choose a suboptimal execution plan, but the query will not return an ORA-01502 error message.

10. Identify the new partitioning method available for global indexes.
A. Range partitioned
B. Range-hash partitioned
C. Hash partitioned
D. List-hash partitioned

---
Ans: C.
Oracle 10g now supports hash-partitioned global indexes; each partition contains values derived from an internal hash function based on the partitioning key or keys and the number of partitions defined for the global index. Range partitioned global indexes are not new to Oracle 10g. There is no such partitioning method known as global list-hash partitioning.


11. Identify the main differences between the procedures COPY_FILE and PUT_FILE in the DBMS_FILE_TRANSFER package. (Choose all that apply.)
A. COPY_FILE copies a file to a destination on the same server, and PUT_FILE copies a file to a remote server.
B. PUT_FILE copies a file to a destination on the same server, and COPY_FILE copies a file to a remote server.
C. PUT_FILE can copy only binary files; COPY_FILE can copy binary and Unicode files.
D. The PUT_FILE procedure requires a destination server name.

---
Ans: A, D.
COPY_FILE copies files on the same server; PUT_FILE copies files to a remote server. Both procedures can copy only binary files. Since PUT_FILE copies to a remote server, it requires a destination server name, unlike COPY_FILE, which copies to a destination on the same server.

12. Which of the following operations is not supported for hash-partitioned global indexes?
A. DROP INDEX IX_ORD;
B. ALTER INDEX IX_ORD REBUILD;
C. ALTER INDEX IX_ORD UNUSABLE;
D. ALTER INDEX IX_ORD MODIFY PARTITION IX_ORD_P1 UNUSABLE;
E. ALTER INDEX IX_ORD REBUILD PARTITION IX_ORD_P2;

---
Ans: B.
For hash-partitioned indexes, each individual index must be rebuilt individually. Other operations not supported for hash-partitioned indexes are ALTER TABLE SPLIT INDEX PARTITION and ALTER INDEX MODIFY PARTITION.

13. Given the commands
CREATE TEMPORARY TABLESPACE PRDTTS1
TEMPFILE 'prdtts1.dbf' SIZE 100M
TABLESPACE GROUP PRDTMP;

CREATE TEMPORARY TABLESPACE PRDTTS2
TEMPFILE 'prdtts2.dbf' SIZE 100M
TABLESPACE GROUP PRDTMP;
which command does not assign the temporary tablespace group PRDTMP to a user?

A. CREATE USER KELLYM IDENTIFIED BY TJPO
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE PRDTMP;

B. ALTER USER KELLYM TEMPORARY TABLESPACE GROUP PRDTMP;
C. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PRDTMP;
D. CREATE USER KELLYM IDENTIFIED BY TJPO
TEMPORARY TABLESPACE PRDTMP;I.

---
Ans: B. Logically, a temporary tablespace group is equivalent to an individual temporary tablespace. If a user is not assigned a default temporary tablespace, they are assigned the database's default temporary tablespace.

14. Given a hash-partitioned global index IX_ORD on the table ORD, with four partitions, and the following SELECT statement

SELECT /*+ PARALLEL_INDEX(ORD,IX_ORD,12) */
ORDER_ID, ORDER_DATE FROM ORD
WHERE ORDER_ID BETWEEN 110000 AND 190000;
which of the following is true about the number of processes used to execute the query?
A. Only one process is spawned since the index is hash-partitioned, and the WHERE clause uses a range.
B. After pruning the partitions down to those having the range of order IDs in the WHERE clause, the 12 processes are divided equally among the remaining partitions.
C. If the number of remaining partitions after pruning is fewer than 12, then not all 12 query processes are spawned; a maximum of one query process per partition is allowed for hash-partitioned global indexes.
D. The number of parallel query processes can only be a power of two, therefore, as many as 16 processes may be spawned.

---
Ans: B.
With range-partitioned indexes, partition pruning occurs, but only one parallel query process is spawned per partition whereas multiple query processes may be spawned for each pruned partition in a hash-partitioned global index.

15. Which of the following are true about renaming tablespaces? (Choose all that apply.)
A. Tablespaces that are READ ONLY cannot be renamed and must be changed to READ WRITE before renaming.
B. When a tablespace is renamed, all references to the tablespace name in the data dictionary, control file, online datafile headers, and initialization parameter files are updated.
C. You cannot rename the SYSTEM or SYSAUX tablespaces.
D. The tablespace must be online to be renamed.
E. Temporary tablespaces, undo tablespaces, and permanent tablespaces can be renamed.

---
Ans: C, D, E.
Tablespaces that are READ ONLY can be renamed, but the datafile header is not changed. References to the tablespace are updated in an SPFILE if necessary, but a text-based initialization parameter file is not changed.

16. Which of the following is a benefit of hash-partitioned global indexes?
A. Contention for the same index leaf blocks is reduced in an OLTP environment.
B. Indexes are smaller in a DSS environment because hash partitioning compresses duplicate entries for dimension keys in a star schema.
C. Hash-partitioned global indexes do not become invalid when partition maintenance occurs on the table partitions.
D. The application developer no longer needs to use a reverse-key index to optimize the updates to the index.

---
Ans: A. Hash partitioning spreads the activity to more leaf blocks and therefore reduces the contention for a given leaf block in an OLTP environment.
A

17. Identify the way(s) a DBA can find out if a suboptimal execution plan is being used for a query because a local partitioned index has become invalid and the SKIP_UNUSABLE_INDEXES parameter is set to TRUE. (Choose all that apply.)
A. Monitoring the data dictionary view DBA_IND_PARTITIONS for invalid indexes
B. Monitoring the alert log
C. Using EXPLAIN PLAN to preview the execution plan used for all queries
D. Monitoring user trace files
E. All of the above

---
Ans: A, B.
The DBA can monitor the view DBA_IND_PARTITIONS to see if index partitions become invalid; in addition, the alert log will contain messages when an index has been marked unusable. While EXPLAIN PLAN may alert a user that the index is not being chosen to run the query, it is impractical for the DBA to run the EXPLAIN PLAN command for all user queries. User trace files will not contain messages regarding invalid indexes.
E.

18. Under which of the following conditions is the tablespace SYSAUX created? (Choose all that apply.)
A. When the database is created
B. When you need to use features such as Ultra Search or the EM Repository
C. When the SYSTEM tablespace can no longer autoextend
D. When the database is upgraded from a previous version of Oracle
E. You do not need the SYSAUX tablespace; it is optional

---
Ans: A, D.
The SYSAUX tablespace is required for all new Oracle 10g database installations, as well as upgrading a previous version of Oracle to Oracle 10g. The SYSAUX table must exist, even if the applications that use the SYSAUX table are not installed.

19. Which of the following operations are allowed on the SYSAUX tablespace?
A. Transporting the SYSAUX tablespace to another database
B. Renaming the SYSAUX tablespace
C. Adding a datafile to the SYSAUX tablespace
D. Dropping the SYSAUX tablespace
E. Changing the SYSAUX tablespace from SEGMENT SPACE AUTO to SEGMENT SPACE MANUAL

---
Ans: C.
A datafile can be added to the SYSAUX tablespace, just as any other tablespace, as long as it is a smallfile tablespace. All the other operations listed are not allowed on the SYSAUX tablespace.

20. Which of the following methods can be used to verify the bigfile tablespace bfile.dbf with the DBVERIFY utility and enable parallel processing?


A

$ dbv FILE=bfile.dbf START=1 END=25000 &
$ dbv FILE=bfile.dbf START=25000 END=50000 &
$ dbv FILE=bfile.dbf START=50001 &

B $ dbv FILE=bfile.dbf PARALLEL=3

C Parallel processing is automatically enabled for DBVERIFY depending on the value of PARALLEL_MAX_SERVERS.

D Since a bigfile tablespace has only one datafile, parallel processing cannot be enabled.

E Parallel processing is automatically enabled for offline datafiles only.

F
$ dbverify FILE=bfile.dbf START=1 END=25000 &
$ dbverify FILE=bfile.dbf START=25000
END=50000 &
$ dbverify FILE=bfile.dbf START=50001 &

----
Ans: A.
The DBVERIFY utility, invoked as dbv on every platform, can be spawned multiple times, with each instance of DBVERIFY accessing a different portion of the datafile. No PARALLEL clause exists for DBVERIFY. Since the DBVERIFY utility is an external utility, it does not use database initialization parameters such as PARALLEL_MAX_SERVERS.