• Lister les tablespaces par défaut d'un user (sysdba)
SELECT username,default_tablespace FROM dba_users WHERE default_tablespace='TOOLS';


  • Lister le tablespace par défaut d'un user
SELECT default_tablespace FROM user_users;


  • Lister les tablespaces + taille (Alloué - Occupé - Libre)
clear breaks
clear computes
clear COLUMNS
SET pagesize 50
SET linesize 120
SET heading ON
COLUMN tablespace_name heading 'Tablespace' justify LEFT format a20 truncated
COLUMN tbsize heading 'Size (Mb) ' justify LEFT format 9,999,999.99
COLUMN tbused heading 'Used (Mb) ' justify RIGHT format 9,999,999.99
COLUMN tbfree heading 'Free (Mb) ' justify RIGHT format 9,999,999.99
COLUMN tbusedpct heading 'Used % ' justify LEFT format a8
COLUMN tbfreepct heading 'Free % ' justify LEFT format a8
break ON report
compute SUM label 'Totals:' OF tbsize tbused tbfree ON report
SELECT t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
FROM dba_tablespaces t,
(SELECT tablespace_name, round(SUM(bytes)/1024/1024,2) bytes
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name, round(SUM(bytes)/1024/1024,2) bytes
FROM dba_temp_files
GROUP BY tablespace_name ) a,
(SELECT e.tablespace_name, round(SUM(e.bytes)/1024/1024,2) bytes
FROM dba_segments e
GROUP BY e.tablespace_name
UNION
SELECT tablespace_name, SUM(max_size) bytes
FROM v$sort_segment
GROUP BY tablespace_name) b,
(SELECT f.tablespace_name, round(SUM(f.bytes)/1024/1024,2) bytes
FROM dba_free_space f
GROUP BY f.tablespace_name
UNION
SELECT tmp.tablespace_name, (SUM(bytes/1024/1024) - SUM(max_size)) bytes
FROM dba_temp_files tmp, v$sort_segment sort
WHERE tmp.tablespace_name = sort.tablespace_name
GROUP BY tmp.tablespace_name) c
WHERE
t.tablespace_name = a.tablespace_name (+)
AND t.tablespace_name = b.tablespace_name (+)
AND t.tablespace_name = c.tablespace_name (+)
ORDER BY t.tablespace_name;


  • Lister les tablespaces + fichiers et status
SET linesize 200
COL "STATUS" FORMAT A10
COL "TABLESPACE_NAME" FORMAT A20
COL "FILE_NAME" FORMAT A80
SELECT 
FILE_ID, STATUS, TABLESPACE_NAME, FILE_NAME
FROM 
DBA_DATA_FILES
ORDER BY FILE_ID; 


  • Si un tablespace n'est plus rattaché à un datafile
ALTER DATABASE DATAFILE '<filename>' OFFLINE DROP;
DROP TABLESPACE <TableSpace>;


  • Vérifier le contenu du TBS SYSAUX
SELECT occupant_desc, space_usage_kbytes/1024 AS usage_GB FROM v$sysaux_occupants WHERE space_usage_kbytes > 0 ORDER BY space_usage_kbytes DESC;


  • Savoir si un TBS est en AUTOEXTEND
SELECT tablespace_name, file_name ,AUTOEXTENSIBLE,MAXBYTES,INCREMENT_BY FROM dba_data_files ORDER BY 1,2;