Le stockage de données

Date de publication : 06/10/2004

Par Orafrance (orafrance.developpez.com)
 

Architecture Oracle : Fichiers de données et tablespaces

Retour au sommaire Architecture

Les tablespaces

1. Qu'est ce qu'un tablespace ?

1.1. Les différents types de tablespaces

a. Le tablespace temporaire

Paramètrage du tablespace temporaire de la base
CREATE DATABASE <SID>...
   DEFAULT TEMPORARY TABLESPACE temp;

Modification du tablespace temporaire de la base
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Création d'un tablespace temporaire locally managed
CREATE TEMPORARY TABLESPACE temp 
   TEMPFILE 'g:\oracle\oradata\orafrance\temp01.dbf' 
   SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

Création d'un tablespace temporaire dictionnary managed
CREATE TABLESPACE temp 
   DATAFILE 'g:\oracle\oradata\orafrance\temp01.dbf' 
   SIZE 20M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 150M;

Assignation d'un tablespace temporaire à un utilisateur
ALTER USER orafrance
   TEMPORARY TABLESPACE temp;

b. Le tablespace UNDO

UNDO_MANAGEMENT	AUTO ou MANUAL	Met en place la gestion automatique du mécanisme d'annulation ou permet d'utiliser les ROLLBACK SEGMENT (comme dans les versions précédentes).
UNDO_TABLESPACE	nom du tablespace	Indique le nom du tablespace.
UNDO_SUPPRESS_ERRORS	TRUE ou FALSE	Permet d'ignorer ou non les erreurs liées à une gestion inappropriée du tablespace UNDO (i.e. commande prévue pour les rollback segments sur le UNDO : ALTER ROLLBACK...SET TRANSACTION USE ROLLBACK...).
UNDO_RETENTION	nombre de secondes	Durée de rétention des données dans le tablespace UNDO (cf.  flaskback)

Paramètrage du tablespace UNDO de la base
CREATE DATABASE <SID>...
   UNDO TABLESPACE undotbs 
   DATAFILE 'g:\oracle\oradata\orafrance\undotbs.dbf' size 100M;

Création du tablespace UNDO
CREATE UNDO TABLESPACE undotbs
   DATAFILE 'g:\oracle\oradata\orafrance\undotbs.dbf' size 100M;

Modification du tablespace UNDO utilisé
ALTER SYSTEM
   SET UNDO_TABLESPACE=undotbs2;

c. Le tablespace transportable

• Les bases de données source et cible doivent être sur des plateformes identiques, impossible donc de transporter un tablespace d'un serveur Sun Solaris à un serveur Windows 2000.
  
• La source et la cible doivent utiliser le même jeu de caractère (character set et national character set).
  
• Il est évidemment impossible de transporter un tablespace dans une base qui contiendrait un tablespace de même nom.
  
• Enfin, les tablespaces transportables ne supportent pas : les vues matérialisées et les index de fonction

Création des tablespaces
SQL> create tablespace DATA_TBS
		datafile 'd:\dvp1\oradata\data_tbs.ora' size 1M reuse
		AUTOEXTEND OFF
		ONLINE
		default storage
		(initial 32 k next 32 k
		minextents 2 maxextents unlimited
		pctincrease 1);

Tablespace created.

SQL> create tablespace INDEX_TBS
		datafile 'd:\dvp1\oradata\index_tbs.ora' size 1M reuse
		AUTOEXTEND OFF
		ONLINE
		default storage
		(initial 32 k next 32 k
		minextents 2 maxextents unlimited
		pctincrease 1) ;

Tablespace created.

Création de la table et sa Primary Key
SQL> create table orafrance.matable (col1 NUMBER, col2 NUMBER )
		tablespace DATA_TBS;

Table created.

SQL> alter table orafrance.matable add
		(constraint matable_pk primary key (col1)
		USING INDEX TABLESPACE INDEX_TBS
		);

Table altered.

Ici, la clé primaire est créée dans le tablespace INDX_TBS alors que la table est dans DATA_TBS.

