LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon