Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)

Part Number A89852-02
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

34
DBMS_OLAP

The DBMS_OLAP package provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program. Some of the functions generate output tables.

See Also:

Oracle9i Data Warehousing Guide for more information regarding how to use DBMS_OLAP and its output tables 

This chapter discusses the following topics:

Requirements

DBMS_OLAP performs seven major functions, which include materialized view strategy recommendation, materialized view strategy evaluation, reporting and script generation, repository management, workload management, filter management, and dimension validation.

To perform materialized view strategy recommendation and evaluation functions, the workload information can either be provided by the user or synthesized by the Advisor engine. In the former case, cardinality information of all tables and materialized views referenced in the workload are required. In the latter case, dimension objects must be present and cardinality information for all dimension tables, fact tables, and materialized views are required. Cardinality information should be gathered with the DBMS_STATS.GATHER_TABLE_STATS procedure. Once these functions are completed, the analysis results can be presented with the reporting and script generation function.

The workload management function handles three types of workload, which are user-specified workload, SQL cache workload, and Oracle Trace workload. To process the user-specified workload, a user-defined workload table must be present in the user's schema. To process Oracle Trace workload, Oracle Trace formatter must be run to pre-process collected workload statistics into default V-tables in the user's schema.

Error Messages

Table 34-1 lists basic DBMS_OLAP error messages.

Table 34-1 DBMS_OLAP Error Messages
Error Code  Description 
ORA-30442
 

Cannot find the definition for filter <NUMBER

ORA-30443
 

Definition for filter <NUMBER>'s item <NUMBER> is invalid 

ORA-30444
 

Rewrite terminated by the SQL Analyzer 

ORA-30445
 

Workload queries not found 

ORA-30446
 

Valid workload queries not found 

ORA-30447
 

internal data for run number <NUMBER> is inconsistent 

ORA-30448
 

Internal data of the Advisor repository is inconsistent 

ORA-30449
 

Syntax error in parameter <NUMBER

ORA-30465
 

Supplied run_id is not valid: <NUMBER

ORA-30466
 

Cannot find the specified workload <NUMBER

ORA-30477
 

The input select_clause is incorrectly specified 

ORA-30478
 

Specified dimension does not exist 

ORA-30479
 

Summary Advisor error QSM message with more details 

QSM-00501
 

Unable to initialize Summary Advisor environment 

QSM-00502
 

OCI error 

QSM-00503
 

Out of memory 

QSM-00504
 

Internal error 

QSM-00505
 

Syntax error in <parse_entity_name> - <error_description

QSM-00506
 

No fact-tables could be found 

QSM-00507
 

No dimensions could be found 

QSM-00508
 

Statistics missing on tables/columns 

QSM-00509
 

Invalid parameter - <parameter_name

QSM-00510
 

Statistics missing on summaries 

QSM-00511
 

Invalid fact-tables specified in fact-filter 

QSM-00512
 

Invalid summaries specified in the retention-list 

QSM-00513
 

One or more of the workload tables is missing 

QSM-00550
 

The filter item type <NAME> is missing the required data 

QSM-00551
 

The file <NAME> was not found 

QSM-00552
 

The workload source was not defined or was not recognized 

QSM-00553
 

The string value for filter item <NAME> has a maximum length of <NUMBER> characters 

QSM-00554
 

The required table name was not provided 

QSM-00555
 

The table <NAME> can not be accessed or does not exist 

QSM-00556
 

The file <NAME> could not be opened 

QSM-00557
 

The owner <NAME> can not be accessed or does not exist 

QSM-00558
 

An error occurred while reading file <NAME

QSM-00559
 

A workload already exists for the specified collection ID 

QSM-00560
 

The character <NAME> is invalid at line <LINE_NUMBER>, column <COLUMN_NUMBER 

QSM-00561
 

Found <TOKEN> at line <NUMBER>, column <NUMBER>. Expecting 1of the following items: <ITEMS

QSM-00562
 

The requested Advisor task was not found 

QSM-00563
 

Found <TOKEN> at line <NUMBER>, column <NUMBER> of file <NAME>. Expecting 1 of the following items: <ITEMS

QSM-00564
 

An internal lexical error occurred: <Additional error text> 

QSM-00565
 

The <NAME> was not found while validating the <TABLE or COLUMN> at line <NUMBER>, column <NUMBER

QSM-00566
 

The <TOKEN> is ambiguous while validating the <TABLE or COLUMN> at line <NUMBER>, column <NUMBER

QSM-00567
 

A runtime error occurred: <Additional error text> 

QSM-00568
 

The end-of-file was encountered 

QSM-00569
 

The required column <NAME> was not found in table <NAME

