Introduction
Cet article est le deuxième d'une série consacrée aux tablespaces. Le premier concernait la recherche d'informations sur les tablespaces, celui-ci concerne la création et la suppression de ces mêmes tablespaces.
Partie 1 : afficher les informations sur les tablespaces
Partie 2 : création et suppression de tablespaces (cet article)
Partie 3 : modifications de tablespaces
Points d'attention
Les recommandations Oracle sont les suivantes pour créer un tablespace :
• EXTENT MANAGEMENT LOCAL AUTOALLOCATE : pour les tablespaces de type PERMANENT, UNDO
• SEGMENT SPACE MANAGEMENT AUTO pour être en mode ASSM (AUTOMATIC SEGMENT STORAGE MANAGEMENT), connu aussi comme "BITMAP FREELIST" : que pour les tablespaces de type PERMANENT
Base de tests
Une base Oracle 12c.
Exemples
============================================================================================
Créer les trois types de tablespaces : PERMANENT, TEMPORARY et UNDO
============================================================================================
Il existe trois types de tablespaces sous Oracle selon le type des données manipulé : PERMANENT, UNDO, TEMPORARY.
- PERMANENT : données utilisateurs et système --> tablespaces SYSTEM, SYSAUX, DATA (ou autre nom), INDEX (ou autre nom) etc etc
- UNDO : les segments UNDO --> tablespace UNDO (ou autre nom)
- TEMPORARY : données temporaires --> tablespace TEMP (ou autre nom)
SQL> select contents, TABLESPACE_NAME from dba_tablespaces order by TABLESPACE_NAME
CONTENTS TABLESPACE_NAME
--------------------- ------------------------------
PERMANENT APEX01
PERMANENT APEX02
PERMANENT IDX
PERMANENT SYSAUX
PERMANENT SYSTEM
PERMANENT TBSNEW01
TEMPORARY TEMP
PERMANENT TEST_4K
PERMANENT TEST_OMF
UNDO UNDOTBS1
PERMANENT USERS
Chaque type de tablespace a des mots clés à utiliser lors de leur création; certains sont obligatoires, d'autres non comme DATAFILE pour les tablespaces PERMANENT si on est en mode OMF, mais c'est plus propre de les mentionner pour bien comprendre ce qui est créé.
Création d'un tablespace de type PERMANENT : le mot clé est DATAFILE.
SQL> CREATE TABLESPACE IDX02 DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/idx02.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
Création d'un tablespace de type UNDO : les mots clés sont UNDO et DATAFILE.
Faut-il mettre le tablespace UNDO en AUTO EXTENT? Certains disent oui, mais cela risque de saturer votre file system Linux car le tablespace va grossir juqu'à remplir celui-ci et, manque de chance, Oracle ne va pas réutiliser l'espace des undo segments libérés pour les transactions commitées ou rollbackées; mon conseil, si vous mettez AUTO EXTENT, mettez également une taille limite à votre tablespace. D'autres disent que non car ainsi Oracle réutilise l'espace des segments undo qui ont été libérés à la fin des transactions précédentes.
Attention, il ne faut pas mettre l'option SEGMENT SPACE MANAGEMENT AUTO.
SQL> CREATE UNDO TABLESPACE UNDO02 DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/undo02.dbf' size 100m autoextend on next 50m SEGMENT SPACE MANAGEMENT AUTO EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
*
ERROR at line 1:
ORA-30024: Invalid specification for CREATE UNDO TABLESPACE
SQL> CREATE UNDO TABLESPACE UNDO02 DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/undo02.dbf' size 100m autoextend on next 50m EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace created.
Création d'un tablespace de type TEMPORARY : les mots clés sont TEMPORARY et TEMPFILE.
Attention, il ne faut pas mettre les options EXTENT MANAGEMENT LOCAL AUTOALLOCATE ni SEGMENT SPACE MANAGEMENT AUTO.
SQL> CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/u01/app/oracle/oradata/orcl12c/orcl/temp02.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
*
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE
SQL> CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/u01/app/oracle/oradata/orcl12c/orcl/temp02.dbf' size 10m SEGMENT SPACE MANAGEMENT AUTO;
*
ERROR at line 1:
ORA-30573: AUTO segment space management not valid for this type of tablespace
SQL> CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/u01/app/oracle/oradata/orcl12c/orcl/temp02.dbf' size 10m;
Tablespace created.
============================================================================================
Créer un tablespace avec plusieurs fichiers
============================================================================================
La syntaxe pour créer un tablespace avec plusieurs fichiers est la suivante : utiliser le mot clé DATAFILE sans S à la fin et lister, derrière lui, les différents fichiers du système d'exploitation, séparés par une virgule, en n'oubliant pas de donner leur taille.
SQL> CREATE TABLESPACE "TBS_TEST" DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf' size 10M, '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST02.dbf' SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
Vérification que deux fichiers ont bien été créés pour le tablespace TBS_TEST.
SQL> SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME FILE_ID
------------------------------ ------------------------------------------------------------------------------------------------
APEX01 /u01/app/oracle/oradata/orcl12c/orcl/APEX01.dbf 13
APEX02 /u01/app/oracle/oradata/orcl12c/orcl/APEX02.dbf 14
SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf 10
SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system02.dbf 9
TBS_TEST /u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf 39
TBS_TEST /u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST02.dbf 40
UNDOTBS2 /u01/app/oracle/oradata/orcl12c/orcl/undotbs2.dbf 34
USERS /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf 12
============================================================================================
Créer un tablespace de taille infinie
============================================================================================
Il est possible de créer un tablespace de taille infinie, avec bien sur la limite du disque dur ou du file system alloué à Oracle sous Unix. Pour cela, les mots clé sont MAXSIZE UNLIMITED. Attention, il faut aussi préciser AUTOEXTEND ON sinon Oracle déclenche une erreur.
SQL> CREATE TABLESPACE TBS_MAX datafile '/u01/app/oracle/oradata/orcl12c/orcl/TBS_MAX.dbf' size 80M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
Ce SELECT n'est pas très parlant pour vérifier le UNLIMITED : le MAX_SIZE vaut 2Go alors qu'il devrait en faire 32; 4 millions de bloc max dans un tablespace smallfile * 8000 octets = 32Go.
SQL> SELECT STATUS, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT, MAX_SIZE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'TBS_MAX';
STATUS CONTENTS EXTENT_MAN ALLOCATIO SEGMEN MAX_SIZE
--------- --------------------- ---------- --------- ------ -------------------------------------------------------------------------------
ONLINE PERMANENT LOCAL SYSTEM AUTO 2147483645
Ce select est plus fiable : on voit bien les 32Go.
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'TBS_MAX') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','TBS_MAX')
--------------------------------------------------------------------------------
CREATE TABLESPACE "TBS_MAX" DATAFILE
'/u01/app/oracle/oradata/orcl12c/orcl/TBS_MAX.dbf' SIZE 83886080
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
============================================================================================
Créer un tablespace avec le minimum de paramètres afin d'utiliser les paramètres par défaut
============================================================================================
Un CREATE TABLESPACE est un ordre SQL qui peut être plus ou moins complexe selon que l'on renseigne tous les paramètres ou si on utilise leurs valeurs par défaut.
Si on est en mode OMF (gestion automatique des noms de fichiers - voir plus bas), la création d'un tablespace peut-être extrèmement simple : il suffit juste de donner son nom, point et le nom du fichier sous-jacent sera créé automatiquement; même le mot clé DATAFILE n'est pas obligatoire.
SQL> CREATE TABLESPACE test_OMF;
Tablespace created.
Si on n'est pas en mode OMF, il faut au minimum donner le nom du fichier ainsi que sa taille sinon Oracle retourne une erreur.
SQL> CREATE TABLESPACE TBS_TEST DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf' size 10m;
Tablespace created.
Néanmoins comme les recommandations Oracle sont les suivantes pour créer un tablespace, je vous conseille de mettre ces options dans le CREATE TABLESPACE pour être sur quelles seront prises en compte (attention : cela dépend du type de tablespace à créer, PERMANENT, UNDO ou TEMPORARY).
• EXTENT MANAGEMENT LOCAL AUTOALLOCATE
• SEGMENT SPACE MANAGEMENT AUTO
SQL> CREATE TABLESPACE IDX datafile '/u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf' size 10m AUTOEXTEND ON NEXT 1310720 MAXSIZE 100M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Vérification de la création.
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('TABLESPACE', 'IDX') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','IDX')
--------------------------------------------------------------------------------
SQL> CREATE TABLESPACE "IDX" DATAFILE
'/u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf' SIZE 10485760
AUTOEXTEND ON NEXT 1310720 MAXSIZE 104857600
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
============================================================================================
Créer un tablespace en utilisant OMF (Oracle Managed Files) pour générer les fichiers
============================================================================================
OMF permet de laisser à Oracle la création du nom des fichiers constituant le tablespace. Pour utiliser OMF, il faut que les paramètres suivants soit renseignés :
- db_create_file_dest : obligatoire
- db_recovery_file_dest : optionnel mais c'est mieux d'avoir une FRA (Fast Recovery Area)
SQL> show parameter create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> show parameter recovery_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area/orcl12c
Si on n'est pas en mode OMF, la création d'un tablespace échoue si les noms des fichiers sont absents et le mot DATAFILE.
SQL> CREATE TABLESPACE test_OMF;
SQL> CREATE TABLESPACE test_OMF
*
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
Après avoir mis à jour le paramètre db_create_file_dest, la création du tablespace est OK.
SQL> alter system set db_create_file_dest = '/$ORACLE_BASE/oradata/orcl12c/orcl' scope = MEMORY;
System altered.
SQL> CREATE TABLESPACE test_OMF;
Tablespace created.
Pour ma part, je n'accroche pas comment Oracle détermine le nom des fichiers en mode OMF, je préfère maîtriser leur nom et leur emplacement en donnant le chemin complet des fichiers plus leur nom lors de la création du tablespace.
SQL> select file_name, file_id, bytes, status, maxbytes from dba_data_files where tablespace_name = 'TEST_OMF';
FILE_NAME FILE_ID BYTES STATUS MAXBYTES
--------------------------------------- --------- -----------------------------------------------------------
/u01/app/oracle/oradata/orcl12c/orcl/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_test_omf_dqjpcgck_.dbf 22 104857600 AVAILABLE 3.4360E+10
============================================================================================
Créer un tablespace de taille fixe
============================================================================================
Quand on crée un tablespace de taille fixe, l'objectif est d'éviter que le disque dur ne se remplisse si un traitement mettant à jour la base ne déraille. Certes le tablespace sera plus vite rempli et Oracle génèrera une erreur mais nous serons averti sans que le disque dur ne soit plein et donc que d'autres traitements ne soient bloqués. Il ne faut donc pas mettre l'option AUTOEXTEND sur ce tablespace.
SQL> CREATE TABLESPACE IDX datafile '/u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Sous Unix on voit bien que le fichier a été créé avec une taille de 10Mo. A ce stade il est vide car aucun objet ne lui est associé.
SQL> host ls -l /u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 25 11:18 /u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf
On crée maintenant une table spécialement dans ce tablespace et on va faire des INSERT pour le remplir : un message d'erreur est vite affiché car le tablespace ne peut pas s'étendre au delà des 10 Mo.
SQL> create table TESTT (NOM CHAR(30 CHAR)) tablespace IDX;
insert into TESTT SQL> select object_name from dba_objects where rownum < 1001;
insert into TESTT SQL> select * from TESTT;
insert into TESTT SQL> select * from TESTT;
insert into TESTT SQL> select * from TESTT;
insert into TESTT SQL> select * from TESTT;
insert into TESTT SQL> select * from TESTT;
*
ERROR at line 1:
ORA-01653: unable to extend table HR.TESTT by 128 in tablespace IDX
============================================================================================
Créer un tablespace avec une taille de bloc non par défaut (différent de DB_BLOCK_SIZE)
============================================================================================
Cela n'est pas possible pour les tablespaces UNDO et TEMP.
Pré-requis : il faut que la SGA soit paramétrée pour accueillir les données venant d'un bloc de données d'une taille différente de DB_BLOCK_SIZE, sinon le CREATE TABLESPACE échoue.
SQL> create tablespace TEST_4K blocksize 4k;
*
ERROR at line 1:
ORA-29339: tablespace block size 4096 does not match configured block sizes
On vérifie que la SGA n'a pas de zone 4k configurée : la valeur du paramètre db_4k_cache_size vaut bien 0. Il faut donner une taille à cette zone de la SGA mais cela se fait uniquement depuis le CDB$ROOT en Oracle 12c.
SQL> show parameter _k_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
SQL> alter system set db_4k_cache_size = 16m;
alter system set db_4k_cache_size = 16m
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> alter session set container = CDB$ROOT;
Session altered.
SQL> alter system set db_4k_cache_size = 16m;
System altered.
SQL> alter session set container=orcl;
Cette fois c'est OK.
SQL> create tablespace TEST_4K blocksize 4k;
Tablespace created.
SQL> select dbms_metadata.get_ddl('TABLESPACE', 'TEST_4K') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','TEST_4K')
--------------------------------------------------------------------------------
CREATE TABLESPACE "TEST_4K" DATAFILE
SIZE 104857600
AUTOEXTEND ON NEXT 104857600 MAXSIZE 16383M
LOGGING ONLINE PERMANENT BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
============================================================================================
Créer un objet dans un tablespace précis
============================================================================================
Si on veut créer un objet dans un tablespace précis, il faut préciser le nom du tablespace lors de sa création.
SQL> create table TBS_TEST (ID NUMBER(10)) tablespace TBSNEW01;
Table created.
SQL> select tablespace_name from dba_tables where table_name = 'TBS_TEST';
TABLESPACE_NAME
------------------------------
TBSNEW01
============================================================================================
Supprimer un tablespace avec des objets
============================================================================================
Supprimer un tablespace avec des objets : option INCLUDING CONTENTS
ATTENTION : avec l'option INCLUDING CONTENTS mais sans l'option AND DATAFILES, les fichiers du tablespace ne sont pas supprimés et il n'est plus possible de trouver leur nom dans le dictionnaire de données d'Oracle. Il faut alors aller dans $ORACLE_BASE/oradata pour retrouver les fichiers orphelins (absents de DBA_DATA_FILES) et les supprimer avec un rm sous Linux.
CASCADES CONSTRAINTS : dans le cas où les tables du tablespace à supprimer ont des FK, il faut utiliser l'option CASCADES CONSTRAINTS en plus de INCLUDING CONTENTS pour qu'Oracle gère les foreign keys.
SQL> drop tablespace idx;
drop tablespace idx
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL> drop tablespace idx INCLUDING CONTENTS;
Tablespace dropped.
Vérification de la suppression.
SQL> select tablespace_name from dba_data_files order by tablespace_name;
TABLESPACE_NAME
------------------------------
APEX01
APEX02
SYSAUX
SYSTEM
UNDOTBS1
USERS
6 rows selected.
Supprimer un tablespace avec ses fichiers option INCLUDING CONTENTS AND DATAFILES
Pour supprimer un tablespace et ses fichiers du système d'exploitation, il faut utiliser l'option INCLUDING CONTENTS AND DATAFILES.
SQL> CREATE TABLESPACE IDX datafile '/u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Vérification de la création du fichier.
Unix> ls -l /u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf
Unix> -rw-r----- 1 oracle oinstall 10493952 Jul 24 10:31 /u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf
Suppression du tablespace et de ses fichiers.
SQL> drop tablespace idx INCLUDING CONTENTS AND DATAFILES;
Unix> ls -l /u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf
Unix> ls: cannot access /u01/app/oracle/oradata/orcl12c/orcl/idx01.dbf: No such file or directory
Supprimer un tablespace par défaut
A noter qu'il n'est pas possible de supprimer un tablespace défini comme DEFAULT.
SQL> drop tablespace TEMP10 including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
