Oracle | DBMS_SWAT_MM

Oracle DBMS_SWAT_MM_INTERNAL 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 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