Vue matérialisée Oracle et erreur ORA-01031
Résoudre l'erreur ORA-01031 lors de la création d'une vue matérialisée dans un autre schéma, et récupérer son DDL.
La création d'une vue matérialisée dans un autre schéma peut échouer avec l'erreur ORA-01031 si l'utilisateur ne possède pas le privilège CREATE TABLE. Une vue matérialisée stocke ses données dans une table, ce privilège est donc nécessaire.
Erreur
CREATE MATERIALIZED VIEW app_owner.app_mv AS SELECT * FROM dual;
ERROR at line 1:
ORA-01031: insufficient privileges
Solution
Accorder le privilège CREATE TABLE directement au propriétaire du schéma :
alter session set current_schema = app_owner;
GRANT CREATE TABLE TO app_owner;
Vérification
Contrôler l'utilisateur courant et ses privilèges système, puis recréer la vue matérialisée :
select user from dual;
select * from user_sys_privs;
CREATE MATERIALIZED VIEW app_owner.app_mv AS SELECT * FROM dual;
Materialized view created.
Récupérer la définition de la vue matérialisée
Générer le DDL d'une vue matérialisée avec DBMS_METADATA :
set long 200000 pages 0 lines 131 doc off
column txt format a121 word_wrapped
spool recreate_sql
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','APP_MV','APP_OWNER') txt from dual;
spool off
set pages 9999
La requête d'une vue matérialisée existante est également disponible dans dba_mviews :
select query from dba_mviews
where mview_name = 'your materialized view';
Références
- https://www.krenger.ch/blog/create-materialized-view-ora-01031/
- https://support.oracle.com/epmos/faces/DocContentDisplay?id=749112.1