===== tablespace & datafile =====
== DB SIZE ==
<code PLSQL>
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
</code>
  * Check the CDB Size of the databases
<code PLSQL>
select sum(bytes)/1024/1024/1024 from cdb_data_files;
</code>
  * Check the PDB Size of the databases
<code PLSQL>
select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs;
</code>

=== tablespace ===
<code PLSQL>
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;
</code>
\\  
=== DATAFILE ===
<code PLSQL>
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;
</code>
\\  
=== REDO ===
<code PLSQL>
column member format a50;
select group#, member from v$logfile order by 1;
</code>
\\  
=== Temp Tablespaces ===
<code PLSQL>
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#;
</code>

<code PLSQL>
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#;
</code>
\\  
=== SYSAUX ===
== Occupants: ==
 <code PLSQL>
select occupant_name,occupant_desc,space_usage_kbytes/1024 space_usage_Mbytes from v$sysaux_occupants
order by space_usage_kbytes DESC; 
</code>

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

<code PLSQL>
Set lines 300
Col SNAP_INTERVAL format A30
Col RETENTION format A30
Col SRC_DBNAME format A30
select * from DBA_HIST_WR_CONTROL;
</code>
 <code PLSQL>
select min(snap_id),MAX(snap_id) from dba_hist_snapshot;
</code>
 \\  
=== AJOUT DATAFILE ===

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

<code PLSQL>
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';
 </code>


Ou
<code PLSQL>
show parameter db_block_size
 </code>

Compléter avec DB_BLOCK vs KB

<code PLSQL>
ALTER TABLESPACE PAT_PIL_STA ADD DATAFILE '/oracle/oradata/PATIENT/u03/PAT_PIL_STA03.dbf' SIZE 1G AUTOEXTEND ON NEXT  128M MAXSIZE 8192M ;
</code>
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 ===
<code PLSQL>
ALTER DATABASE DATAFILE '/u04/oradata/his/data/core_dataHIS70.dbf' RESIZE 20G;
</code>

\\  
=== changer MAX SIZE ===
<code PLSQL>
ALTER DATABASE DATAFILE 'D:\DBDATA\TESTPTIX02.ORA' AUTOEXTEND ON MAXSIZE 3840M;
</code>
\\  
=== Générer ALTER DATAFILE ===
<code PLSQL>
select 'alter database datafile '''||FILE_NAME||''' autoextend on next 128M;' from dba_data_files where AUTOEXTENSIBLE = 'YES' and INCREMENT_BY/BLOCKS*BYTES/1024 < 131072;
</code>

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

<code PLSQL>
alter database default temporary tablespace temp;
</code>

<code PLSQL>
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
</code>