The most common reason people get the ORA-30354: Query Rewrite Not Allowed on SYS Relations error is because they’re trying to create a materialized view on a table that exists in the SYS schema.
This is a security feature in Oracle to prevent people from doing nasty things with Oracle’s data dictionary–if Oracle allowed people to use query rewrite against objects owned by SYS, it would compromise the data dictionary tables and fixed views.
How to Fix ORA-30354
Fortunately, the fix for ORA-30354 is pretty simple. If you have:
- Created a table
And! - The table is owned by SYS
And! - You want to create a materialized view on this table
…then re-create the table under a different schema (not SYS).
As a general practice, it’s usually a bad idea to create objects under SYS, or even login as SYS. Generally, you want to stay out of this account.
Use the CREATE USER command to create a user other than SYS, and use this non-SYS user as your regular login to create things like tables and materialized views.
Below is a “create a non-sys user” starter script for your convenience, if you need one.
-- Create a new user named MY_USERNAME, and give them the password MY$PASSWORD
create user MY_USERNAME identified by MY$PASSWORD;
-- Give the new user permission make tables, materialized views, and
-- create a new database session
grant CREATE TABLE, CREATE MATERIALIZED VIEW, CREATE SESSION to MY_USERNAME;
-- Give the user a 1 gigabyte quota on the USERS tablespace
-- (The tablespace where you want to put your table/Materialized view may
-- not be called USERS--check with your DBA and change accordingly)
alter user MY_USERNAME quota 1G on USERS;Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