QSM-00570
 

The job has ended in error. Status changes are not permitted 

QSM-00571
 

The job has already completed. Status changes are unnecessary 

QSM-00572
 

No repository connection has been established 

QSM-00573
 

The date <VALUE> must be in the form 'DD/MM/YYYY HH24:MI:SS' 

QSM-00574
 

The file <NAME> could not be accessed due to a security violation 

QSM-00575
 

The string <VALUE> can not be converted to a number 

QSM-00576
 

A usable Oracle Trace collection was not found in schema <NAME

QSM-00577
 

The current operation was cancelled by the user 

QSM-00578
 

A temporary file cannot be created using the specification <FILE_NAME

QSM-00579
 

The job has already completed. Cancellation is unnecessary 

QSM-00580
 

The job has ended in error. Cancellation is not permitted 

QSM-00581
 

Internal error: <Additional error text> 

QSM-00582
 

A database error has occurred. <Additional error text> 

QSM-00583
 

The filter item type <NAME> is invalid 

QSM-00584
 

The SQL cache is not accessible by user <NAME

QSM-00585
 

The workload was not found for collection ID <NUMBER

QSM-00586
 

The filter was not found for filter ID <NUMBER

QSM-00587
 

The analysis data was not found for run ID <NUMBER

QSM-00588
 

The current user does not have the privilege to access the requested workload, which is owned by user <NAME

QSM-00589
 

The current user does not have the privilege to access the requested workload filter, which is owned by user <NAME

QSM-00590
 

The current user does not have the privilege to access the requested Advisor items, which are owned by user <NAME

QSM-00591
 

The specified report style <NAME> was not found 

QSM-00592
 

The specified report field <NAME> already exists 

QSM-00593
 

The specified report field <NAME> was not found 

QSM-00594
 

The specified ID number is already being used by another user 

QSM-00595
 

The specified ID number is being used by an Advisor <NAME> object and can not be used for this operation 

QSM-00596
 

A specified ID number cannot be NULL or zero 

QSM-00597
 

Found <TOKEN> at line <NUMBER>, column <NUMBER

QSM-00598
 

The minimum range value for filter item <NAME> is greater than the maximum range value 

QSM-00599
 

The supplied workload filter contains items that are unsupported for the requested workload operation: <OPERATION

QSM-00602
 

The ID <NUMBER> is not a valid Summary Advisor run or collection ID for the current user 

QSM-00601
 

The flags value of <NUMBER> for the Summary Advisor detail report is invalid 

Summary of DBMS_OLAP Subprograms

Table 34-2 lists the subprograms available with DBMS_OLAP.

Table 34-2 DBMS_OLAP Package Subprograms  
Subprogram  Description 

"ADD_FILTER_ITEM Procedure" 

Filters the contents being used during the recommendation process. 

"CREATE_ID Procedure" 

Generates an internal ID used by a new workload collection, a new filter, or a new advisor run 

"ESTIMATE_MVIEW_SIZE Procedure" 

Estimates the size of a materialized view that you might create, in bytes and rows. 

"EVALUATE_MVIEW_STRATEGY Procedure" 

Measures the utilization of each existing materialized view. 

"GENERATE_MVIEW_REPORT Procedure" 

Generates an HTML-based report on the given Advisor run 

"GENERATE_MVIEW_SCRIPT Procedure" 

Generates a simple script containing the SQL commands to implement Summary Advisor recommendations 

"LOAD_WORKLOAD_CACHE Procedure" 

Obtains a SQL cache workload. 

"LOAD_WORKLOAD_TRACE Procedure" 

Loads a workload collected by Oracle Trace. 

"LOAD_WORKLOAD_USER Procedure" 

Loads a user-defined workload. 

"PURGE_FILTER Procedure" 

Deletes a specific filter or all filters. 

"PURGE_RESULTS Procedure" 

Removes all results or those for a specific run. 

"PURGE_WORKLOAD Procedure" 

Deletes all workloads or a specific collection. 

"RECOMMEND_MVIEW_STRATEGY Procedure" 

Generates a set of recommendations about which materialized views should be created, retained, or dropped. 

"SET_CANCELLED Procedure" 

Stops the Advisor if it takes too long returning results. 

"VALIDATE_DIMENSION Procedure" 

Verifies that the relationships specified in a dimension are correct. 

"VALIDATE_WORKLOAD_CACHE Procedure" 

Validates the SQL Cache workload before performing load operations 

"VALIDATE_WORKLOAD_TRACE Procedure" 

Validates the Oracle Trace workload before performing load operations 

"VALIDATE_WORKLOAD_USER Procedure" 

Validates the user-supplied workload before performing load operations 


Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback