| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: MaterializedView last update?
Sven <sven_horn_at_web.de> wrote in message news:<40866AF7.80105_at_web.de>...
> Hi all,
>
> I'm using Oracle 9.2.
> how can I get the last refresh-date of my Materialized View using SQL?
> Is it possible to get the last change-date of a table using SQL ?
>
> thank you
> sven
Sven,
In summary,
(1) To monitor a materialized view, use the view,
'USER/ALL/DBA_MVIEW_REFRESH_TIMES' to query the value of the
LAST_REFRESH column;
(2) To monitor a table, use the view, 'USER/ALL/DBA_TAB_MODIFICATIONS'
for tables that have the MONITORING option set. Query the value of
the TIMESTAMP column. Be aware that this value can be up to fifteen
(15) minutes after the last modification time.
This test was done on 9.2.0.1 on WinXP Pro.
(1) Setting up the test environment
Tables, EMP and DEPT, are created via CTAS from the standard SCOTT schema. I will create the materialized view over these tables and enable MONITORING on them. Note that the materialized view does have FAST REFRESH enabled.
CREATE MATERIALIZED VIEW
emp_dept
AS
SELECT
e.ename,
d.dname,
d.loc
FROM
emp e
INNER JOIN
dept d
ON
e.deptno = d.deptno
Following the advice given by Johnathon Lewis on p.73 of "Practical Oracle 8i" (Addison-Wesley:2001), I turned table monitoring on with the following commands:
ALTER TABLE emp MONITORING;
ALTER TABLE dept MONITORING;
(2) Initial analysis
Prior to any DML on the base tables, I queried the static data dictionary views. There were no rows in USER_TAB_MODIFICATIONS, while the materialized view showed the creation time of the materialized view:
SQL> EXEC tkyte.print_table('SELECT * FROM user_mview_refresh_times')
OWNER : TEST_USER NAME : EMP_DEPT MASTER_OWNER : TEST_USER MASTER : DEPT LAST_REFRESH : 23-APR-2004 14:58:03
(3) Generate Test Data
INSERT INTO dept
(deptno, dname, loc)
VALUES
( 60, 'Web.de', 'Germany')
/
After fifteen minutes, I inserted into the EMP table:
INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
( 8000, 'SVEN', 'DBA', 7839, SYSDATE, 4000, NULL, 60)
/
The following query indicates that, in 9.2.0.1, the data collection for table modifications is done about every fifteen (15) minutes:
SQL> EXEC tkyte.print_table('SELECT * FROM user_tab_modifications')
TABLE_NAME : DEPT PARTITION_NAME : SUBPARTITION_NAME : INSERTS : 1 UPDATES : 0 DELETES : 0 TIMESTAMP : 23-APR-2004 15:17:21 TRUNCATED : NO ----------------- TABLE_NAME : EMP PARTITION_NAME : SUBPARTITION_NAME : INSERTS : 1 UPDATES : 0 DELETES : 0 TIMESTAMP : 23-APR-2004 15:32:42 TRUNCATED : NO -----------------
Note this is the time the view, 'USER_TAB_MODIFICATIONS', was updated not the time when the tables, 'DEPT' and 'EMP', were updated.
The materialized view shows no change as to be expected.
SQL> EXEC tkyte.print_table('SELECT * FROM user_mview_refresh_times')
OWNER : TEST_USER NAME : EMP_DEPT MASTER_OWNER : TEST_USER MASTER : DEPT LAST_REFRESH : 23-APR-2004 14:58:03 -----------------
(4) Manual Refresh of Materialized View
I manually refreshed the materialized view and checked the LAST_REFRESH column was updated.
SQL> EXEC dbms_mview.refresh('EMP_DEPT')
SQL> EXEC tkyte.print_table('SELECT * FROM user_mview_refresh_times')
OWNER : TEST_USER NAME : EMP_DEPT MASTER_OWNER : TEST_USER MASTER : DEPT LAST_REFRESH : 23-APR-2004 16:18:16 -----------------
(5) Setting Up Automatic Refresh of Materialized View
Because there is no unique constraint on the DEPTNO column in the DEPT table (ORA-12051), and the MATERIALIZED VIEW compiler does not recognise INNER JOIN as a join operator (ORA-12054), I had to drop the MATERIALIZED VIEW and recreate it as follows:
CREATE MATERIALIZED VIEW
emp_dept
REFRESH COMPLETE ON COMMIT
AS
SELECT
e.ename,
d.dname,
d.loc
FROM
emp e,
dept d
WHERE
e.deptno = d.deptno
(6) Even More Test Data
I inserted one more row and did a COMMIT. The LAST_REFRESH column was immediately updated but the TIMESTAMP column for the base tables was not updated until about four (4) minutes later.
INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
( 8001, 'SVEN', 'DBA', 7839, SYSDATE, 4000, NULL, 60)
/
Note that the output of the static view is now different because I changed the JOIN structure.
SQL> EXEC tkyte.print_table('SELECT * FROM user_mview_refresh_times')
OWNER : TEST_USER
NAME : EMP_DEPT
MASTER_OWNER : TEST_USER
MASTER : DEPT
LAST_REFRESH : 23-APR-2004 17:33:58
-----------------
OWNER : TEST_USER
NAME : EMP_DEPT
MASTER_OWNER : TEST_USER
MASTER : EMP
LAST_REFRESH : 23-APR-2004 17:33:58
-----------------
SQL> EXEC tkyte.print_table('SELECT * FROM user_tab_modifications')
TABLE_NAME : DEPT
PARTITION_NAME :
SUBPARTITION_NAME :
INSERTS : 1
UPDATES : 0
DELETES : 0
TIMESTAMP : 23-APR-2004 15:17:21
TRUNCATED : NO
-----------------
TABLE_NAME : EMP
PARTITION_NAME :
SUBPARTITION_NAME :
INSERTS : 3
UPDATES : 0
DELETES : 1
TIMESTAMP : 23-APR-2004 17:37:53
TRUNCATED : NO
Douglas Hawthorne Received on Fri Apr 23 2004 - 02:54:43 CDT
 |
 |