Les vues matérialisées

Administration Oracle 12 Commentaires »

Une vue matérialisée (VM) est un moyen simple de créer une vue physique d’une table. Comme son nom l’indique et ? la différence d’une vue standard, les données sont dupliquées. On l’ utilise ? des fins d’optimisation de performance, lorsque le select associé est particulièrement complexe ou lourd, ou pour faire des réplications de table.
La ‘fraicheur’ des données de la VM dépend des options choisies. Le décalage entre les données de la table maître et la VM peut être nul (raffraichissement synchrone) ou d’une durée planifiée : heure, jour,etc.
Suivant le contexte il existe différents types de VMs possibles : sur clé primaire, rowid, et plus ou moins complexes : avec fonctions aggrégées, sous requêtes, jointures, etc.

Bien que séduisants ces objets devront être utilisés avec parcimonie et ? bon escient. Il pourra être utile de faire des tests de refresh (complete et fast), de bien peser les volumétries, doser les fréquence de refresh en fonction des besoins réels, etc.

si on n’a pas l’option « réplication avancée » d’Oracle (Oracle Advanced replication option) les VM ne pourront être utilisées qu’en lecture seule


Syntaxe minimale

SQL > CREATE MATERIALIZED VIEW MV1
AS SELECT * FROM scott.emp

créé une vue matérialisée simple, copie conforme de EMP de SCOTT, avec par défaut un raffraîchissement ? la demande sur clé primaire.

par défaut le contenu de la VM est initialisé, la VM est remplie, lors de sa création. Il est possible de crééer une VM vide avec l’otion BUILD DEFERRED (le défaut est BUILD IMMEDIATE)

le CREATE créé aussi de manière transparente une table de même nom que la vue matérialisée et une clé primaire
Infos dans le référentiel / dictionnaire de données

Il existe essentiellement 2 tables sur les vues matérialisées : USER_MVIEWS et USER_MVIEW_LOGS dont voici les descriptions :

USER_MVIEWS
OWNER, MVIEW_NAME, CONTAINER_NAME, QUERY, QUERY_LEN , UPDATABLE, UPDATE_LOG, MASTER_ROLLBACK_SEG , MASTER_LINK, REWRITE_ENABLED , REWRITE_CAPABILITY , REFRESH_MODE, REFRESH_METHOD, BUILD_MODE , FAST_REFRESHABLE , LAST_REFRESH_TYPE, LAST_REFRESH_DATE, STALENESS , AFTER_FAST_REFRESH, UNKNOWN_PREBUILT, UNKNOWN_PLSQL_FUNC, UNKNOWN_EXTERNAL_TABLE, UNKNOWN_CONSIDER_FRESH, UNKNOWN_IMPORT, COMPILE_STATE, USE_NO_INDEX

USER_MVIEW_LOGS
:
LOG_OWNER , MASTER , LOG_TABLE , LOG_TRIGGER, ROWIDS , PRIMARY_KEY , OBJECT_ID , FILTER_COLUMNS, SEQUENCE, INCLUDE_NEW_VALUES

Ainsi le create materialized view MV1 précédent nous renverrait (essentiellement) les infos suivantes dans le dictionnaire

select MVIEW_NAME, QUERY ,UPDATABLE, REFRESH_MODE, REFRESH_METHOD, BUILD_MODE, FAST_REFRESHABLE
from user_mviews

MVIEW_NAME
———-
QUERY
—–
UPDATABLE REFRESH_MODE REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE
——— ———— ————– ———- —————-
MV1
SELECT « EMP ». »EMPNO » « EMPNO », »EMP ». »ENAME » « ENAME », »EMP ». »JOB » « JOB », »EMP ». »MGR »…
N DEMAND COMPLETE IMMEDIATE DML

On vérifie également les objets créés implicitement :

SQL> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
———— —————-
MV1 TABLE
MV1 MATERIALIZED VIEW
PK_EMPNO INDEX

le mot SNAPSHOT fréquemment utilisé est en fait en voie d’obsolescence et synonym ede ‘MVIEW’ dans le dictionnaire

Les droits et contraintes particulières

En général (sauf cas d’école) la vue matérialisée sera dans un schéma, voire dans le cas de réplication sur une base de données différente de la table maître. Dans ce cas la gestion des droits sera un peu plus délicate.
opération droit sur son propre schéma +  droit sur un autre schéma
CREATE MATERIALIZED VIEW + CREATE TABLE
REFRESH FAST SELECT ON la table de LOG concernée+  SELECT ON ANY TABLE ;-(
clause QUERY REWRITE QUERY REWRITE +  GLOBAL QUERY REWRITE
clause ON COMMIT REFRESH priv obj + ON COMMIT REFRESH priv system ON COMMIT REFRESH
clause PREBUILT TABLE priv SELECT + WITH GRANT OPTION sur table container

pour faire un faire un FAST REFRESH il faut un droit direct sur la table de LOG, un ROLE y compris DBA ou SYSDBA n’est pas suffisant. voir le fast refresh plus loin pour plus de détails

Pour une création simple il faut en résumé :

  • le droit CREATE MATERIALIZED VIEW
  • le droit CREATE TABLE (car une MV c’est une MV …. +  une TABLE)
  • le GRANT SELECT sur les objets accédés par la vue

Note :

  • on peut créer une vue matérialisée sur une vue
  • on peut créer une vue matérialisée sur une vue matérialisée
  • par défaut la table sous jacente doit avoir une clé primaire…

Les différents types de vues matérialisées

VM sur clé primaire
c’est le défaut. A utiliser sauf exception (voir paragraphe suivant)
VM sur rowid
SQL> CREATE MATERIALIZED VIEW MV2
REFRESH with ROWID
AS SELECT * FROM scott.emp

Utile lorsque la vue ne contient pas de (ou pas toutes les colonnes de la) clé primaire

contraintes (assez fortes !) :
* ne permet pas le fast refresh si il n’a pas eu un complet avant
* interdit si SELECT avec distinct, group by, connect by, fonctions d’aggregats, opérateur ensemblistes, sous requêtes

rem : on peut vérifier si le rafraichissement des VMs est actifs dans USER_JOBS

SQL> select what from user_jobs;

WHAT
——————————————————————————–
dbms_refresh.refresh(‘ »DDELEGLI ». »REF_GROUP_2″‘);
dbms_refresh.refresh(‘ »DDELEGLI ». »REF_GROUP_1″‘);


Modalités de rafraîchissement

Méthodes de refresh

il existe 3 méthodes : FAST, COMPLETE ou FORCE

  • FAST REFRESH ( incrémental) c’est la méthode la + efficace, elle utilise des journaux spécifiques traçant les modification de la table maître : les LOGs.( Voir l’instruction CREATE MATERIALIZED VIEW LOG…)
  • COMPLETE effectue le refresh complet en executant le SELECT de définition de la MV
  • FORCE effectue un FAST si possible, sinon un COMPLET. C’est la méthode par défaut.

rappel : le fait que la VM soit initialisée avec des valeurs ou rafraichie ou non n’est pas du tout lié à la méthode (y compris FORCE!) cela dépend respectivement de l’option ‘build’ pour l’initialisation et du mode, synchrone ou non pour le raffraichissement…
méthode complete
c’est la méthode par défaut. Toutes les lignes de la table maître sont (re) synchronisées. Ca peut donc couter du temps mais ca fait les économies de place de la table log….

SQL> create materialized view mv2 refresh complete
as select * from scott.emp;
vue matérialisée créée


méthode refresh partiel ou rapide ou ‘fast’

Si on essaie de faire la meme VM avec un refresh FAST…on obtient une erreur :

SQL> create materialized view mv2
2 refresh FAST
3 as select * from scott.emp;
as select * from scott.emp
*
ERREUR a la ligne 3 : ORA-23413: table « SCOTT ». »EMP » does not have a materialized view log

C’est très clair (on l’a dit + haut) avec cette méthode ‘incrémentale’ il faut tracer les mises a jour faites sur la table maitre dans des fichiers LOG…encore faut il qu’ils existent !
On le fait (faute de mieux ou de droits adéquats ?) dans le compte maitre (le compte de SCOTT) et après on eut faire des FAST REFRESH…

SQL> connect scott/tiger
SQL> create materialized view log on emp;
Journal de vue materialisee cree.

Les logs, comme on peut le voir dans le référentiel sur les vues matérialisées, prennent le nom de la table maître préfixé par ‘MLOG$_’. Donc ici MLOG$_EMP.
on retente le coup et on obtient encore une erreur ORA-1031 « insufficient privilege » car on n’a pas les accèsm nécessaires, non pas sur SCOTT.EMP mais sur SCOTT.MLOG$EMP.
On pourra donner un SELECT ANY TABLE (c’est la solution parfois proposée dans le forums) ou plus finement un
SQL> GRANT SELECT ON SCOTT.MLOG$_EMP TO le_user_propriétaire_de_la_VM

Modes de refresh

Globalement synchrone (répercuté en temps réel) ou asynchrone (mis dans une queue et traité en différé) , mais il existe en fait 3 modes, 3 ‘fréquences’ de refresh :

  • synchrone, sur commit : clause ON COMMIT
    par définition ne nécessite pas de commande REFRESH c’est la mise à jour COMMITée de la table maître qui déclenche automatiquement le REFERESH.
  • asynchrone, à la demande : clause ON DEMAND. C’est le mode par défaut.
    On utilise la procédure standard DBMS_MVIEW.refresh qui est publique (DBMS_MVIEW est un synonyme public sur SYS.DBMS_SNAPSHOT et il existe un GRANT EXECUTE TO PUBLIC ON SYS.DBMS_SNAPSHOT ).
    Ne nécessite donc pas de droit particulier pour être exécuté (pour le rafraichissement d’une vue matérialisée dans un autre schéma voir les exemples ci-après).
    exemple :

SQL> execute DBMS_MVIEW.REFRESH(‘MA_VM’);

  • asynchrone, cyclique : clauses START WITH et NEXT, qui précise une date de début et une période de temps intermédiaire

on peut changer de fréquence de refresh avec la commande ALTER :

SQL> ALTER MATERIALIZED VIEW mv1
refresh complete
start with sysdate next sysdate + n

cette commande sans la clause NEXT, ne peut pas être dévoyée pour provoquer un refresh immediat d’une vue non à jour

Infos complémentaires sur la planification des refresh

L’automatisation du raffraichissement des vues matérialisées par Oracle se fait grace à des JOBS Oracle. On peut donc avoir des infos, notamment sur les refresh à venir, dans les vues du dictionnaires associées au JOBs.
En pratique il n’y a pas de colonne NEXT REFRESH dans les vues concernant les vues matérialisées, ce manque est donc comblé.

AInsi la requête suivante nous donne des infos sur le LAST REFRESH, le NEXT REFRESH, et comment a été défini l’intervalle entre les 2 dans la vue matérialisée.

SQL> SELECT job, SCHEMA_USER,
TO_CHAR(last_date, ‘DD/MM/YYY HH:MI’) « Dernier refresh »,
TO_CHAR(next_date, ‘DD/MM/YYY HH:MI’) « Prochain refresh »,
interval « Intervalle »,
what « Refresh concerné »
FROM dba_jobs
WHERE what LIKE ‘%refresh%’
AND SCHEMA_USER=’SCOTT’

JOB SCHEMA_USER Dernier refresh Prochain refres Intervalle
Refresh concerné
229 SCOTT 11/09/008 05:00 12/09/008 05:00 trunc(sysdate + 1) + 17/24 dbms_refresh.refresh(‘ »SCOTT ». »MV1″‘);
230 SCOTT 12/09/008 08:00 14/09/008 12:00 trunc(sysdate + 1) + 1 dbms_refresh.refresh(‘ »SCOTT ». »MV2″‘);


Des exemples variés

  • rafraichissement synchrone sur commit de la table maître (on commit refresh)

Des que les modifs de la table maitre sont commitées elle sont automatiquement synchronisées (répercutées) dans le VM.

SQL> create materialized view mv2 refresh complete
as select * from scott.emp;
vue matérialisée créée
SQL> insert into scott.emp(empno, ename) values (1111,’DD’);
SQL> select ename from mv2 where empno=1111;
aucune ligne selectionnee
SQL> commit;
SQL> select ename from mv2 where empno=1111;
ENAME
—–
DD

  • rafraichissement asynchrone à la demande (on demand)

SQL> execute DBMS_MVIEW.REFRESH(‘MA_VM’);
SQL> — si la vue appartient à un autre schéma

SQL> connect system/xxx

SQL> grant ALTER ANY SNAPSHOT TO user1
SQL> connect user1/xxx
SQL> execute DBMS_MVIEW.REFRESH(‘user2.MA_VM’);

  • rafraichissement asynchrone à la demande (on demand) d’une vue d’un groupe

La vue matérialisée doit faire partie d’un groupe a raffraichir (refresh group)
On peut le creer avec le package DBMS_REFRESH et la procédure MAKE(). Une VM peut être ajouté lors de la
création du groupe ou plus tard avec la procédure ADD().
SQL> execute DBMS_REFRESH.MAKE(‘ref_group_1′,’MV1′,null,null);
– cree le refresh group ‘ref_group_1′ et y ajoute la VM ‘MV1′
Une fois cela fait on peut faire le refresh effectif.
On utilise encore le package DBMS_REFRESH et l’une de ses procédures de refresh : REFRESH(), REFRESH_ALL_MVIEWS, ou REFRESH_DEPENDENT)
SQL> execute DBMS_REFRESH.REFRESH(‘ref_group_1′);

Attention ! DBMS_REFRESH.refresh  comme son nom ne l’indique pas, ne sert qu’à rafraichir les GROUPES et nécessite des droits sur SYS.DBMS_REFRESH ! Pour une VM simple on utilise DBMS_MVIEW.refresh .
Dans le cas on on utilise DBMS_REFRESH.REFRESH pour une VM simple on obtient une erreur :

execute DBMS_REFRESH.REFRESH(‘dd’);
BEGIN DBMS_REFRESH.REFRESH(‘dd’); END;
*
ERROR at line 1:
ORA-23404: refresh group « HR ». »DD » does not exist
ORA-06512: at « SYS.DBMS_SYS_ERROR », line 95
ORA-06512: at « SYS.DBMS_REFRESH », line 23
ORA-06512: at « SYS.DBMS_REFRESH », line 195
ORA-06512: at line 1

  • rafraichissement asynchrone cyclique (a date ou heure fixe)

on utilise la clause START WITH. SI elle est omise, Oracle se basera sur l’heure de création de la vue
matérialisée.
et la clause NEXT avec en paramètre une expression basée sur SYSDATE.
rafraîchissemebt auto tous les jours :

SQL> create materialized view mv4
refresh next sysdate + 1
as select * from scott.emp;
– et ci apres rafraîchissement toutes les minutes
– !!!!! pas vraiment conseillé… :
SQL> create materialized view mv4
refresh next sysdate + 1/24/60
as select * from scott.emp

  • raffraichissement d’une vue matérilaisée  tous les jours à la même heure , tous les jours à 8H par exemple :

SQL> CREATE materialized view scott.mv1
refresh complete
START WITH trunc(sysdate) + 8/24
NEXT trunc(sysdate) + 32/24
WITH PRIMARY KEY
AS
SELECT * FROM scott.emp


  • Exemple (pénible) de création de vue avec REFRESH ON COMMIT

SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;
as select * from scott.bonus
*
ERROR at line 3:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.96
SQL> select * from scott.bonus;
DD

Elapsed: 00:00:01.05
SQL>
SQL> grant on commit refresh to system;

Grant succeeded.

Elapsed: 00:00:01.35
SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;
as select * from scott.bonus
*
ERROR at line 3:
ORA-12014: table ‘BONUS’ does not contain a primary key constraint
Elapsed: 00:00:01.30
SQL> desc scott.bonus
Name Null? Type
—————————————– ——– —————————-
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER

SQL> alter table scott.bonus modify (ename primary key);

Table altered.

Elapsed: 00:00:00.30
SQL> create materialized view mvdd
2 refresh on commit
3 as select * from scott.bonus;

Materialized view created.

Vues utiles du dictionnaire concernant les VMs

SQL> select * from dict where table_name like ‘DBA_%MVIEW%’ ;

DBA_BASE_TABLE_MVIEWS : All materialized views with log(s) in the database
DBA_MVIEWS : All materialized views in the database
DBA_MVIEW_AGGREGATES : Description of the MV aggregates accessible to dba
DBA_MVIEW_ANALYSIS : Description of the materialized views accessible to dba
DBA_MVIEW_COMMENTS : Comments on all materialized views in the database
DBA_MVIEW_DETAIL_RELATIONS : Description of MV detail tables accessible to dba
DBA_MVIEW_JOINS : Description of join between 2 columns in the WHERE clause of a MV DBA_MVIEW_KEYS : Description of the columns that appear in the GROUP BY list of a materialized view accessible to dba
DBA_MVIEW_LOGS : All materialized view logs in the database
DBA_MVIEW_LOG_FILTER_COLS : All filter columns (excluding PK cols) being logged in the MV logs
DBA_MVIEW_REFRESH_TIMES : All fast refreshable materialized views and their last refresh times for each master table
DBA_REGISTERED_MVIEWS : Remote materialized views of local tables
DBA_REGISTERED_MVIEW_GROUPS : Materialized view repgroup registration information
DBA_TUNE_MVIEW : Catalog View to show the result after executing TUNE_MVIEW() API

Introduction PHP / MySQL

Divers pas de Commentaire »

A l’instar d’Oracle , MySQL peut être accédée grace ? 2 extensions PHP différentes :

* l’extension standard ‘mysql‘ (ensemble de fonctions préfixées par mysql_)
* l’extension améliorée ou Improved MySQL extension ‘mysqli‘ (ensemble de fonctions préfixées par mysqli_)

l’extension standard ‘mysql’

Cette extension souvent associé ? la version basique du moteur MySQL, utilise 2 ressources principales :

* un identifiant de connexion
* un ensemble de lignes résultats (si le SQL est un SELECT bien sûr) : le result set

Les ordres SQL sont exécutés directement sans phase de préparation (parsing, bind), et non pas comme dans le cas général d’un accès aux BDs via un langage de script.

il n’y a pas non plus de gestion explicite de transaction (commit, rollback) réservée ? la version PRO. Les mises ? jour seront par défaut AUTOCOMMITées

Voici d’après le site officiel PHP, la liste complètes des fonctions de l’extension ‘mysql’ en PHP5

mysql_affected_rows — Retourne le nombre de lignes affectées lors de la dernière opération MySQL
mysql_change_user — Change le nom de l’utilisateur de la connexion active
mysql_client_encoding — Retourne le nom du jeu de caractères utilisé par le client MySQL
mysql_close — Ferme la connexion MySQL
mysql_connect — Ouvre une connexion ? un serveur MySQL
mysql_create_db — Crée une base de données MySQL
mysql_data_seek — Déplace le pointeur interne de résultat MySQL
mysql_db_name — Lit les noms des bases de données
mysql_db_query — Envoie une requête MySQL ? un serveur MySQL
mysql_drop_db — Efface une base de données MySQL
mysql_errno — Retourne le numéro d’erreur de la dernière commande MySQL
mysql_error — Retourne le texte associé avec l’erreur générée lors de la dernière requête
mysql_escape_string — Protège une chaîne pour la passer ? mysql_query
mysql_fetch_array — Retourne une ligne de résultat MySQL sous la forme d’un tableau associatif, d’un tableau indexé, ou les deuxmysql_fetch_assoc — Lit une ligne de résultat MySQL dans un tableau associatif
mysql_fetch_field — Retourne les données enregistrées dans une colonne MySQL sous forme d’objet
mysql_fetch_lengths — Retourne la taille de chaque colonne d’une ligne de résultat MySQL
mysql_fetch_object — Retourne une ligne de résultat MySQL sous la forme d’un objet
mysql_fetch_row — Retourne une ligne de résultat MySQL sous la forme d’un tableau
mysql_field_flags — Retourne des détails sur une colonne MySQL
mysql_field_len — Retourne la taille d’un champ de résultat MySQL
mysql_field_name — Retourne le nom d’une colonne dans un résultat MySQL
mysql_field_seek — Déplace le pointeur de résultat vers une position donnée
mysql_field_table — Retourne le nom de la table MySQL où se trouve une colonne
mysql_field_type — Retourne le type d’une colonne MySQL spécifique
mysql_free_result — Libère le résultat de la mémoire
mysql_get_client_info — Lit les informations sur le client MySQL
mysql_get_host_info — Lit les informations sur l’hôte MySQL
mysql_get_proto_info — Lit les informations sur le protocole MySQL
mysql_get_server_info — Lit les informations sur le serveur MySQL
mysql_info — Lit des informations ? propos de la dernière requête MySQL
mysql_insert_id — Retourne l’identifiant généré par la dernière requête INSERT MySQL
mysql_list_dbs — Liste les bases de données disponibles sur le serveur MySQL
mysql_list_fields — Liste les champs d’une table MySQL
mysql_list_processes — Liste les processus MySQL
mysql_list_tables — Liste les tables d’une base de données MySQL
mysql_num_fields — Retourne le nombre de champs d’un résultat MySQL
mysql_num_rows — Retourne le nombre de lignes d’un résultat MySQL
mysql_pconnect — Ouvre une connexion persistante ? un serveur MySQL
mysql_ping — Pingue le serveur MySQL, et s’y reconnecte au besoin
mysql_query — Envoi une requête ? un serveur MySQL
mysql_real_escape_string — Protège les caractères spéciaux d’une commande SQL
mysql_result — Retourne un champ d’un résultat MySQL
mysql_select_db — Sélectionne une base de données MySQL
mysql_stat — Retourne le statut courant du serveur MySQL
mysql_tablename — Lit le nom de la table qui contient un champ
mysql_thread_id — Retourne l’identifiant du thread MySQL courant
mysql_unbuffered_query — Exécute une requête SQL sans mobiliser les résultats MySQL

Afin d’y voir plus clair, nous allons tenter de les regrouper par types d’opération ou de fonctionnalités
Taxinomie des fonctions MySQL de PHP
connexion mysql_connect, mysql_connect, mysql_close, mysql_ping, (my_sql_select_db, mysql_change_user)
gestion d’erreurs mysql_error, mysql_errno
méta données DB

mysql_field_flags, mysql_field_name, mysql_field_len, mysql_field_table, mysql_field_type, mysql_list_fields, mysql_info, mysql_insert_id,

mysql__num_rows

mysql_list_tables, mysql_table_name
méta données Serveur

mysql_client_encoding, mysql_get_client_info, mysql_get_host_info, mysql_get_proto_info, mysql_get_server_info,

mysql_list_dbs, mysql_ list_processes, mysql_stat, mysql_thread_id
lecture lignes mysql_fetch_array, mysql_fetch_result_row, mysql_fetch_assoc, mysql_fetch_field, mysql_fetch_lengths, mysql_fetch_object, mysql_fetch_row,
execution SQL mysql_drop_db, mysql_db_query, mysql_query, mysql_unbuffered_query

l’extension améliorée ‘mysqli’

Cette extension souvent associé ? la version PRO du moteur MySQL, utilise 3 ressources principales :

* un identifiant de connexion
* un ordre SQL ‘préparé’ explicitement
* un ensemble de lignes résultats (si le SQL est un SELECT bien sûr) : le result set

Les principales fonctionnalités supplémentaires sont les suivantes :

* phase d’analyse préalable des ordres SQL (parsing) que l’on peut éviter de ré exécuter lors d’ordres SQL récurrents ou simililaires
* gestion de pramètres d’entrées associées ? des variables PHP : bind variables
* gestion de transaction (commit, rollback)
* accès direct dans le résult set
* gestion d’erreur et debugging amélioré

PHP5 étant fortement orienté objet, 3 classes prédéfinies sont fournies, qui peuvent agréablement pour les adeptes de la P.O.O se substituer aux fonctions classiques. :

* mysqli : une classe de connexion
* mysqli_statement : une classe pour la gestion de ordres SQL (éventueelemnt ‘préparés’)
* mysqli_result_set : une classe pour la gestion du résultat (lignes ramenées par le SELECT)

La liste complète des fonction de l’extension mysqli ainsi que la description détaillée des méthodes des classes prédéfinies est accessible dans la doc officielle de mysqli sur PHP.net

pour finir, INFOS INTERESSANTES SUR LE SUJET sur le site PHP MYSQL TUTORIAL

Les paramètres d’initialisation

Administration Oracle 3 Commentaires »

Lors de son démarrage, une instance Oracle lit ses paramètres dans un fichier, dit de démarrage ou d’initialisation.
Ce fichier peut être de 2 types :
- un ‘init.ora’, fichier texte simple en lecture seule
- un ‘sp file’, fichier de paramètre serveur, binaire, en lecture / écriture.

Dans l’ordre Oracle recherche ses paramètres PAR DEFAUT dans :
* spfile$ORACLE_SID.ora file ? l’emplacement par défaut
* spfile.ora
* et un fichier init.ora par défaut init$ORACLE_SID.ora.

Il est toujours possible de spécifier explicitement un fichier de parametres lors du démarrage de la base :

SQL> startup PFILE=/mon_chemin/mon_fic_init.ora
ou
SQL> startup SPFILE=/mon_chemin/mon_fic_spfile

Le fichier INIT.ORA

Il est éditable avec un éditeur de texte standard. Les modifications faites en son sein sont prises en compte lors du redémarrage de la base.Son contenu est de la forme suivante :

# commentaire
#
nom_parametre = valeur
nom_parametre = ( valeur1, valeurs2, … )

Un fichier init.ora MINIMISSIME contient 2 paramètres :

‘DB_NAME’ : le nom de la base de données
‘CONTROL_FILES’ : la liste des fichiers de contrôle utilisés

Les autres paramètres les plus fréquemment utilisés sont les suivants (S:statique, M:modifiable, D: dérivé d’un autre parametre)

COMPATIBLE S : Compatibilité arrière avec les versions précédentes (>= 9.2.0)
DB_DOMAIN S : Nom de domaine, qui associé au DB_NAME donne un nom de base unique
DB_RECOVERY_FILE_DEST M : Répertoire de destination de la zone de FLASH recovery
LOG_ARCHIVE_DEST_n M : Répertoire des fichiers REDO LOGS archivés
NLS_LANGUAGE D : langue utilisée (fonction de NLS_LANG dans l’environnement Unix ou windows)
NLS_TERRITORY M : le territoire (France par ex). Impacte les dates, et la monnaie
OPEN_CURSORS M : nb max de cursor ouvrable par une application
PGA_AGGREGATE_TARGET M : si 0, dimensionnement auto de la PGA, pour les proccess utilisateur server
PROCESSES S : nb max de process connectés ? Oracle (background et user)
SESSIONS D : = 1,1 * PROCESSES
SGA_TARGET D : si 0 auto dimensionnement de la SGA
SHARED_SERVER M : 0 si serveur dédié, n = nb de serveurs partagés lancés au startup instance
SP_FILE S : emplacement du SP File
UNDO_MANAGEMENT S : Manual | Auto, par dafaut Manual indique qu’on utilise des Rollback segments

Les noms et emplacements standards de ce fichier INIT.ORA sont les suivants :
sur Unix/Linux $ORACLE_HOME/dbs/init$ORACLE_SID.ora
sur Windows %ORACLE_HOME%\\database\\init%PRACLE8SID%.ora

note : il existe un fichier init.ora d’exemple (sample init.ora file) abondamment commenté sur chaque plate forme installé.
Il se trouve
sur Unix/Linux dans $ORACLE_HOME/dbs/
sur Windows dans %ORACLE_HOME%\\admin\\samplepfile

Le fichier SPFILE

Le fichier spfile, est aussi un fichier externe localisé sur le serveur de données, qui contient les paramètres.

Ceux ci sont persistants, c’est a dire qu’on peut les modifier ? n’importe quel moment, et que ces changements resteront pérennes au del? du re démarrage de la base. Ceci se fait avec la commande ALTER SYSTEM.

exemple :

SQL> ALTER SYSTEM SET DB_FILES=500 SCOPE = SPFILE;

On peut créer un SPFILE ex nihilo avec l’assistant de création de base de données ‘dbca’ ou ? partir d’un ‘init.ora’ existant avec la commande ‘CREATE SPFILE’.

exemple :

SQL> CREATE SPFILE =  » FROM PFILE =  »

Les noms et emplacements standards de ce fichier SPFILE sont les suivants :
sur Unix/Linux $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
sur Windows %ORACLE_HOME%databasespfile%ORACLE_SID%.ora

Pour savoir quel SPFILE est effectivement utilisé on peut faire sous SQL+ :

SQL> SHOW PARAMETER SPFILE
NAME     TYPE              VALUE
-----     ---------         ------------------
SPFILE     STRING          F:ORACLEPRODUCT10.2.0DB_2 DBSSPFILEORCL.ORA

Enfin, un fichier init.ora peut être quasiment vide et simplement référencer un SPFILE, par un genre  » d’inclusion’ :

# exemple de init.ora referencant simplement un spfile :
#
SPFILE=’spfile.ora’