Oracle | DBMS_SWAT_MM

Oracle DBMS_SWAT_MM_INTERNAL Version 21c

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 Undocumented internal utility supporting Automatic Materialized Views

AUTHID DEFINER

Data Types TYPE change_data_rec IS RECORD .... TYPE outputarray IS VARRAY ....

Dependencies

ACTIVITY_MVIEW$	DBMS_ASSERT	DBMS_UTILITY
ACTIVITY_TABLE$	DBMS_AUTOTASK_PRVT	OBJ$
DBA_ACTIVITY_CONFIG	DBMS_DATA_MINING	PLITBLM
DBA_ACTIVITY_SNAPSHOT_META	DBMS_LOCK	SUMDEP$
DBA_ACTIVITY_TABLE	DBMS_MVIEW	SUMPARTLOG$
DBA_MINING_MODELS	DBMS_OUTPUT	SWAT$CONFIG_PARAMS
DBA_MVIEWS	DBMS_STANDARD	SWAT_MM_refresh_HISTORY$
DBA_MVIEW_LOGS	DBMS_SWAT	TAB$
DBA_PART_KEY_COLUMNS	DBMS_SWAT_INTERNAL	TABPART$
DBA_SEGMENTS	DBMS_SWAT_MM_UTILS	USER$
DBA_TABLES	DBMS_SYSTEM	V$NLS_PARAMETERS
DBA_VIEWS	DBMS_SYS_ERROR

Documented No

First Available 20c

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvsswatmm.plb

Subprograms

CHECK_VOL	GET_ACT_CHANGE_DATA	NEXT_QUIET_WINDOW
CLEANUP_BEFORE_DISABLE	GET_CANON_DATE_INFO	PCT_OR_FAST_OR_FULL_REFRESH
DML_OCCURRED	GET_MOS_RUN_PARAMS	RECORD_REFRESH_STATUS
DROP_AUTO_MVIEW	GET_NAME	REMOVE_DOUBLE_QUOTE
DROP_FAILING_REFRESH_AUTOMVS	GET_NUM_DAYS	REWRITE
GETNAMETOKEN	GET_REFRESH_ID	SWAT_MM_DRIVER
GETNUMBEROFITEMS	MVIEW_FAST_REFRESHABLE	WHENMODELCREATED

CHECK_VOL (new 21c)

Undocumented dbms_swat_mm_internal.check_vol(obj_num IN NUMBER) RETURN NUMBER;

SELECT dbms_swat_mm_internal.check_vol(1)

      FROM dual;

      

      DBMS_SWAT_MM_INTERNAL.CHECK_VOL(12)

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

                                        0

CLEANUP_BEFORE_DISABLE (new 21c)

Undocumented dbms_swat_mm_internal.cleanup_before_disable;

exec dbms_swat_mm_internal.cleanup_before_disable;

      

      PL/SQL procedure successfully completed.

DML_OCCURRED (new 21c)

Undocumented dbms_swat_mm_internal.dml_occurred( load_rows IN NUMBER, insert_rows IN NUMBER, delete_rows IN NUMBER, update_rows IN NUMBER, partition_truncates IN NUMBER, partition_creates IN NUMBER, partition_drops IN NUMBER, partition_moves IN NUMBER, partition_splits IN NUMBER, partition_merges IN NUMBER, partition_coalesces IN NUMBER, partition_exchanges IN NUMBER) RETURN NUMBER;

SELECT dbms_swat_mm_internal.dml_occurred(12, 11, 10,9,8,7,6,5,4,3,2,1)

      FROM dual;

      

      
      DBMS_SWAT_MM_INTERNAL.DML_OCCURRED(12,11,10,9,8,7,6,5,4,3,2,1)

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

                                                                   1

DROP_AUTO_MVIEW (new 21c)

Drops a materialized view ... and ... unfortunately ... drops ANY materialized view ... not just an Auto MV.

We understand that this is an internal package and not supposed to be [mis]used as we have just done but Oracle Corp. should harder to sanitize inputs: Something that clearly didn't happen here. dbms_swat_mm_internal.drop_auto_mview( mv_owner IN VARCHAR2, mv_name IN VARCHAR2);

SELECT owner, mview_name, auto

      FROM dba_mviews;

      

      OWNER                     MVIEW_NAME           AUT

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

      C##UWCLASS                MV_SIMPLE            NO

      C##UWCLASS                MV_DEMAND            NO

      

      exec sys.dbms_swat_mm_internal.drop_auto_mview('C##UWCLASS', 'MV_DEMAND');

      

      PL/SQL procedure successfully completed.

      

      SELECT owner, mview_name, auto

      FROM dba_mviews;

      

      OWNER                     MVIEW_NAME           AUT

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

      C##UWCLASS                MV_SIMPLE            NO

