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.
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 da 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 s
elect 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