// This script uses system catalog tables to create a materialized view. If we create indexes on the mat view that are in a different scheam we need to make sure to extract and script them as well. Currently you will need to use the schema script genearator to script the indexes with the view
CREATE MATERIALIZED VIEW C##IEG.MGMT_ECM_MD_ALL_TBL_01
("MetaData_ID", "Target_Type", "SnapShot_Type", "Table_Name", "Name", "UI_Name", "Type", "Type_Format", "UI_On", "Compare_On", "Compare_UI_On",
"History_On", "History_UI_On", "Is_Key", "Is_Context", "Is_Summary", "Is_Child_Link", "Link_Column_Name", "Col_Order", "Source_Table_Name")
PCTFREE 44 PCTUSED 25 INITRANS 7
STORAGE (INITIAL 65536 NEXT 41536 MINEXTENTS 88 MAXEXTENTS 999 PCTINCREASE 3 FREELISTS 3 FREELIST GROUPS 2 BUFFER_POOL KEEP)
TABLESPACE EXAMPLE LOGGING
MAXTRANS 123
NOCOMPRESS NOCACHE NOPARALLEL BUILD DEFERRED
USING INDEX
PCTFREE 18 MAXTRANS 119 STORAGE (INITIAL 11111 NEXT 11536 MINEXTENTS 188 MAXEXTENTS 199 PCTINCREASE 4 FREELISTS 4 FREELIST GROUPS 3 BUFFER_POOL KEEP)
REFRESH FORCE ON DEMAND DISABLE QUERY REWRITE
AS SELECT c.METADATA_ID,
m.TARGET_TYPE, m.SNAPSHOT_TYPE, t.name as TABLE_NAME, c.NAME, c.UI_NAME, c.TYPE, c.TYPE_FORMAT, c.UI_ON, c.COMPARE_ON, c.COMPARE_UI_ON, c.HISTORY_ON, c.HISTORY_UI_ON,
c.IS_KEY, c.IS_CONTEXT, c.IS_SUMMARY, c.IS_CHILD_LINK, c.LINK_COLUMN_NAME, rank() over (partition by c.metadata_id, t.name order by anc.tbl_order asc, c.col_order asc) as COL_ORDER,
c.table_name as SOURCE_TABLE_NAME
FROM sysman.mgmt_ecm_snapshot_metadata m, sysman.mgmt_ecm_snapshot_md_tables t, sysman.mgmt_ecm_snapshot_md_tables anc, sysman.mgmt_ecm_snapshot_md_columns c
WHERE (anc.name, anc.metadata_id) in (SELECT anc_t.name, anc_t.metadata_id FROM sysman.mgmt_ecm_snapshot_md_tables anc_t START WITH anc_t.name = t.name
AND anc_t.metadata_id = t.metadata_id CONNECT BY anc_t.name = PRIOR anc_t.parent_table_name AND anc_t.metadata_id = PRIOR anc_t.metadata_id)
AND c.metadata_id = anc.metadata_id AND c.table_name = anc.name AND c.metadata_id = m.metadata_id AND m.kind = 'P'
AND ((c.is_key = 'Y') OR (anc.name = t.name))
GO
CREATE INDEX CHINOOK.MGMT_ECM_MD_ALL_TBL_01IDX01 ON C##IEG.MGMT_ECM_MD_ALL_TBL_01 (UPPER("Name"))
GO
CREATE INDEX HR.MGMT_ECM_MD_ALL_TBL_01IDX02 ON C##IEG.MGMT_ECM_MD_ALL_TBL_01 ("Link_Column_Name") tablespace EXAMPLE
GO
CREATE INDEX BI.MGMT_ECM_MD_ALL_TBL_01IDX03 ON C##IEG.MGMT_ECM_MD_ALL_TBL_01 ("Col_Order") STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED)
GO
// make sure that column expressions are correctly scripted without quotes.
// We will create some tables to create materialized viess with the ON PREBUILD option. I am unable to extract the [WITH/WITHOUT REDUCED PRECISION] option information
CREATE TABLE C##IEG.IEGTESTME (a NUMBER(15,5) NOT NULL, b VARCHAR2(25) NULL, c VARCHAR2(25) NULL, d DATE NOT NULL, e VARCHAR2(25) NULL)
GO
CREATE TABLE C##IEG.IEGTESTME1 (a NUMBER(15,5) NOT NULL, b VARCHAR2(25) NULL, c VARCHAR2(25) NULL, d DATE NOT NULL, e VARCHAR2(25) NULL)
GO
CREATE TABLE C##IEG.IEGTESTME2 (a NUMBER(15,5) NOT NULL, b VARCHAR2(25) NULL, c VARCHAR2(25) NULL, d DATE NOT NULL, e VARCHAR2(25) NULL)
GO
ALTER TABLE C##IEG.IEGTESTME1 ADD CONSTRAINT IEGPK01 PRIMARY KEY(A)
GO
CREATE MATERIALIZED VIEW C##IEG.IEGTESTME ON PREBUILT TABLE WITH REDUCED PRECISION AS SELECT A, B, C, D, E FROM C##IEG.IEGTESTME1
GO
CREATE MATERIALIZED VIEW C##IEG.IEGTESTME2 ON PREBUILT TABLE WITHOUT REDUCED PRECISION AS SELECT A, B, C, D, E FROM C##IEG.IEGTESTME1
GO
// We need to create a materiazlied log on a table so that we can create a materialized view with the refresh fast on commit option
CREATE MATERIALIZED VIEW LOG ON C##IEG.IEGTESTME1 WITH PRIMARY KEY INCLUDING NEW VALUES
GO
CREATE MATERIALIZED VIEW C##IEG.IEGTESTMERF
USING INDEX PCTFREE 18 MAXTRANS 119 STORAGE (INITIAL 11111 NEXT 11536 MINEXTENTS 188 MAXEXTENTS 199 PCTINCREASE 4 FREELISTS 4 FREELIST GROUPS 3 BUFFER_POOL KEEP)
REFRESH FAST ON COMMIT
AS SELECT A, B, C, D, E FROM C##IEG.IEGTESTME1
GO
CREATE UNIQUE INDEX IEGTESTMERFIDX01 ON C##IEG.IEGTESTMERF (UPPER("A"))
GO
// We will need to create an object type to create a materialized view with the [OF OBJECT_TYPE] option. You can compare this DDL with the scripting in the oracle sql developer.
// I have scripted this lower materialized view with lots of options to see how much we are recreating. I have made dramatic changes to the extraction for oracle 10g and above
// but I'm sure we can extract even more information. We currently do not extract any partitioning information.
CREATE OR REPLACE TYPE "C##IEG"."PrOjEcT_TYPE" as object (name varchar2(20), stdate date)
GO
CREATE MATERIALIZED VIEW "C##IEG"."IEGTESTMERFOFOBJTYPE" OF "C##IEG"."PrOjEcT_TYPE"
ORGANIZATION HEAP PCTFREE 15 PCTUSED 44 INITRANS 3 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE
(INITIAL 925696 NEXT 114688 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE
(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE"
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS
DISABLE QUERY REWRITE
AS SELECT b, d FROM C##IEG.IEGTESTME1
GO
CREATE UNIQUE INDEX "C##IEG"."idxdropme01" ON "C##IEG"."IEGTESTMERFOFOBJTYPE" ("NAME", "STDATE")
PCTFREE 8 INITRANS 3 MAXTRANS 144 COMPUTE STATISTICS
STORAGE(INITIAL 11111 NEXT 33333 MINEXTENTS 7 MAXEXTENTS 1111111111
PCTINCREASE 3 FREELISTS 4 FREELIST GROUPS 2 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE"
GO
Check under Oracle 11g SYSMAN schema and use Script Generator to script the MAT View named MGMT_ECM_MD_ALL_TBL_COLUMNS and you can see that it is displayed twice.