Script to Extract Code to Recreate Materialized

This function will extract the DDL for an existing materialized view

SET serveroutput on
SET feedback off
UNDEF v_sql

DECLARE
v_task_name VARCHAR2 (100);
v_mview_owner VARCHAR2 (30) := UPPER ('&&ENTER_MVIEW_OWNER');
v_mview_name VARCHAR2 (30) := UPPER ('&&ENTER_MVIEW_NAME');
v_mview_sql VARCHAR2 (4000);
v_mview_log_sql VARCHAR2 (4000);
BEGIN
-- get mview text from data dictionary
SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner)
INTO v_mview_sql
FROM dba_mviews
WHERE owner = v_mview_owner AND mview_name = v_mview_name;

SELECT DBMS_METADATA.get_dependent_ddl ('MATERIALIZED_VIEW_LOG',
referenced_name,
referenced_owner
)
INTO v_mview_log_sql
FROM dba_dependencies
WHERE referenced_type = 'TABLE'
AND referenced_name != v_mview_name
AND owner = v_mview_owner
AND NAME = v_mview_name;

DBMS_OUTPUT.put_line ('MVIEW SQL Is: ' || v_mview_sql);
DBMS_OUTPUT.put_line ('MVIEW LOG SQL Is: ' || v_mview_log_sql);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/

One thought on “Script to Extract Code to Recreate Materialized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.