DROP_FAILING_REFRESH_AUTOMVS (new 21c)

Drops automatically created MVs that are failing refreshes dbms_swat_mm_internal.drop_failing_refresh_automvs;

exec dbms_swat_mm_internal.drop_failing_refresh_automvs;

      

      PL/SQL procedure successfully completed.

GETNAMETOKEN (new 21c)

Undocumented dbms_swat_mm_internal. tablestring IN CLOB, startpos IN NUMBER, delimitter IN CHAR) RETURN VARCHAR2;

TBD

GETNUMBEROFITEMS (new 21c)

Undocumented dbms_swat_mm_internal.getNumberOfItems(instr IN CLOB) RETURN NUMBER;

SELECT dbms_swat_mm_internal.getNumberOfItems('Test,String')

      FROM dual;

      

      DBMS_SWAT_MM_INTERNAL.GETNUMBEROFITEMS('TESTSTRING')

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

                                                         0

      SELECT dbms_swat_mm_internal.getNumberOfItems('"Morgans,Library,Test,String"')

      FROM dual;

      

      
      DBMS_SWAT_MM_INTERNAL.GETNUMBEROFITEMS('"MORGANS,LIBRARY,TEST,STRING"')

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

                                                                           .5

GET_ACT_CHANGE_DATA (new 21c)

Undocumented dbms_swat_mm_internal.get_act_change_data( in_mv_owner IN VARCHAR2, in_mv_name IN VARCHAR2, last_refresh_date IN DATE, in_current_date IN DATE, act_change_data OUT sys.dbms_swat_mm_internal.change_data_rec, num_tables OUT NUMBER);

TBD

GET_CANON_DATE_INFO (new 21c)

Undocumented dbms_swat_mm_internal.get_canon_date_info( curds IN DATE, curde IN DATE, snapshot_interval IN NUMBER, real_snap_id IN NUMBER, same_canon_snapshot_flag OUT BOOLEAN, first_snapid OUT NUMBER, first_snap_startdate OUT DATE, first_snap_enddate OUT DATE, first_fraction OUT NUMBER, last_snapid OUT NUMBER, last_snap_startdate OUT DATE, last_snap_enddate OUT DATE, last_fraction OUT NUMBER, trace_flag IN BOOLEAN);

TBD

GET_MOS_RUN_PARAMS (new 21c)

Undocumented dbms_swat_mm_internal.get_mos_run_params( in_current_date IN DATE, current_dow OUT NUMBER, current_snap OUT NUMBER, starting_dow OUT NUMBER, starting_snap OUT NUMBER, ending_dow OUT NUMBER, ending_snap OUT NUMBER, prediction_window OUT NUMBER mos_prob_method OUT VARCHAR2 mos_pred_window OUT NUMBER tot_gen_days OUT NUMBER);

DECLARE

       a NUMBER;

       b NUMBER;

       c NUMBER;

       d NUMBER;

       e NUMBER;

       f NUMBER;

       g NUMBER;

       h dbms_id;

       i NUMBER;

       j NUMBER;

      BEGIN

        dbms_swat_mm_internal.get_mos_run_params(SYSDATE, a, b, c, d, e, f, g, h, i, j);

        dbms_output.put_line(a);

        dbms_output.put_line(b);

        dbms_output.put_line(c);

        dbms_output.put_line(d);

        dbms_output.put_line(e);

        dbms_output.put_line(f);

        dbms_output.put_line(g);

        dbms_output.put_line(h);

        dbms_output.put_line(i);

        dbms_output.put_line(j);

      END;

      /

      4

      14

      4

      14

      4

      62

      48

      WEIGHTED

      12

      11

      

      PL/SQL procedure successfully completed.

GET_NAME (new 21c)

Undocumented but appears to perform no more useful function than to encapsulate two strings with double-quotes

Apparently the author(s) of this object never heard about DBMS_ASSERT dbms_swat_mm_internal.get_name( name IN VARCHAR2, myowner IN OUT VARCHAR2, myname IN OUT VARCHAR2, downer IN VARCHAR2);

DECLARE

       mownr dba_objects.owner%TYPE := 'A';

       mname dba_objects.object_name%TYPE := 'B'; 

      BEGIN

        dbms_swat_mm_internal.get_name('C', mownr, mname, 'D');

        dbms_output.put_line(mownr);

        dbms_output.put_line(mname);

      END;

      /

      "D"

      "C"

