Your organization is growing, and their managers from different states are in need of data warehousing. You should pre-compute database information into a summary data aggregated to be easily understood.

They are asking you to create a materialized view to pre-compute and store information manually on the number of employees with their total salary paid for each department (to project the employees’ payment budget). They want, when new employees and their salaries are entered into the base tables, to be able to manually update their materialized view with its summary, aggregated data, to reflect these changes from the base tables.

They should be able to download a subset of data from the company’s headquarter table to another company associated servers, assuming that their system is not in a distributed environment.

In a distributed environment, they could use materialized views to replicate data at distributed sites and synchronize updates between these sites.

Now, the company wants the ability to create an aggregated snapshot for the accounting department only. The required data include the total number of employees, and the total paid salaries for the accounting department only. Once the aggregated snapshot was created, it should define, in such a way, that it can update the materialized view once a month without any human intervention. The snapshot must be refreshed, based on the rule that your client asked.

-- Hands-On 01 (Materialized Views - The view that stores data) 

SET ECHO ON
CLEAR SCR
-- Log in to sqlplus as the iself user.
--
Pause

CONNECT iself/schooling
pause


CLEAR SCR
-- To do this Hands-On exercise, you must first create
-- a materialized view log.

-- Create a materialized view log with a PRIMARY KEY, 
-- the ROWID, and <INCLUDING NEW VALUES> options.
--
pause

CREATE MATERIALIZED VIEW LOG ON emp
WITH PRIMARY KEY, 
ROWID (deptno) 
INCLUDING NEW VALUES 
/ 
pause

CLEAR SCR
-- Begin by setting your linesize to 1000.
--
pause 

SET LINESIZE 1000;
pause

CLEAR SCR
-- Query your snapshot logs' view.
--
Pause

SELECT * FROM user_snapshot_logs 
/
-- This result shows that your log was 
-- created successfully and named MLOG$_EMP.
--
pause

CLEAR SCR
-- Create a materialized view with the BUILD IMMEDIATE 
-- and REFRESH ON DEMAND options.

-- Your materialized view should have the department number, 
-- number of employees, and total salaries paid to employees 
-- by department.
--
pause


CREATE MATERIALIZED VIEW mv_sal 
BUILD IMMEDIATE 
REFRESH ON DEMAND 
AS SELECT deptno, COUNT(1) AS no_of_emp, SUM(sal) AS salary 
FROM emp 
GROUP BY deptno 
/
-- Notice on the error message INSUFFICIENT PRIVILEGE.

-- In order to create a materialized view, you must be granted a
-- CREATE MATERIALIZED VIEW privilege.
--
Pause

CLEAR SCR
-- Connect to SQLPLUS as system/manager.

pause

CONNECT system/manager
pause

CLEAR SCR
-- Grant the CREATE MATERIALIZED VIEW privilege 
-- to the iself user.
--
pause

GRANT CREATE MATERIALIZED VIEW TO iself 
/
pause

CLEAR SCR
-- Now, log back in as the iself user.
--
pause

CONNECT iself/schooling
pause

CLEAR SCR
-- This time you should be able to create a
-- materialized view without any problems.

-- Create your materialized view again.
--
pause

CREATE MATERIALIZED VIEW mv_sal 
BUILD IMMEDIATE 
REFRESH ON DEMAND 
AS SELECT deptno, COUNT(1) AS no_of_emp, SUM(sal) AS salary 
FROM emp 
GROUP BY deptno 
/
-- Note that this time the materialized view was 
-- created successfully.
--
Pause

CLEAR SCR
-- Query the user's materialized views.
--
pause

SELECT * FROM user_mviews
/
pause 

CLEAR SCR
-- Query the mv_sal materialized view.
--
pause

SELECT * FROM mv_sal
/
-- Note the total salary paid to department 10.
pause

CLEAR SCR
-- Query all the information about empno 7934.
--
pause

SELECT * FROM emp WHERE empno = 7934
/
-- Make a note about her salary.
--
pause 

CLEAR SCR
-- Change her salary to 5000 and save it
-- into the EMP table.
--
Pause

