FAQ OracleConsultez toutes les FAQ

Nombre d'auteurs : 15, nombre de questions : 137, dernière mise à jour : 26 octobre 2006  Ajouter une question

 

Cette F.A.Q. a été réalisée à partir des questions fréquemment posées sur le forum Oracle de www.developpez.com et de l'expérience personnelle des auteurs. Elle pourra traiter de tout type de questions portant sur les technologies Oracle.

Nous espérons que cette F.A.Q. saura répondre à un maximum de vos questions. Nous vous souhaitons une bonne lecture.

L'équipe Oracle de Developpez.


SommaireAdministrationLes Tablespaces et autres fichiers (16)
précédent sommaire suivant
 

La requête suivante permet d'afficher l'identifiant, le nom, le status et le tablespace des fichiers de données :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

Mis à jour le 18 septembre 2006 laurentschneider

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é

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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)

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
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

Mis à jour le 18 septembre 2006 laurentschneider

La requête suivante nous permet d'afficher leurs identifiants, noms et status :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
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.

Mis à jour le 18 septembre 2006 laurentschneider

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.

Mis à jour le 30 novembre 2004 helyos

Via la requête suivante :

Code sql :
1
2
3
4
5
6
7
8
9
SELECT NAME, 
    STATUS 
FROM V$CONTROLFILE 
ORDER BY NAME; 
  
NAME                                              STATUS 
------------------------------------------------- ------- 
/u01/LSC63/ctrl/ctrl01aLSC63.dbf 
/u01/LSC63/ctrl/ctrl01bLSC63.dbf

Mis à jour le 18 septembre 2006 laurentschneider

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

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

Mis à jour le 18 septembre 2006 laurentschneider

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

Code sql :
1
2
3
4
5
6
7
8
9
10
11
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) :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
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

Mis à jour le 29 août 2006 laurentschneider

Via la requête suivante :

Code sql :
1
2
3
4
5
6
7
SELECT INSTANCE_NAME, 
       VERSION 
  FROM V$INSTANCE; 
  
INSTANCE_NAME    VERSION 
---------------- ----------------- 
LSC63            10.2.0.2.0

Mis à jour le 18 septembre 2006 laurentschneider

Via la requête suivante :

Code sql :
1
2
3
4
5
6
SQL> SELECT GLOBAL_NAME 
  2    FROM GLOBAL_NAME; 
  
GLOBAL_NAME 
------------------------------ 
LSC63.DOMAIN.CH

Mis à jour le 18 septembre 2006 laurentschneider

À partir d'Oracle 10g, via la requête suivante :

Code sql :
1
2
3
4
5
6
7
8
9
SELECT NAME, 
       DETECTED_USAGES 
  FROM DBA_FEATURE_USAGE_STATISTICS 
 ORDER BY NAME; 
  
NAME                  DETECTED 
--------------------- -------- 
Partitioning (system)       11 
Partitioning (user)          0

Mis à jour le 18 septembre 2006 laurentschneider

À 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 :

Code sql :
SQL> DROP DATABASE;

Mis à jour le 18 septembre 2006 Jaouad

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.

Mis à jour le 18 septembre 2006 orafrance

Depuis la version 10g, Il est possible de renommer un Tablespace grâce à la commande suivante :

Code sql :
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.

Mis à jour le 18 septembre 2006 Jaouad

C'est normalement 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 :

Code sql :
1
2
3
4
5
6
7
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.

Mis à jour le 18 septembre 2006 laurentschneider orafrance

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 :

Code sql :
1
2
3
4
5
6
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 !

Mis à jour le 15 octobre 2006 LeoAnderson rouardg

Le tablespace SYSAUX est apparue avec la version 10g, et a pour but de décharger le tablespace SYSTEM de certains segments.

Code sql :
1
2
3
4
5
6
7
8
9
10
11
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 
...

Mis à jour le 18 septembre 2006 Jaouad

Proposer une nouvelle réponse sur la FAQ

Ce n'est pas l'endroit pour poser des questions, allez plutôt sur le forum de la rubrique pour ça


Réponse à la question

Liens sous la question
précédent sommaire suivant
 

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2014 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

 
 
 
 
Partenaires

PlanetHoster
Ikoula