Les tablespaces sous Oracle - article 3 : modifications - Tablespaces under Oracle - article 3: changes
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. Attention, quand on a fait un RESIZE du tablespace, cette opération apparait dans l'ordre DDL comme un ALTER DATABASE; c'est donc la dernière taille du code DDL (celle du resize) qu'il faut prendre.
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. Si en revanche nous avons un tablespace avec des tables, le SELECT ci-dessous nous donnera la taille occupée par les objets du tbs sur le disque dur; cette taille est en général inférieure à celle du datafile qui elle est un max pour le 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
Cas spécial du tablespace TEMPORAIRE
Il faut remplacer le mot clé datafile par tempfile.
SQL> ALTER DATABASE TEMPFILE '/reco/NFDD/db/NFDD999/oradata/sys/NFDD_temptbs1_01.dbf' RESIZE 10G;
============================================================================================
Retailler un tablespace : ajouter des fichiers
============================================================================================
Il est possible aussi d'ajouter un fichier à un tablespace pour augmenter la taille de celui-ci. En général on augmente la taille du fichier mais, dans le cas d'un tablespace smallfile et non pas bigfile, on peut atteindre assez vite le nombre de blocs max pour un fichier d'un tbs smallfile et donc cette opération peut provoquer une erreur ORA-01144. La seule solution est alor d'ajouter un fichier.
SQL> alter database datafile '/u01/oradata/OEM11G/mgmt.dbf' resize 37G;
alter database datafile '/u01/oradata/OEM11G/mgmt.dbf' resize 37G
*
ERREUR ▒ la ligne 1 :
ORA-01144: La taille de fichier (4849664 blocs) est sup▒rieure au maximum de 4194303 blocs
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
============================================================================================
Retailler un tablespace au niveau du file system Unix/Linux
============================================================================================
Ce sujet a fait l'objet d'un post dédié ici : http://dbaoraclesql.canalblog.com/archives/2020/10/14/38590081.html