Materialized view

Last updated 11 months ago
--DROP MATERIALIZED VIEW MV_USERS;
--DROP TABLE MV_USERS;
CREATE MATERIALIZED VIEW MV_USERS
TABLESPACE TBSUN4_TABLES
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
WITH PRIMARY KEY
AS
/* Formatted on 30.11.2010 10:04:25 (QP5 v5.115.810.9015) */
SELECT NVL (i.ivalue, i.svalue) cod, n.svalue username, i.obj_id
FROM a$adm o, a$adp i, a$adp n
WHERE o.obj_type = 7
AND o.obj_subtype = 0
AND i.obj_id = o.obj_id
AND i.key = 'ID'
AND n.obj_id = o.obj_id
AND n.key = 'USERNAME';
COMMENT ON MATERIALIZED VIEW MV_USERS IS 'snapshot table for snapshot MV_USERS';
CREATE UNIQUE INDEX MVUSERS_UQ ON MV_USERS
(COD)
LOGGING
TABLESPACE TBSUN4_TABLES
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;