My Oracle Support Banner

DDL Not Generated For Materialized View When "On Prebuilt Table" Is Used (Doc ID 2976113.1)

Last updated on JANUARY 08, 2024

Applies to:

Oracle SQL Developer - Version 23.1 and later
Information in this document applies to any platform.

Symptoms

DETAILED PROBLEM DESCRIPTION
============================
When a materialized view is created with "ON PREBUILT TABLE WITH REDUCED PRECISION", under "SQL" tab, no DDL is generated even if SELECT_CATALOG_ROLE & SELECT ANY DICTIONARY are granted to the connected user.
If "ON PREBUILT TABLE WITH REDUCED PRECISION" is not used, the DDL is generated successfully.

Steps-to-reproduce
==================

---------------------------------------------------------------
SCENARIO 1
---------------------------------------------------------------
Create user with SELECT_CATALOG_ROLE & SELECT ANY DICTIONARY role + Materialized View WITH "ON PREBUILT TABLE WITH REDUCED PRECISION"

1. Create user:
create user testsqltab1 identified by testsqltab1;

2. Grant privileges:

grant create materialized view to testsqltab1;
grant create table to testsqltab1;
grant connect to testsqltab1;
Grant SELECT_CATALOG_ROLE to testsqltab1;
Grant SELECT ANY DICTIONARY to testsqltab1;

3. connect to testsqltab1:

4. Create table my_objects & materialized view + ON PREBUILT TABLE WITH REDUCED PRECISION

create table my_objects as select object_name, object_type, created from user_objects;

create materialized view my_objects ON PREBUILT TABLE WITH REDUCED PRECISION as select object_name, object_type, created from user_objects;
commit;

5. Check results:

Check Sql tab for materialized view "MY_OBJECTS"
RESULTS: no DDL UNDER "SQL" tab.

---------------------------------------------------------------
SCENARIO 2
---------------------------------------------------------------
Create user with SELECT_CATALOG_ROLE & SELECT ANY DICTIONARY role + Materialized View (without "ON PREBUILT TABLE WITH REDUCED PRECISION")

1. Create user:
create user testsqltab2 identified by testsqltab2;

2. Grant privileges:
grant create materialized view to testsqltab2;
grant create table to testsqltab2;
grant connect to testsqltab2;
Grant SELECT_CATALOG_ROLE to testsqltab2;
Grant SELECT ANY DICTIONARY to testsqltab2;

3.Connect to testsqltab2:

4. Create table my_objects & materialized view ( without ON PREBUILT TABLE WITH REDUCED PRECISION)

create table my_objects as select object_name, object_type, created from user_objects;

create materialized view my_objects1 as select object_name, object_type, created from user_objects;
commit;

5. Check results:

Check Sql tab for materialized view "MY_OBJECTS1"
---> DDL is generated as expected.

NOTE: Even if SELECT_CATALOG_ROLE & SELECT ANY DICTIONARY roles are not granted, the DDL is generated successfully.

Changes

 None.

Cause

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
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.