Vérification de la transportabilité des tablespaces
SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('DATA_TBS',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('INDEX_TBS',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS 
------------------------------------------------------------------------------
Index ORAFRANCE.MATABLE_PK in tablespace INDX_TBS enforces primary constraints 
of table ORAFRANCE.MATABLE in tablespace DATA_TBS

Le tablespace DATA_TBS peut être transporté contrairement à INDX_TBS. Effectivement, comment créer des index d'une table qui n'existe pas ?

Création d'une FK et test de transportabilité
SQL> create table orafrance.matable_fille
		(col1 NUMBER)
		tablespace INDX_TBS;

Table created.

SQL> alter table orafrance.matable_fille
		add constraint matable_matablefille_fk foreign key (col1)
		references orafrance.matable(col1);

Table altered.

SQL> 
SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('ttbs_01',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('ttbs_02',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS 
------------------------------------------------------------------------------
Constraint MATABLE_MATABLEFILLE_FK between table ORAFRANCE.MATABLE in 
tablespace DATA_TBS and table ORAFRANCE.MATABLE_FILLE in tablespace INDX_TBS 

Index ORAFRANCE.MATABLE_PK in tablespace INDX_TBS enforces primary constraints 
of table ORAFRANCE.MATABLE in tablespace DATA_TBS

On a désormais un deuxième problème avec INDX_TBS, le tablespace contient la table MATABLE_FILLE liée à ORAFRANCE.MATABLE, ces deux tables étant dans deux tablespaces distincts.

Et si on vérifie le transport des deux tablespaces ?
SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('ttbs_01,ttbs_02',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

Ici, le transport des deux tablespaces est possible, toutes les contraintes étant levées.

En mode ligne de commande
exp userid = 'system/manager@dvp1' 
TRANSPORT_TABLESPACE=y 
TABLESPACES=DATA_TBS,INDX_TBS
TRIGGERS=y 
CONSTRAINTS=y 
GRANTS=y
rows=y

imp userid = 'system/manager@dvp2' 
TRANSPORT_TABLESPACE=y 
TABLESPACES=DATA_TBS,INDX_TBS
DATAFILES=d:\dvp2\oradata\data_tbs.ora, d:\dvp2\oradata\indx_tbs.ora

Pour plus d'information sur les tablespaces transportables, je vous invite à lire la documentation Oracle :  Transporting Tablespaces Between Databases.

1.2. Gestion de l'allocation d'espace

CREATE TABLESPACE ora_data
DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA01.dbf' size 100M
BLOCKSIZE 4k;

1.3. Le tablespace LOCALLY MANAGED et les extents

1.4. La High-Water Mark ou niveau de flottaison

1.5. Paramètres de stockage et optimisation de l'espace

Exemple
SQL> CREATE TABLE MATABLE (ID       NUMBER NOT NULL, 
						   COMMENTS VARCHAR2(4000)
						   )  
		TABLESPACE TOOLS PCTFREE 0 PCTUSED 0 
		STORAGE ( INITIAL 4096 NEXT 4096 PCTINCREASE 0) ; 

Table créée.

SQL> INSERT INTO matable (id) values (1); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (2); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (3); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (4); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (5); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (6);    

1 ligne créée.

SQL> COMMIT; 

Validation effectuée.

SQL> create table CHAINED_ROWS ( 
		owner_name         varchar2(30), 
		table_name         varchar2(30), 
		cluster_name       varchar2(30), 
		partition_name     varchar2(30), 
		subpartition_name  varchar2(30), 
		head_rowid         rowid, 
		analyze_timestamp  date ); 

Table créée.

SQL> analyze table matable list chained rows into chained_rows; 

Table analysée.

SQL> select count(*) from chained_rows; 

  COUNT(*)
----------
         0

SQL> declare 
		toto varchar2(4000); 
		begin 
		for i in 1..4000 loop 
		toto := toto || 'a'; 
		end loop; 
		update matable set comments = toto; 
		COMMIT; 
		end; 
		/ 

Procédure PL/SQL terminée avec succès.

SQL> analyze table matable list chained rows into chained_rows; 

Table analysée.

SQL> select count(*) from chained_rows; 

  COUNT(*)
----------
         2

SQL> declare
	 toto varchar2(4000);
	 begin
	 for i in 1..4000 loop
	 toto := toto || 'a';
	 end loop;
	 insert into matable values (7,toto);
	 COMMIT;
	 end;
	 /

Procédure PL/SQL terminée avec succès.

SQL> truncate table chained_rows;

Table tronquée.

SQL> analyze table matable list chained rows into chained_rows; 

Table analysée.

SQL> 
SQL> select * from chained_rows; 

aucune ligne sélectionnée

3. Syntaxe commentée de création d'un tablespace

3.1. Création de tablespace

Attention : si le tablespace est en LOCAL UNIFORM, alors il faut s'assurer que le prochain extent pourra contenir au moins 5 blocs, c'est à dire que la taille définie pour les prochains extents est d'au moins 6 fois la taille du bloc de base de données. Si au contraire, le tablespace est en LOCAL AUTOALLOCATE et que les blocs de base sont au moins de 16k, alors Oracle créera des extents d'au moins 1 Mo.

3.2. Suppression de tablespace

3.3. Modification de tablespace

3.4. Modification de la base de données (spécifique au tablespace)

4. Exemples d'ordres SQL pour la gestion des tablespaces

Création d'un tablespace
CREATE TABLESPACE ora_data
   DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA01.dbf' size 100M,
         'g:\oracle\oradata\orafrance\ORA_DATA02.dbf' size 100M
   MINIMUM EXTENT 500K --(uniquement V8)
   DEFAULT STORAGE (initial 500K next 500K MAXEXTENTS 500 PCTINCREASE 0);

Création d'un tablespace géré localement
CREATE TABLESPACE ora_data 
   DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA01.dbf' SIZE 10M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Si la taille d'un bloc est de 2k alors ce tablespace s'étendra de 64 blocs à chaque nouvel extent.

Placer un tablespace à l'état OFFLINE
ALTER TABLESPACE ora_data OFFLINE;

Cette commande peut être utile pour éviter que les données du tablespace soient accessibles, particulièrement lors des opérations de maintenance.

Création d'un tablespace UNDO de 10 Mo autoextensible
 

CREATE UNDO TABLESPACE undotbs
   DATAFILE 'undotbs.dbf'
   SIZE 10M AUTOEXTEND ON;

Suppression d'un tablespace et ses contraintes
DROP TABLESPACE ora_data INCLUDING CONTENTS CASCADE CONSTRAINTS;

Modifier les clauses de stockage d'un tablespace
ALTER TABLESPACE ora_data 
   DEFAULT STORAGE ( INITIAL 100K 
                     NEXT 100K 
			   MINEXTENTS 1 
			   MAXEXTENTS 300 
			  PCTINCREASE 1);

Ajout d'un datafile
ALTER TABLESPACE ora_data ADD
  DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA03.dbf' size 100M;

Déplacement d'un datafile
ALTER TABLESPACE ora_data OFFLINE NORMAL; 
-- Après avoir copié le fichier ORA_DATA03.dbf 
-- de g:\oracle\oradata\orafrance vers f:\oracle\oradata\orafrance
ALTER TABLESPACE ora_data
  RENAME DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA03.dbf'
  TO              'f:\oracle\oradata\orafrance\ORA_DATA03.dbf'; 
ALTER TABLESPACE ora_data ONLINE NORMAL;

Suppression d'un datafile
ALTER DATABASE  
   DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA03.dbf' OFFLINE DROP;

Modifie la taille d'un datafile
ALTER DATABASE  
   DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA03.dbf' resize 50M;

Suivi du remplissage des tablespaces
set lines 150
col Max_Libre for 999,999,999,999
col Total_Dispo for 999,999,999,999
col Taille_Ts for 999,999,999,999
col Ts_Frag_Ext_Libres for A50
col tablespace_name for A10

SELECT tablespace_name, Taille_Ts, Max_Libre, Total_Dispo,
to_char(100*Total_Dispo/Taille_Ts, '999.99') || '%' AS pct_dispo,
DECODE(Total_Dispo - Max_Libre,0,'Tous les extents libres sont contigus', 
       'Le nb d''extents libres non contigus est de: ' || Nb_Frag) 
FROM ( SELECT tablespace_name,
       sum(bytes) AS Taille_Ts
       FROM dba_data_files
       GROUP BY tablespace_name
      ),
( SELECT tablespace_name AS fs_ts_name,
  max(bytes) AS Max_Libre
, sum(bytes) AS Total_Dispo,
count(*) AS Nb_Frag
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
order by 5 desc;

5. Tordons le cou aux mythes

5.1. Trop d'extents dégrade les performances

5.2. COALESCE me permet de défragmenter mes datafiles

Remerciements

Les sources présentés sur cette pages sont libre de droits, et vous pouvez les utiliser à votre convenance. Par contre cette page de présentation de ces sources constitue une oeuvre intellectuelle protégée par les droits d'auteurs. Copyright ©2004 Frédéric Dubois. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérets. Cette page est déposée à la SACD.