DROP PACKAGE BODY SDSADM.SDS_UTIL;

CREATE OR REPLACE PACKAGE BODY SDSADM.sds_util
AS
  constraint_state      STRING(8);
  cons_type             STRING(1);
  ref_user              STRING(30);

  CURSOR my_fkeys IS select CONSTRAINT_NAME, TABLE_NAME
                       from ALL_CONSTRAINTS
                      where CONSTRAINT_TYPE = 'R'
                        and STATUS <> constraint_state
                        and OWNER = user;

  CURSOR other_fkeys IS select CONSTRAINT_NAME, TABLE_NAME, OWNER
                       from ALL_CONSTRAINTS
                      where CONSTRAINT_TYPE = 'R'
                        and STATUS <> constraint_state
                        and R_OWNER = ref_user;

  CURSOR my_constraints IS select CONSTRAINT_NAME, TABLE_NAME
                             from ALL_CONSTRAINTS
                            where CONSTRAINT_TYPE = cons_type
                              and STATUS <> constraint_state
                              and OWNER = user;

  CURSOR my_triggers IS select TRIGGER_NAME
                          from ALL_TRIGGERS
                         where TABLE_OWNER = user;

  CURSOR my_tables IS select table_name
                        from all_tables
                       where owner = user;

  CURSOR my_mvlogs IS select master
                        from user_mview_logs;

  CURSOR my_mviews IS select mview_name
                        from user_mviews;

  CURSOR my_mviews_rep IS select mview_name,
                                 master_link
                            from user_mviews
                           where master_link IS NOT null
                        order by mview_name;

  sql_stmt          STRING(1000);
  local_row_count   NUMBER(9);
  master_row_count  NUMBER(9);
  start_replication TIMESTAMP;
  end_replication   TIMESTAMP;
  replication_time  INTERVAL DAY(1) TO SECOND(0);
  mview_list        STRING(32767);
  mview_method      STRING(1024);

  role_does_not_exist    EXCEPTION;
  priv_not_granted       EXCEPTION;
  primary_keys_ref_fkeys EXCEPTION;
  no_matching_parent     EXCEPTION;
  dependencies_exist     EXCEPTION;
  parent_keys_not_found  EXCEPTION;
  primary_key_violated   EXCEPTION;
  sys_user_privilege     EXCEPTION;

  PRAGMA EXCEPTION_INIT(role_does_not_exist, -1917);
  PRAGMA EXCEPTION_INIT(priv_not_granted, -1927);
  PRAGMA EXCEPTION_INIT(primary_keys_ref_fkeys, -2266);
  PRAGMA EXCEPTION_INIT(no_matching_parent, -2270);
  PRAGMA EXCEPTION_INIT(dependencies_exist, -2297);
  PRAGMA EXCEPTION_INIT(parent_keys_not_found, -2298);
  PRAGMA EXCEPTION_INIT(primary_key_violated, -2437);

/***************************************************************************
 *
 *    NAME
 *      check_for_sys_user
 *
 *    DESCRIPTION
 *      check the current effective user_id, raise an exception if
 *      a privileged id is in use
 *
 */
  PROCEDURE check_for_sys_user IS
  BEGIN
    IF user IN ('SYS', 'SYSTEM') THEN
      RAISE sys_user_privilege;
    END IF;
  END check_for_sys_user;

