Ceci est une ancienne révision du document !

---

=== tablespace

SET linesize 100
SET pagesize 100
col file_name FOR a60
SELECT
    a.tablespace_name "TABLESPACE_NAME",
    ROUND(a.current_mb) "CURRENT_SIZE_MB",
    ROUND(a.max_mb) "MAX_SIZE_MB",
    ROUND(a.current_mb - c.free) "USED_MB",
    ROUND(a.max_mb - (a.current_mb - c.free)) "FREE_MB",
    ROUND(((a.current_mb - c.free)*100)/a.max_mb) "USED_PCT"
FROM
    (
        SELECT
            tablespace_name,
            SUM(a.bytes)/(1024*1024) current_mb,
            SUM(DECODE(a.autoextensible, 'NO', a.bytes/(1024*1024), GREATEST (a.maxbytes/(1024*1024),a.bytes/(1024*1024)))) max_mb
        FROM
            dba_data_files a
        GROUP BY tablespace_name
    ) a,
    (
        SELECT
            d.tablespace_name, SUM(NVL(c.bytes/(1024*1024),0)) free
        FROM
            dba_tablespaces d,
            dba_free_space c
        WHERE
            d.tablespace_name = c.tablespace_name(+)
        --AND d.contents='PERMANENT'
        --AND d.status='ONLINE'
        GROUP BY
            d.tablespace_name
    ) c
WHERE
    a.tablespace_name = c.tablespace_name
ORDER BY 6;

=== DATAFILE

col tablespace_name format A40 heading tablespace col file_name format a70 col tot_KB format 99,999,999 col tot_freeMB format 9,999,999 col Kb_max format 9,999,999 Set lines 300 select

 f.tablespace_name,
 f.file_name,
 f.bytes/1024/1024 tot_MB,
 f.MAXBYTES/1024/1024 MAX_MB,
 sum(nvl(s.bytes,0)/1024/1024) tot_freeMB,
 max(nvl(s.bytes,0)/1024/1024) maxextent_freeMB,
 f.INCREMENT_BY INCREMENT_BY ,
( (sum(nvl(s.bytes,0)/1024/1024)) / (f.bytes/1024/1024) )*100 Pct_available

from

 sys.dba_data_files f,
 sys.dba_free_space s

where

 f.file_id = s.file_id(+)

group by

 f.tablespace_name,
 f.file_name,
 f.MAXBYTES/1024/1024,
 f.bytes/1024/1024,

INCREMENT_BY order by f.tablespace_name,Pct_available;

— REDO column member format a50; select group#, member from v$logfile order by 1;

— Temp Tablespaces set lines 300 col a.name format A20 col FILE_NAME format A80 select b.TS#,a.name FILE_NAME ,b.name TABLESPACE_NAME , a.bytes/1024/1024/1024 SIZE_GB, a.STATUS STATUS from v$tempfile a, v$tablespace b where a.ts#=b.ts#;

set lines 300 col a.name format A20 col FILE_NAME format A60 select b.TS#,a.name FILE_NAME ,b.name TABLESPACE_NAME , a.STATUS STATUS from v$tempfile a, v$tablespace b where a.ts#=b.ts#;

— SYSAUX —

– Occupants:

select occupant_name,occupant_desc,space_usage_kbytes/1024 space_usage_Mbytes from v$sysaux_occupants order by space_usage_kbytes DESC;

Si les AWR sont trop gros, vérifier la durée de rétention (cf DB -SYSAUX énorme en 19c si 19C)

Set lines 300 Col SNAP_INTERVAL format A30 Col RETENTION format A30 Col SRC_DBNAME format A30 select * from DBA_HIST_WR_CONTROL;

select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

— AJOUT DATAFILE —

Il faut au préalable vérifier la taille des blocs dans l'instance

set lines 300 col name format A20 col display_value format A20 col ISINSTANCE_MODIFIABLE format A20

select num,name,type,display_value,ISINSTANCE_MODIFIABLE,DESCRIPTION FROM v$parameter WHERE name = 'db_block_size';

Ou

show parameter db_block_size

Compléter avec DB_BLOCK vs KB

ALTER TABLESPACE PAT_PIL_STA ADD DATAFILE '/oracle/oradata/PATIENT/u03/PAT_PIL_STA03.dbf' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 8192M ;

Si l'option NEXT xxx n'est pas précisée, Oracle prendra la valeur pas défaut qui est 8KB. Ce n'est pas bon car le datafile, avec une valeur EXTEND aussi petite, passera son temps à s'étendre et les perf risquent d'être dégradées

— Agrandir DATAFILE —

ALTER DATABASE DATAFILE '/u04/oradata/his/data/core_dataHIS70.dbf' RESIZE 20G;

— changer MAX SIZE —-

ALTER DATABASE DATAFILE 'D:\DBDATA\TESTPTIX02.ORA' AUTOEXTEND ON MAXSIZE 3840M;

— Générer ALTER DATAFILE select 'alter database datafile '||FILE_NAME||' autoextend on next 128M;' from dba_data_files where AUTOEXTENSIBLE = 'YES' and INCREMENT_BY/BLOCKS*BYTES/1024 < 131072;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/SIATIC_DATA3/oracle/SIATICI3/tempfile/temp.dbf' SIZE 5G;

alter database default temporary tablespace temp;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;