Oracle 9I : Evolutions et Optimisations

05/01/2004

Par SheikYerbouti (Page d'accueil)
 

                           Synthèse des principales évolutions et optimisations de la version 9i du noyau Oracle

• Toutes les modifications par ALTER SYSTEM deviennent permanentes.
• Paramètres d’initialisation dynamiques grâce au SPfile.
• S’il existe, est prioritaire par rapport au PFile (Pfile devient facultatif).
• Se trouve par défaut sous %ORACLE_HOME%/database (NT)
  $ORACLE_HOME%/dbs (Unix)
• A comme nom spfile<sid>.ora et ne peut pas être mentionné dans la commande Startup (au contraire du Pfile).
• Est créé à partir du Pfile (nécessite les privilèges Sysdba ou Sysoper) avec :

  CREATE SPFILE = ‘NomDuSpfile’
  FROM PFILE = ‘NomPfile’

  On peut le créer avec les noms par défaut avec:

  CREATE SPFILE FROM PFILE

• Est modifiable par la commande :

  ALTER SYSTEM SET paramètre = valeur
  [COMMENT=’moncommentaire’]
  [SCOPE = MEMORY | SPFILE |BOTH]
  [DEFERRED]

  Où MEMORY : en mémoire uniquement (par défaut comme la 8i).
  SPFILE : uniquement dans le SPFILE.
  BOTH : en mémoire et dans le SPFILE.
  DEFERRED : pour les paramètres dynamiques, les active pour les sessions futures (pas celles en cours).
  COMMENT : commentaire associé au paramètre de longueur maximale de 255 caractères.
  
  
• Son contenu est montré par la vue V$SPPARAMETER.
• Est exportable vers un Pfile (nécessite les privilèges Sysdba ou Sysoper) avec :

  CREATE PFILE = ‘NomDuPfile’ FROM SPFILE = ‘NomDuSpfile’

• Peut être partagé entre plusieurs instances d’un parallel server (Real Application Cluster en 9i).

• PGA_AGGREGATE_TARGET
  Représente le total PGA de toutes les sessions ouvertes à atteindre.
  
  
• WORKAREA_SIZE_POLICY (MANUAL OU AUTO)
  Valeur par défaut à MANUAL si PGA_AGGREGATE_TARGET n’est pas défini (le tuning mémoire est alors géré par les *_AREA_SIZE)
  
  Taille total de la PGA < PGA_AGGREGATE_TARGET en mode auto.

• a - V$SYSSTAT et V$SESSTAT :
  work area memory allocate
  work area executions – optimal size
  work area executions – one pass size
  work area executions – multipass size
  
  
• b - V$PROCESS
  PGA_USED_MEM
  PGA_ALLOC_MEM
  PGA_MAX_MEM

• Sa taille peut changer dynamiquement pendant le fonctionnement d’une instance (par l’intermédiaire du Buffer Cache et de la Shared Pool Size).
• Nouvelle unité d’allocation : Le Granule.
  C’est une unité d’allocation mémoire contiguë et qui dépend de la taille de la SGA :
  => Si SGA_MAX_SIZE < 128 Mo, taille granule = 4Mo.
  => Si SGA_MAX_SIZE >= 128 Mo, taille granule = 16Mo.
  => taille de la SGA exprimée en granules et en contient au moins 3 (Fixed SGA (dont Redo Buffer), buffer Cache, Share Pool)
  
  L’allocation des granules est montrée par la vue V$BUFFER_POOL.
  
  La Shared Pool peut être retaillée dynamiquement avec la commande :

  ALTER SYSTEM
  SET SHARED_POOL_SIZE = 128 M;

  
  Cette taille doit être un multiple du granule et doit être inférieure à SGA_MAX_SIZE.
  
  Le Buffer Cache peut être retaillé dynamiquement avec la commande :

  ALTER SYSTEM
  SET DB_CACHE_SIZE= 64 M;

  
  Cette taille doit être un multiple du granule et doit être inférieure à
  SGA_MAX_SIZE. Elle doit être différente de 0
  => db_block_buffer inutilisé en 9i
  Nouveaux paramètres : DB_CACHE_SIZE, DB_KEEP_CACH_SIZE, et DB_RECYCLE_CACHE_SIZE.
  Paramètres obsolètes et bientôt supprimés : DB_BLOCK_BUFFER, BUFFER_POOL_KEEP et BUFFER_POOL_RECYCLE.

• sert principalement pour les bases de test.
• Crée un nom unique pour chaque fichier et se charge de les supprimer automatiquement en cas de suppression logique.
• Les répertoires physiques sont gérés par 2 paramètres dynamiques :
  • DB_CREATE_FILE_DEST : Utilisé pour les Data Files(Fichiers de données)
  • DB_CREATE_ONLINE_LOG_DEST_n (1 <= n <= 5) : Utilisé pour les controls files et les redo logs.
  Ex : La commande suivante peut être lancée pour définir le premier paramètre :

  ALTER SYSTEM SET db_create_file_dest = ‘/oracle/dbs/files’ ;

• Il faut définir un ou les deux paramètres dynamiques.
• Ne pas spécifier le paramètre CONTROL_FILES lors de la création de la base.
  • un control file est créé dans chacun des DB_CREATE_ONLINE_LOG_DEST_n
  • ou dans DB_CREATE_FILE_DEST sinon.
  • les fichiers sont affichés dans le fichier d’alerte.

• si SPFILE est défini, les controls files générés sont automatiquement inclus dans ce fichier binaire.

• Définir les paramètres DB_CREATE_ONLINE_LOG_DEST, les groupes seront créés dans chacun des DB_CREATE_ONLINE_LOG_DEST_1 et DB_CREATE_ONLINE_LOG_DEST_2.
• Chaque groupe aura 2 membres.
• Chaque fichier a comme taille par défaut : 100 Mo.

ALTER DATABASE ADD LOGFILE ;

ALTER DATABASE DROP LOGFILE GROUP 3 ;

CREATE TABLESPACE omf_2 
	DATAFILE SIZE 500M ;

ALTER TABLESPACE TBS1 ADD DATAFILE;

DROP TABLESPACE TBS1
	INCLUDING CONTENTS AND DATAFILES;

• A la création de la base avec la clause :

  DEFAULT TEMPORARY TABLESPACE tmp1
  	TEMPFILE '/oracle/dbs/temp01.dbf' SIZE 100M

• Avec la commande:

  ALTER DATABASE db1
  	DEFAULT TEMPORARY TABLESPACE tmp1.

• AUTO : Géré par l'instance (sur un seul tablespace de type UNDO_TABLESPACE).
  nécessite d'avoir les paramètres suivants:
  • UNDO_MANAGEMENT = AUTO
  • UNDO_TABLESPACE = UNDO1
  (Le fonctionnement des rollbacks segments de SYSTEM est identique aux versions antérieures et un rollback segment est créé automatiquement dans le tablespace SYSTEM).
  
  
• MANUAL : Géré par le DBA. (Identique à la gestion des rollbacks segments avant la 9i).
  nécessite d'avoir les paramètres suivants:
  • UNDO_MANAGEMENT=MANUAL
  • CREATE TABLESPACE RBS
  • CREATE ROLLBACK SEGMENT rbs1 STORAGE (…)
  • ROLLBACK_SEGMENTS= (rbs1,rbs2)

• A la création de la base, avec la clause supplémentaire:

  UNDO TABLESPACE undotbs1
  	DATAFILE '/oracle/dbs/undotbs01.dbf' SIZE 50M

  (si au démarrage d'une instance, UNDO_MANAGEMENT=AUTO et qu'il n'y a pas de clause UNDO_TABLESPACE, il en crée un par défaut, de nom SYS_UNDOTBS, en AUTOEXTEND ON, et dans un fichier de 10 Mo).
• Après la création de la base avec :

  CREATE UNDO TABLESPACE
  	DATAFILE '/oracle/dbs/undotbs01.dbf' SIZE 50M

  (Ce tablespace ne peut servir que pour stocker les données d'annulation).

ALTER TABLESPACE undotbs1
	ADD DATAFILE …
	RENAME …
	DATAFILE [ONLINE, OFFLINE]
	BEGIN BACKUP
	END BACKUP

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

ALTER SYSTEM
	SET UNDO_TABLESPACE=tbsundo2;

• UNDO_MANAGEMENT: MANUAL ou AUTO
• UNDO_TABLESPACE : Nom du tablespace d'annulation
• UNDO_SUPPRESS_ERRORS : TRUE OU FALSE (par défaut).
  => si positionné à TRUE, supprime les messages d'erreurs affichés, quand on est en mode AUTO et qu'une opération manuelle est exécutée (telle que ALTER ROLLBACK SEGMENT ou ALTER SESSION SET TRANSACTION USE ROLLBACK SEGMENT).
• UNDO_RETENTION: Durée (par défaut 30 s) pendant laquelle la donnée modifiée est gardée= Flashback.

CREATE TABLESPACE TBS1
	DATAFILE '/oracle/dbs/tbs01.dbf' SIZE 10 M
	BLOCKSIZE 4096;

• Toutes les partitions d'un objet partitionné doivent être réparties dans des tablespaces de tailles de bloc identiques.
• Tous les tablespaces temporaires doivent utiliser la taille de bloc standard.

• DB_CACHE_SIZE : Taille réservée aux blocs standard de la base.
• DB_nK_CACHE_SIZE : Taille allouée aux différents blocs non standard, par défaut à 0 (n=2, 4, 8,16 et 32), excluant celle des blocs standard.

• DB_KEEP_CACHE_SIZE (au lieu de BUFFER_POOL_KEEP auparavant) : garde les tables en mémoire plus longtemps.
• DB_RECYCLE_CACHE_SIZE (au lieu de BUFFER_POOL_RECYCLE auparavant) : inverse du précédent.

CREATE TABLESPACE tbs1
	DATAFILE '/oracle/dbs/tbs1.dbf'
	SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT (tablespace géré localement).
	SPACE MANAGEMENT AUTO. (Gestion automatique).

ALTER SESSION ENABLE RESUMABLE
	NAME 'update ma table' TIMEOUT 3600; (par défaut à 2h)

ALTER SESSION DISABLE RESUMABLE;

• ALTER SESSION ENABLE RESUMABLE TIMEOUT montimeout;

• Utiliser le package DBMS_RESUMABLE :
  • DBMS_RESUMABLE.Set_Timeout (montimeout) => ma session en cours.
  • DBMS_RESUMABLE.Set_Session_Timeout (Session_Id, montimeout) => n'importe quelle session en cours.

ALTER SESSION ENABLE RESUMABLE NAME 'monnom';

• Requêtes de type Select.
• Ordres DML.
• Ordres DDL.
• Import et Export.
• SQL*Loader.

• USER_RESUMABLE
• DBA_RESUMABLE

• ACTIVE_SESSION_POOL_P1 : nombre de sessions actives qui établissent le seuil d'un groupe de consommateurs.
• QUEUEING_P1 : Temps d'attente maximum (en s) pour une session en file d'attente, après cette durée, la session est annulée avec une erreur.

• MAX_ESTIMATED_EXEC_TIME: Temps maximal estimé autorisé pour une transaction avant son exécution. Si la transaction dépasse ce seuil, elle n'est pas exécutée.

• SWITCH_GROUP: Groupe sur lequel on a commuté.
• SWITCH_TIME : Temps (en s) durant laquelle une session doit être exécutée avant de changer de groupe.
• SWITCH_ESTIMATE : Si positionné à TRUE, le temps d'exécution est utilisé pour décider quand une opération va être switchée, et ce peut être avant qu'elle commence.

• UNDO_POOL (Kb), par défaut à UNLIMITED, restreint les opérations de type DML, et correspond au quota d'annulation par groupe de consommateurs (après ce quota, aucun ordre DML n'est autorisé).

• DBMS_RESOURCE_MANAGER.Create_Plan_Directive(PLAN => 'Mon_Plan', 
  	GROUP_OR_SUBPLAN => 'support',
  	COMMENT =>  'OLTP sessions',
  	UNDO_POOL => 10000);

  Et

  DBMS_RESOURCE_MANAGER.Create_Plan_Directive(PLAN => 'Mon_Plan', 
  	GROUP_OR_SUBPLAN => 'support',
  	UNDO_POOL => 25000);

• Autonome (Standalone) : Connexion directe aux bases Oracle.
• OMS : Oracle Management Server(sysman/oracle), basé sur un référentiel (repository) propre et qui se connecte aux bases via un agent (service sous NT) installé sur chacune d'elles.

• SPFILE.
• UNDO MANAGEMENT (gestion des segments d'annulation automatique).
• Jeu de caractères (Character set) UNICODE.
• Gestion dynamique de la mémoire.
• Tablespace temporaire par défaut.
• Différentes tailles de blocs.
• Temps moyen de récupération de la base (MTTR: Mean Time to Recovery).
• Améliorations des sauvegardes et récupérations (géré par RMAN).
• "Advanced Queuing".

• Génération de rapports en HTML
  • => sur la configuration complète de la base
  • => sur les propriétés d'un objet sélectionné dans l'arbre graphique de la console.

Amélioration de la gestion des événements (events)(nécessite l'agent Oracle9i).

• Ajout de 3 colonnes dans la table PLAN_TABLE:
  • cpu_cost : Coût CPU estimé de l'opération.
  • io_cost : Coût accès disque estimé de l'opération.
  • temp_space : Espace temporaire estimé par l'opération.

• Estimation tient compte de l'utilisation réseau et CPU, des effets du cache (sur les jointures) et de la préextraction (prefetching) (rapatrie plusieurs blocs feuilles d'un index en un seul accès disque).

• Nouvelle valeur pour le paramètre estimate_percent : AUTO_SAMPLE_SIZE
  => estime la taille de l'échantillon nécessaire pour obtenir les meilleurs gains de performances.
• Nouvelles options pour le paramètre size :
  • REPEAT : répète la forme de l'histogramme déjà existant.
  • AUTO : Histogramme basé sur la distribution des données et l'utilisation de la colonne par l'application.
  • SKEWONLY : Histogramme basé uniquement sur la distribution des données => intéressant pour la première "collecte" de statistiques (sinon AUTO).

execute DBMS_STATS.GATHER_SCHEMA_STATS(…..
	estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
	method_opt   => 'for all columns size AUTO')

• Nouvelles procédures:
  • DBMS_STATS.GATHER_SYSTEM_STATS : récupère des statistiques système durant un intervalle de temps défini.
  • DBMS_STATS.SET_SYSTEM_STATS: Spécifie certains paramètres.
  • DBMS_STATS.GET_SYSTEM_STATS: Récupère certains paramètres.

execute DBMS_STATS.GATHER_SYSTEM_STATS(interval => 120, stattab=>'messtats', statid=>'app')

• intervalle en minutes
• stattab : nom de la table où seront stockées les stats
• statid : identifiant des stats générées.

• FIRST_ROWS_1
• FIRST_ROWS_10
• FIRST_ROWS_100
• FIRST_ROWS_1000

ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS_100;

Select /*+  FIRST_ROWS (25)  */

• l'ordre des jointures
• méthodes des jointures
• méthodes d'accès (full scan ou par index).
• méthodes de distribution (parallel query).
• ré-écriture des sous-requêtes et vues.

• le privilège SELECT_ANY_CATALOG.
• le privilège CREATE ANY_OUTLINE.

• crée les tables nécessaire dans le schéma en cours pour dupliquer et éditer les plans stockés (<=> exécution du script utledtol.sql)
• nécessite d'avoir le privilège EXECUTE sur le package DBMS_OUTLN_EDIT.

ALTER SESSION SET USE_PRIVATE_OUTLINES = [TRUE|FALSE|nom_categorie]

• TRUE : Active les plans privés dans la catégorie DEFAULT.
• FALSE : Désactive les plans privés.
• nom_categorie : Active les plans privés dans la catégorie nom_categorie.

CREATE [OR REPLACE] [PUBLIC|PRIVATE] OUTLINE [nom_outline]
	[FROM  [PUBLIC | PRIVATE] nom_outline_source]
	[FOR CATEGORY nom_catégorie] [ON statement]

• FORCE : pas de remplacement.
• EXACT (par défaut): partage pour toutes les valeurs.
• SIMILAR (nouveauté 9i) :l'optimiseur examine l'ordre SQL avant de décider de recalculer (ou pas) le plan.

ALTER INDEX mon_index MONITORING USAGE;
ALTER INDEX mon_index NOMONITORING USAGE;

CREATE BITMAP INDEX mon_index_bitmap
ON sales (c.region)
FROM sales s, customers c
Where c.cust_id = s.cust_id

SELECT SUM(s.cost)
FROM sales s, customers c   
WHERE s.cust_id = c.cust_id
AND c.region ='Est';

• Limitation au parallel DML sur la table fille uniquement (pas sur les tables de référence).
• Les tables concernées peuvent être mises à jour une à la fois seulement.
• Les tables ne peuvent apparaître plus d'une fois dans la requête.
• Les tables ne peuvent pas être organisées en index ou être temporaires.
• Les colonnes citées dans l'index n'appartiennent qu'aux tables maîtres (ou de référence).
• La colonne de jointure sur la table maître ne peut être qu' une clé primaire ou unique.
• Si la clé primaire est une clé composée, toutes les colonnes doivent apparaître dans la jointure.

CREATE TABLE CLIENTS
	(CLIENT_ID        VARCHAR2(7),
	CLIENT_ADRESS       VARCHAR2(32),
	CLIENT_DEPT        VARCHAR2(2))
	 PARTITION BY LIST (CLIENT_REGION)
	 STORAGE (INITIAL 100K NEXT 10K PCTINCREASE 0) TABLESPACE tbs_cli
	(PARTITION region_nord
	  VALUES ('62','75','92','60','45','44')
	  STORAGE(INITIAL 25K, NEXT 5K) TABLESPACE tbs_cli1
	  PARTITION region_sud
	  VALUES ('86','87','33','13','69','84'));

ALTER TABLE CLIENT
	ADD PARTITION region_autre
	VALUES ('20','97');

ALTER TABLE CLIENTS
	MERGE PARTITIONS nord_est, nord_ouest
	INTO PARTITION region_nord;

ALTER TABLE CLIENTS
	MODIFY PARTITION region_sud
	ADD VALUES ('09','31');

ALTER TABLE CLIENT
	MODIFY PARTITION region_sud
	DROP VALUES ('87');

ALTER TABLE CLIENTS
	SPLIT PARTITION region_nord
	VALUES ('44','45','92')
	INTO (PARTITION region_nord_ouest
	TABLESPACE tbs_cli2
	PARTITION region_nord_est) ;

• la valeur Null n'est pas acceptée dans les listes.
• partition sur plusieurs colonnes non acceptées.
• la liste des valeurs ne peut dépasser 4 Ko.
• chaque liste doit comporter au moins une valeur.

ALTER TABLE matable
	DROP SUBPARTITION tab1
	UPDATE GLOBAL INDEXES
	PARALLEL (DEGREE 4);

• Tablespaces transportables : Support de différentes tailles de bloc entre deux bases.
• Oracle Change Data Capture (CDC) : A l'aide de déclencheurs (triggers), Oracle capture les ordres DML et les insère dans une table de modifications (change table) afin que les changements puissent être publiés ultérieurement (et seulement les changements).
  Pour publier les changement d'une application transactionnelle vers un DataWareHouse:
  • Sélectionner les tables (sources) pour lesquelles on veut surveiller les changements
  • Utiliser le package DBMS_LOGMNR_CDC_PUBLISH pour récupérer les changements sur ces tables.
  • Publier les changements à l'aide des tables de modifications
  • Donner ou révoquer les droits de select sur ces tables de modification.
  Pour Souscrire :
  • Utiliser le package DBMS_LOGMNR_CDC_SUBSCRIBE pour souscrire aux tables sources.
  • Préparer les vues du souscripteur qui permettent de faire un select sur les tables de modification
  • Purger puis supprimer les vues devenues inutiles.
  Note : Il faut bien sûr donner les droits d'exécution sur les 2 packages précités. Les tables de modification sont gérées en interne par LogMiner.
• Vues supportant CDC :
  • CHANGE_SOURCES: Liste des changements des sources.
  • CHANGE_SETS: Liste des ensembles de changements.
  • CHANGE_TABLES: Liste les tables de modification.
  • DBA_SOURCE_TABLES : Liste des tables sources publiées.
  • DBA_PUBLISHED_COLUMNS : Liste des colonnes des tables sources publiées.
  • DBA_SUBSCRIPTIONS : Liste des souscriptions enregistrées.
  • DBA_SUBSCRIBED_TABLES : Liste des tables publiées pour lesquelles les souscripteurs ont souscrit.
  • DBA_SUBSCRIBED_COLUMNS : Liste des colonnes des tables publiées pour lesquelles les souscripteurs ont souscrit.

• Pipelines Data Transformation (9i)
• External Tables : Tables en lecture seule dont les données sont stockées dans des fichiers plats et décrivent comment les données sont présentées à la base de données.
  • table virtuelle
  • aucun ordre DML n'est autorisé
  • pas d'indexation possible
  • droits gérés par les privilèges système : SELECT TABLE et READ DIRECTORY
  • complémentaires à SQL*Loader
  • créées avec l'ordre SQL :

    CREATE TABLE order_items_ext
    	(order_id number (12), line_item_id number (3) ,
    	product_id number (6), unit_price number (8,2),
    	quantity number(8))
    	ORGANIZATION EXTERNAL           => table externe
    		(TYPE oracle_loader       => type d'accès (drivers utilisés)
    		DEFAULT DIRECTORY  ext_tables    => Répertoire contenant les fichiers
    		ACCESS PARAMETERS   
    		(RECORDS DELIMITED BY NEWLINE
    		FIELDS TERMINATED BY ','
    		BADFILE 'bad_order'
    		LOGFILE 'log_order'
    		MISSING FIELS ARE NULL)
    		LOCATION ('order_items1.dat','order_items2.dat'));

    Les vues suivantes ont été créées pour stocker les informations des tables externes :
    • DBA_EXTERNAL_TABLES
    • DBA_EXTERNAL_LOCATIONS

CREATE OR REPLACE FUNCTION transform (p r.ref_cur_type)
RETURN table_order_items_type (=> type prédéfini par l'utilisateur)
PIPELINED is
BEGIN
	FOR enr in p LOOP
		-- This is simple but in the real world.....
		enr.quantity:= enr.quantity*2;
		pipe row (enr);
	END LOOP;
RETURN;
END;

SELECT * FROM       
	TABLE ( transform (cursor (select * from order_items_ext)));

• insertion dans plusieurs tables simultanément.
• peut être utilisé en interne pour rafraîchir des vues matérialisées.
• peut bénéficier de la parallélisation et du mécanisme "Direct Load".

INSERT ALL
INTO sales     VALUES (today,product_id,quantity)
INTO prd_sales VALUES (today,product_id,total)
SELECT TRUNC(s.sales_date) today, 
		S.product_id,
		SUM(s.amount_sold) total,
		SUM(s.quantity) quantity,
FROM sales_activity_direct s, product_information p
WHERE s.product_id = p.product_id
AND trunc(sales_date)=trunc(sysdate)
GROUP BY trunc(sales_date), s.product_id;

INSERT ALL
WHEN product_id in ('01','05','06') THEN
INTO sales_01     VALUES (today, product_id, total)
WHEN product_id in ('02','03','04') THEN
INTO sales_02 VALUES (today, product_id, total)
SELECT TRUNC (s.sales_date) today, 
		S.product_id,
	SUM (s.amount_sold) total,
FROM sales;

INSERT FIRST
WHEN SALARY > 40000 THEN 
INTO EMPLOYE_BOSS (name, salary)
WHEN SALARY > 25000 THEN 
INTO EMPLOYE_CADRE (name, salary)
WHEN SALARY > 15000 THEN 
INTO EMPLOYE_SIMPLE (name, salary)
ELSE
INTO EMPLOYE_OTHERS (name, salary)
SELECT name, salary from emp;

MERGE INTO emp e
USING emp_ref s    
ON (e.emp_id = s.emp_id)
WHEN MATCHED THEN UPDATE 
	SET e.name = s.name
WHEN NOT MATCHED THEN INSERT (emp_id, name … )
	VALUES (emp_id, name … );

• Utilisation de SQL*PLUS qui possède les mêmes fonctionnalités.
  • sqlplus /nolog : pour ouvrir SQL*Plus sans se connecter
  • puis connect …. as sysdba

• connect internal ne fonctionne plus.

ALTER TABLE matable
MODIFY(nom_long CLOB);

• AnyData
• AnyDataSet
• AnyType
• XMLType
• TIMESTAMP (Précision) : Stocke la date avec les heures, minutes, secondes et fractions de secondes (de 0 à 9 décimales).
• INTERVAL DAY TO SECOND : Période de temps en jours, heures, minutes et secondes.
• INTERVAL YEAR TO MONTH : Période de temps en années et mois.

Select e.ename,
	d.deptno
From emp e CROSS JOIN
	dept d;

Select e.ename,
	deptno
From emp e NATURAL JOIN
	dept d;

Select e.ename,
	deptno
From emp e JOIN
	dept d USING (deptno)
Where d.dept_name = 'PARIS';

Select e.ename,
	deptno
From emp e JOIN
	dept d ON (e.deptno = d.deptno) JOIN
	region r ON (d.regionno = r.regionno)
Where r.region_name = 'EUROPE';

Select e.ename,
	deptno
From emp e {LEFT|RIGHT|FULL} OUTER JOIN
	dept d ON (e.deptno = d.deptno)
Where d.dept_name = 'PARIS';

• LEFT : rapatrie les données de la table de gauche.
• RIGHT : rapatrie les données de la table de droite.
• FULL : rapatrie les données des deux tables.

EXTRACT (YEAR FROM madate)

Select e.ename , (CASE salary
	WHEN 10000 THEN 'EMPLOYE'
	WHEN 15000 THEN 'CADRE'
	ELSE 'BIG BOSS' END) status
From Emp e;

Select e.ename, (CASE 
	WHEN salary < 10000 THEN 'EMPLOYE'
	WHEN salary > 15000 THEN 'CADRE'
	ELSE 'BIG BOSS' END) status
From Emp e;

Equivalence <=>

IF expr1 = expr2 Then 
	return null
ELSE
	return expr1;
End If;

Equivalence
COALESCE(expr1,expr2,expr3,...,exprn)

Select t.libelle_type_client ,
	(Select count (*)
	From Client c
	Where c.typ_cli = t.typ_cli) Total
From Type_Client T;

select time_id, support_id , item_id,
	sum( montant) as cumul
From sales
Group By GROUPING SETS
	((time_id, support_id , item_id),
	(time_id, support_id), 
	(time_id, item_id))

• GROUP BY ROLLUP (a, b, c) -> crée 4 regroupements
  • un pour le triplet (a,b,c)
  • un pour le couple (a,b)
  • un pour (a)
  • un pour le total général.
    ((a,(b,c)) ne ramène alors que 3 trois groupes).

GROUP BY CUBE (a, b, c) => ramène 2 puissance 3 soit 8 groupes possibles :
• a, b et c
• a, b, tout (c)
• a, tout (b), c
• a, tout (b), tout(c)
• tout (a), b et c
• tout (a), b, tout (c)
• tout (a), tout (b), c
• tout (a), tout (b), tout (c) = total général.
• Ces 3 types de regroupement sont bien sûr cumulables.

• TIMESTAMP : Format de date incluant jusqu'au milliardième de secondes
• TIMESTAMP WITH TIME ZONE : TIMESTAMP + décalage en heures de la zone.
• TIMESTAMP WITH LOCAL TIME ZONE : TIMESTAMP + décalage en heures et minutes de la zone intégrée dans la date affichée.
• INTERVAL YEAR TO MONTH : stocke le nombre d'années et de mois d'une période.
• INTERVAL DAY TO SECOND : stocke le nombre de jours, heures, minutes, secondes et fractions de seconde d'une période.
• Paramètre statique de base TIME_ZONE :
  • peut avoir comme valeur soit un nombre d'heures et de minutes, soit un nom de zone.
  • peut être spécifié à la création de la base ou après avec un ALTER DATABASE.

• Heures d'été / d'hiver : géré automatiquement par la base pour TIMESTAMP WITH TIME ZONE et TIMESTAMP WITH LOCAL TIME ZONE (pas pour TIMESTAMP).

• Seul utilisable pour le national character set.
• En UTF-8 (1 à 4 octets) ou UTF-16 (2 * 2 octets).

• PLSQL_NATIVE_LIBRARY_DIR : Répertoire de stockage des objets créés par le compilateur.
• PLSQL_NATIVE_MAKE_UTILITY : Nom de l'utilitaire Make.
• PLSQL_NATIVE_MAKE_FILE_NAME : Chemin complet du Make File.
• PLSQL_NATIVE_C_COMPILER : Chemin complet du compilateur C(prend par défaut celui du système d'exploitation).
• PLSQL_NATIVE_LINKER : Chemin complet de l'éditeur de liens (linker) (sous UNIX).
• PLSQL_COMPILER_FLAG : 'INTERPRETED' ou 'NATIVE' (peut être modifié au cours de la session).

• Héritage complet pour les types SQL.
• Combinaisons multiples possibles entre les collections, tables imbriquées (Nested tables) et VARRAY.
• Type : FINAL ou NOT FINAL (signifie qu'il y a encore des fils dessous ).
• Pouvoir de substitution: une colonne d'un type peut contenir des instances de son type ou de n'importe quel de ses sous-types.
• Vues hiérarchiques : du même genre que pour les types et sous-types.
• Privilèges système:
  • UNDER ANY TYPE : permet de créer des sous-types sous des types non finaux.
  • UNDER ANY VIEW : permet de créer des sous-vues.
  • WITH HIERARCHY OPTION : Donne (par un GRANT) un privilège objet sur tous ses fils.

• SQL*Loader, Import et Export supportent ces nouvelles fonctionnalités.
• Possibilité d'évolution des Types d'Objet :
  • Avant 9i, seul l'ajout de méthodes était autorisé;
  • Une modification de l'objet est répercutée sur les objets le référençant.
  • Les attributs et méthodes peuvent être ajoutés, supprimés ou modifiés.
  • Les types SQL peuvent être modifiés sans être recréés.

• FAST_START_IO_TARGET : nombre de blocs de données à récupérer;
• LOG_CHECKPOINT_INTERVAL : intervalle de temps maximum pour limiter le nombre d'enregistrements de redo logs à lire.
  (Ces 2 paramètres ne peuvent pas être modifiés manuellement).

• Index à clé inversée.
• Index basé sur une fonction
• Index à clé compressée sur les tables normales
• Index à clé compressée sur les tables organisées en index.

ALTER TABLE matable MOVE ONLINE TABLESPACE data1 OVERFLOW TABLESPACE data1;

ALTER INDEX monindex UPDATE BLOCK REFERENCES;

• Vérifier que la table est réorganisable en ligne avec DBMS_REDEFINITION.Can_Redef_Table.
• Créer la table d'intérim qui servira à stocker les mises à jour effectuées lors de la redéfinition.
• Réorganiser la table avec DBMS_REDEFINITION.Start_Redef_Table.
• Créer les objets et triggers rattachés à la table d'intérim.
• Finir la réorganisation avec DBMS_REDEFINITION.Finish_Redef_Table.

• colonnes de clés primaires non modifiables.
• tables des schémas SYS ou SYSTEM non modifiables.
• colonnes de type LONG ou FILE non modifiables.
• réorganisation dans le même schéma.
• nouvelles colonnes ne peuvent être déclarées NOT NULL qu'après la réorganisation.

• Permet de créer une vue de la base à une date située dans le passé.
• Peut démarrer à partir d'une date ou d'un numéro SCN.
• Seules les transactions commités jusqu'à ce moment sont visibles.
• Valable au niveau de la session.
• Se base sur les tablespaces d'annulation : plus on veut regarder loin dans le temps, plus ceux ci doivent être importants.
• Utilise le package DBMS_FLASHBACK.
• Pour spécifier le temps de rétention:

  ALTER SYSTEM SET UNDO_RETENTION= tempsensecondes;

• se lance avec la commande :

  DBMS_FLASHBACK.Enable_At_Time('30/08/2002 0:51:42');

  (il prend le N° SCN le plus proche de la date spécifiée, dans un intervalle de 5 minutes).
• se termine avec la commande:

  DBMS_FLASHBACK.disable.

  ou en fermant la session.
• ne s'applique qu'aux ordres DML.

• Null (N) => non utilisée.
• Shared (S) => partagée entre plusieurs instances.
• Exclusive (X) => réservée par une seule instance.

• Local => utilisées par 1 seul serveur.
• Global => utilisées par plusieurs serveurs.

• GC_FILES_TO_LOCKS : Assigne le Global Cache Service à de multiples blocs.
• INSTANCE_NUMBER : Identifiant de l'instance, un numéro par instance, par défaut à 1.
• INSTANCE_NAME : nom de l'instance, généralement le nom du SID.

• sql_redo : donne l'ordre SQL exécuté puis stocké dans les fichiers redo.
• sql_undo : génère l'ordre inverse à l'ordre SQL utilisé.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

DBMS_LOGMNR_D.Build('dictionary.ora','/oracle/dbs',option =>dbms_logmnr_d.store_in_flat_file) (vers un fichier plat)
                                            	   option => dbms_logmnr_d.store_in_redo_logs) (vers les redo logs).

• LONG
• Type Objet
• Les collections (Varray …)
• Objet de type REF
• Les tables organisées en index (IOTs).

• synchrone : toute modification est répercutée immédiatement sur la standby => pas de perte de données
• asynchrone : modification envoyée avec un délai par l'intermédiaire d'un processus esclave.
  (cet outil existe aussi en ligne de commande : DGMGRL

• nécessite d'avoir installé Oracle Management Server (OMS) et les agents sur chacune des bases.

• la base de données primaire soit arrêtée proprement (en mode normal, immédiate ou transactionnel).
• les redo logs archivés soient disponibles.
• les redo logs, datafiles et control files de la base primaire soient disponibles.

• pour la base primaire :

  ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

• puis faire un shutdown, un start nomount puis un mount standby database.
• pour la base de standby :

  ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY

• synchronous data copy : fichier copié par logwriter (LGWR) de manière synchrone.
• asynchronous data copy : fichier copié par logwriter (LGWR) de manière asynchrone.
• batched data copy : fichier copié de manière traditionnelle (comme 8i) avec le processus ARCH.

• synchrone : Attribut AFFIRM positionné dans le paramètre Log_Archive_Dest_n.
• asynchrone : Attribut NOAFFIRM positionné dans le paramètre Log_Archive_Dest_n.

ALTER DATABASE SET STANDBY DATABASE PROTECTED

ALTER DATABASE SET STANDBY DATABASE UNPROTECTED

• la base de secours (standby) est ouverte
• les modifications sur la primaire sont écrites sur la standby par le process server de cette dernière, ce qui ne garantit plus une équivalence totale entre les deux bases.