The 'ORDER BY' Clause is Missing from the QUERY Column in DBA_MVIEWS.
(Doc ID 1273985.1)
Last updated on NOVEMBER 05, 2019
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
When creating a Materialized View it is noticed that the ORDER BY clause of the MV definition has disappeared, and this was without any error/warning message at MV creation time.
This can be seen by conducting a simple test, as follows:
Materialized view created.
SQL> select query from user_mviews where mview_name = 'TEST123_DUMMY_MV';
As can be seen, the "order by object_id" has been excluded. This occurs also on 10.2.0.3.0 and 220.127.116.11.0 database versions.
Even when running the DBMS_METADATA routines we can see the phrase 'ORDER BY' is missing:
set pagesize 80
col DDL format a140
select DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', '<MView_Name>','<OWNER_SCHEMA>') DDL from dual;
CREATE MATERIALIZED VIEW "<OWNER_SCHEMA>"."<MView_Name>" ("OBJECT_NAME", "SUBOBJECT_NAME",
"OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIME
STAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_N
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select * from user_objects
None, as this is expected behaviour as can be seen below.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document