ALTER DIMENSION

Syntax

level_clause::=

hierarchy_clause::=

join_clause::=

attribute_clause::=

Purpose

To change the hierarchical relationships or dimension attributes of a dimension.

See Also: "CREATE DIMENSION" for more information on dimensions,

Prerequisites

The dimension must be in your schema or you must have the ALTER ANY DIMENSION system privilege to use this statement.

A dimension is always altered under the rights of the owner.

Keywords and Parameters

The following keywords and parameters have meaning unique to ALTER DIMENSION. The remaining keywords and parameters have the same functionality that they have in the CREATE DIMENSION statement. See "CREATE DIMENSION".

schema	is the schema of the dimension you want to modify. If you do not specify schema, Oracle assumes the dimension is in your own schema.
dimension	is the name of the dimension. This dimension must already exist.
ADD	lets you add a level, hierarchy, or attribute to the dimension. Adding one of these elements does not invalidate any existing materialized view. Oracle processes ADD LEVEL clauses prior to any other ADD clauses.
DROP	lets you drop a level, hierarchy, or attribute from the dimension. Any level, hierarchy, or attribute you specify must already exist. Restriction: If any attributes or hierarchies reference a level, you cannot drop the level until you either drop all the referencing attributes and hierarchies or specify CASCADE.
	CASCADE	causes Oracle to drop any attributes or hierarchies that reference the level, along with the level itself.
	RESTRICT	prevents Oracle from dropping a level that is referenced by any attributes or hierarchies. This is the default.
COMPILE	explicitly recompiles an invalidated dimension. Oracle automatically compiles a dimension when you issue an ADD clause or DROP clause. However, if you alter an object referenced by the dimension (for example, if you drop and then re-create a table referenced in the dimension), the dimension will be invalidated, and you must recompile it explicitly.

Examples

This example modifies the TIME dimension:

ALTER DIMENSION time 
   DROP HIERARCHY week_month;
ALTER DIMENSION time
   DROP ATTRIBUTE cur_date;
ALTER DIMENSION time
   ADD LEVEL day IS time_tab.t_day
   ADD ATTRIBUTE day DETERMINES t_holiday;