|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Statements: ALTER CLUSTER to ALTER SEQUENCE, 6 of 21
DIMENSION statement to change the hierarchical relationships or dimension attributes of a dimension.
CREATE DIMENSION for more information on dimensions
The dimension must be in your schema or you must have the
DIMENSION system privilege to use this statement.
A dimension is always altered under the rights of the owner.
The following keywords and parameters have meaning unique to
DIMENSION. The remaining keywords and parameters have the same functionality that they have in the
Specify the schema of the dimension you want to modify. If you do not specify
schema, Oracle assumes the dimension is in your own schema.
Specify the name of the dimension. This dimension must already exist.
ADD clauses let you add a level, hierarchy, or attribute to the dimension. Adding one of these elements does not invalidate any existing materialized view.
LEVEL clauses prior to any other
DROP clauses let you drop a level, hierarchy, or attribute from the dimension. Any level, hierarchy, or attribute you specify must already exist.
Restriction on DROP: 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 if you want Oracle to drop any attributes or hierarchies that reference the level, along with the level itself.
RESTRICT if you want to prevent Oracle from dropping a level that is referenced by any attributes or hierarchies. This is the default.
COMPILE to explicitly recompile 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.
The following examples modify the
customers_dim dimension in the sample schema