Oracle Tips by Burleson

Altering a OUTLINE
 

Outlines are altered using the ALTER OUTLINE or CREATE OR REPLACE form of the CREATE command. The format of the command is identical whether it is used for initial creation or replacement of an existing outline. For example, what if we want to add SUM(b.blocks) to the previous example?
 

CREATE OR REPLACE OUTLINE get_tables

ON

SELECT

a.owner,

a.table_name,

a.tablespace_name,

SUM(b.bytes),

COUNT(b.table_name) extents,

SUM(b.blocks)

FROM

            dba_tables a,

            dba_extents b

WHERE

            a.owner=b.owner

            AND a.table_name=b.table_name

GROUP BY

            a.owner, a.table_name, a.tablespace_name; 

The above example has the effect of altering the stored outline get_tables to include any changes brought about by inclusion of the SUM(b.blocks) in the SELECT list. But what if we want to rename the outline or change a category name? The ALTER OUTLINE command has the format:
 

ALTER OUTLINE outline_name

[REBUILD]

[RENAME TO new_outline_name]

[CHANGE CATEGORY TO new_category_name]

The ALTER OUTLINE command allows us to rebuild the outline for an existing outline_name as well as rename the outline or change its category. The benefit of using the ALTER OUTLINE command is that we do not have to respecify the complete SQL statement as we would have to using the CREATE OR REPLACE command.