FAQ OracleConsultez toutes les FAQ
Nombre d'auteurs : 16, nombre de questions : 138, dernière mise à jour : 16 février 2013
Sommaire→Administration→Les Tablespaces et autres fichiers- Comment lister les tablespaces et leurs fichiers associés ?
- Comment obtenir la liste des tablespaces avec leurs caractéristiques principales ?
- Comment lister les tablespaces temporaires (locally managed) ?
- Comment vider un tablespace temporaire ?
- Comment afficher le nom et l'état des fichiers de contrôle ?
- Comment lister les fichiers Redo Log ainsi que leurs caractéristiques principales ?
- Comment afficher les caractéristiques de la base de données ?
- Comment afficher le nom et la version de l'instance ?
- Comment afficher le nom global de la base ?
- Comment afficher la fonction et le nombre d'utilisations détectées (ex: Partitioning) ?
- Comment supprimer une base de données sous SQL*Plus ?
- Comment migrer un tablespace de DICTIONNARY en LOCALLY MANAGED ?
- Comment renommer un Tablespace ?
- Comment supprimer un datafile ?
- Comment connaître la taille d'un tablespace et de son espace libre ?
- Comment connaître l'occupation du tablespace SYSAUX (10g) ?
La requête suivante permet d'afficher l'identifiant, le nom, le status et le tablespace des fichiers de données :
select
FILE_ID,
FILE_NAME,
STATUS,
TABLESPACE_NAME
from
DBA_DATA_FILES
order by FILE_ID;
FILE_ID FILE_NAME STATUS TABLESPACE_NAME
---------- ---------------------------------- --------- ---------------
1 /u01/LSC63/data/system01LSC63.dbf AVAILABLE SYSTEM
2 /u01/LSC63/data/undo01LSC63.dbf AVAILABLE UNDOTBS1
3 /u01/LSC63/data/sysaux01LSC63.dbf AVAILABLE SYSAUX
4 /u01/LSC63/data/sysaux02LSC63.dbf AVAILABLE SYSAUX
5 /u01/LSC63/data/sysaux03LSC63.dbf AVAILABLE SYSAUX
6 /u01/LSC63/data/users01LSC63.dbf AVAILABLE USERS
La requête suivante permet d'afficher les tablespaces, avec les propriétés suivantes :
- Type
- Mode de management d'extent
- Type d'allocation
- Mode de management de l'espace segment (9i)
- Option bigfile (10g)
- État
- Nombre de fichiers
- Taille
- Taille maximale (avec autoextend)
- Espace utilisé
select
a.TABLESPACE_NAME,
a.CONTENTS,
a.EXTENT_MANAGEMENT,
a.ALLOCATION_TYPE,
a.SEGMENT_SPACE_MANAGEMENT,
a.BIGFILE,
a.STATUS,
nvl(sum(b.count_files),0) FILES,
nvl(sum(b.bytes),0) "SIZE",
nvl(sum(b.maxbytes),0) MAX_SIZE,
nvl(sum(b.bytes),0)-nvl(sum(c.free_bytes),0) "USED"
from DBA_TABLESPACES a,
(
select TABLESPACE_NAME,
sum(BYTES) bytes,
count(*) count_files,
sum(greatest(MAXBYTES,BYTES)) maxbytes
from DBA_DATA_FILES
group by TABLESPACE_NAME
union all
select TABLESPACE_NAME,
sum(BYTES),
count(*),
sum(greatest(MAXBYTES,BYTES)) maxbytes
from DBA_TEMP_FILES
group by TABLESPACE_NAME
) b,
(
select TABLESPACE_NAME,
sum(BYTES) free_bytes
from DBA_FREE_SPACE
group by TABLESPACE_NAME
union all
select TABLESPACE_NAME,
sum(BYTES_FREE) free_bytes
from V$TEMP_SPACE_HEADER
group by TABLESPACE_NAME
) c
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
and a.TABLESPACE_NAME = c.TABLESPACE_NAME (+)
group by
a.TABLESPACE_NAME,
a.CONTENTS,
a.EXTENT_MANAGEMENT,
a.ALLOCATION_TYPE,
a.SEGMENT_SPACE_MANAGEMENT,
a.BIGFILE,
a.STATUS
order by a.TABLESPACE_NAME;
TABLESPA CONTENTS EXTENT_MAN ALLOCATIO SEGMEN BIG STATUS FILES SIZE MAX_SIZE USED
-------- --------- ---------- --------- ------ --- ------ ----- --------- ---------- ---------
SYSAUX PERMANENT LOCAL SYSTEM AUTO NO ONLINE 3 397410304 6442450944 395771904
SYSTEM PERMANENT LOCAL SYSTEM MANUAL NO ONLINE 1 471859200 2147483648 470548480
TEMP TEMPORARY LOCAL UNIFORM MANUAL NO ONLINE 1 20971520 2147483648 17039360
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL NO ONLINE 1 182452224 2147483648 13041664
USERS PERMANENT LOCAL SYSTEM MANUAL NO ONLINE 1 26214400 2147483648 524288
Concernant les fichiers d'archives, la requête suivante permet de connaître les informations suivantes :
- Chemin et nom de fichier
- Numéro de thread
- Numéro de séquence
- Taille
- Identifiant resetlogs (10g)
- Date
- État (9i)
select NAME,
THREAD#,
SEQUENCE#,
BLOCKS*BLOCK_SIZE "SIZE",
RESETLOGS_ID,
COMPLETION_TIME,
STATUS
from V$ARCHIVED_LOG
order by SEQUENCE#;
NAME THR SEQ SIZE RESETLOGS COMPLETION S
----------------------------------------- --- ---- ------- --------- ---------- -
/u01/LSC63/arch/arch_588083500_1_2472.dbf 2472 2081280 588083500 2006-06-30 A
La requête suivante nous permet d'afficher leurs identifiants, noms et status :
select
FILE_ID,
FILE_NAME,
STATUS,
TABLESPACE_NAME
from
DBA_TEMP_FILES
order by FILE_ID;
FILE_ID FILE_NAME STATUS TABLESPACE_NAME
---------- -------------------------------- --------- ---------------
1 /u01/LSC63/data/temp01LSC63.dbf AVAILABLE TEMP
Cela ne liste que les tablespaces temporaires à allocation d'extent locale (locally managed). En effet, les tablespace temporaires dictionary managed (qui datent d'Oracle 7) ne seront pas listées, car ils ne contiennent pas de "tempfile" mais des datafiles.
Il ne sert à rien de vider un tablespace temporaire, car celui-ci est automatiquement vidé par le processus SMON.
De plus il est important de savoir que les tablespaces temporaires peuvent apparaître pleins lors d'une utilisation normale de la base.
En effet les extents sont alloués une fois puis gérés par le système.
Une fois l'utilisation de l'extent terminée il est alors marqué comme libre mais n'est pas libéré pour des raisons de tuning.
Il n'est donc pas rare de ne pas avoir d'espace temporaire libre lors de l'utilisation d'une base.
Les extents seront libérés par SMON lors du redémarrage de la base.
Via la requête suivante :
select NAME,
STATUS
from V$CONTROLFILE
order by NAME;
NAME STATUS
------------------------------------------------- -------
/u01/LSC63/ctrl/ctrl01aLSC63.dbf
/u01/LSC63/ctrl/ctrl01bLSC63.dbf
La requête suivante permet d'afficher les fichiers redo Log, avec les propriétés suivantes :
- Groupe
- Thread
- Séquence
- Taille
- Nombre de log dans le groupe
- Type (9i)
- État
- Nom des fichiers
- Groupe
select l.GROUP#,
l.THREAD#,
l.SEQUENCE#,
l.BYTES,
l.MEMBERS,
l.STATUS,
f.TYPE,
f.MEMBER
from V$LOGFILE f, V$LOG l
where l.GROUP# = f.GROUP#
order by GROUP#,MEMBER;
GROUP# THR SEQ BYTES MEMBERS STATUS TYPE MEMBER
------ --- ---- ------- ------- ---------- ------ --------------------------------
1 1 2473 4194304 2 INACTIVE ONLINE /u01/LSC63/redo/redo01aLSC63.dbf
1 1 2473 4194304 2 INACTIVE ONLINE /u01/LSC63/redo/redo01bLSC63.dbf
2 1 2474 4194304 2 ACTIVE ONLINE /u01/LSC63/redo/redo02aLSC63.dbf
2 1 2474 4194304 2 ACTIVE ONLINE /u01/LSC63/redo/redo02bLSC63.dbf
3 1 2475 4194304 2 CURRENT ONLINE /u01/LSC63/redo/redo03aLSC63.dbf
3 1 2475 4194304 2 CURRENT ONLINE /u01/LSC63/redo/redo03bLSC63.dbf
La requête suivante nous permet d'obtenir les caractéristiques suivantes :
- Nom
- Identifiant DBID
- Mode d'archivage
- Mode flashback (10g)
- Mode d'ouverture (RW/RO)
- Date de création de la base de données
SELECT NAME,
DBID,
LOG_MODE,
FLASHBACK_ON,
OPEN_MODE,
CREATED
FROM v$database;
NAME DBID LOG_MODE FLASHBACK OPEN_MODE CREATED
--------- ------------- ------------ --------- ---------- ----------
LSC63 1119326572 ARCHIVELOG NO READ WRITE 2006-04-18
À partir d'Oracle 9i, la requête suivante permet d'afficher le nom et la valeur des propriétés de la base de données (ex: tablespace properties) :
SELECT PROPERTY_NAME,
PROPERTY_VALUE,
DESCRIPTION
FROM DATABASE_PROPERTIES
ORDER BY PROPERTY_NAME;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ --------------- -------------------------------------
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
Via la requête suivante :
SELECT INSTANCE_NAME,
VERSION
FROM V$INSTANCE;
INSTANCE_NAME VERSION
---------------- -----------------
LSC63 10.2.0.2.0
Via la requête suivante :
SQL> SELECT GLOBAL_NAME
2 FROM GLOBAL_NAME;
GLOBAL_NAME
------------------------------
LSC63.DOMAIN.CH
À partir d'Oracle 10g, via la requête suivante :
SELECT NAME,
DETECTED_USAGES
FROM DBA_FEATURE_USAGE_STATISTICS
ORDER BY NAME;
NAME DETECTED
--------------------- --------
Partitioning (system) 11
Partitioning (user) 0
A partir de la 10g il est possible de supprimer une base de données Oracle via une commande sous Sql*Plus :
Il faut être connecté en sysdba avec une base de données en mode open.
Nous vous recommandons bien entendu la plus grande vigilance quant à l'utilisation de cette commande, dont la syntaxe est la suivante :
SQL> drop database;
Depuis la version 9.2, il est possible d'utiliser la procédure
sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('NOM_DU_TABLESPACE')
SYSTEM ne peut être migré que si tous les autres tablespaces ont été migré et si la base de données n'est pas configurée avec SYSTEM comme tablespace temporaire par défaut.
Attention, la migration inverse est impossible !
Néanmoins, je vous rappelle qu'Oracle préconise l'utilisation de LMT.
Note : la note Metalink 175434.1 décrit en détail cette migration.
Depuis la version 10g, Il est possible de renommer un Tablespace grâce à la commande suivante :
SQL> alter tablespace Tbs1 rename to Tbs2 ;
Il n'est pas possible de renommer les tablespaces System et Sysaux.
On peut renommer les tablespaces permanents et temporaires : pour ce faire, le tablespace ou l'un de ses fichiers de données ne doit pas être hors ligne, sinon l'opération de renomage ne peut avoir lieu !
Lorsque l'on modifie le nom d'un tablespace, Oracle met à jour les références de ce tablespace : à savoir les fichiers de contrôle, le dictionnaire de données et les en-têtes de fichiers.
Si ce Tablespace est en mode Read Only les entêtes de fichiers ne sont pas modifiés (ils ont besoin pour ce faire d'être en mode Read Write) :
Un message est consigné dans le fichier alerte de l'instance.
Si la modification de nom concerne le tablespace d'annulation UNDO et qu'il s'agit du tablespace désigné par le paramètre d'initialisation, la modification affecte le Spfile.
Si c'est le Pfile qui est utilisé, alors un message d'alerte est consigné dans le fichier d'alerte de l'instance
Le paramètre compatible doit au moins être a 10.0.0.
C'est normallement impossible :
Au mieux un datafile peut être renommé, retaillé ou déplacé.
Pour le supprimer d'un tablespace il faut recréer le tablespace sans ce datafile.
C'est maintenant possible en 10gR2 :
SQL> create tablespace x datafile '/tmp/x1.dbf' size 4m, '/tmp/x2.dbf' size 4m;
Tablespace created.
SQL> alter tablespace x drop datafile '/tmp/x2.dbf';
Tablespace altered.
La vue DBA_DATA_FILES va nous permettre de déterminer la taile d'un tablespace,
La vue DBA_EXTENTS permet de connaitre la taille de tous les segments dans les différents datafiles,
La vue DBA_FREE_SPACE qui indique les espaces libres dans les database files.
La requête suivante vous permet de récupérer toutes ces informations :
SQL> SELECT A.tablespace_Name, A.Alloue, B.Occupe, C.Libre
2 FROM (select tablespace_name, sum(bytes)/1024/1024 AS ALLOUE from dba_data_files group by tablespace_name) a,
3 (select tablespace_name, Sum(bytes)/1024/1024 AS OCCUPE from dba_segments group by tablespace_name) b,
4 (select tablespace_name, Sum(bytes)/1024/1024 AS LIBRE from dba_free_space group by tablespace_name) c
5 WHERE B.tablespace_Name = A.tablespace_Name
6 AND C.Tablespace_Name = B.Tablespace_Name;
Attention : En 10g, le "LIBRE" peut être différent de ("ALLOUE"- "OCCUPE") à cause de la recyclebin !
Le tablespace SYSAUX est apparue avec la version 10g, et a pour but de décharger le tablespace SYSTEM de certains segments.
SQL> select occupant_name, space_usage_kbytes from v$sysaux_occupants ;
OCCUPANT_NAME SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
LOGMNR 6080
LOGSTDBY 896
STREAMS 512
XDB 49600
AO 21248
XSOQHIST 21248
...