/***************************************************************************
 *
 *    NAME
 *      disable_constraints
 *
 *    DESCRIPTION
 *      disable_constraints searches the user's schema for constraints
 *      not disabled and disables those constraints.
 *
 */

  PROCEDURE disable_constraints IS
  BEGIN
    check_for_sys_user;

    constraint_state := 'DISABLED';
    cons_type := 'R';
    FOR this_cons IN my_constraints LOOP
      sql_stmt := 'ALTER TABLE ' || this_cons.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_cons.CONSTRAINT_NAME || ' DISABLE';
      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION
        WHEN dependencies_exist THEN
          DBMS_OUTPUT.PUT_LINE('Cannot DISABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' with dependenices');
      END;
    END LOOP;

    cons_type :='U';
    FOR this_cons IN my_constraints LOOP
      sql_stmt := 'ALTER TABLE ' || this_cons.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_cons.CONSTRAINT_NAME || ' DISABLE KEEP INDEX';
      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION
        WHEN dependencies_exist THEN
          DBMS_OUTPUT.PUT_LINE('Cannot DISABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' with dependenices');

      END;
    END LOOP;

    cons_type := 'P';
    FOR this_cons IN my_constraints LOOP
      sql_stmt := 'ALTER TABLE ' || this_cons.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_cons.CONSTRAINT_NAME || ' DISABLE KEEP INDEX';
      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION
        WHEN dependencies_exist THEN
          DBMS_OUTPUT.PUT_LINE('Cannot DISABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' with dependenices');
        NULL;
      END;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

   END disable_constraints;

/***************************************************************************
 *
 *    NAME
 *      disable_fkeys
 *
 *    DESCRIPTION
 *      disable_fkeys searches the user's schema for Foreign Key Constraints
 *      not disabled and disables those constraints.
 *
 */

  PROCEDURE disable_fkeys IS
  BEGIN
    check_for_sys_user;
    constraint_state := 'DISABLED';
    FOR this_fk IN my_fkeys LOOP
      sql_stmt := 'ALTER TABLE ' || this_fk.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_fk.CONSTRAINT_NAME || ' DISABLE';
      EXECUTE IMMEDIATE sql_stmt;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

   END disable_fkeys;

/***************************************************************************
 *
 *    NAME
 *      disable_fkeys(schema_name)
 *
 *    DESCRIPTION
 *      disable_fkeys searches the specified schema for Foreign Key Constraints
 *      not disabled and disables those constraints.
 *
 */

  PROCEDURE disable_fkeys(schema_name IN VARCHAR2) IS
  BEGIN
    constraint_state := 'DISABLED';
    ref_user := UPPER(schema_name);

    IF ref_user IN ('SYS', 'SYSTEM') THEN
      RAISE sys_user_privilege;
    END IF;

    FOR this_fk IN other_fkeys LOOP
      sql_stmt := 'ALTER TABLE ' || this_fk.OWNER || '.' || this_fk.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_fk.CONSTRAINT_NAME || ' DISABLE';
      EXECUTE IMMEDIATE sql_stmt;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure on a privileged user''s schema!');

   END disable_fkeys;

/***************************************************************************
 *
 *    NAME
 *      disable_triggers
 *
 *    DESCRIPTION
 *      disable_triggers searches the user's schema for triggers and
 *      disables those triggers.
 *
 */

  PROCEDURE disable_triggers IS
  BEGIN
    check_for_sys_user;

    FOR this_trigger IN my_triggers LOOP
    sql_stmt := 'ALTER TRIGGER ' || this_trigger.TRIGGER_NAME || ' DISABLE';
      EXECUTE IMMEDIATE sql_stmt;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

  END disable_triggers;

/***************************************************************************
 *
 *    NAME
 *      drop_fkeys
 *
 *    DESCRIPTION
 *      drop_fkeys searches the user's schema for Foreign Key
 *      constraints and drops those constraints.
 *
 */

  PROCEDURE drop_fkeys IS
  BEGIN
    check_for_sys_user;
    constraint_state := 'ANY';
    FOR this_fk IN my_fkeys LOOP
      sql_stmt := 'ALTER TABLE ' || this_fk.TABLE_NAME || ' DROP CONSTRAINT ' || this_fk.CONSTRAINT_NAME;
      EXECUTE IMMEDIATE sql_stmt;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

   END drop_fkeys;

/***************************************************************************
 *
 *    NAME
 *      drop_mviews
 *
 *    DESCRIPTION
 *      drop_mviews searches the user's schema for Materialized Views
 *      and drops them.
 *
 */

  PROCEDURE drop_mviews IS
  BEGIN
    check_for_sys_user;

    FOR this_mview IN my_mviews LOOP
      sql_stmt := 'DROP MATERIALIZED VIEW ' || this_mview.mview_name;
      EXECUTE IMMEDIATE sql_stmt;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

   END drop_mviews;

/***************************************************************************
 *
 *    NAME
 *      drop_mvlogs
 *
 *    DESCRIPTION
 *      drop_mvlogs searches the user's schema for Materialized View Logs
 *      and drops them.
 *
 */

  PROCEDURE drop_mvlogs IS
  BEGIN
    check_for_sys_user;

    FOR this_mvlog IN  my_mvlogs LOOP
      sql_stmt := 'DROP MATERIALIZED VIEW LOG ON ' || this_mvlog.master;
      EXECUTE IMMEDIATE sql_stmt;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

   END drop_mvlogs;

/***************************************************************************
 *
 *    NAME
 *      drop_fkeys(schema_name)
 *
 *    DESCRIPTION
 *      drop_fkeys searches for Foreign Key constraints that reference the
 *      specified schema and drops those constraints.
 *
 */

  PROCEDURE drop_fkeys(schema_name IN VARCHAR2) IS
  BEGIN
    constraint_state := 'ANY';
    ref_user := UPPER(schema_name);

    IF ref_user IN ('SYS', 'SYSTEM') THEN
      RAISE sys_user_privilege;
    END IF;

    FOR this_fk IN other_fkeys LOOP
      sql_stmt := 'ALTER TABLE ' || this_fk.OWNER || '.' || this_fk.TABLE_NAME || ' DROP CONSTRAINT ' || this_fk.CONSTRAINT_NAME;
      EXECUTE IMMEDIATE sql_stmt;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure on a privileged user''s schema!');

   END drop_fkeys;

/***************************************************************************
 *
 *    NAME
 *      enable_constraints
 *
 *    DESCRIPTION
 *      enable_constraints searches the user's schema for constraints
 *      not enabled and enables those constraints.
 *
 */

  PROCEDURE enable_constraints IS
  BEGIN
    check_for_sys_user;

    constraint_state := 'ENABLED';
    cons_type := 'P';
    FOR this_cons IN my_constraints LOOP
      sql_stmt := 'ALTER TABLE ' || this_cons.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_cons.CONSTRAINT_NAME || ' ENABLE';

      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION
        WHEN parent_keys_not_found THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' parent keys not found');

        WHEN primary_key_violated THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' primary key violated');
          NULL;
        WHEN no_matching_parent THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' ORA-002270');

      END;

    END LOOP;

    cons_type := 'U';
    FOR this_cons IN my_constraints LOOP
      sql_stmt := 'ALTER TABLE ' || this_cons.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_cons.CONSTRAINT_NAME || ' ENABLE';

      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION
        WHEN parent_keys_not_found THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' parent keys not found');
          NULL;

        WHEN primary_key_violated THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' primary key violated');

        WHEN no_matching_parent THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' ORA-002270');

      END;
    END LOOP;

    cons_type := 'R';
    FOR this_cons IN my_constraints LOOP
      sql_stmt := 'ALTER TABLE ' || this_cons.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_cons.CONSTRAINT_NAME || ' ENABLE';

      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION
        WHEN parent_keys_not_found THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' parent keys not found');

        WHEN primary_key_violated THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' primary key violated');

        WHEN no_matching_parent THEN
          DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_cons.CONSTRAINT_NAME, 32) || ' on table ' || this_cons.TABLE_NAME || ' ORA-002270');

      END;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

  END enable_constraints;

