Tablespace
| Fiche express | |
|---|---|
| Type | Gestion des tablespaces Oracle (création, dimensionnement, ASM) |
| Voir aussi | Tables · Schema · Asm |
Un tablespace est l'unité logique de stockage d'une base Oracle : il regroupe un ou plusieurs fichiers physiques (datafiles) et accueille les segments (tables, index, données temporaires, annulation).
- Le tablespace peut gérer ses métadonnées dans le dictionnaire de données (DICTIONARY)
ou localement (LOCAL, mode par défaut depuis Oracle 9i, largement préférable en performance).
- La taille des extents peut être gérée uniformément (UNIFORM) ou automatiquement
(AUTOALLOCATE).
Architecture type
- tablespace pour les segments d'annulation (undo)
- tablespace temporaire (tris, jointures, index temporaires)
- tablespace(s) pour les tables
- tablespace(s) pour les index
Syntaxe de création
CREATE [ BIGFILE | SMALLFILE ] TABLESPACE nom
DATAFILE specification_fichier [,...]
[ clause_gestion_extension ]
[ clause_gestion_segment ]
[ DEFAULT [ clause_compression ] [ clause_stockage ] ]
[ BLOCKSIZE valeur [K] ]
[ LOGGING | NOLOGGING ]
[ FORCE LOGGING ]
[ FLASHBACK { ON | OFF } ]
[ ONLINE | OFFLINE ];
-- specification_fichier
'nom_fichier' [ SIZE valeur {K|M|G|T} ] [ REUSE ] [ clause_auto_extension ]
-- clause_auto_extension AUTOEXTEND OFF | AUTOEXTEND ON [ NEXT valeur [K|M|G|T] ] [ MAXSIZE UNLIMITED | valeur [K|M|G|T] ]
-- clause_gestion_extension
EXTENT MANAGEMENT DICTIONARY
| EXTENT MANAGEMENT LOCAL { AUTOALLOCATE | UNIFORM [ SIZE valeur [K|M|G|T] ] }
-- clause_gestion_segment
SEGMENT SPACE MANAGEMENT { MANUAL | AUTO }
-- clause_stockage
STORAGE ( [ INITIAL valeur [K|M] ] [ NEXT valeur [K|M] ]
[ MINEXTENTS valeur ] [ MAXEXTENTS { valeur | UNLIMITED } ]
[ PCTINCREASE valeur ] )
-- clause_compression
COMPRESS [ FOR { ALL | DIRECT_LOAD } OPERATIONS ] | NOCOMPRESS
Définir le tablespace par défaut de la base :
ALTER DATABASE SET DEFAULT [ BIGFILE | SMALLFILE ] TABLESPACE; ALTER DATABASE DEFAULT TABLESPACE nom;
Sous ASM
Sous ASM, on n'agrandit pas directement le "tablespace" mais le fichier qui lui est associé. Par convention, un fichier ASM ne dépasse généralement pas 16 Go dans les configurations classiques (au-delà, préférer plusieurs fichiers).
Retrouver le fichier associé à un tablespace :
SELECT tablespace_name, file_name FROM dba_data_files WHERE tablespace_name = 'TS_DATA';
Agrandir un fichier existant :
ALTER DATABASE DATAFILE '+DATA/BDD/datafile/ts_name.274.xxxxxxxxx' RESIZE 512M; ALTER DATABASE DATAFILE '+DATA/BDD/datafile/users.259.xxxxxxxxx' AUTOEXTEND OFF; ALTER DATABASE DATAFILE '/chemin/fichier.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;
Créer un nouveau fichier une fois la taille maximale atteinte :
ALTER TABLESPACE TS_DATA ADD DATAFILE SIZE 16384M AUTOEXTEND OFF;
Opérations courantes
Création :
CREATE TABLESPACE TS_APP DATAFILE SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 128M; CREATE TABLESPACE USERS DATAFILE SIZE 4M AUTOEXTEND OFF;
SELECT tablespace_name, autoextensible, file_name FROM dba_data_files WHERE tablespace_name = 'USERS';
Supprimer un tablespace et ses fichiers :
DROP TABLESPACE nom INCLUDING CONTENTS AND DATAFILES;
Renommer :
ALTER TABLESPACE ts_ancien_nom RENAME TO ts_nouveau_nom;
Ajouter un fichier :
ALTER TABLESPACE nom ADD DATAFILE '+DATA/BDD/datafile/ts_name' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M;
Mettre hors/en ligne :
ALTER TABLESPACE nom [ OFFLINE | ONLINE ];
Déplacer un datafile (tablespace hors ligne) :
ALTER DATABASE RENAME FILE '/oradata/db/data01.dbf' TO '/nouveau/chemin/data01.dbf';
Supprimer un fichier d'un tablespace multi-fichiers :
ALTER TABLESPACE nom DROP DATAFILE 'nom_complet' | numero_fichier;
Extraire le DDL de tous les tablespaces (migration/documentation)
SET heading off
SET echo off
SET pages 999
SET long 90000
SPOOL ddl_tablespaces.sql
SELECT dbms_metadata.get_ddl('TABLESPACE', tb.tablespace_name) FROM dba_tablespaces tb;
SPOOL OFF
Tablespace temporaire
Un tablespace temporaire peut techniquement héberger n'importe quel type de traitement, mais il est recommandé d'en dédier un pour des raisons de performance. Il sert à stocker les structures temporaires nécessaires à l'exécution des traitements :
- SELECT ... ORDER BY / GROUP BY / DISTINCT
- CREATE INDEX
- opérations ensemblistes (UNION, INTERSECT, MINUS)
- calcul de statistiques
- jointures par tri-fusion (sort merge join)
Les tablespaces temporaires stockent aussi les tables temporaires créées par CREATE GLOBAL TEMPORARY TABLE.
Groupes de tablespaces temporaires
Disponibles depuis la 10g. Un groupe n'a d'intérêt que si ses membres sont situés sur des disques physiques différents (répartition de charge I/O).
CREATE TEMPORARY TABLESPACE TEMP_G1 TEMPFILE SIZE 512M AUTOEXTEND OFF TABLESPACE GROUP TEMP_G; CREATE TEMPORARY TABLESPACE TEMP_G2 TEMPFILE SIZE 512M AUTOEXTEND OFF TABLESPACE GROUP TEMP_G; CREATE TEMPORARY TABLESPACE TEMP_G3 TEMPFILE SIZE 512M AUTOEXTEND OFF TABLESPACE GROUP TEMP_G; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_G;
TEMPFILE indique un fichier géré localement (tablespace temporaire) ; DATAFILE un fichier de données classique géré via le dictionnaire ou en local.
Notes :
- un tablespace temporaire n'est utilisé que s'il est affecté aux utilisateurs — sans clause
TEMPORARY TABLESPACE à la création d'un compte, c'est le tablespace temporaire par défaut de la base qui s'applique.
- ALTER DATABASE DEFAULT TEMPORARY TABLESPACE change le tablespace temporaire par
défaut de tous les nouveaux comptes.
- pas de RECOVER possible sur un tablespace temporaire : en cas de problème, on le recrée
(DROP puis CREATE).
Afficher le tablespace temporaire par défaut :
SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
Ajouter un fichier :
ALTER TABLESPACE nom_tablespace ADD TEMPFILE spec_fichier;
Redimensionner :
ALTER DATABASE TEMPFILE 'nom_complet' RESIZE valeur [K|M|G|T]; ALTER TABLESPACE nom_tablespace_bigfile RESIZE valeur [K|M|G|T];
Modifier l'auto-extension :
ALTER DATABASE TEMPFILE 'nom_complet' AUTOEXTEND ON NEXT valeur MAXSIZE valeur;
Supprimer un fichier :
ALTER DATABASE TEMPFILE '/oradata/db/temp01.dbf' DROP INCLUDING DATAFILES;
Réduire (shrink) :
ALTER TABLESPACE nom SHRINK SPACE [ KEEP taille [K|M|G] ]; ALTER TABLESPACE nom SHRINK TEMPFILE 'nom_complet' [ KEEP taille [K|M|G] ];
Recommandations Oracle
- privilégier les tablespaces gérés localement (LOCAL) pour tous les usages : SYSTEM,
tablespace temporaire (à créer en même temps que la base), segments d'annulation, tables et index.
Trouver des informations
DBA_TABLESPACES / V$TABLESPACE DBA_DATA_FILES / V$DATAFILE DBA_TEMP_FILES / V$TEMPFILE DBA_TABLESPACE_GROUPS DATABASE_PROPERTIES
SELECT tablespace_name, contents, extent_management, allocation_type, bigfile, block_size, status FROM dba_tablespaces;
SELECT tablespace_name, file_name, status, autoextensible, blocks AS user_blocks, maxblocks FROM ( SELECT * FROM dba_data_files UNION ALL SELECT * FROM dba_temp_files );
SELECT file#, name, status, enabled, checkpoint_change# FROM v$datafile;
SELECT property_name, property_value FROM database_properties
WHERE property_name IN ('DEFAULT_TEMP_TABLESPACE', 'DEFAULT_PERMANENT_TABLESPACE', 'DEFAULT_TBS_TYPE');
Supervision
DBA_FREE_SPACE DBA_SEGMENTS DBA_EXTENTS V$SORT_SEGMENT V$SYSAUX_OCCUPANTS
SELECT segment_name, segment_type, initial_extent/1024 AS initial_ko, blocks, extents FROM dba_segments WHERE tablespace_name = 'TEST';
SELECT block_id, extent_id, segment_name, blocks, bytes/1024 AS taille_ko FROM dba_extents WHERE tablespace_name = 'TEST' UNION SELECT block_id, NULL, '*** LIBRE ***', blocks, bytes/1024 AS taille_ko FROM dba_free_space WHERE tablespace_name = 'TEST';
Segments (exemples de gestion d'extent)
CREATE TABLESPACE nom DATAFILE '+DATA/BDD/datafile/ts_name' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; CREATE TABLESPACE nom DATAFILE '+DATA/BDD/datafile/ts_name' SIZE 100M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Bug corrigé : la commande "asmca &" présente en préambule de la page source (lancement de l'assistant graphique ASM) a été retirée — hors sujet direct de la gestion CLI des tablespaces, et cette page ne concernait de toute façon pas l'installation d'ASM lui-même.