LLI Wiki — My Digital Knowledge Base

Oracle : gérer les tablespaces et les datafiles

Requêtes et commandes Oracle pour suivre l'espace, ajouter, redimensionner, déplacer et supprimer tablespaces et datafiles.

Recueil de requêtes et de commandes Oracle Database pour inspecter et gérer les tablespaces et les datafiles : tailles, espace libre, croissance, ajout, redimensionnement, déplacement et suppression de fichiers. À exécuter avec un utilisateur ayant accès aux vues DBA_* et V$.

Déplacer ou renommer un datafile

ALTER DATABASE MOVE DATAFILE '/u01/oradata/ORCL/users01.dbf' TO '/u02/oradata/ORCL/users01.dbf';

Référence : Renaming or moving Oracle files (oracle-base.com).

Taille des schémas et des tables

SELECT sum(bytes)/1024/1024/1024 as "Size in GB"
from dba_segments
WHERE owner = UPPER('&schema_name');

select owner,sum(bytes)/1024/1024/1024 as "Size in GB"
from dba_segments group by owner;

Les 10 plus grosses tables de toute la base :

set linesize 300;
set pagesize 300;
column owner format a10;
column table_name format a30;
column "SIZE (GB)" format 99999.99;
select *
from (
        select owner, segment_name table_name, bytes/1024/1024/1024 "SIZE (GB)"
        from dba_segments
        where segment_type = 'TABLE' and segment_name not like 'BIN%'
        order by 3 desc
    )
where rownum <= 10;

Les 10 plus grosses tables d'un tablespace :

column owner format a10;
column table_name format a30;
column "SIZE (GB)" format 99999.99;

select *
from (
    select owner, segment_name table_name, bytes/1024/1024/1024 "SIZE (GB)"
    from dba_segments
    where segment_type = 'TABLE' and segment_name not like 'BIN%' and tablespace_name in ('USERS')
    order by 3 desc
    )
where rownum <= 10;

Taille, emplacement et nombre de lignes d'une table :

select owner, table_name, tablespace_name
from dba_tables
where table_name='MY_TABLE';

column segment_name format a20;
column "SIZE (GB)" format 99999.99;
Select
    segment_name,sum( bytes)/1024/1024/1024 as "SIZE (GB)"
from
    dba_segments
where
    segment_name not like 'BIN%'
    and segment_type = 'TABLE'
    and segment_name = 'MY_TABLE'
group by segment_name;

select count(*) from my_schema.MY_TABLE;

Taille de bloc, tablespaces par défaut, répertoires

select value from v$parameter where name = 'db_block_size';
show parameter DB_BLOCK_SIZE

select username, account_status, default_tablespace, TEMPORARY_TABLESPACE from dba_users;

select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

Lister les tablespaces et les datafiles

SELECT TABLESPACE_NAME "TABLESPACE",status ,EXTENT_MANAGEMENT,FORCE_LOGGING,BLOCK_SIZE,SEGMENT_SPACE_MANAGEMENT
  FROM DBA_TABLESPACES;
SET PAGESIZE 60
SET LINESIZE 300

COLUMN Tablspace FORMAT A30
COLUMN "Filename" FORMAT A50
COLUMN "Size MB" FORMAT 999,999,999
COLUMN "Maximum Size MB" FORMAT 999,999,999

select
  TABLESPACE_NAME "Tablspace",
  FILE_NAME "Filename",
  BYTES/1024/1024 "Size MB",
  MAXBYTES/1024/1024 "Maximum Size MB",
  AUTOEXTENSIBLE "Autoextensible"
from
  SYS.DBA_DATA_FILES;

Propriétés des tablespaces (gestion des extents, type d'allocation, bigfile, fichiers, taille, 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;

Paramètres d'extents par tablespace :

col max_extents format a4 head 'Max|Ext'
col pct_increase format 999 head 'Pct|Inc'
col extent_management format a10 head 'Extent|Management'
col allocation_type format a10 head 'Allocation|Type'
col status format a7 head 'Status'
select   tbs.tablespace_name
,           tbs.initial_extent
,           tbs.next_extent
,           decode(tbs.max_extents,2147483645,'UL',tbs.max_extents) max_extents
,           tbs.pct_increase
,           tbs.extent_management
,           tbs.allocation_type
,           tbs.status
from    dba_tablespaces tbs
order by 1
/

Tablespaces temporaires et tempfiles :

SELECT TABLESPACE_NAME FROM dba_tablespaces WHERE contents = 'TEMPORARY';
SELECT Name FROM v$tempfile;

select FILE_ID, FILE_NAME, STATUS, TABLESPACE_NAME
from DBA_TEMP_FILES
order by FILE_ID;

Occupation du tablespace SYSAUX :

select occupant_name, space_usage_kbytes from v$sysaux_occupants;

Utilisation et espace libre des tablespaces

Espace alloué, utilisé et libre par tablespace, avec pourcentages (voir aussi How to resolve ORA-01653) :

SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
        round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"
FROM   (SELECT tablespace_name,round(sum(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name) df,
       (SELECT round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name FROM dba_segments GROUP BY tablespace_name) tu
WHERE  df.tablespace_name = tu.tablespace_name;

Utilisation en tenant compte de l'autoextend (taille maximale via maxbytes) :

set linesize 1000;

with  usp as ( select
                  tablespace_name,count(*) nb_datafile
                , sum(bytes)/1024/1024 tot_file_mb
                , sum(greatest(bytes, maxbytes))/1024/1024 max_mb
             from
                 dba_data_files
            group by tablespace_name),
            fs as  (
                    select tablespace_name,sum(bytes)/1024/1024 as freespace_mb
                    from dba_free_space
                group by tablespace_name
                    )
select
      usp.tablespace_name, Freespace_mb,max_mb,NB_datafile,round(((tot_file_mb/usp.max_mb)* 100),2)
from
        usp inner join fs on usp.tablespace_name = fs.tablespace_name;

Utilisation via la vue de métriques intégrée (blocs convertis en Mo avec la taille de bloc) :

column value new_value dbblock noprint
select value from v$parameter where name = 'db_block_size';
select tablespace_name,
used_space used_blocks,
(used_space*&dbblocks)/(1024*1024) used_mb,
tablespace_size tablespace_blocks,
(tablespace_size*&dbblocks)/(1024*1024) tablespace_mb,
used_percent
from dba_tablespace_usage_metrics;

Espace libre, plus grand bloc libre et nombre de blocs libres d'un tablespace :

set feedback off
set echo off
set numwidth 15
set linesize 150
Accept tname Prompt "Enter Tablespace Name : "
Select (Sum(bytes)/1024/1024) Free_space_MB,(max(bytes)/1024/1024) Largest_Free_chunck_MB,count(*) No_of_free_chunk
from dba_free_space where tablespace_name=upper('&tname');

Taille allouée d'un tablespace :

Select (sum(bytes)/1024/1024) Space_allocated
from dba_data_files
where tablespace_name=upper('&tname');

High-water mark de chaque datafile

Utile pour savoir jusqu'où un datafile peut être réduit :

column file_name format a50;
column tablespace_name format a15;
column highwater format 9999999999;
set pagesize 9999
select a.tablespace_name
,a.file_name
,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a
,(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b
,dba_extents c
,(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name;

Historique de croissance des tablespaces

Basé sur l'historique AWR (nécessite la licence Diagnostics Pack) :

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;

Taille maximale d'un datafile

La taille de bloc s'obtient avec :

select distinct bytes/blocks from user_segments;

Un datafile de type smallfile contient au maximum 4194303 blocs :

Taille de bloc Taille maximale du datafile
2k 8 Go
4k 16 Go
8k 32 Go
16k 64 Go
32k 128 Go

Un tablespace BIGFILE (depuis Oracle 10g) n'a qu'un seul datafile, qui peut contenir jusqu'à 4294967295 blocs :

Taille de bloc Taille maximale du datafile
2k 8 To
4k 16 To
8k 32 To
16k 64 To
32k 128 To

Voir How big can a datafile be.

Créer des tablespaces et ajouter des datafiles

create tablespace app_tabspace
   datafile '/u01/oradata/ORCL/app_tabspace01.dbf'
    size 10M autoextend on;

ALTER TABLESPACE users
ADD DATAFILE '/u01/oradata/ORCL/users_02.dbf'
size 100m;

ALTER TABLESPACE APP_DATA ADD DATAFILE '/u01/oradata/ORCL/app_data02.dbf' SIZE 200M AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;

Avec une taille de bloc de 8k, la limite de 32 Go est de 33554432 Ko ; la note d'origine utilise MAXSIZE 33554416K (32 Go moins 16 Ko) comme maximum sûr :

ALTER TABLESPACE "APP_INDEX"
    ADD DATAFILE '/u01/oradata/ORCL/app_index03.dbf'
    SIZE 100M
    AUTOEXTEND ON NEXT 100M MAXSIZE 33554416K;

Tablespace temporaire :

create temporary tablespace app_tabspace_temp
tempfile '/u01/oradata/ORCL/app_tabspace_temp.dbf'
size 5M autoextend on;

Redimensionnement et autoextend

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 40000M;

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND ON NEXT 30K MAXSIZE 1000K;

Changer le statut d'un tablespace, le renommer

alter tablespace ica offline;
alter tablespace ica online;

alter tablespace ica read only;
alter tablespace ica read write;

alter tablespace Tbs1 rename to Tbs2;

Paramètres de stockage par défaut (tablespaces gérés par le dictionnaire) :

ALTER TABLESPACE SYSTEM
DEFAULT STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS 300 PCTINCREASE 1);

Supprimer un tablespace ou un datafile

Vérifier d'abord que rien n'est stocké dans le tablespace :

select * from dba_segments
where tablespace_name = '<tablespace_to_drop>';
drop tablespace app_tabspace;
drop tablespace <tablespace_name> including CONTENTS AND datafiles;

-- tablespace dont les tables sont référencées par des contraintes d'autres tablespaces
DROP TABLESPACE tbs3
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

-- supprimer un tablespace non vide (les datafiles restent sur disque)
DROP TABLESPACE APP_OLD INCLUDING CONTENTS;

Supprimer un seul datafile d'un tablespace multi-fichiers (référence) :

create tablespace x datafile '/tmp/x1.dbf' size 4m, '/tmp/x2.dbf' size 4m;
alter tablespace x drop datafile '/tmp/x2.dbf';

Plus de détails : Oracle DROP TABLESPACE.

Où sont stockés mes objets ?

select tablespace_name from all_tables where owner = 'MY_SCHEMA' and table_name = 'MY_TABLE';
select table_name, tablespace_name from all_tables where owner = 'MY_SCHEMA';
select tablespace_name from all_indexes where owner = 'MY_SCHEMA' and index_name = 'MY_INDEX';
select index_name, tablespace_name from all_indexes where owner = 'MY_SCHEMA';

Autres types de fichiers

Archived logs :

select NAME,
    THREAD#,
    SEQUENCE#,
    BLOCKS*BLOCK_SIZE "SIZE",
    RESETLOGS_ID,
    COMPLETION_TIME,
    STATUS
from V$ARCHIVED_LOG
order by SEQUENCE#;

Fichiers de contrôle :

select NAME, STATUS
from V$CONTROLFILE
order by NAME;

Fichiers redo log :

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;

Réduire un tablespace

Pour récupérer de l'espace, vérifier la high-water mark de chaque datafile (voir plus haut), puis redimensionner le datafile en dessous de sa taille actuelle mais pas sous la high-water mark. Voir How to shrink an Oracle tablespace.

Remarques

Mastodon LLIMastodon SocialFosstodon