/***************************************************************************
 *
 *    NAME
 *      enable_fkeys
 *
 *    DESCRIPTION
 *      enable_fkeys searches the user's schema for Foreign Key constraints
 *      not enabled and enables those constraints.
 *
 */

  PROCEDURE enable_fkeys IS
  BEGIN
    check_for_sys_user;
    constraint_state := 'ENABLED';

    FOR this_fk IN my_fkeys LOOP
      sql_stmt := 'ALTER TABLE ' || this_fk.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_fk.CONSTRAINT_NAME || ' ENABLE';

      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION WHEN parent_keys_not_found THEN
        DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_fk.CONSTRAINT_NAME, 32) || ' on table ' || this_fk.TABLE_NAME || ' parent keys not found');

      END;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

  END enable_fkeys;

/***************************************************************************
 *
 *    NAME
 *      enable_fkeys(schema_name)
 *
 *    DESCRIPTION
 *      enable_fkeys searches the specified schema Foreign Key Constraints
 *      not enabled and enables those constraints.
 *
 */

  PROCEDURE enable_fkeys(schema_name IN VARCHAR2) IS
  BEGIN
    ref_user := UPPER(schema_name);

    IF ref_user IN ('SYS', 'SYSTEM') THEN
      RAISE sys_user_privilege;
    END IF;

    constraint_state := 'ENABLED';

    FOR this_fk IN other_fkeys LOOP
      sql_stmt := 'ALTER TABLE ' || this_fk.OWNER || '.' || this_fk.TABLE_NAME || ' MODIFY CONSTRAINT ' || this_fk.CONSTRAINT_NAME || ' ENABLE';

      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION WHEN parent_keys_not_found THEN
        DBMS_OUTPUT.PUT_LINE('Cannot ENABLE constraint ' || rpad(this_fk.CONSTRAINT_NAME, 32) || ' on table ' || this_fk.OWNER || '.' || this_fk.TABLE_NAME || ' parent keys not found');

      END;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure on a privileged user''s schema!');

   END enable_fkeys;

/***************************************************************************
 *
 *    NAME
 *      enable_triggers
 *
 *    DESCRIPTION
 *      enable_triggers searches the user's schema for triggers and
 *      enables those triggers.
 *
 */

  PROCEDURE enable_triggers IS
  BEGIN
    check_for_sys_user;

    FOR this_trigger IN my_triggers LOOP
      sql_stmt := 'ALTER TRIGGER ' || this_trigger.TRIGGER_NAME || ' ENABLE';

      EXECUTE IMMEDIATE sql_stmt;

    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

  END enable_triggers;

/***************************************************************************
 *
 *    NAME
 *      refresh_mviews([doComplete])
 *
 *    DESCRIPTION
 *      test_mviews looks for all of the Materialized Views in the current
 *      schema with remote masters and refreshes them. Optionally it will
 *      force a complete refresh.
 *
 *    PARAMETERS
 *      doCompleteRefresh FALSE Do not test the complete refresh of all
 *                              Materialized Views in the current schema
 *                        TRUE  Execute a complete refresh for all Materialized
 *                              Views in the current schema and display the
 *                              total time required for the refresh
 *
 */

  PROCEDURE refresh_mviews(doCompleteRefresh BOOLEAN DEFAULT FALSE) IS
  BEGIN
    DBMS_OUTPUT.ENABLE(NULL);
    mview_list := '';
    mview_method := '';

    -- For each materialized view in the current schema
    FOR this_mview IN my_mviews_rep LOOP

      -- Add the Materialized View name to a comma separated list for timing measures later
      IF my_mviews_rep%ROWCOUNT = 1 THEN
        -- Skip the comma before the first item in the list
        mview_list := this_mview.mview_name;
      ELSE
        -- Place a comma and a space after the privious item in the list and
        -- add the current Materialized View name to the list
        mview_list := mview_list || ', ' || this_mview.mview_name;
      END IF;

      -- Add a 'C' for Complete refresh to the refresh method string for each Materialized View in this schema
      mview_method := mview_method || 'C';
    END LOOP;

    IF doCompleteRefresh THEN
    -- Measure the time for a complete refresh of all Materialized Views
      start_replication := sysdate;
      dbms_mview.refresh(mview_list, mview_method);
      end_replication := sysdate;
      replication_time := (end_replication - start_replication) DAY TO SECOND;

      -- Display the results
      DBMS_OUTPUT.PUT_LINE('Time required for COMPLETE refresh: ' || replication_time);
    ELSE
    -- Measure the time for a default (FAST) refresh of all Materialized Views in this schema
      start_replication := sysdate;
      dbms_mview.refresh(mview_list);
      end_replication := sysdate;
      replication_time := (end_replication - start_replication) DAY TO SECOND;

      -- Display the results
      DBMS_OUTPUT.PUT_LINE('Time required for default (FAST) refresh: ' || replication_time);
    END IF;

  END refresh_mviews;

