对一个实体化视图重建。

最新推荐文章于 2024-07-09 15:47:00 发布

iteye_8899

最新推荐文章于 2024-07-09 15:47:00 发布

阅读量92

点赞数

本文展示了如何使用PL/SQL过程成功刷新名为T89的物化视图，并提供了该视图的DDL定义。此外，还演示了删除、插入操作及回滚等步骤。

SQL> exec dbms_mview.refresh('T89','F')

PL/SQL procedure successfully completed.

SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','T89','ARADMIN') from dual

CREATE MATERIALIZED VIEW "ARADMIN"."T89"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE + 1) + 2/24
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT
C1,
C2,
C3,
C4,
C5,
C6,
C7,
C8,
C536870914,
C536870916,
C536870918,
C536870919,
C536870922,
C536870925,
C536870927,
C536870931,
C536870932,
C536870935,
C536870937,
C536870938,
C536870939,
C536870943,
C536870944,
C536870945,
C536870946,
C536870947,
C536870948,
C536870950,
C536870951,
C536870953,
C536870955,
C536870956,
C536870957,
C536870960,
C536870961,
C536870962,
C536870963,
C536870964,
C536870965,
C536870966,
C536870967,
C536870968,
C536870969,
C536870971,
C536870972,
C536870975,
C536870977,
C536870978,
C536870979,
C536870981,
C536870982,
C536870983,
C536870984,
C536870986,
C536870987,
C536870988,
C536870989,
C536870990,
C536870991,
C536870992,
C536870993,
C536870994,
C536870995,
C536870996,
C536870997,
C536870998,
C536870999,
C536871000,
C536871001,
C536871002,
C536871003,
C536871004,
C536871005,
C536871006,
C536871007,
C536871008,
C536871009,
C536871010,
C536871013,
C536871014,
C536871015,
C536871016,
C536871017,
C536871018,
C536871019,
C536871020,
C536871021,
C536871022,
C536871023,
C536871024,
C536871025,
C536871026,
C536871027,
C536871029,
C536871031,
C536871034,
C536871035,
C536871036,
C536871037,
C536871038,
C536871039,
C536871040,
C536871041,
C536871042,
C536871043,
C536871044,
C536871047,
C536871048,
C536871049,
C536871050,
C536871051,
C536871052,
C536871053,
C536871054,
C536871055,
C536871056,
C536871057,
C536871058,
C536871059,
C536871062,
C536871063,
C536871065,
C536871066,
C536871067,
C536871068,
C536871069,
C536871072,
C536871073,
C536871074,
C536871076,
C536871077,
C536871078,
C536871079,
C536871080,
C536871083,
C536871086,
C536871087,
C536871088,
C536871089,
C536871090,
C536871091 ,
C536871092 ,
C536871094 ,
C536871096 ,
C536871099 ,
C536871101 ,
C536871105 ,
C536871106 ,
C536871107 ,
C536871108 ,
C536871109 ,
C536871110 ,
C536871111 ,
C536871112 ,
C536871113 ,
C536871114 ,
C536871116 ,
C536871131 ,
C536871132 ,
C536871134 ,
C536871135 ,
C536871136,
C536871137,
C536871138,
C536871140,
C536871142,
C536871143,
C536871161,
C536871163,
C536871189,
C536871165,
C536871172,
C536871169,
C536871171,
C536871168,
C536871170,
C536871167,
C536871173,
C536871175,
C536871176,
C536871177,
C536871178,
C536871191,
C536871180,
C536871185,
C536871186,
C536871187,
C536871184,
C536871183,
C536871179,
C536871192,
C536871194,
C536871196,
C536871195,
C536871197,
C536871198,
C536871060,
C536871070,
C536871071,
C536871075,
C536871081,
C536871082,
C536871084,
C536871085,
C536871095,
C536871097,
C536870923,
C536870924,
C536870928,
C536870926,
C536870929,
C536871103,
C536870930,
C536870933,
C536871128,
C536870934,
C536871152,
C536870941,
C536871129,
C536870942,
C536870949,
C536870952,
C536870954,
C536870974,
C536870976,
C536870980,
C536870985,
C536871030,
C536871121,
C536871122,
C536871151,
C536871133,
C536871139,
C536871145,
C536871148,
C536871149,
C536871154,
C536871155,
C536871158,
C536871157,
C536871159,
C536871193,
C536871199,
C536871203,
C536871210,
C536871229,
C536871230,
C536871127,
C536871150,
C536871153,
C536871156,
C536871200,
C536871201
FROM aradmin.t89@arsprd.NILE002A_ZONE01.EAST

SQL>

drop materialized view t89 preserve table;

SQL> delete t89
2 where c6 > date_as_epoch('27-Jul-2007') and c6 < date_as_epoch('02-AUG-07');

4466 rows deleted.

SQL> insert into t89 select * from t89_subset;
insert into t89 select * from t89_subset
*
ERROR at line 1:
ORA-00001: unique constraint (ARADMIN.I89) violated

SQL> rollback;

Rollback complete.

SQL> delete t89 where c1 in(select c1 from t89_subset);

5317 rows deleted.

SQL> insert into t89 select * from t89_subset;

5317 rows created.

SQL> commit;

Commit complete.

recreate materialized view.

SQL> exec dbms_mview.refresh('T89','F')

PL/SQL procedure successfully completed.