GET_NUM_DAYS (new 21c)

Undocumented dbms_swat_mm_internal.get_num_days( in_mv_owner IN VARCHAR2, in_mv_name IN VARCHAR2, in_current_date IN DATE, min_canon_snap_id OUT NUMBER, num_gen_days OUT NUMBER, tot_dow_days OUT NUMBER, num_dow_days OUT NUMBER);

DECLARE

       a NUMBER;

       b NUMBER;

       c NUMBER;

       d NUMBER;

      BEGIN

        dbms_swat_mm_internal.get_num_days('C##UWCLASS','MV_SIMPLE', SYSDATE, a, b, c, d);

        dbms_output.put_line('MCSI: ' || TO_CHAR(a));

        dbms_output.put_line('NGD:  ' || TO_CHAR(b));

        dbms_output.put_line('TDD:  ' || TO_CHAR(c));

        dbms_output.put_line('NDD:  ' || TO_CHAR(d));

      END;

      /

      MCSI:

      NGD: 0

      TDD: 1

      NDD: 0

GET_REFRSH_ID (new 21c)

Undocumented dbms_swat_mm_internal.get_refresh_id( swat_mm_start_time IN DATE, in_mv_owner IN VARCHAR2, in_mv_name IN VARCHAR2) RETURN NUMBER;

DECLARE

       retVal NUMBER;

      BEGIN

        retVal := dbms_swat_mm_internal.get_refresh_id(SYSDATE, 'C##UWCLASS', 'MV_SIMPLE');

        dbms_output.put_line(retVal);

      END;

      /

      2

      

      PL/SQL procedure successfully completed.

MVIEW_FAST_REFRESHABLE (new 21c)

Returns TRUE if a materialized view is fast refreshable, else FALSE dbms_swat_mm_internal.mview_fast_refreshable(qualified_mv_name IN VARCHAR2) RETURN BOOLEAN;

BEGIN

        IF dbms_swat_mm_internal.mview_fast_refreshable('LIBRARY') THEN

          dbms_output.put_line('Fast Refreshable');

        ELSE

          dbms_output.put_line('Not Fast Refreshable');

        END IF;

      END;

      /

      Fast Refreshable

      

      PL/SQL procedure successfully completed.

NEXT_QUIET_WINDOW (new 21c)

Undocumented dbms_swat_mm_internal.next_quiet_window( tabledmarray IN sys.dbms_swat_mm_internal.outputarray, startsnapid IN NUMBER, duration OUT NUMBER) RETURN NUMBER;

TBD

PCT_OR_FAST_OR_FULL_REFRESH (new 21c)

Undocumented dbms_swat_mm_internal.pct_or_fast_or_full_refresh(qualified_mv_name IN VARCHAR2) RETURN CHAR;

SELECT dbms_swat_mm_internal.pct_or_fast_or_full_refresh('LIBRARY')

      FROM dual;

      

      DBMS_SWAT_MM_INTERNAL.PCT_OR_FAST_OR_FULL_refresh('LIBRARY')

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

      F

RECORD_REFRESH_STATUS (new 21c)

Undocumented dbms_swat_mm_internal.record_refresh_status( in_swat_mm_start_time IN DATE, in_refresh_id IN NUMBER, in_mv_owner IN VARCHAR2, in_mv_name IN VARCHAR2, in_status IN NUMBER, in_error IN NUMBER);

TBD

REMOVE_DOUBLE_QUOTE (new 21c)

Removes leading and trailing double quotes but does not remove double quotes within the body of a string dbms_swat_mm_internal.remove_double_quote(str IN VARCHAR2) RETURN VARCHAR2;

DECLARE

       x dbms_id := '"This is a " test"';

      BEGIN

        dbms_output.put_line(dbms_swat_mm_internal.remove_double_quote(x));

      END;

      /

      This is a " test

      

      PL/SQL procedure successfully completed.

REWRITE (new 21c)

Undocumented dbms_swat_mm_internal.rewrite(num_rewrites IN NUMBER) RETURN NUMBER;

SELECT dbms_swat_mm_internal.rewrite(12)

      FROM dual;

      

      DBMS_SWAT_MM_INTERNAL.REWRITE(12)

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

                                      1

SWAT_MM_DRIVER (new 21c)

Undocumented dbms_swat_mm_internal.swat_mm_driver;

exec dbms_swat_mm_internal.swat_mm_driver;

      

      PL/SQL procedure successfully completed.

WHENMODELCREATED (new 21c)

Undocumented dbms_swat_mm_internal.whenModelCreated(modelname IN VARCHAR2) RETURN NUMBER;

TBD