/***************************************************************************
 *
 *    NAME
 *      truncate_tables
 *
 *    DESCRIPTION
 *      truncate_tables searches the user's schema for tables and truncates
 *      all data in those tables.  Any Foreign Key constraints will be
 *      disabled before running this procedure.
 *
 */

  PROCEDURE truncate_tables IS
  BEGIN
    check_for_sys_user;
    disable_fkeys;
    FOR this_tab IN my_tables LOOP
      sql_stmt := 'TRUNCATE TABLE ' || this_tab.TABLE_NAME;
      BEGIN
        EXECUTE IMMEDIATE sql_stmt;

      EXCEPTION WHEN primary_keys_ref_fkeys THEN
        DBMS_OUTPUT.PUT_LINE('Cannot TRUNCATE table ' || this_tab.TABLE_NAME || ' dependencies exist in other schemas.');
      END;
    END LOOP;

    EXCEPTION
      WHEN sys_user_privilege THEN
        DBMS_OUTPUT.PUT_LINE('WARNING:  You cannot run this procedure as a privileged user!');

  END truncate_tables;

/***************************************************************************
 *
 *    NAME
 *      test_mviews
 *
 *    DESCRIPTION
 *      test_mviews looks for all of the Materialized Views in the current
 *      schema and counts the rows in each Materialized View and the
 *      corresponding table/view at the Materialized View's Master site. It
 *      can optionally measure the timing of a default and/or complete refresh
 *      of all Materialized Views in the current schema
 *
 *    PARAMETERS
 *      doRefresh         FALSE Do not test the default refresh of all
 *                              Materialized Views in the current schema
 *                        TRUE  Execute a default refresh for all Materialized
 *                              Views in the current schema and display the
 *                              total time required for the refresh
 *
 *      doCompleteRefresh FALSE Do not test the complete refresh of all
 *                              Materialized Views in the current schema
 *                        TRUE  Execute a complete refresh for all Materialized
 *                              Views in the current schema and display the
 *                              total time required for the refresh
 *
 */
  PROCEDURE test_mviews(doRefresh BOOLEAN DEFAULT FALSE,
                        doCompleteRefresh BOOLEAN DEFAULT FALSE) IS
  BEGIN
    DBMS_OUTPUT.ENABLE(NULL);

    -- For each materialized view in the current schema
    FOR this_mview IN my_mviews_rep LOOP

        -- Add the Materialized View name to a comma separated list for timing measures later
        IF my_mviews_rep%ROWCOUNT = 1 THEN
           -- Output results headers if this is the first row
           DBMS_OUTPUT.PUT_LINE('Materialized View Name           Master Database Link     Local Rows Master Rows');
           DBMS_OUTPUT.PUT_LINE('-------------------------------- ------------------------ ---------- -----------');
        END IF;

        -- Build a SQL statement to count the rows in the local Materialized View
        sql_stmt := 'SELECT COUNT(*) FROM ' || this_mview.mview_name;
        EXECUTE IMMEDIATE sql_stmt INTO local_row_count;

        -- Modify the SQL statement to count the rows in the table at the Master Site
        sql_stmt := sql_stmt || this_mview.master_link;
        EXECUTE IMMEDIATE sql_stmt INTO master_row_count;

        -- Display the results
        DBMS_OUTPUT.PUT_LINE(RPAD(this_mview.mview_name, 32) || ' ' ||
                             this_mview.master_link || ' ' ||
                             LPAD(master_row_count, 11) || ' ' ||
                             LPAD(local_row_count, 11));

    END LOOP;

    DBMS_OUTPUT.NEW_LINE;

    -- Measure the time for a default (FAST) refresh of all Materialized Views in this schema
    IF doRefresh THEN
       refresh_mviews;
    END IF;

    -- Measure the time for a complete refresh of all Materialized Views
    IF doCompleteRefresh THEN
      refresh_mviews(TRUE);
    END IF;
  END test_mviews;
END sds_util;
/


CREATE OR REPLACE PUBLIC SYNONYM SDS_UTIL FOR SDSADM.SDS_UTIL;


GRANT EXECUTE ON SDSADM.SDS_UTIL TO PUBLIC;
