Hiding a materialized view definition

You can hide a materialized view definition from users, which obfuscates the view definition stored in the database. This setting is irreversible.

Prérequis

You must be the owner of the materialized view or have one of the following system privileges:

ALTER ANY MATERIALIZED VIEW
ALTER ANY OBJECT

Contexte et remarques

When a materialized view is hidden, debugging using the debugger does not show the view definition, nor is the definition available through procedure profiling. The view can still be unloaded and reloaded into other databases.

Hiding a materialized view is irreversible and only possible using SQL.

Task

Connect to the database.
Execute an ALTER MATERIALIZED VIEW...SET HIDDEN statement.

Résultat

An automatic commit is executed.

The view is no longer visible when browsing the catalog. The view can still be directly referenced, and is still eligible for use during query processing.

Exemple

The following statements create a materialized view, EmployeeConfid3, refresh it, and then obfuscate its view definition.

CREATE MATERIALIZED VIEW EmployeeConfid3 AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid3;
ALTER MATERIALIZED VIEW EmployeeConfid3 SET HIDDEN;

Caution

When you are done running the following example, you should drop the materialized view you created. Otherwise, you will not be able to make schema changes to its underlying tables, Employees and Departments, when trying out other examples.