De l’utilité des tablespaces

Un tablespace ou espace disque logique, est une partition logique de la base contenant un ou plusieurs fichiers.
Un fichier appartient à 1 et 1 seul tablespace.
Par défaut un tablespace à la création est ON LINE et donc accessible, il peut être mis OFFLINE (et les fichiers qu’il contient par conséquent) pour en interdire l’accès ou pour certaines opérations de maintenance

Il existe toujours deux tablespace baptisés SYSTEM et SYSAUX .
- SYSTEM : contient le dictionnaire de données et segment d’annulation SYSTEM
- SYSAUX : contient les informations nécessaires aux composants et outils supplémentaires
et traditionnellement on créera également
- ‘TEMP’ : pour les données ’swappées’ sur disque lors d’opération de tri ou de fusion trop volumineuses en mémoire
- ‘UNDO’ : pour les segment d’annulation, qui stockent les images avant, lors des ROLLBACKS

Outre ces tablespaces ’système’ qui servent en quelque sorte à la cuisine interne d’Oracle, il faudra bien tout de même stocker quelques données (et indexs)
Ici plusieurs stratégies sont possibles :
- séparation des indexs et des datas,
- séparation des différents domaines fonctionnels

note : Il serait possible également de stocker les datas, les index dans ces SYSTEM ou SYSAUX.
Ceci est vivement déconseillé, car on aurait ainsi une base minimale peu structurée.

Les tablespaces sont donc utiles pour répartir les données, les index, mais aussi les segments d’annulations et les espaces temporaires sur plusieurs espaces logiques et disques.
Ils permettent :
- performance (répartitions des accès disques),
- souplesse (séparation fonctionnelle ou métier, meilleure granularité des sauvegardes),
- sécurité (séparation des infos systèmes des données utilisateurs)

Les tablespaces  peuvent être actif  ( ONLINE ) c’est bien sûr le cas par défaut, ou inactif ( OFFLINE).
Accessible en lecture /écriture (par défaut) ou en lecture seule (READ ONLY).
Journalisés ou pas (FORCE LOGGING / NO LOGGING)

SQL de base pour la  création  des TBS

SQL> CREATE TABLESPACE …
SQL> DROP TABLESPACE…

exemples

SQL> CREATE TABLESPACE compta
DATAFILE’/database/TEST/compta1TEST.ora’ SIZE 100M;
SQL> DROP TABLESPACE COMPTA
INCLUDING CONTENTS AND DATAFILE;

Description des tablespaces et fichiers de la base courante dans les vues
DBA_TABLESPACES , DBA_DATA_FILES, DBA_FREE_SPACE du dictionnaire.

SQL> SELECT TABLESPACE_NAME “Nom TBS”, CONTENTS “Type de contenu”, STATUS “EN ligne?”, LOGGING “Journalise?”, BIGFILE FROM DBA_TABLESPACES;

Nom TBS Type de contenu EN ligne? Journalise?
——— —————– ——— ———-
BIGFILE SYSTEM PERMANENT ONLINE LOGGING NO
UNDOTBS1 UNDO ONLINE LOGGING NO
SYSAUX PERMANENT ONLINE LOGGING NO
TEMP TEMPORARY ONLINE NOLOGGING NO
USERS PERMANENT ONLINE LOGGING NO
EXAMPLE PERMANENT ONLINE LOGGING NO
6 rows selected.

Tablespaces et fichiers

Un tablespace contient AU MOINS un fichier. Celui-ci est créé lors de la création du tablespace, de manière automatique par
Oracle, en fonction des paramètres donnés par la commande CREATE ou ALTER tablespace (emplacement du fichier, nom, taille, et mode d’extension).

note : Lors de la suppression du tablespace (DROP TABLESPACE…) les fichiers correspondant ne sont PAS SUPPRIMES par Oracle par défaut. Utilisez la clause ‘AND DATAFILE’…

exemples

SQL> select tablespace_name, file_name,
bytes/1024/1024 Taille_MO, status, autoextensible
from dba_data_files

TABLESPACE_NAME	FILE_NAME	TAILLE_MO	STATUS	AUT
SYSTEM	/data/prdun/systPRDUN.dbf	1400	AVAILABLE	YES
UNDO	/data/prdun/undo.dbf	25000	AVAILABLE	YES
SRS_DAT	/data/prdun/srs_dat.dbf	6610	AVAILABLE	YES
DRSYS	/data/prdun/drsys01.dbf	5,625	AVAILABLE	YES
USERS	/data/prdun/users.dbf	150	AVAILABLE	YES
XDB	/data/prdun/xdb01.dbf	48,125	AVAILABLE	YES

Extension des tablespaces et des fichiers

La taille d’un tablespace est la taille de son (ses) fichier(s) d’origine.
Pour augmenter la taille d’un tablespace, il y a 2 solutions :
* Ajouter un fichier au tablespace, qui sera chainé au premier (ALTER TABLESPACE toto ADD DATAFILE…)
* mettre le fichier du tablespace en AUTO extension (ALTER DATABASE DATAFILE toto.dbf AUTOEXTEND ON)
Une table (et tout segment en général) , peut “s’étaler” sur plusieurs fichiers. Ainsi le fait qu’une table sature un tablespace n’est pas bloquant il suffit d’augmenter la taille du tablespace.

Autoextension des fichiers

ATTENTION : la clause AUTOEXTEND spécifie la taille d’extension du fichier d’un tablespace. La clause STORAGE INITIAL, NEXT, MINEXTENTS … spécifie la taille d’extension d’UN SEGMENT du tablespace par exemple une table. Ces 2 paramètres sont totalement indépendants. La preuve en est qu’une table (un segment de données) est forcément en allocation dynamique alors qu’un fichier peut avoir une taille fixe (AUTOEXTEND OFF)

note : Le changement de mode AUTOEXTEND se fait avec la commande ‘ALTER DATABASE’ pour les ‘SMALLFILE’ et ‘ALTER TABLESPACE’ pour les ‘BIGFILE’

exemples

SQL> — passage en AUTO extension d’un fichier de tablespace existant
SQL> ALTER DATABASE DATAFILE ‘/database/TEST/compta1TEST.ora‘ AUTOEXTEND ON;
SQL> ALTER DATABASE DATAFILE ‘/database/TEST/compta1TEST.ora‘
AUTOEXTEND OFF
SQL> — ajout d’un ficheir auto extensible jusqu’a 100 MO
SQL> ALTER TABLESPACE toto
ADD DATAFILE ‘/database/TEST/compta1TEST.ora‘
SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

Extension des segments

* clause LOCAL : Tablespaces gérés localement (Locally managed tablespaces)
Anciennement les tablespaces étaient gérés au niveau du dictionnaire de données, la gestion de l’espace physique (allocation / libération de blocs) se fait désormais dans l’entête du fichier(s) du tablespace. Une table binaire d’allocation (bitmap) y
est maintenue. C’est le fonctionnement par défaut (sauf pour le tablespace SYSTEM)
Avantages :
* pas de contention en mise à jour au niveau du dictionnaire
* et conséquemment pas d’utilisation de Rollback segment pour ces transactions
* pas de soucis de gestion de l’espace (calcul d’un storage adéquat)
* “coalesce” automatique (fusion des espaces libres contigus pour optimiser l’espace libre)
Evidemment la clause “DEFAULT STORAGE” est invalide pour les tablespaces gérés localement.

* Clause AUTOALLOCATE
C’est Oracle qui gère !

* Clause UNIFORM
Les extents ont tous la meme taille, par défaut 1MO, sinon elle est précisée par le paramètre ‘SIZE’

* clause STORAGE
Les règles et les statistiques d’allocations sont gérées au niveau du dictionnaire.
Pour plus d’informations voir le chapitre sur les ’segments et extents’
changement des paramètres d’un tablespace existant
ALTER TABLESPACE SYSTEM
DEFAULT STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS 300 PCTINCREASE 1);

Quelques exemples de syntaxe d’allocation

SQL> CREATE TABLESPACE COMPTA DATAFILE
‘/database/TEST/compta1TEST.ora’ SIZE 100M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SQL> CREATE TABLESPACE COMPTA DATAFILE
‘/database/TEST/compta1TEST.ora’ SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K;
SQL> CREATE TABLESPACE COMPTA DATAFILE
‘/database/TEST/compta1TEST.ora’ SIZE 100M
EXTENT MANAGEMENT DICTIONARY;

Gestion des tablespaces

Voici quelques exemples de syntaxe utiles.
Rem les unités de taille (pour SIZE, NEXT, MAXSIZE, etc.) sont
M = MO , G =GO, T=Teraoctets , P=Petaoctets, E=Exaoctets

– rappel sur création / modifs basiques
– creation / suppression
SQL> CREATE TABLESPACE compta
DATAFILE’/database/TEST/compta1TEST.ora’ SIZE 100M;
SQL> DROP TABLESPACE COMPTA
INCLUDING CONTENTS AND DATAFILE;

– modification d’état du tablespace
SQL> ALTER TABLESPACE COMPTA OFFLINE;
SQL> ALTER TABLESPACE CHARGEMENT_BATCH NOLOGGING;
SQL> ALTER TABLESPACE INFOCENTRE READ ONLY;

– extension de la taille du tablespace
– à l’ancienne
SQL> ALTER TABLESPACE COMPTA
ADD DATAFILE ‘/database/TEST/compta2TEST.ora’ SIZE 100M;
– à la moderne
SQL> ALTER TABLESPACE COMPTA
RESIZE 100M;

– modification des paramètres d’extension du tablespace
SQL> ALTER TABLESPACE COMPTA
AUTOEXTEND ON NEXT 10M MAXSIZE 100G;

– extension dynamique de la taille du fichier
SQL> ALTER DATABASE DATAFILE ‘/data/dba_dat.dbf’ RESIZE 100M;
– modification des paramètres d’extension du fichier
SQL>   ALTER DATABASE DATAFILE ‘/data/dba_dat.dbf’
AUTOEXTEND ON NEXT 500K MAXSIZE 10M

– renommage des tablespaces
SQL> ALTER TABLESPACE COMPTA RENAME TO FINANCES;

Quelques Contraintes supplémentaires lors du renommage
on ne peut renommer SYSTEM ou SYSAUX
on ne peut renommer un TBS offline ou qui contient des fichiers OFFLINE
et bien sûr
renommer un TBS ne renomme pas les fichiers qu’il contient…

Dernier cas de renommage (le + lourd) : les fichiers

– renommage des fichiers d’un tablespace
– en 4 temps !
SQL> ALTER TABLESPACE users OFFLINE NORMAL;
– renommer les fichiers au niveau OS
SQL> HOST mv/oracle/user1.dbf /oracle/users2.dbf
SQL> ALTER TABLESPACE users  RENAME DATAFILE ‘/oracle/user1.dbf’
TO ‘/oracle/users2.dbf’;
– on remet ONLINE
SQL> ALTER TABLESPACE users ONLINE ;

Il existe différents types de tablespaces spéciaux…

Tablespaces en lecture seule (READ ONLY tablespaces)

Ces tablespaces sont utilisés (on s’en serait douté) en lecture seule. Ils permettent de stocker des données statiques (ou variant très peu souvent, éventuellement sur des CDROMS, et ne rentrent pas en ligne de compte dans les sauvegardes / restaurations.
Pour modifier les données d’un Tablespace READ ONLY il est évidemment obligatoire de modifier préalablement son statut.

SQL> ALTER TABLESPACE toto READ ONLY;
SQL> ALTER TABLESPACE toto READ WRITE;

Tablespaces temporaires (temporary tablespaces)

Ces tablespaces apparus avec la 9i remplacent les segments temporaires placés précédemment dans des tablespaces standards.
On peut (et doit) créer un tablespace temporaire par défaut autre que SYSTEM, où seront stockées toutes les données temporaires (utilisées lors des tris, création d’index, jointures, etc). Ils sont définis lors de la création de la base :
SQL> CREATE DATABASE ma_base…
DEFAULT TEMPORARY TABLESPACE mon_temp;
ou a posteriori :
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;

En plus de ce tablespace temporaire par défaut, chaque utilisateur peut se voir assigner un tablespace temporaire particulier
SQL> CREATE TEMPORARY TABLESPACE mon_temp TEMPFILE ‘/oracle/data/temp01.dbf’
SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
SQL> CREATE USER toto
IDENTIFIED BY tutu
DEFAULT TABLESPACE data
QUOTA 100M ON data
TEMPORARY TABLESPACE temp_ts

Voir les vues dynamiques V$TEMP_EXTENT_MAP et V$TEMP_SPACE_HEADER pour des infos précises sur l’utilisation en temps réel de ces tablespaces.

Tablespace d’annulation (undo tablespaces)

Les UNDO tablespaces sont exclusivement réservés au stockage de segments d’images avant modification des données pour des annulations éventuelles (ROLLBACK).
Dans les versions précédentes d’Oracle, ces structures n’existaient pas et on utilisait des ROLLBACK SEGMENTS implantables dans n’importe quel tablespace. Oracle peut désormais fonctionner avec des UNDO tablespaces (gestion Automatique, préconisée) ou avec des rollback segments (gestion manuelle)

Paramètres de l’INIT.ORA associés
UNDO MANAGEMENT AUTO | MANUAL
utiliser les UNDO tbs ou les rollback segments
UNDO TABLESPACE nom tablespace
précise le tablespace d’UNDO à utiliser par la base
UNDO_SUPPRESS_ERRORS TRUE | FALSE
évite les erreurs lorsque l’on tente d’utiliser explicitement les Rollback Segments (ALTER ROLLBACK…, SET TRANSACTION USE ROLLBACK…) alors que la base utilise des UNDO tbs

SQL > CREATE UNDO TABLESPACE undo_1

ou dès la creation de la base
SQL> CREATE DATABASE test…
UNDO TABLESPACE undo_1
DATAFILE ‘/tmp/undo1.dbf’ SIZE 10M AUTOEXTEND ON

Tablespaces BIGFILE

Ces tablespaces comme leur nom le laisse supposer sont réservés aux bases très volumineuses.
Il contiennent un fichier UNIQUE dont la taille peut atteindre jusqu’à 4 milliards de blocs, soit pour pour un bloc minimum de 2KO, une taille respectable de 8TO!
Le type de tablespace par défaut SMALLFILE ou BIGFILE peutt être spécifié pour toute la base à la création ou par une commade ‘ALTER DATABASE’

SQL> CREATE BIGFILE TABLESPACE gulliver
DATAFILE ‘/oracle/data/gulliver1.dbf’ SIZE 100GO;

note : si toutes les données sont sur un seul disque on aura tout intérêt à ce que ce dernier soit ’strippé’

Segments

Un segment est composé d’extents. Un extent est composé de blocs contigus dont la taille dépend de l’OS. Le segment s’étend dynamiquement au sein du tablespace (éventuellement sur plusieurs fichiers donc…).

Il existe plusieurs types de segments :
- segment de données = table
- segment d’index = index
- segment d’annulation = rollback segment, qui stocke l’image avant modification des données
- segment temporaire , utilisé en interne par Oracle, si la zone mémoire de tri est insuffisante

remarque : seuls les objets ‘physiques’ peuvent être des segments. Ainsi une vue ou un synonyme n’est pas un segment…

On peut forcer les segments de données et d’index à s’implanter dans un tablespace particulier :
- explicitement à la création du segment
- implicitement en affectant un tablespace par défaut à l’utilisateur qui va créer le segment.

SQL> create table credit (n number, …)
tablespace TBS_COMPTA;
ou bien
SQL> create user Appli_comptable default tablespace COMPTA;
SQL> create table credit (n number, …);

Description dans la vue dba_segments du dictionnaire.

principe d’allocation des tables spaces

Allocation des Tablespaces standards

L’allocation obéit à des règles définies par une clause STORAGE. Oracle applique d’abord la clause STORAGE du segment (définie explicitement dans un create table par exemple), sinon il utilise la clause du tablespace (définie explicitement lors du create tablespace), sinon il utilise les valeurs par défaut (implicites) du tablespace.

Au départ, lors de la création du segment (table, index ou autre) Oracle alloue MINEXTENTS extents de taille INITIAL. Ensuite, lorsque le segment se remplit, quand le(s) premier(s) extent(s) est (sont) plein(s), il alloue un extent de taille NEXT. Ensuite il augmente la taille des extents d’un pourcentage fixé par PCTINCREASE. La limite est définie par MAXEXTENTS.

SQL> create table credit (n number,…)
storage ( initial 10K next 10K pctincrease 50 maxextents 100) ;

voici ce que donne le segment après création, puis 2 allocations supplémentaires :

Voir dba_extents dans le dictionnaire.

Allocation des Tablespaces gérés localement

Il y a deux types d’allocation pour ces tablespaces :

* “SYSTEM” : le DBA précise la taille de l’extent INITIAL puis Oracle détermine au mieux la taille des extents suivants (minimum 64K)
* “UNIFORM” : tous les extents auront la même taille, précisée par le DBA, ou la taille par défaut : 1MO

La gestion de l’espace libre

Lorsqu’un tablespace est créé (et le fichier minimum associé) Il est pratiquement vide hormis l’entête du fichier. L’espace libre (FREE SPACE) diminue au fur et à mesure de la creation ou de l’augmentation des segments dans ce tablespace et se fragmente lors de la libération de blocs (DELETE, TRUNCATE, etc).
Une “carte des trous” est donnée dans DBA_FREE_SPACE. Cette table possède une ligne par trou et la colonne BYTES donne la taille de chaque trou en octets.
Pour avoir l’espace libre total d’un tablespace il faudra donc sommer tous les espaces libres de tous les fichiers du tablespace.

SELECT SUM(BYTES)/1024 “Taille en KO” FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME= ‘TBS_TOTO

Pour récupérer l’espace libre d’un segment, on peut utiliser
TRUNCATE table_toto DROP STORAGE
ou
ALTER TABLE table_toto DEALLOCATE UNUSED

L’espace libre est défragmenté de temps en temps par Oracle. En clair les espaces libres contigus sont fusionnés (COALESCED).

voir DBA_FREE_SPACE_COALESCED dans le dictionnaire.

On peut faire un COALESCE manuel :
ALTER TABLESPACE toto COALESCE