Does the following query, that shows inconsistencies in data dictionary,return
anything?
SELECT sowner, vname FROM sys.snap_refop$
WHERE operation# IN (21, 22)
group by sowner,vname
having count(*) = 1 ;
Best regards,
Nenad
Von meinem iPhone gesendet
Am 16.05.2023 um 21:25 schrieb Ls Cheng <exriscer@xxxxxxxxx>:
Hi
I am trying to create a MV on a prebuilt table in Oracle 19c, running the
create statements in SYS for user lsc, at the bottom are the DDL's. The
problem is the MV create statement fails with ORA-01031 because of the ENABLE
QUERY REWRITE clause in the create statement however the user already has the
required privileges. Anyone know if I am missing any privilege?
Thanks
-- connected as SYS
create user lsc identified by lsc;
grant connect, resource, unlimited tablespace to lsc;
grant create TABLE to lsc;
grant create MATERIALIZED view to lsc;
grant on commit refresh to lsc;
grant global query rewrite to lsc;
create table lsc.t1
as
select sysdate mydate, a.*
from dba_objects a;
CREATE MATERIALIZED VIEW LOG ON lsc.t1 with rowid, SEQUENCE (
MYDATE ,
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
OBJECT_ID ,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY ,
NAMESPACE ,
EDITION_NAME ,
SHARING ,
EDITIONABLE ,
ORACLE_MAINTAINED ,
APPLICATION ,
DEFAULT_COLLATION ,
DUPLICATED ,
SHARDED ,
CREATED_APPID ,
CREATED_VSNID ,
MODIFIED_APPID ,
MODIFIED_VSNID
)
including new values;
create table lsc.t1_mv
as
select owner, count(*) cnt, sum(object_id) sum_object_id
from lsc.t1
group by owner;
create materialized view lsc.t1_mv
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FAST ON COMMIT
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION
ENABLE QUERY REWRITE
as
(
select owner, count(*) cnt, sum(object_id) sum_object_id
from lsc.t1
group by owner
);
