Oracle Materialized View Manual Refresh

Following is the call that can be made to refresh your materialized view in Oracle manually:

1
EXECUTE DBMS_SNAPSHOT.REFRESH('[MV_NAME]','[refresh_option]');

Refresh Options:

  • ? – Uses default
  • F or f – Fast
  • C or c – Complete
  • A – Always complete

One Comment on “Oracle Materialized View Manual Refresh”

  1. Dear all,
    This is Code For Refresh Materialized in .NET
    Public Sub RefreshMView()
    Dim tran As OracleTransaction
    Dim Con As String = “Data Source=” & ServerName & “;User ID=” & UserName & “;Password=” & Pwd & “;Unicode=True”
    Dim Conn As New OracleConnection(Con)
    Try
    Conn.Open()
    tran = Conn.BeginTransaction
    Dim cmd As New OracleCommand()
    cmd.Connection = Conn
    cmd.Transaction = tran
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = “refresh_P_V_PO_QTY”
    cmd.ExecuteNonQuery()
    tran.Commit()
    Conn.Close()
    cmd = Nothing
    Catch ex As Exception
    tran.Rollback()
    Finally
    Conn.Close()
    End Try
    End Sub

    //Procedure in ORACLE For Refresh Materialized View in VB.NET
    CREATE PROCEDURE refresh_P_V_PO_QTY
    IS
    v_error_code NUMBER;
    v_error_message VARCHAR2 (400);
    v_date DATE;
    BEGIN
    /*Materialized 1*/
    dbms_mview.refresh(‘V_SCH_QTY’,’C’);
    COMMIT;
    /*Materialized 2*/
    dbms_mview.refresh(‘V_DISP_QTY’,’C’);
    COMMIT;
    /*Materialized 3*/
    dbms_mview.refresh(‘V_PO_QTY’,’C’);
    COMMIT;
    /*Materialized 4*/
    dbms_mview.refresh(‘V_PO_DETAIL’,’C’);
    COMMIT;
    EXCEPTION
    WHEN OTHERS
    THEN
    v_error_code := SQLCODE;
    v_error_message := SUBSTR (SQLERRM, 1, 100);
    DBMS_OUTPUT.put_line ( ‘mv_opbyiss_temp_or:-‘
    || ‘ Date:’
    || v_date
    || ‘ ‘
    || v_error_code
    || ‘ ‘
    || v_error_message
    );
    END;

Leave a Reply

Your email address will not be published. Required fields are marked *