Oracle DBMS_SUMMARY
Version 19.3

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_SQL DBMS_SYS_ERROR
DBMS_DIMENSION DBMS_SUMREF_UTIL PLITBLM
DBMS_OLAP DBMS_SUMVDM V$OPTION
DBMS_SNAPSHOT    
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

SQL> DECLARE
  2   stmt VARCHAR2(256) := 'SELECT program_id, COUNT(*) FROM airplanes GROUP BY program_id';
  3 nrows NUMBER;
  4 nbytes NUMBER;
  5 BEGIN
  6 dbms_summary.estimate_mview_size('EMS_TEST', stmt, nrows, nbytes);
  7 dbms_output.put_line(nrows);
  8 dbms_output.put_line(nbytes);
  9 END;
 10 /
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_SUMREF_UTIL
DBMS_SUMVDM
What's New In 18c
What's New In 19c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx