Cacher is the code snippet organizer for pro developers

set pages 1000
set lines 200
SELECT tablespace_name, autoextend,
round ((taille_courante / 1024 / 1024), 0) as taille_courante,
round ((libre_taille_courante_space / 1024 / 1024), 0) as libre_taille_courante,
round (((taille_courante - libre_taille_courante_space) / 1024 / 1024), 0) as taille_utilisee,
round ((max_bytes_alloues / 1024 / 1024), 0) as maxtaille,
round (((max_bytes_alloues / 1024 / 1024) - ((taille_courante - libre_taille_courante_space) / 1024 / 1024)), 0) as taille_restante,
round(100*round(((max_bytes_alloues/1024/1024)-((taille_courante-libre_taille_courante_space)/1024/1024)),0)/(max_bytes_alloues/1024/1024),0) as "%Libre"
FROM
(SELECT tablespace_name, autoextensible autoextend, decode(sum(maxbytes),0,sum(bytes),sum(maxbytes)) max_bytes_alloues, sum (bytes) as taille_courante
FROM sys.dba_data_files
GROUP BY tablespace_name,autoextensible),
(SELECT tablespace_name tsname, nvl (sum (free), 0) as libre_taille_courante_space
from (
(SELECT b.tablespace_name, SUM (a.BYTES) free
FROM dba_free_space a, dba_tablespaces b
WHERE a.tablespace_name (+) = b.tablespace_name
GROUP BY b.tablespace_name
)
UNION
(SELECT tablespace_name, SUM (BYTES) free
FROM dba_undo_extents
WHERE status = 'EXPIRED' or status='UNEXPIRED'
GROUP BY tablespace_name
)
)
GROUP BY tablespace_name)
WHERE tablespace_name = tsname
UNION
SELECT tablespace_name, autoextend,
round ((taille_courante / 1024 / 1024), 0) as taille_courante,
round ((libre_taille_courante_space / 1024 / 1024), 0) as libre_taille_courante,
round (((taille_courante - libre_taille_courante_space) / 1024 / 1024), 0) as taille_utilisee,
round ((max_bytes_alloues / 1024 / 1024), 0) as maxtaille,
round (((max_bytes_alloues / 1024 / 1024) - ((taille_courante - libre_taille_courante_space) / 1024 / 1024)), 0) as taille_restante,
round(100*round(((max_bytes_alloues/1024/1024)-((taille_courante-libre_taille_courante_space)/1024/1024)),0)/(max_bytes_alloues/1024/1024),0) as "%Libre"
FROM
(SELECT tablespace_name, autoextensible autoextend, decode(sum(maxbytes),0,sum(bytes),sum(maxbytes)) max_bytes_alloues, sum (bytes) as taille_courante
FROM sys.dba_temp_files
GROUP BY tablespace_name,autoextensible),
(SELECT tablespace_name tsname, nvl (sum (free), 0) as libre_taille_courante_space
from (
(SELECT b.tablespace_name, SUM (a.BYTES) free
FROM dba_free_space a, dba_tablespaces b
WHERE a.tablespace_name (+) = b.tablespace_name
GROUP BY b.tablespace_name
)
UNION
(SELECT tablespace_name, SUM (BYTES) free
FROM dba_undo_extents
WHERE status = 'EXPIRED' or status='UNEXPIRED'
GROUP BY tablespace_name
)
)
GROUP BY tablespace_name)
WHERE tablespace_name = tsname
ORDER BY tablespace_name
;

SELECT tablespace_name,
segment_name ,
extents ,
max_extents ,
bytes ,
owner ,
segment_type ,
next_extent
FROM dba_segments
WHERE tablespace_name='TON_TABLESPACE'
ORDER BY next_extent desc ,
segment_type ,
segment_name;

col FILE_NAME for A60;
SET LINE 180
select TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024, MAXBYTES/1024/1024 from dba_data_files;

SET LINES 132
SET pages 200
col tablespace_name format A30
col cont format A4
col file_name format A55
col pct_used format 999.99
SET verify off
SET trimout ON
  
  
SELECT
   t.tablespace_name, substr(t.contents,1,4) AS "Cont",
   (df.totalspace - nvl(fs.freespace,0)) "Used MB", nvl(fs.freespace,0) "Free MB",
   df.totalspace "Total MB",
   round(100 * (( df.totalspace - nvl(fs.freespace,0)) / greatest(1,df.totalspace))) "Pct. Used",
   df.autoext AutoExtensible, df.MaxSizeMb "Max Size Mb",
   round(100 * ( (df.totalspace - nvl(fs.freespace,0)) / greatest(1,df.MaxSizeMb))) "Pct. Max used",
   round((df.totalspace - nvl(fs.freespace,0))/0.9 - df.totalspace , 0) AS "Miss MB to 90%"
FROM
   dba_tablespaces t,
   (SELECT
      tablespace_name, round(sum(bytes) / 1048576) TotalSpace,
      round(sum(decode(autoextensible, 'YES', greatest(bytes, maxbytes), bytes))/1024/1024, 0) MaxSizeMb,
      max (autoextensible) AutoExt
   FROM dba_data_files
   GROUP BY tablespace_name
  UNION ALL
   SELECT
      tablespace_name, round(sum(bytes) / 1048576) TotalSpace,
      round(sum(decode(autoextensible, 'YES', greatest(bytes, maxbytes), bytes))/1024/1024, 0) MaxSizeMb,
      max (autoextensible) AutoExt
   FROM dba_temp_files
   GROUP BY tablespace_name
   ) df,
   (SELECT
      tablespace_name, round(sum(bytes) / 1048576) FreeSpace
   FROM dba_free_space GROUP BY tablespace_name
   ) fs
WHERE
   t.tablespace_name = df.tablespace_name
   AND df.tablespace_name = fs.tablespace_name (+)
ORDER BY 9 DESC, df.tablespace_name
;

col "Tablespace" for a40
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

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 ;

# Requete 1
COL TABLESPACE_NAME FOR A16
SELECT
TABLESPACE_NAME, CURRENT_USERS,
TOTAL_BLOCKS, USED_BLOCKS, FREE_BLOCKS,
MAX_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS
FROM V$SORT_SEGMENT
ORDER BY TABLESPACE_NAME;

# Requete 2
SELECT * FROM DBA_TEMP_FREE_SPACE;

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT 
   A.tablespace_name tablespace, 
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM 
   v$sort_segment A,
(
SELECT 
   B.name, 
   C.block_size, 
   SUM (C.bytes) / 1024 / 1024 mb_total
FROM 
   v$tablespace B, 
   v$tempfile C
WHERE 
   B.ts#= C.ts#
GROUP BY 
   B.name, 
   C.block_size
) D
WHERE 
   A.tablespace_name = D.name
GROUP by 
   A.tablespace_name, 
   D.mb_total
/