Oracle | DBMS

Oracle DBMS_SUMMARY Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose This package provides a PUBLIC interface for Summary Refresh

AUTHID CURRENT_USER

Dependencies

DBMS_ASSERT	DBMS_SNAPSHOT	DBMS_SYS_ERROR
DBMS_DIMENSION	DBMS_SQL	PLITBLM
DBMS_OLAP	DBMS_SUMVDM	V$OPTION

Documented No

Exceptions

Error Name	Reason
dimensionnotfound	The specified dimension was not found

First Available 8.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmssum.sql

Subprograms

DISABLE_DEPENDENT	ESTIMATE_MVIEW_SIZE	VALIDATE_DIMENSION
ENABLE_DEPENDENT

DISABLE_DEPENDENT

Disables a dependent detail table dbms_summary.disable_dependent(detail_tables IN VARCHAR2);

conn sh/sh@pdbdev

      

      exec dbms_summary.disable_dependent('COUNTRIES');

      

      PL/SQL procedure successfully completed.

ENABLE_DEPENDENT

Enables a dependent detail table dbms_summary.enable_dependent(detail_tables IN VARCHAR2);

conn sh/sh@pdbdev

      

      exec dbms_summary.enable_dependent('COUNTRIES');

      

      PL/SQL procedure successfully completed.

ESTIMATE_MVIEW_SIZE

Estimates the size of a materialized view in rows and bytes dbms_summary.estimate_mview_size( stmt_id IN VARCHAR2, select_clause IN VARCHAR2, num_rows OUT NUMBER, num_bytes OUT NUMBER);

conn uwclass/uwclass@pdbdev

      

      DECLARE

       stmt VARCHAR2(256) := 'SELECT program_id, COUNT(*) FROM airplanes GROUP BY program_id';

       nrows  NUMBER;

       nbytes NUMBER;

      BEGIN

        dbms_summary.estimate_mview_size('EMS_TEST', stmt, nrows, nbytes);

        dbms_output.put_line(nrows);

        dbms_output.put_line(nbytes);

      END;

      /

      5

      125

      

      PL/SQL procedure successfully completed.

VALIDATE_DIMENSION

Used to To verify that the relationships specified in a DIMENSION are correct. Offending rowids are stored in the advisor repository. dbms_summary.validate_dimension( dimension_name IN VARCHAR2, dimension_owner IN VARCHAR2, incremental IN BOOLEAN, check_nulls IN BOOLEAN);

conn sh/sh@pdbdev

      

      SELECT dimension_name

      FROM user_dimensions

      ORDER BY 1;

      

      DIMENSION_NAME

      ---------------

      CHANNELS_DIM

      CUSTOMERS_DIM

      PRODUCTS_DIM

      PROMOTIONS_DIM

      TIMES_DIM

      

      exec dbms_summary.validate_dimension('TIMES_DIM', USER, TRUE, TRUE);

      

      PL/SQL procedure successfully completed.