Oracle DBMS_SUMMARY
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
Disables a dependent detail table dbms_summary.disable_dependent(detail_tables IN VARCHAR2);
conn sh/sh@pdbdev

exec dbms_summary.disable_dependent('COUNTRIES');
 
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');
 
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.

Related Topics
Built-in Functions
Built-in Packages
Dimensions
DBMS_SUMVDM
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx