Blog d'un DBA sur le SGBD Oracle et SQL

04 août 2017

Les tablespaces sous Oracle - article 3 : modifications


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



Posté par David DBA à 09:10 - - Commentaires [0] - Permalien [#]
Tags : ,


01 août 2017

Les tablespaces sous Oracle - article 2 : création et suppression


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.
          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


Posté par David DBA à 17:30 - - Commentaires [0] - Permalien [#]
Tags : ,