Important Views:
select * from DBA_REFRESH; -- describes all refresh groups in the database
select * from DBA_REFRESH_CHILDREN; -- lists all of the objects in all refresh groups in the database
select * from dba_jobs; -- describes all jobs in the database
DBMS_REFRESH - Procedure MAKE
The MAKE procedure is used to create a new Refresh group.
We will make a refresh group Test_Refresh_Group_1:
Code: Select all
SQL> BEGIN DBMS_REFRESH.MAKE(
name => 'Test_Refresh_Group_1',
list => ' Daily_Sales_MV, Daily_Expenses_MV',
next_date => sysdate,
interval => 'sysdate + 1/24');
END;
/DBMS_REFRESH - Procedure ADD
Add a snapshot/materialized view to the already existing refresh group:
Code: Select all
SQL> BEGIN DBMS_REFRESH.ADD(
name => 'Test_Refresh_Group_1',
list => 'Weekly_Report_MV');
END;
/DBMS_REFRESH - Procedure SUBTRACT
Removes a snapshot/materialized view from the already existing refresh group.
Code: Select all
SQL> BEGIN DBMS_REFRESH.SUBTRACT(
name => 'Test_Refresh_Group_1',
list => 'Daily_Sales_MV');
END;
/DBMS_REFRESH - Procedure REFRESH
Manually refreshes the already existing refresh group.
Code: Select all
SQL> BEGIN DBMS_REFRESH.REFRESH(
name => 'Test_Refresh_Group_1');
END;
/The CHANGE procedure is used to change the refresh interval of the refresh group.
Code: Select all
SQL> BEGIN DBMS_REFRESH.CHANGE(
name => 'Test_Refresh_Group_1',
next_date => NULL,
interval => 'sysdate+1/96');
END;
/DBMS_REFRESH - Procedure DESTROY
Removes all materialized views from the refresh group and deletes the refresh group.
Code: Select all
SQL> BEGIN DBMS_REFRESH.DESTROY(
name => 'Test_Refresh_Group_1');
END;
/