Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

ALTER DIMENSION

Purpose

Use the ALTER DIMENSION statement 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.

Syntax

alter_dimension::=

Text description of statements_171.gif follows
Text description of alter_dimension


level_clause::=

Text description of statements_172.gif follows
Text description of level_clause


hierarchy_clause::=

Text description of statements_173.gif follows
Text description of hierarchy_clause


join_clause::=

Text description of statements_154.gif follows
Text description of join_clause


attribute_clause::=

Text description of statements_174.gif follows
Text description of attribute_clause


Semantics

The following keywords, parameters, and clauses have meaning unique to ALTER DIMENSION. Keywords, parameters, and clauses that do not appear here have the same functionality that they have in the CREATE DIMENSION statement.

See Also:

CREATE DIMENSION

schema

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.

dimension

Specify the name of the dimension. This dimension must already exist.

ADD

The 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.

Oracle processes ADD LEVEL clauses prior to any other ADD clauses.

DROP

The 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.

CASCADE

Specify CASCADE if you want Oracle to drop any attributes or hierarchies that reference the level, along with the level itself.

RESTRICT

Specify RESTRICT if you want to prevent Oracle from dropping a level that is referenced by any attributes or hierarchies. This is the default.

COMPILE

Specify 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.

Example

Modifying a Dimension: Examples

The following examples modify the customers_dim dimension in the sample schema sh:

ALTER DIMENSION customers_dim
   DROP ATTRIBUTE country;

ALTER DIMENSION customers_dim
   ADD LEVEL zone IS customers.cust_postal_code
   ADD ATTRIBUTE zone DETERMINES (cust_city);