FACULTE DES SCIENCES ET TECHNIQUES-SETTAT

Administration Oracle
11g
Partie II
Noreddine GHERABI

FILIERE

DES INGENIEURS-GI

Sommaire

Noreddine GHERABI

I-

Cration de la base de donnes

1- Privilges ncessaires pour crer, dmarrer ou arrter une base

SYSDBA (arrt, dmarrage, modification, cration d une base, )

SYSOPER(arrt dmarrage d une base, modification)

2- Variables denvironnement positionner

ORACLE_SID=nom_de_instance
ORACLE_HOME=home_oracle
PATH=homme_oracle\bin

3- Outils de lancement des commandes

Il faut prparer les outils de cration de la base tel que :

Sqlplus

Le Database Configuration Assistant : Outil graphique permettant de

crer et modifier les bases

4- Prparer le fichier dinitialisation init.ora

Ce fichier permet de fixer un certain nombre de paramtres tel que ( ce
fichier peut tre appel fichier PFILE:

# Cache et E/S
db_block_size=4096
db_cache_size=33554432
# Configuration du fichier de contrle
control_files=("C:\oracle\ora \CONTROL01.CTL",
"C:\ oracle\ora \CONTROL02.CTL",
# Diagnostics et statistiques
diagnostic_dest=C:\oracle\ora\diag\bdump
# Le nom de la base de donnes
db_name=DBTEST

# Diver
- compatible=9.0.0

# Pools
- java_pool_size=33554432
- large_pool_size=1048576
- shared_pool_size=33554432

Exemple :
ora.__db_cache_size=163577856
ora.__java_pool_size=12582912
ora.__large_pool_size=4194304
3

Noreddine GHERABI

ora.__pga_aggregate_target=331350016
ora.__sga_target=490733568
ora.__shared_pool_size=285212672
ora.__streams_pool_size=12582912
*.compatible='11.2.0.0.0'
*.control_files='G:\oracle\ora\control01.ctl','G:\oracle\ora\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora'
*.diagnostic_dest='G:\oracle\ora'
*.sga_max_size=821035008
*.memory_target=821035008
*.shared_pool_reserved_size=12582912

5- Cration de linstance
Pour crer une nouvelle instance nous allons utiliser loutil de oracle appel
ORADIM :

C:\>oradim -NEW -SID ora INTPWD manager -STARTMODE auto

-

NEW : pour crer nouvelle instance

SID : ler nom de linstance

INITPWD ou SYSPWD : initialiser le mot de passe de lutilisateur

SYS

STARTMODE : le mode de dmarrage de linstance ( auto ou

manual)

6- Connection linstance :
-

SQL> sqlplus /nolog

SQL> conn / as sysdba

7- Creation des paramtres serveur (SPFILE)

Charger le fichier SPFILE partir du fichier dinitialisation (ou le fichier PFILE)
-

SQL> create SPFILE from PFILE='c:\init.TXT;

8- Dmarrage de linstance
-

SQL> startup nomount

Noreddine GHERABI

9- Cration de la base
Maintenant nous pouvons crer la base de donnes en spcifiant les
paramtres ncessaires de cration :
CREATE DATABASE [nom_base]
[ USER SYS IDENTIFIED BY mot_de_passe ]
[ USER SYSTEM IDENTIFIED BY mot_de_passe ]
[ CONTROLFILE REUSE ]
[ DATAFILE spcification_fichier [,...] ]
[ SYSAUX DATAFILE spcification_fichier [,...] ]
[[ BIGFILE | SMALLFILE ] UNDO TABLESPACE nom
[ DATAFILE spcification_fichier [,...] ] ]
[[ BIGFILE | SMALLFILE ] DEFAULT TEMPORARY TABLESPACE nom
[ TEMPFILE spcification_fichier [,...] ]
[ DEFAULT TABLESPACE nom
[ DATAFILE spcification_fichier [,...] ]
[ LOGFILE [GROUP numro] spcification_fichier_redo [,...] ]
[ ARCHIVELOG | NOARCHIVELOG ]
[ MAXINSTANCES nombre ]
[ MAXLOGFILES nombre ]
[ MAXLOGMEMBERS nombre ]
[ MAXDATAFILES nombre ] ;
Exemple :
CREATE DATABASE ora
USER SYS IDENTIFIED BY manager
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('G:\oracle\ora\redo01.log') SIZE 100M,
GROUP 2 ('G:\oracle\ora\redo02.log') SIZE 100M,
GROUP 3 ('G:\oracle\ora\redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE 'G:\oracle\ora\system01.dbf' SIZE 325M
SYSAUX DATAFILE 'G:\oracle\ora\sysaux01.dbf' SIZE 325M
UNDO TABLESPACE undotbs
DATAFILE 'G:\ORACLE\ORA\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

10-

Construction du dictionnaire de donnes

5

Noreddine GHERABI

Aprs il faut excuter les 3 codes SQL en dessous pour crer le dictionnaire
de donnes :
SQL> @%ORACLE_HOME%/rdbms/admin/catalog.sql
SQL> @%ORACLE_HOME%/rdbms/admin/catproc.sql
SQL> @%ORACLE_HOME%/sqlplus/admin/pupbld.sql

II-

Gestion du stockage- Tablespaces

Dans cette partie, nous allons faire le lien entre les structures logiques dOracle et ces
fichiers.
Nous allons tudier comment Oracle stocke les donnes des tables et des index dans
les fichiers et comment ces donnes sont gres physiquement sur le disque.

1- Notion de tablespace
Un tablespace est une unit logique de stockage compose dun ou de plusieurs
fichiers physiques.
Les fichiers de donnes sont dcoups en blocs dune taille dfinie la cration de la
base de donnes (2 ko, 4 ko, 8 ko, etc ). La taille des blocks Oracle correspondent
un multiple du block gr par le systme dexploitation.
La taille dun bloc Oracle correspond au paramtre DB_BLOCK_SIZE.
En version 10, Oracle a introduit la notion de tablespace BIGFILE : un tablespace
BIGFILE est un tablespace compos dun seul fichier de donnes qui peut tre
particulirement volumineux (jusqu 2^32 blocs Oracle soit plus de 4 milliards de
blocs). A contrario, un tablespace traditionnel, dornavant appel tablespace
SMALLFILE, peut contenir plusieurs fichiers de donnes (jusqu 1 022 fichiers),
mais de taille plus limite ("seulement" 2^22 blocs Oracle, soit tout de mme plus de
4 millions de blocs).
Lorsquun tablespace SMALLFILE contient plusieurs fichiers de donnes, les fichiers
sont gnralement situs sur des disques diffrents avec deux objectifs possibles :
- Allouer de lespace supplmentaire un tablespace dont le fichier de donnes
initial ne peut plus stendre ;
- Rpartir le stockage du tablespace sur plusieurs disques (striping au niveau
dOracle).
Il y a 4 types de segments grs dans une base Oracle :
Les segments de table = espace occup par les tables
Les segments dindex = espace occup par les index
Les segments dannulation = espace temporaire utilis pour stocker les
informations permettant dannuler une transaction (ROLLBACK).
Les segments temporaires = espace temporaire utilis lors dun tri dans une
requte.

Noreddine GHERABI

Ainsi on mettra les tables et les index dans des tablespaces ddis aux tables ou aux
index, les segments temporaires dans des tablespaces temporaires et les segments
dannulation dans le tablespace UNDO.
Lespace utilis par les objets sur le disque est appel segment (comme les tables par
exemple).

1.1

Organisation du stockage lintrieur dun tablespace

Lorganisation du stockage lintrieur dun tablespace peut tre rsume par le

schma ci aprs.

Noreddine GHERABI

 lintrieur dun tablespace, le stockage est organis en segments contenant une ou

plusieurs extensions (extents), une extension tant un ensemble de blocs Oracle
contigus.
Lorsquun segment est cr dans un tablespace, Oracle lui alloue une (ou plusieurs)
extension(s) dans un des fichiers de donnes du tablespace. Lorsque lespace
initialement allou est plein (suite linsertion de donnes par exemple), Oracle
alloue une nouvelle extension au segment, et ainsi de suite. Toutes les extensions
alloues un segment sont dans le tablespace de cration du segment, mais pas
forcment cte cte, ni forcment dans le mme fichier de donnes (si le tablespace
est compos de plusieurs fichiers de donnes). Lorsquun segment est supprim, les
extensions quil occupe sont libres et rendues disponibles pour dautres segments.
Des crations/suppressions frquentes de segments dans un tablespace peuvent
donc conduire une fragmentation de lespace disponible dans ce tablespace.

1.2

Structure

1.3

Pourquoi Utiliser plusieurs tablespaces

1.4

Une base de donnes est compose d'units logiques : les tablespaces;

Pour chaque base, il y a au moins un tablespace d'origine, appel SYSTEM, qui
contient le dictionnaire des donnes (toutes les informations sur la base)
Un tablespace peut tre actif (online) ou dsactiv (offline)
Un tablespace correspond un ou plusieurs fichiers physiques;
Une table ou ses index sont cres dans un tablespace , qui peut s'tendre sur
plusieurs fichiers physiques..
Un tablespace peut tre TEMPORARY ou PERMANENT (mode par dfaut).
Un tablespace temporaire contient des donnes temporaires lis au TRI par
exemple
Un tablespace peut tre rserv contenir des donnes Rollback : UNDO
TABLESPACE

Pour sparer les donnes de lapplication des donnes du dictionnaire Oracle ;

Pour sparer les donnes de plusieurs applications stockes dans la mme
base de donnes ;
Pour sparer le stockage des diffrents types dobjets ;
Pour rpartir les entres/sorties sur plusieurs disques ;
Pour raliser des sauvegardes/restaurations partielles ;
Pour contrler la disponibilit des donnes.
Contrler l'allocation des espaces disques aux utilisateurs via des quotas.
Augmenter la disponibilit des donnes (arrt partiel d'une base via l'arrt
d'un Tablespace)

Les autres tablespaces

8

Noreddine GHERABI

Permettent de regrouper les donnes par type dapplications ou dactivits

(exemple le tablespace TOOLS, le tablespace COMPTA, ...)
Permettent de sparer les donnes pour augmenter les performances
Sont par dfaut ONLINE la cration
Peuvent tre mis OFFLINE
Peuvent tre sauvegards et restaurs sans arrter la base entire
Sont crer explicitement par l'administrateur
Peuvent tre mis READ ONLY

2- Cration dun tablespace permanent

Lordre SQL CREATE TABLESPACE permet de crer un tablespace :

CREATE [ BIGFILE | SMALLFILE ] TABLESPACE Ts_nom

DATAFILE nom_fichier [ SIZE valeur [K|M|G|T] ] [REUSE]
AUTOEXTEND { OFF | ON [ NEXT valeur [K|M|G|T] ]
[ MAXSIZE { UNLIMITED | valeur [K|M|G|T] } ] }
EXTENT MANAGEMENT
LOCAL { AUTOALLOCATE | UNIFORM [ SIZE valeur [K|M|G|T] ] }
SEGMENT SPACE MANAGEMENT { MANUAL | AUTO }
[ BLOCKSIZE valeur (K) ]
[ LOGGING | NOLOGGING ]
[ FORCE LOGGING ]
[ ONLINE | OFFLINE ];

- DATAFILE = spcification (emplacement, taille, ) dun (ou plusieurs) fichier(s) de

donnes pour le tablespace, la taille peut tre spcifie en octets (pas de symbole), en
Ko (symbole K) ou en Mo (symbole M) ou en Gigas (G) ou en terra pour les
tablespace de type BIGFILE uniquement.
- AUTOEXTEND : Indique si le fichier de donnes peut (ON) ou non (OFF) grossir
une fois que tout lespace initialement allou est utilis..
- NEXT : espace minimum allou lors dune extension
- MAXSIZE : taille maximum du fichier, ventuellement non limite (UNLIMITED)
- EXTENT MANAGEMENT : permet de dfinir le mode de gestion des extents
lintrieur du tablespace.
- SEGMENT SPACE MANAGEMENT : permet de dfinir le mode de gestion de
lespace libre des segments lintrieur du tablespace.
- BLOCKSIZE valeur [K] : Cette clause dfinit la taille de bloc utilise par le
tablespace. Les valeurs autorises sont 2 Ko, 4 Ko, 8 Ko, 16 Ko et 32 Ko (certaines
plates formes sont plus restrictives). La valeur par dfaut est la taille de bloc standard
dfinie par le paramtre DB_BLOCK_SIZE. Pour utiliser une taille de bloc non
standard pour un tablespace, vous devez configurer un pool pour cette taille de bloc
dans le Buffer Cache, grce un des paramtres DB_nK_ CACHE_SIZEn valant 2, 4,
8, 16 ou 32).
- LOGGING | NOLOGGING : enregistre les segments dans les Redo Log si
LOGGING (par dfaut) est dfini.
9

Noreddine GHERABI

- FORCE LOGGING : oblige lenregistrement des segments dans les Redo Log
mme si la clause NOLOGGING est dfinie.
- ONLINE | OFFLINE : indique si le tablespace est accessible (ON) ou non (OFF).
Exemple : Tablespace pour les tables, avec une gestion locale uniforme des extensions :
CREATE TABLESPACE data
DATAFILE g:\oracle\ora\data01.dbf SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 800M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace permanent par dfaut

Lorsquun utilisateur cre un segment sans prciser de tablespace , Oracle stocke le
segment dans le tablespace par dfaut de lutilisateur.
Ce tablespace par dfaut est dfini grce la clause DEFAULT TABLESPACE des
ordres SQL CREATE USER et ALTER USER. Si cette clause est omise, cest le
tablespace SYSTEM qui est affect comme tablespace par dfaut lutilisateur. Dans
la pratique, ce comportement par dfaut ne pose pas de problme car les utilisateurs
non DBA nont pas (normalement cest le cas par dfaut) de quotas sur le tablespace
SYSTEM, et ne peuvent y crer de segments.
Le tablespace permanent par dfaut peut tre dfini lors de la cration de la base de
donnes, grce la clause DEFAULT TABLESPACE de lordre SQL CREATE
DATABASE.
Exemple
DEFAULT TABLESPACE data
DATAFILE g:\oracle\ora\datadaf01.dbf SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE

Notez que le tablespace ainsi dfini est obligatoirement de type SMALLFILE.

Pour crer et dfinir un tablespace permanent par dfaut aprs la cration de la base
de donnes, vous devez :
- Crer un tablespace permanent, grce lordre SQL CREATE TABLESPACE
prsent prcdemment ;
- Le dfinir comme tablespace permanent par dfaut, grce la clause
DEFAULT TABLESPACE de lordre SQL ALTER DATABASE.
Syntaxe
ALTER DATABASE DEFAULT TABLESPACE nom ;

10

Noreddine GHERABI

Pour retrouver le nom du tablespace permanent par dfaut, vous pouvez interroger
la
vue
DATABASE_PROPERTIES
pour
la
proprit
DEFAULT_PERMANENT_TABLESPACE :
SELECT property_value FROM database_properties
WHERE property_name = DEFAULT_PERMANENT_TABLESPACE ;

3- Modification dun tablespace permanent

Aprs cration, il est possible de modifier un tablespace, notamment pour :
- le renommer ;
- lui allouer de lespace supplmentaire ;
- dplacer le(s) fichier(s) de donnes ;
- le passer OFFLINE / ONLINE ;
- le passer READ ONLY / READ WRITE ;
- modifier ces autres caractristiques (LOGGING / NOLOGGING, FORCE
LOGGING, FLASHBACK ON / OFF, etc.).
Ces oprations seffectuent selon les cas avec lordre SQL ALTER TABLESPACE ou
ALTER DATABASE.
Il est possible dallouer de lespace supplmentaire une base de donnes :
- en ajoutant un nouveau tablespace (avec un ou plusieurs fichiers de donnes) ;
- en ajoutant un fichier de donnes un tablespace existant ;
- en augmentant la taille dun fichier de donnes dun tablespace.

3.1

Renommer un tablespace

Renommer un tablespace seffectue avec lordre SQL ALTER TABLESPACE. Cette

possibilit est apparue en version 10.
Exemple :
ALTER TABLESPACE tbs1 RENAME TO tbs2;

3.2

Ajouter un fichier de donnes un tablespace

Ajouter un fichier de donnes un tablespace seffectue avec lordre SQL ALTER

TABLESPACE.
Syntaxe
ALTER TABLESPACE nom ADD DATAFILE spcification_fichier [,...];
Exemple :
ALTER TABLESPACE data
ADD DATAFILE g:\oracle\ora\data02.dbf SIZE 100M
AUTOEXTEND ON NEXT 100M MAXSIZE 500M;

11

Noreddine GHERABI

3.3

Modifier la taille dun fichier de donnes

Modifier la taille dun fichier de donnes seffectue avec lordre SQL ALTER
DATABASE, ou lordre SQL ALTER TABLESPACE dans le cas dun tablespace
BIGFILE.
Syntaxe
ALTER DATABASE
DATAFILE nom_complet | numro_fichier [,...] RESIZE valeur [K|M|G|T];
ALTER TABLESPACE nom_tablespace_bigfile RESIZE valeur [K|M|G|T];
Exemples :
- Tout type de tablespace
ALTER DATABASE
DATAFILE g:\oracle\ora\data02.dbf RESIZE 200M;
-

Tablespace BIGFILE uniquement

ALTER TABLESPACE grand_fichier RESIZE 300G;

3.4

Modifier lextension automatique dun fichier de donnes

Modifier lextension automatique dun fichier de donnes seffectue avec lordre SQL
ALTER DATABASE, ou lordre SQL ALTER TABLESPACE dans le cas dun
tablespace BIGFILE.
Syntaxe
ALTER DATABASE
DATAFILE nom_complet | numro_fichier[,...] clause_auto_extension;
ALTER TABLESPACE nom_tablespace_bigfile clause_auto_extension;
La spcification de la clause dextension automatique (clause_auto_extension) est la
mme que lors de la cration du tablespace (section Cration dun tablespace
permanent).
Exemple :
- Dsactivation de la clause AUTOEXTEND
ALTER DATABASE
DATAFILE g:\oracle\ora\data01.dbf AUTOEXTEND OFF;
- Activation (ou modification) de la clause AUTOEXTEND
ALTER DATABASE
DATAFILE g:\oracle\ora\data01.dbf
AUTOEXTEND ON NEXT 200M MAXSIZE 800M;
- Exemple avec un tablespace BIGFILE
ALTER TABLESPACE grand_fichier
AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

12

Noreddine GHERABI

Activer lextension automatique dun fichier de donnes permet ce dernier de

grossir automatiquement en cas de besoin despace supplmentaire pour un segment
(nouveau ou dj prsent) dans le tablespace ; cest un bon moyen pour viter les
problmes et ne pas avoir augmenter soi mme la taille dun fichier de donnes.
Dsactiver lextension automatique dun fichier de donnes peut tre envisag (et
mme conseill) sil ny a plus despace disponible sur un disque.

3.5

Passer un tablespace OFFLINE / ONLINE

Passer un tablespace OFFLINE / ONLINE seffectue avec lordre SQL ALTER

TABLESPACE.
Syntaxe
ALTER TABLESPACE nom ONLINE | OFFLINE;

3.6

Dplacer un fichier de donnes

Cette action est possible par un ALTER TABLESPACE ou un ALTER DATABASE

RENAME FILE.
Le ALTER DATABASE est particulirement utile pour dplacer le tablespace
SYSTEM qui ne peut pas tre mis OFFLINE.
RENAME DATAFILE ancien_nom_complet
TO nouveau_nom_complet ;
Mthode de dplacement :
- Mettre le tablespace OFFLINE
ALTER TABLESPACE nom_ts OFFLINE ;
- Dplacer les fichiers vers le nouvel emplacement
COPIER .. +.. COLLER
- Indiquer Oracle le nouvel emplacement
ALTER TABLESPACE nom_tablespace RENAME DATAFILE ancien_nom
TO nouveau_nom ;
- Mettre le tablespace ONLINE
ALTER TABLESPACE nom_ts ONLINE ;

3.7

Supprimer un tablespace

DROP TABLESPACE nom [ INCLUDING CONTENTS [ AND DATAFILES ]

[ CASCADE CONSTRAINTS ] ] ;
Exemple :

13

Noreddine GHERABI

Alter tablespace data OFFLINE ;

DROP TABLESPACE data INCLUDING CONTENTS and DATAFILES;
INCLUDING CONTENTS : Cette clause est ncessaire si le tablespace nest pas
vide, pour forcer la suppression pralable des segments qui y sont stocks. Si le
tablespace nest pas vide et que loption nest pas utilise, lerreur ORA-01549 est
retourne :
ORA-01549: le tablespace nest pas vide ; utiliser loption INCLUDING CONTENTS
AND DATAFILES :Cette option de la clause prcdente permet en plus, de
supprimer les fichiers physiques du tablespace. Un message est crit dans le fichier
dalerte de linstance pour chaque fichier physique supprim par Oracle.
CASCADE CONSTRAINTS : Cette clause permet en plus, de supprimer les
contraintes dintgrit rfrentielle dfinies sur des tables hors du tablespace et qui
rfrencent des tables lintrieur du tablespace. Si de telles contraintes existent et
que loption nest pas utilise, lerreur ORA-02449 est retourne :
ORA-02449: cls uniques/primaires de la table rfrences par des cls trangres.

3.8

Supprimer un fichier de donnes

Supprimer un fichier de donnes dun tablespace seffectue avec lordre SQL ALTER
TABLESPACE.
Le fichier de donnes doit tre vide (ne doit contenir aucune extension) ;
Le fichier de donnes ne peut pas tre le premier fichier cr pour le
tablespace ;
Le fichier de donnes ne doit pas appartenir un tablespace en lecture seule ;
Le fichier de donnes doit tre en ligne (ONLINE) ;
Le fichier ne doit pas appartenir au tablespace SYSTEM.
Syntaxe :
ALTER TABLESPACE nom DROP DATAFILE nom_complet;

4- Le tablespace UNDO
Le tablespace UNDO, comme son nom l'indique, est rserv exclusivement
l'annulation des commandes DML (UPDATE, INSERT, etc...).
Lorsqu'on excute l'ordre DELETE par exemple, Oracle commence par copier les
lignes supprimer dans le tablespace UNDO et ensuite indique que les blocs
contenant
les
donnes
dans
le
tablespace
d'origine
sont
libres.
Un ROLLBACK permettre de revenir en arrire alors que le COMMIT supprimera les
lignes du tablespace UNDO (on comprend mieux ici pourquoi un DELETE est si long
: 2 critures pour une suppression :-/).

4.1

Cration du tablespace UNDO

14

Noreddine GHERABI

Au moment de la cration de la base de donnes :

CREATE DATABASE <SID>...
UNDO TABLESPACE undotbs
DATAFILE 'g:\oracle\ora\undotbs.dbf' size 100M;
Cration du tablespace :
CREATE UNDO TABLESPACE undotbs
DATAFILE 'g:\oracle\ora\undotbs.dbf' size 100M;

4.2

Modification du tablespace UNDO utilis :

ALTER SYSTEM
SET UNDO_TABLESPACE=undotbs;

5- Tablespaces temporaires
Le tablespace temporaire hberge les segments temporaires issus des requtes ou des
commandes SQL telles que :
Select order by
Select group by
Select distinct ...
Les requtes ensemblistes (UNION, MINUS, INTERSECT)
Create INDEX
Les jointures ( JOIN)
Les tables temporaires (CREATE GLOBAL TEMPORARY TABLE )
Lorsquune requte ncessite un tri, Oracle tente de faire le tri en mmoire dans la
PGA du processus serveur qui excute la requte. Sil ne peut pas car le tri ne tient
pas en mmoire, Oracle le dcoupe en morceaux et trie chaque morceau en stockant
des rsultats intermdiaires sur disque dans des segments temporaires.
Au bout dun certain temps ces segments temporaires sont supprims par Oracle.
Le tablespace temporaire par dfaut ainsi cr est forcment gr localement. Il a
une taille de bloc correspondant au paramtre DB_BLOCK_SIZE.
informations sur les tablespaces par dfaut
Select property_name, property_value
from database_properties where property_name like DEFAULT_%;

5.1

Crer un tablespace temporaire

Il est cr avec lordre SQL CREATE TEMPORARY TABLESPACE :

CREATE [ BIGFILE | SMALLFILE ] TEMPORARY TABLESPACE Ts_nom
15

Noreddine GHERABI

TEMPFILE nom_fichier [ SIZE valeur [K|M|G|T] ] [REUSE]

AUTOEXTEND { OFF|ON [ NEXT valeur [K|M|M|T] ] [ MAXSIZE { UNLIMITED
| valeur [K|M|G|T] } ]
[EXTENT MANAGEMENT LOCAL] [UNIFORM [SIZE valeur [K|M] ] ]

5.2

Administrer les tablespaces temporaires

Ladministration dun tablespace temporaire gr localement seffectue avec les

ordres SQL habituels, en remplaant le mot cl DATAFILE par le mot cl TEMPFILE
:
ALTER TABLESPACE
ALTER DATABASE pour la gestion des fichiers de donnes
DROP TABLESPACE pour la suppression
Il existe nanmoins quelques restrictions :
Un tablespace temporaire ne peut pas tre mis OFFLINE.
Les fichiers dun tablespace temporaire ne peuvent pas tre dplacs, ils
doivent tre supprims puis recrs.
Toujours en mode NOLOGGING, les modifications ne sont pas enregistres
dans les fichiers de Redo Log (intressant pour les performances).
Il ne peut pas tre sauvegard.

5.3

Agrandir un tablespace temporaire

Il est possible dajouter un fichier temporaire un tablespace temporaire.

ALTER TABLESPACE nom_ts_temp
ADD TEMPFILE spcification_fichier_temp ;

5.4

Modifier la taille dun fichier temporaire

ALTER DATABASE TEMPFILE nom_complet [, ] RESIZE valeur [L|M]

5.5

Supprimer un tablespace temporaire

DROP TABLESPACE nom [ INCLUDING CONTENTS [ AND DATAFILES ] ]

5.6

Dfinir un tablespace temporaire par dfaut

La cration du tablespace temporaire par dfaut peut se faire aprs cration de la

base de donnes avec lordre CREATE TEMPORARY TABLESPACE.
Il faut ensuite dsigner ce tablespace temporaire comme tablespace temporaire par
dfaut en utilisant la commande ci-dessous.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE nom_tbs

16

Noreddine GHERABI

5.7

Assignation d'un tablespace temporaire un utilisateur

ALTER USER Nom_utilisateur

TEMPORARY TABLESPACE temp;

5.8

Vues du dictionnaire de donnes

Plusieurs vues du dictionnaire permettent dobtenir des informations sur les

tablespaces et les fichiers de donnes :
DBA_TABLESPACES : informations sur les tablespaces
V$TABLESPACE : informations sur les tablespaces ( partir du fichier de contrle)
DBA_TEMP_FILES : informations sur les fichiers de donnes des tablespaces
temporaires grs localement.
V$TEMPFILE : informations sur les fichiers de donnes des tablespaces temporaires
grs localement.
V$SORT_SEGMENT : supervision du stockage des segments temporaires.
DATABASE_PROPERTIES : informations sur les proprits par dfaut de la base de
donnes, pour le tablespace temporaire par dfaut, le nom de la proprit est
DEFAULT_TEMP_TABLESPACE.
DBA_TEMP_FREE_SPACE : nouvelle table en version 11g, qui permet de grer plus
facilement les tablespaces temporaires. Elle contient la taille totale utilise pour le
tablespace temporaire, ainsi que lespace temporaire utilis et lespace temporaire
disponible.
Exemple : Connaitre les Statuts des Tablespaces avec la vue DBA_TABLESPACES.
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces;

I-

Gestion du stockage- Segments

1- Notion de segment
Lespace utilis par les objets sur le disque est appel segment (comme les tables
par exemple).
A lintrieur dun tablespace, le stockage est organis en segments comportant un ou
plusieurs extents (ensemble de bloc Oracle contigus de taille DB_BLOCK_SIZE dans
un fichier de donnes).
Pour mmoire, il existe quatre types principaux de segments :
les segments de table : espace occup par les tables ;
les segments dindex : espace occup par les index ;
les segments dannulation : espace temporaire utilis pour stocker les
informations permettant dannuler une transaction ;
17

Noreddine GHERABI

 les segments temporaires : espace temporaire utilis notamment lors dun tri.
La premire extension dun segment contient au minium deux blocs, le premier tant
rserv lentte du segment (ne contient pas de donnes utiles mais la carte des
extensions alloues au segment). Il en est de mme pour chaque fichier de donnes
du tablespace ; le premier bloc est un bloc dentte (nous verrons bientt que lentte
Du fichier peut contenir davantage de blocs).
Un tablespace peut tre "gr par le dictionnaire" ou "gr localement".
Dans un tablespace "gr par le dictionnaire", les informations relatives la gestion
de lespace (extensions libres/alloues) sont enregistres dans le dictionnaire de
donnes.
Dans un tablespace "gr localement", les informations relatives la gestion de
lespace (extensions libres/alloues) sont enregistres dans un bitmap, dans lentte
de chaque fichier de donnes du tablespace. Chaque bit du bitmap correspond une
extension et vaut 0 ou 1 selon que lextension est libre ou alloue.
Les tablespaces grs localement sont apparus dans Oracle8i. Depuis Oracle9i, les
tablespaces sont, par dfaut, grs localement (sauf le tablespace SYSTEM qui est, par
dfaut, gr par le dictionnaire.
Oracle propose deux variantes pour les tablespaces grs localement :
Une gestion dite "automatique" : la taille des extensions est dtermine
automatiquement par Oracle.
Une gestion dite "uniforme" : la taille des extensions est uniforme, gale une
valeur dfinie lors de la cration du tablespace.
Par dfaut, un tablespace permanent gr localement est en gestion automatique des
extensions ; la gestion uniforme doit tre spcifie.
Un tablespace temporaire gr localement est obligatoirement en gestion uniforme
des extensions (dtaill ultrieurement).
Tablespace
Segment 1
Extent 1

Segment 1
Extent 3

Segment 2
Extent 1
Segment 2
Extent 2
Segment 3
Extent 1
Segment 1
Extent 2

Fichier 1.dbf

Fichier 2.dbf

18

Noreddine GHERABI

2- Spcifier le stockage dun segment

Les clauses TABLESPACE et STORAGE peuvent tre utilises dans les ordres de
cration des segments pour spcifier le stockage du segment.
Syntaxe de la clause TABLESPACE
TABLESPACE nom_tablespace
Syntaxe de la clause STORAGE
STORAGE ( [ INITIAL valeur [K|M] ]
[ NEXT valeur [K|M] ]
[ MINEXTENTS valeur ]
[ MAXEXTENTS { valeur | UNLIMITED } ]
[ PCTINCREASE valeur ] )
Exemple pour une table :
CREATE TABLE client
(
Id_client NUMBER(6),
nom VARCHAR2(20)
)
TABLESPACE data
STORAGE (INITIAL 500K) ;

19

Noreddine GHERABI