![logo](https://oracle.developpez.com/images/faqoracle.gif)
FAQ OracleConsultez toutes les FAQ
Nombre d'auteurs : 17, nombre de questions : 139, dernière mise à jour : 30 mai 2016 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.
- 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) ?
- Comment connaître le tablespace par défaut de chaque utilisateur
La requête suivante permet d'afficher l'identifiant, le nom, le status et le tablespace des fichiers de données :
Code sql : | Sélectionner tout |
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 |
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 : | Sélectionner tout |
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 |
- Chemin et nom de fichier
- Numéro de thread
- Numéro de séquence
- Taille
- Identifiant resetlogs (10g)
- Date
- État (9i)
Code sql : | Sélectionner tout |
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 |
La requête suivante nous permet d'afficher leurs identifiants, noms et status :
Code sql : | Sélectionner tout |
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 |
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 :
Code sql : | Sélectionner tout |
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 |
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 : | Sélectionner tout |
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 |
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 : | Sélectionner tout |
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 |
Code sql : | Sélectionner tout |
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 |
Via la requête suivante :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 | SELECT INSTANCE_NAME, VERSION FROM V$INSTANCE; INSTANCE_NAME VERSION ---------------- ----------------- LSC63 10.2.0.2.0 |
Via la requête suivante :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 | SQL> SELECT GLOBAL_NAME 2 FROM GLOBAL_NAME; GLOBAL_NAME ------------------------------ LSC63.DOMAIN.CH |
À partir d'Oracle 10g, via la requête suivante :
Code sql : | Sélectionner tout |
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 |
À 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 : | Sélectionner tout |
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 ![]() |
Depuis la version 10g, Il est possible de renommer un Tablespace grâce à la commande suivante :
Code sql : | Sélectionner tout |
SQL> alter tablespace Tbs1 rename to Tbs2 ;
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 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 : | Sélectionner tout |
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. |
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 : | Sélectionner tout |
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 ! |
Le tablespace SYSAUX est apparue avec la version 10g, et a pour but de décharger le tablespace SYSTEM de certains segments.
Code sql : | Sélectionner tout |
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 ... |
La requête suivante permet d'afficher le tablespace par défaut de chaque utilisateur :
Code sql : | Sélectionner tout |
select username, account_status, default_tablespace from dba_users;
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 çaLes 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 © 2024 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.