Oracle DBMS_SUMMARY
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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_KKXRCA    
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 SET_LOGFILE_NAME  
 
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.
 
SET_LOGFILE_NAME
Interface for private trace facility used by the advisor dbms_summary.set_logfile_name(filename IN VARCHAR2);
exec dbms_summary.set_logfile_name('sumfile.txt');
 
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 12cR1
What's New In 12cR2

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