New Features for Administrators - Question 002

Examine the extract from a SQL*Plus session.

SQL> SELECT partition_name, high_value, tablespace_name
2 FROM user_tab_partitions
3 WHERE table_name = ‘LOCATIONS’;

PARTITION_NAME HIGH_VALUE TABLES
----------- ----------------------------------------------------- -----
REGION_EAST ‘MA’, ‘NY’, ‘CT’, ‘NH’, NULL, ‘MD’, ‘VA’, ‘PA’, ‘NJ’ tbs1
REGION_WEST ‘CA’, ‘AZ’, ‘NM’, ‘OR’, ‘WA’, ‘UT’, ‘NV’, ‘CO’, tbs2
REGION_SOUTH ‘TX’, ‘KY’, ‘TN’, ‘LA’, ‘MS’, ‘AR’, ‘AL’, ‘GA’ tbs3
REGION_CENTRAL ’OH’, ‘ND’, ‘SD’, ‘MO’, ‘IL’, ‘MI’, NULL, ‘IA’ tbs4

SQL> ALTER TABLE locations
2 SPLIT PARTITION region_east
3 VALUES(‘CT’,’MA’,’MD’)
4 INTO
5 (PARTITION region_north_east TABLESPACE tbs2
6 ,PARTITION region_south_east STORAGE (NEXT 2M));

Assuming that there is at least one row for each value shown in the HIGH VALUE column, what is the result of the ALTER TABLE statement?

A. The REGION_SOUTH_EAST partition will be created in the TBS2 tablespace.

B. The statement will fail because no values are supplied for REGION_SOUTH_EAST partition keys.

C. The REGION_SOUTH_EAST partition will contain only rows with a NULL value in the current REGION_EAST partition.

D. The REGION_SOUTH_EAST partition will be created with storage characteristics inherited from the LOCATIONS table.

E. Rows with partitioning keys in the current REGION_EAST partition not included in the VALUES clause will be stored in the REGION_SOUTH_EAST partition.

Answer: E

Explanation:

If you are splitting a partition, the list of values in the VALUES clause applies to the first partition defined. All the remaining states not included in the VALUES clause will be included in a new partition called REGION_SOUTH_EAST.

Incorrect Answers

A: The REGION_SOUTH_EAST partition will stay in the same tablespace where REGION_EAST partition is located. Only the REGION_NORTH_EAST partition will be created in the TBS2 tablespace.

B: The statement will not fail. The REGION_SOUTH_EAST partition will keep all values of REGION_EAST partition not included into the REGION_NORTH_EAST partition.

C: The REGION_SOUTH_EAST partition will keep all values of REGION_EAST partition not included into the REGION_NORTH_EAST partition, but it will not store rows with a NULL value in the current REGION_EAST partition..

D: The REGION_SOUTH_EAST partition will not use the storage characteristics inherited from the LOCATIONS table.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 134-136

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-108

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features