IdentifiantMot de passe
Mot de passe oublié ?Je m'inscris ! (gratuit)
logo
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) ?
rechercher
precedent    sommaire    suivant    telechargermiroir

Comment lister les tablespaces et leurs fichiers associés ?
auteur : Laurent Schneider
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

Comment obtenir la liste des tablespaces avec leurs caractéristiques principales ?
auteur : Laurent Schneider
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

Comment lister les tablespaces temporaires (locally managed) ?
auteur : Laurent Schneider
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.


Comment vider un tablespace temporaire ?
auteur : Helyos
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.


Comment afficher le nom et l'état des fichiers de contrôle ?
auteur : Laurent Schneider
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

Comment lister les fichiers Redo Log ainsi que leurs caractéristiques principales ?
auteur : Laurent Schneider
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

Comment afficher les caractéristiques de la base de données ?
auteur : Laurent Schneider
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

Comment afficher le nom et la version de l'instance ?
auteur : Laurent Schneider
Via la requête suivante : 

SELECT INSTANCE_NAME,
       VERSION
  FROM V$INSTANCE;
  
INSTANCE_NAME    VERSION
---------------- -----------------
LSC63            10.2.0.2.0

Comment afficher le nom global de la base ?
auteur : Laurent Schneider
Via la requête suivante : 

SQL> SELECT GLOBAL_NAME
  2    FROM GLOBAL_NAME;

GLOBAL_NAME
------------------------------
LSC63.DOMAIN.CH

Comment afficher la fonction et le nombre d'utilisations détectées (ex: Partitioning) ?
auteur : Laurent Schneider
À 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

Comment supprimer une base de données sous SQL*Plus ?
auteur : Jaouad
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;

Comment migrer un tablespace de DICTIONNARY en LOCALLY MANAGED ?
auteur : Fred_D
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 fr LMT.

Note : la note Metalink 175434.1 décrit en détail cette migration.


Comment renommer un Tablespace ?
auteur : Jaouad
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.


Comment supprimer un datafile ?
auteurs : Fred_D, Laurent Schneider
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.

Comment connaître la taille d'un tablespace et de son espace libre ?
auteurs : LeoAnderson, rouardg
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 !


Comment connaître l'occupation du tablespace SYSAUX (10g) ?
auteur : Jaouad
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
...
rechercher
precedent    sommaire    suivant    telechargermiroir

Consultez les autres F.A.Q's

Valid XHTML 1.1!Valid CSS!

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 © 2006 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site ni 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.