UPDATE emp 
SET sal = 5000 
WHERE empno = 7934
/ 
COMMIT
/
-- The salary column was changed.
-- 
pause

CLEAR SCR
-- Query the mv_sal materialized view.
--
Pause

SELECT * FROM mv_sal
/
-- Notice that there are no changes made on 
-- the total salary in department 10.
--
Pause

CLEAR SCR
-- Refresh the mv_sal materialized view by 
-- executing the REFRESH procedure at the 
-- DBMS_SNAPSHOT package and use the letter C
-- for the complete option.
--
pause

EXECUTE dbms_snapshot.refresh('mv_sal','C') 
/ 
pause

CLEAR SCR
-- Now, query your mv_sal materialized view.
--
pause

SELECT * FROM mv_sal 
/
-- Notice that the total salary paid to department 10
-- was changed. In fact, you have manually updated 
-- the materialized view.

pause

CLEAR SCR
-- Now, let's create a materialized view named mv_account 
-- that would be updated every month automatically without
-- any human intervention.
--
pause

CREATE MATERIALIZED VIEW mv_account 
BUILD IMMEDIATE 
REFRESH FORCE
START WITH SYSDATE NEXT (SYSDATE+30) 
WITH PRIMARY KEY 
AS 
SELECT * FROM emp 
WHERE deptno = 10 
/
-- Notice that the START WITH SYSDATE option will create 
-- an immediate data, and the NEXT (SYSDATE+30) option
-- will update the table every 30 days.
--
pause

CLEAR SCR
-- Check to see how many materialized views you have created.
-- 
pause

SELECT * FROM user_mviews
/
-- You got one more materialized view.
pause 

CLEAR SCR
-- Query the mv_account materialized view.
--
pause

SELECT * FROM mv_account
/
pause 

CLEAR SCR
-- Insert a record to your EMP table.
--
pause


INSERT INTO emp VALUES 
(9999,'John','Kazerooni',7782,'04-Apr-02',1400,500,10)
/
COMMIT
/
-- A record was added.
--
pause 

CLEAR SCR
-- Since you have 30 days to see your changes,
-- you can update the mv_account materialized view by 
-- executing the REFRESH procedure from the 
-- DBMS_SNAPSHOT package and use the C option for 
-- the complete option.
--
pause

EXECUTE dbms_snapshot.refresh('mv_account','C') 
/ 
pause

CLEAR SCR 
-- Now, query the mv_account table.
--
pause

SELECT * FROM mv_account
/
-- Notice that employee number 9999 was added
-- to the materialized view.
--
pause

CLEAR SCR
-- Check your jobs' queue.
--
pause

SELECT * FROM user_jobs
/
-- Notice that this job is going to run each month.
-- Make a note of it's job number.
--
pause

CLEAR SCR
-- Execute the REMOVE procedure from the DBMS_JOB package
-- to delete a job from the job's queue.
-- Use the runtime variable to enter your job number.
--
pause
EXECUTE dbms_job.remove(&Enter_job_number)
/
pause

CLEAR SCR
-- Delete the added employee number 8888 and 9999 
-- and change the employee's salary back to 1000.
--
pause

DELETE FROM emp WHERE empno IN (8888,9999);
UPDATE emp 
SET sal = 1000 
WHERE empno = 7934
/ 
COMMIT
/
pause

CLEAR SCR
-- Drop all of the created Materialized views.
-- 
pause

DROP MATERIALIZED VIEW LOG ON emp
/
DROP MATERIALIZED VIEW mv_sal
/
DROP MATERIALIZED VIEW mv_account
/
pause

CLEAR SCR
-- Log back in as system/manager and revoke 
-- the CREATE MATERIALIZED VIEW privilege 
-- from the ISELF user.
--
pause

CONNECT system/manager
REVOKE CREATE MATERIALIZED VIEW FROM iself
/
-- You have now cleared your session, so that you can
-- perform this excercise over and over again.
pause

CONNECT iself/schooling

CLEAR SCR
-- Now, You should practice this Hands-On over and over
-- until you become a master at it.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause