Introduction
Cet article est le troisième et dernier d'une série consacrée aux tablespaces. Le premier concernait la recherche d'informations sur les tablespaces, le deuxième la création et la suppression de ces mêmes tablespaces et celui-ci leur modification.

Partie 1 : afficher les informations sur les tablespaces
Partie 2 : création et suppression de tablespaces

Partie 3 : modifications de tablespaces (cet article)



Points d'attention
N/A.



Base de tests
Une base Oracle 12c.



Exemples

============================================================================================
Mettre un tablespace OFFLINE puis ONLINE

============================================================================================
Un tablespace peut être mis OFFLINE. Un des intérêts de cette opération est de rendre inaccessibles les données de ce tablespace, de stopper au niveau de la base des applications etc etc.

L'ordre SQL est le suivant
          SQL> ALTER TABLESPACE TBSNEW01 OFFLINE;

Vérification que le tablespace est offline.
          SQL> select status from dba_tablespaces where tablespace_name = 'TBSNEW01';
          STATUS
          ---------
          OFFLINE

Un SELECT sur une table stockée dans un tablespace offline est maintenant impossible.
          SQL> select * from TBS_TEST;
          SQL> select * from TBS_TEST
          *
          ERROR at line 1:
          ORA-00376: file 18 cannot be read at this time
          ORA-01110: data file 18: '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf'

Idem pour un INSERT.
          SQL> insert into TBS_TEST values (4);
          SQL> insert into TBS_TEST values (4)
          *
          ERROR at line 1:
          ORA-00376: file 18 cannot be read at this time
          ORA-01110: data file 18: '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf'

Le tablespace peut bien sur être remis online.
          SQL> alter tablespace TBSNEW01 ONLINE;

          SQL> select status from dba_tablespaces where tablespace_name = 'TBSNEW01';
          STATUS
          --------
          ONLINE

          SQL> insert into TBS_TEST values (4);
          1 row created.

Attention, il est possible de supprimer un objet d'un tablespace OFFLINE car le DROP s'applique au dictionnaire de données qui se trouve dans le tablespace SYSTEM et pas dans le tablespace mis OFFLINE.
La table TBS_TEST est dans le tablespace TBSNEW01 et elle contient des données; ceci pour dire qu'un segment existe bien dans le tablespace car depuis Oracle 11, on est en mode DEFERRED SEGMENT et lors du CREATE TABLE, aucun segment n'est créé, il faut pour cela attendre le premier INSERT.
          SQL> select tablespace_name from dba_tables where table_name = 'TBS_TEST';
          TABLESPACE_NAME
          ------------------------------
          TBSNEW01

          SQL> alter tablespace TBSNEW01 OFFLINE;
          Tablespace altered.

          SQL> drop table TBS_TEST;
          Table dropped.

          SQL> select tablespace_name from dba_tables where table_name = 'TBS_TEST';
          no rows selected

          SQL> alter tablespace TBSNEW01 online;
          Tablespace altered.

En revanche le TRUNCATE ne marche pas. Visiblement cet ordre DDL touche le tablespace de la table modifiée en plus du dictionnaire de données.
          SQL> create table TEST01 (ID CHAR(1000)) TABLESPACE TBS_TEST;
          SQL> insert into TEST01 values ('A');
          SQL> commit;

          SQL> alter tablespace TBS_TEST offline;
          SQL> truncate table TEST01;
          truncate table TEST01
          *
          ERROR at line 1:
          ORA-00376: file 39 cannot be read at this time
          ORA-01110: data file 39: '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf'


============================================================================================
Mettre un tablespace en READ ONLY puis en READ WRITE

============================================================================================

Mettre un tablespace en mode READ ONLY permet de protéger les données tout en les laissant accessibles, ce qui n'est pas possible avec le mode OFFLINE.
          SQL> alter tablespace idx READ ONLY;

Vérification du mode read_only.
          SQL> select status from dba_tablespaces where TABLESPACE_NAME = 'IDX';
          STATUS
          ---------
          READ ONLY

Curieusement on peut créer un objet dans un tablespace READ ONLY. Il faut se souvenir que depuis la 11g on est en mode DEFFERED SEGMENT donc, lors du CREATE TABLE, aucun segment n'est créé dans le tablespace mais juste une entrée dans le dictionnaire de données, qui lui se trouve dans le tablespace SYSTEM.
On crée une table dans le tablespace IDX : création OK mais INSERT KO (car tablespace en READ ONLY).
          SQL> create table IDX_TEST02 (ID NUMBER) tablespace IDX;
          Table created.

          SQL> insert into IDX_TEST02 values (1);
          insert into IDX_TEST02 values (1)
          *
          ERROR at line 1:
          ORA-01647: tablespace 'IDX' is read-only, cannot allocate space in it

On peut bien sur remettre un tablespace en READ WRITE : vous noterez que son statut devient alors ONLINE; il n'y a pas de statut READ WRITE.
          SQL> alter tablespace idx read write;
          SQL> select status from dba_tablespaces where TABLESPACE_NAME = 'IDX';
          STATUS
          ---------
          ONLINE

          SQL> insert into IDX_TEST02 values (1);
          1 row created.

Attention, il est possible de supprimer un objet d'un tablespace READ ONLY, comme pour les tablespaces OFFLINE, car le DROP s'applique uniquement au dictionnaire de données du tablespace SYSTEM. A la différence du CREATE, ce DROP impacte aussi les données contenue dans la table : elles sont bel et bien supprimées alors que le tablespace est en READ ONLY.
          SQL> alter tablespace TBSNEW01 READ ONLY;
          Tablespace altered.

On vérifie que la table contient bien des données; à la différence d'un TBS OFFLINE, un SELECT est OK sur un TBS READ ONLY.
          SQL> select * from TBS_TEST;
          ID
          ----------
          2

On droppe la table : aucune erreur Oracle alors que le tablespace est READ ONLY.
          SQL> drop table TBS_TEST;
          Table dropped.


============================================================================================
Renommer un tablespace ONLINE

============================================================================================

Renommer un tablespace ONLINE est une opération très simple, il suffit de faire un RENAME.
          SQL> ALTER TABLESPACE TBS01 RENAME TO TBSNEW01;

Vérifions que le nouveau nom est bien pris en compte et que le nom du fichier du tablespace reste inchangé.
         SQL> select tablespace_name from dba_tablespaces order by 1
         TABLESPACE_NAME
         ------------------------------
         APEX01
         APEX02
         SYSAUX
         SYSTEM
         TBSNEW01
         TEMP
         UNDOTBS1
         USERS

         SQL> select file_name from dba_data_files where tablespace_name = 'TBSNEW01';
         FILE_NAME
         --------------------------------------------------------------------------------
         /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf

Attention, il est impossible de renommer les tablespaces SYSTEM et SYSAUX, même en étant SYS; mais on peut renommer leurs fichiers (voir ci-après).
         SQL> show con_name
         CON_NAME
         ------------------------------
         CDB$ROOT

         SQL> show user
         USER is "SYS"

         SQL> alter tablespace SYSTEM rename to SYSTEM01;
         alter tablespace SYSTEM rename to SYSTEM01
         *
         ERROR at line 1:
         ORA-00712: cannot rename system tablespace

         SQL> alter tablespace SYSAUX rename to SYSAUX01;
         alter tablespace SYSAUX rename to SYSAUX01
         *
         ERROR at line 1:
         ORA-13502: Cannot rename SYSAUX tablespace


============================================================================================
Renommer les fichiers d'un tablespace

============================================================================================

Il existe deux méthodes pour renommer les fichiers constituant un tablespace : une avec intervention aux niveaux Oracle/système d'exploitation et l'autre avec intervention uniquement au niveau d'Oracle.

Méthode 1 : renommage des fichiers au niveau Oracle avec un tablespace qui reste online 
METHODE A PRIVILEGIER car zéro indisponiblité de la base puisque le tablespace reste online.
          SQL> select status from dba_tablespaces where tablespace_name = 'TBSNEW01'
          STATUS
          ---------
          ONLINE

Renommage au niveau Oracle du fichier du tablespace.
          SQL> alter database move datafile '/u01/app/oracle/oradata/orcl12c/orcl/TBSNEW01.dbf' to '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf';

Vérification du renommage des fichiers au niveau OS et Oracle; l'ancien nom de fichier n'existe plus sous l'OS.
          SQL> HOST ls -l /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf
          -rw-r----- 1 oracle oinstall 10493952 Jul 25 04:52 /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf

          SQL> HOST ls -l /u01/app/oracle/oradata/orcl12c/orcl/TBSNEW01.dbf
          ls: cannot access /u01/app/oracle/oradata/orcl12c/orcl/TBSNEW01.dbf: No such file or directory

          SQL> select file_name from dba_data_files where tablespace_name = 'TBSNEW01';
          FILE_NAME
          --------------------------------------------------------------------------------
          /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf

Attention : il est possible de renommer le fichier du tablespace SYSTEM mais, par prudence, je ne vous conseille pas de toucher à ce tablespace.
          SQL> alter database move datafile '/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf' to '/u01/app/oracle/oradata/orcl12c/orcl/system02.dbf';
          Database altered.

          SQL> select tablespace_name, file_name, file_id, bytes, status, maxbytes from dba_data_files where tablespace_name = 'SYSTEM';
          TABLESPACE_NAME FILE_NAME FILE_ID BYTES STATUS MAXBYTES
          -------------------------------------------------------------------------------------------------------- ---------- --------- ----------
          SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system02.dbf 9 356515840 AVAILABLE 3.4360E+10

Méthode 2 : renommage des fichiers au niveau OS et Oracle avec un tablespace qui est mis offline temporairement
METHODE A EVITER car indisponibilité du fait que le tablespace est offline.
On met le tablespace offline.
         SQL> alter tablespace tbsnew01 OFFLINE;

On renomme le fichier au niveau de l'OS puis au niveau de Oracle (pour mettre à jour le fichier de contrôle).
         SQL> host mv /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf /u01/app/oracle/oradata/orcl12c/orcl/TBSNEW01.dbf

         SQL> alter database rename file '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf' to '/u01/app/oracle/oradata/orcl12c/orcl/TBSNEW01.dbf';

Vérification du renommage des fichiers au niveau OS et Oracle.
         SQL> HOST ls -l /u01/app/oracle/oradata/orcl12c/orcl/TBSNEW01.dbf
         -rw-r----- 1 oracle oinstall 10493952 Jul 25 04:20 /u01/app/oracle/oradata/orcl12c/orcl/TBSNEW01.dbf

         SQL> select file_name from dba_data_files where tablespace_name = 'TBSNEW01';
         FILE_NAME
         --------------------------------------------------------------------------------
         /u01/app/oracle/oradata/orcl12c/orcl/TBSNEW01.dbf

Ne SURTOUT pas oublier de remettre le tablespace ONLINE.
         SQL> alter tablespace TBSNEW01 online;


============================================================================================
Supprimer un fichier d'un tablespace

============================================================================================

La suppression d'un fichier d'un tablespace se fait sous Oracle mais elle se répercute au niveau du système d'exploitation, il n'y a donc aucune action à faire sous Unix ou Windows. En outre le dictionnaire de données et le fichier de contrôle sont mis à jour directement.

Suppression d'un fichier sur un tablespace comportant 2 fichiers.
          SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TBS_TEST'
          FILE_NAME
          ----------------------------------------------------------------------------------------------------
          /u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf
          /u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST02.dbf

          SQL> ALTER TABLESPACE TBS_TEST DROP DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST02.dbf';
          Tablespace altered.

         SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TBS_TEST';
         FILE_NAME
         ----------------------------------------------------------------------------------------------------
         /u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf

Il n'est pas possible de supprimer le premier fichier d'un tablespace.
On crée un tablespace avec deux fichiers.
         SQL> CREATE TABLESPACE "TBSNEW01" DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf' SIZE 10MAUTOEXTEND ON NEXT 5M MAXSIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
         Tablespace created.

         SQL> alter tablespace TBSNEW01 add datafile'/u01/app/oracle/oradata/orcl12c/orcl/TBS02.dbf' size 100M autoextend on next 100 maxsize 1G;
         Tablespace altered.

         SQL> ALTER TABLESPACE TBSNEW01 DROP DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf';
         ALTER TABLESPACE TBSNEW01 DROP DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf'
         *
         ERROR at line 1:
         ORA-03263: cannot drop the first file of tablespace TBSNEW01

Il n'est pas possible de supprimer un fichier d'un tablespace si celui-ci contient des données.
         SQL> CREATE TABLESPACE "TBSNEW01" DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf' SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 20M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
         Tablespace created.

         SQL> alter tablespace TBSNEW01 add datafile'/u01/app/oracle/oradata/orcl12c/orcl/TBS02.dbf' size 10M autoextend on next 5M maxsize 20M;
         Tablespace altered.

On crée une table dans le tablespace et on remplit celui-ci.
         SQL> create table TEST01 (ID number) tablespace TBSNEW01 ;
         Table created.

         SQL> insert into TEST01 (select 100000 from dual connect by level <= 500000);
         500000 rows created.
         ...
         ...

         SQL> insert into TEST01 (select 100000 from dual connect by level <= 500000)
         *
         ERROR at line 1:
         ORA-01653: unable to extend table SYS.TEST01 by 128 in tablespace TBSNEW01

         SQL> select TABLESPACE_NAME, TABLESPACE_SIZE, USED_SPACE, round(USED_PERCENT, 2) from DBA_TABLESPACE_USAGE_METRICS order by TABLESPACE_NAME;
         TABLESPACE_NAME TABLESPACE_SIZE USED_SPACE ROUND(USED_PERCENT,2)
         ------------------------------ --------------- ---------- -------------------------------------------------------------------------
         APEX01 3208 808 25.19
         APEX02 3208 128 3.99
         SYSAUX 2195191 141944 6.47
         SYSTEM 2090231 43192 2.07
         TBSNEW01 5120 5120 100
         TEMP 2057468 128 .01
         USERS 2056471 9088 .44

         SQL> alter tablespace TBSNEW01 drop DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/TBS02.dbf'
         *
         ERROR at line 1:
         ORA-03262: the file is non-empty


============================================================================================
Retailler un tablespace : modifier la taille d'un fichier

============================================================================================

Attention : cette action doit se faire sur les datafiles et pas sur le tablespace.
En général on augmente la taille du TBS mais on peut aussi la diminuer, uniquement si aucun objet ne se trouve dans les extents que l'on veut supprimer.

Retailler à la hausse
Voir la taille du ou des fichiers existants du tablespace.
          SQL> select dbms_metadata.get_ddl('TABLESPACE', 'TBS_TEST') from dual;
          DBMS_METADATA.GET_DDL('TABLESPACE','TBS_TEST')
          --------------------------------------------------------------------------------
          CREATE TABLESPACE "TBS_TEST" DATAFILE
          '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf' SIZE 10485760
          LOGGING ONLINE PERMANENT BLOCKSIZE 8192
          EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
          NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO

Tiens, le resize se voit comme un ordre DDL de la définition du tablespace. Attention : on est bien passé de 10Mo à 100Mo, il y a bien un zéro en plus dans le nombre suivant le RESIZE.
          SQL> alter database datafile '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf' resize 100M;

          SQL> select dbms_metadata.get_ddl('TABLESPACE', 'TBS_TEST') from dual;
          DBMS_METADATA.GET_DDL('TABLESPACE','TBS_TEST')
          --------------------------------------------------------------------------------
          CREATE TABLESPACE "TBS_TEST" DATAFILE
          '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf' SIZE 10485760
          LOGGING ONLINE PERMANENT BLOCKSIZE 8192
          EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
          NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
          ALTER DATABASE DATAFILE
          '/u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf' RESIZE 104857600

Attention, la taille du tbs n'est pas dans la colonne MAX_SIZE, qui représente la taille MAX du tbs mais pas la taille actuelle.
          SQL> select MAX_SIZE from dba_tablespaces where TABLESPACE_NAME='TBS_TEST';
          MAX_SIZE
          ----------
          2147483645

Il n'est pas non plus dans DBA_SEGMENTS qui affiche la taille actuelle du segment. Ce que nous avons fait c'est créer un fichier de 100 mégas octets pour le système d'exploitation, pas pour Oracle. La valeur est nulle car nous n'avons aucune table dans ce tablespace.
          SQL> select sum(bytes) from dba_segments where TABLESPACE_NAME = 'TBS_TEST';
          SUM(BYTES)
          ----------
         

          SQL> host ls -l /u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf
          -rw-r----- 1 oracle oinstall 104865792 Aug  6 05:59 /u01/app/oracle/oradata/orcl12c/orcl/TBS_TEST01.dbf

Retailler à la baisse
C'est OK si le tablespace est presque vide.
          SQL> alter database datafile '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf' resize 5M;
          Database altered.

          SQL> host ls -l /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf
          -rw-r----- 1 oracle oinstall 5251072 Jul 25 07:18 /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf

Je crée une table dans ce tablespace avec le contenu de DBA_OBJECTS.
          SQL> create table TEST_OBJECTS TABLESPACE TBSNEW01 AS SELECT * FROM DBA_OBJECTS ;
          Table created.

Le tablespace fait 13 mégas car il est en auto extend : la taille dépasse les mégas d'origine mais c'est normal, on n'a pas mis une taille fixe.
          SQL> select sum(bytes) from dba_segments where TABLESPACE_NAME = 'TBSNEW01';
          SUM(BYTES)
          -------------------
          13631488

Le fichier sous Unix a bien grossi.
          SQL> host ls -l /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf
          -rw-r----- 1 oracle oinstall 15736832 Jul 25 07:28 /u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf

Resize à la baisse impossible si des données sont au delà de la nouvelle limite.
          SQL> alter database datafile '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf' resize 5M;
          alter database datafile '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf' resize 5M
          *
          ERROR at line 1:
          ORA-03297: file contains used data beyond requested RESIZE value


============================================================================================
Retailler un tablespace : ajouter des fichiers

============================================================================================

On peut ajouter des paramètres sur ce fichier, avec des valeurs différentes de celui du premier fichier, par exemple concernant la taille.
          SQL> alter tablespace TBSNEW01 add datafile'/u01/app/oracle/oradata/orcl12c/orcl/TBS02.dbf' size 100M autoextend on next 100 maxsize 2G;
          Tablespace altered.

          SQL> select dbms_metadata.get_ddl('TABLESPACE', 'TBSNEW01') from dual;
          DBMS_METADATA.GET_DDL('TABLESPACE','TBSNEW01')
          --------------------------------------------------------------------------------
          CREATE TABLESPACE "TBSNEW01" DATAFILE
          '/u01/app/oracle/oradata/orcl12c/orcl/TBS02.dbf' SIZE 104857600
          AUTOEXTEND ON NEXT 8192 MAXSIZE 2048M,
          '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf' SIZE 10485760
          AUTOEXTEND ON NEXT 1310720 MAXSIZE 2097152000
          LOGGING ONLINE PERMANENT BLOCKSIZE 8192
          EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
          NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
          ALTER DATABASE DATAFILE
          '/u01/app/oracle/oradata/orcl12c/orcl/TBS01.dbf' RESIZE 15728640