====== Oracle Tablespaces & Datafiles ======

===== Data Tablespaces =====
**Check status :**
<sxh sql>
select instance_name,status,database_status,TO_CHAR (startup_time, 'dd-mon-yyyy hh24:mi:ss') start_time from v$instance;
</sxh>

**Show datafiles for tablespace :**
<sxh sql>
col "DATA FILE" format a50
col "CURR.SIZE [MB]" format 999,999,999.99
col "MAX.SIZE [MB]" format 999,999,999.99
col "AUTOEX" format a6
SELECT FILE_NAME "DATA FILE", BYTES/1024/1024 "CURR.SIZE [MB]", 
MAXBYTES/1024/1024 "MAX.SIZE [MB]", 
AUTOEXTENSIBLE "AUTOEX" FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME='&TABLESPACE_NAME';
</sxh>

== Add datafile to tablespace ==
<sxh sql>
alter tablespace <TABLESPACE_NAME> add datafile '</path/to/datafile>' size <1234>[K|M|G]; 
</sxh>

**Convert datafile to autoextendable :**
<sxh sql>
alter database datafile '</path/to/datafile>' autoextend on maxsize <1234>[K|M|G];
</sxh>

**Show tablespace usage overview :**
<sxh sql>
clear columns
set lines 300 pages 100
column tablespace format a20
column total_mb format 999,999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading GRAPH
--(X=5%)&quot;
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report
set lines 200 pages 100
SELECT  total.ts tablespace,
       DECODE(total.mb,null,'OFFLINE',dbat.status) status,
total.mb total_mb,
NVL(total.mb - free.mb,total.mb) used_mb,
NVL(free.mb,0) free_mb,
       DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE(free.mb,
                            null,'XXXXXXXXXXXXXXXXXXXX',
                            NVL(RPAD(LPAD('X',TRUNC((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
 '--------------------'))||']'
        END as GRAPH
FROM
(SELECT tablespace_name ts, SUM(bytes)/1024/1024 mb FROM dba_data_files GROUP BY tablespace_name) total,
(SELECT tablespace_name ts, SUM(bytes)/1024/1024 mb FROM dba_free_space GROUP BY tablespace_name) free,
       dba_tablespaces dbat
where total.ts=free.ts(+) and
     total.ts=dbat.tablespace_name
UNION ALL
SELECT
d.tablespace_name tablespace
, d.status status
, (NVL(a.bytes, 0))/1024/1024 total_mb
, (NVL(t.bytes, 0))/1024/1024 used_mb
, NVL(NVL(a.bytes, 0) - NVL(t.bytes, 0),NVL(a.bytes, 0))/1024/1024 free_mb
, TRUNC(NVL(t.bytes / a.bytes * 100, 0)) pct_used
, '['||DECODE((NVL(a.bytes, 0) - NVL(t.bytes, 0)),0,'XXXXXXXXXXXXXXXXXXXX',
             NVL(RPAD(LPAD('X',(TRUNC(ROUND(((NVL(t.bytes, 0))/(NVL(a.bytes, 0)))*100,2)/5)),'X'),20,'-'),
               '--------------------'))||']'
FROM
sys.dba_tablespaces d
, ( SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name
) a
, ( SELECT tablespace_name, SUM(bytes_cached) bytes
FROM v$temp_extent_pool
GROUP BY tablespace_name
) t
, v$sort_segment s
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
order by 1
/
ttitle off
clear columns
</sxh>

**Tablespace growth report :**
<sxh sql>
set heading on
set linesize 5500
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;
</sxh>

**Tablespace growth analysis and prediction (single tbsp):**
<sxh sql>
##############################################
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');

END;
/
</sxh>

**Tablespace growth analysis and prediction (all tbsps except undo and temp) :**
<sxh sql>
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';

BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');
NULL;
END;
END LOOP;
END;
/
</sxh>

==== Temporary tablespaces ====
**Show Temp usage :**
<sxh sql>
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   off

COLUMN tablespace_name       FORMAT a18               HEAD 'Tablespace Name'
COLUMN tablespace_status     FORMAT a9                HEAD 'Status'
COLUMN tablespace_size       FORMAT 999,999,999,999   HEAD 'Size'
COLUMN used                  FORMAT 999,999,999,999   HEAD 'Used'
COLUMN used_pct              FORMAT 999               HEAD 'Pct. Used'
COLUMN current_users         FORMAT 9,999             HEAD 'Current Users'

BREAK ON report
COMPUTE SUM OF tablespace_size  ON report
COMPUTE SUM OF used             ON report
COMPUTE SUM OF current_users    ON report

SELECT
    d.tablespace_name                      tablespace_name
  , d.status                               tablespace_status
  , NVL(a.bytes, 0)                        tablespace_size
  , NVL(t.bytes, 0)                        used
  , TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct
  , NVL(s.current_users, 0)                current_users
FROM
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_temp_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
      from v$temp_extent_pool
      group by tablespace_name
    ) t
  , v$sort_segment  s
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.tablespace_name = s.tablespace_name(+)
  AND d.extent_management like 'LOCAL'
  AND d.contents like 'TEMPORARY'
/
</sxh>


Show me with home much space can be shrinked current datafiles -- Advanced(autoextend)
<sxh bash>
SET pagesize 300
SET linesize 200
COL "Resize Command" for a110
SELECT 'alter database datafile '''||file_name||''' resize '||
     DECODE(trunc(ceil( (nvl(hwm,1)*(size_db_block))/1024/1024 ) /10),0 ,10, ceil( (nvl(hwm,1)* (size_db_block))/1024/1024 )) ||'M;' "Resize Command",
 AUTOEXTENSIBLE , bytes/1024/1024 "CurrentSize(Mb)" ,
 ( (bytes/1024/1024) - ceil( (nvl(hwm,1)* (size_db_block))/1024/1024 ) ) "FreeSize(Mb)" , TABLESPACE_NAME tablespace
FROM dba_data_files a,
       ( SELECT file_id, max(block_id+blocks-1) AS hwm FROM dba_extents GROUP BY file_id ) b ,
    (SELECT TO_NUMBER(value) AS size_db_block FROM v$parameter WHERE name = 'db_block_size') c
 WHERE a.file_id = b.file_id(+) AND AUTOEXTENSIBLE='YES'
AND ceil(blocks*(c.size_db_block)/1024/1024)- ceil((nvl(hwm,1)*(c.size_db_block))/1024/1024 ) > 0  ORDER BY "FreeSize(Mb)" ASC;  
</sxh>


Show me with home much space can be shrinked current datafiles -- Advanced(No autoextend)
<sxh bash>
SET pagesize 300
SET linesize 200
COL "Resize Command" for a110
SELECT 'alter database datafile '''||file_name||''' resize '||
     DECODE(trunc(ceil( (nvl(hwm,1)*(size_db_block))/1024/1024 ) /10),0 ,10, ceil( (nvl(hwm,1)* (size_db_block))/1024/1024 )) ||'M;' "Resize Command",
 AUTOEXTENSIBLE , bytes/1024/1024 "CurrentSize(Mb)" ,
 ( (bytes/1024/1024) - ceil( (nvl(hwm,1)* (size_db_block))/1024/1024 ) ) "FreeSize(Mb)"
FROM dba_data_files a,
       ( SELECT file_id, max(block_id+blocks-1) AS hwm FROM dba_extents GROUP BY file_id ) b ,
    (SELECT TO_NUMBER(value) AS size_db_block FROM v$parameter WHERE name = 'db_block_size') c
 WHERE a.file_id = b.file_id(+) AND AUTOEXTENSIBLE='NO'
AND ceil(blocks*(c.size_db_block)/1024/1024)- ceil((nvl(hwm,1)*(c.size_db_block))/1024/1024 ) > 0  ORDER BY "FreeSize(Mb)" ASC;  
</sxh>


Show me the tablespaces, devided by all objects in them
<sxh bash>
set lines 132
set pages 90
break on tablespace_name skip 1
compute sum of sum(bytes)/1024/1024 sum(extents) on tablespace_name

col count(*)             format 99999999   heading "Number Of|Segments"
col sum(extents)         format 99999999   heading "Number Of|Extents"
col sum(bytes)/1024/1024 format 999,999.99 heading "Total|Size MB"



select        tablespace_name
,        segment_type
,        count(*)
,        sum(extents)
,        sum(bytes)/1024/1024
from        dba_segments
where owner='&owner'
group by    segment_type
,        tablespace_name
order by    tablespace_name, segment_type
/
</sxh>

###SYSAUX DEFREGMENT####
<sxh bash>
set lines 112
set pages 10000

col TSname heading 'TSpace|Name|||'
col TSname format a25
col TSstatus heading 'TSpace|Status|||'
col TSstatus format a9
col TSSizeMb heading 'TSpace|Size|Mb||'
col TSSizeMb format 99999
col TSUsedMb heading 'TSpace|Used|Space|Mb|'
col TSUsedMb format 99999
col TSFreeMb heading 'TSpace|Free|Space|Mb|'
col TSFreeMb format 99999
col TSUsedPrct heading 'TSpace|Used|Space|%|'
col TSUsedPrct format 99999
col TSFreePrct heading 'TSpace|Free|Space|%|'
col TSFreePrct format 99999
col TSSegUsedMb heading 'TSpace|Segmt|Space|Mb|'
col TSSegUsedMb format 99999
col TSExtUsedMb heading 'TSpace|Extent|Space|Mb|'
col TSExtUsedMb format 99999
col AutoExtFile heading 'Auto|Extend|File|?|'
col AutoExtFile format a6
col TSMaxSizeMb heading 'TSpace|MaxSize|Mb||'
col TSMaxSizeMb format a6
col TSMaxUsedPrct heading 'TSpace|Maxed|Used|Space|%'
col TSMaxUsedPrct format a6
col TSMaxFreePrct heading 'TSpace|Maxed|Free|Space|%'
col TSMaxFreePrct format a6

WITH
  ts_total_space AS (SELECT
                       TableSpace_name,
                       SUM(bytes) as bytes,
                       SUM(blocks) as blocks,
                       SUM(maxbytes) as maxbytes
                     FROM dba_data_files
                     GROUP BY TableSpace_name),
  ts_free_space AS (SELECT
                      ddf.TableSpace_name,
                      NVL(SUM(dfs.bytes),0) as bytes,
                      NVL(SUM(dfs.blocks),0) as blocks
                    FROM
                      dba_data_files ddf,
                      dba_free_space dfs
                    WHERE ddf.file_id = dfs.file_id(+)
                    GROUP BY ddf.TableSpace_name),
  ts_total_segments AS (SELECT
                          TableSpace_name,
                          SUM(bytes) as bytes,
                          SUM(blocks) as blocks
                        FROM dba_segments
                        GROUP BY TableSpace_name),
ts_total_extents AS (SELECT
                       TableSpace_name,
                       SUM(bytes) as bytes,
                       SUM(blocks) as blocks
                     FROM dba_extents
                     GROUP BY TableSpace_name)
SELECT
  dt.TableSpace_name as "TSname",
  dt.status as "TSstatus",
  ROUND(ttsp.bytes/1024/1024,0) as "TSSizeMb",
  ROUND((ttsp.bytes-tfs.bytes)/1024/1024,0) as "TSUsedMb",
  ROUND(tfs.bytes/1024/1024,0) as "TSFreeMb",
  ROUND((ttsp.bytes-tfs.bytes)/ttsp.bytes*100,0) as "TSUsedPrct",
  ROUND(tfs.bytes/ttsp.bytes*100,0) as "TSFreePrct",
  ROUND(ttse.bytes/1024/1024,0) as "TSSegUsedMb",
  ROUND(tte.bytes/1024/1024,0) as "TSExtUsedMb",
  CASE
    WHEN ttsp.maxbytes = 0 THEN 'No' ELSE 'Yes'
  END as "AutoExtFile",
  CASE
    WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND(ttsp.maxbytes/1024/1024,0))
  END as "TSMaxSizeMb",
  CASE
    WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.bytes-tfs.bytes)/ttsp.maxbytes*100,0))
  END as "TSMaxUsedPrct",
  CASE
    WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.maxbytes-(ttsp.bytes-tfs.bytes))/ttsp.maxbytes*100,0))
  END as "TSMaxFreePrct"
FROM
  dba_TableSpaces dt,
  ts_total_space ttsp,
  ts_free_space tfs,
  ts_total_segments ttse,
  ts_total_extents tte
WHERE dt.TableSpace_name = ttsp.TableSpace_name(+)
AND dt.TableSpace_name = tfs.TableSpace_name(+)
AND dt.TableSpace_name = ttse.TableSpace_name(+)
AND dt.TableSpace_name = tte.TableSpace_name(+)
AND dt.TableSpace_name = 'SYSAUX'
;

set lines 130
set pages 10000

col SgmntSize heading 'Sgmnt|Size|Mb'
col SgmntSize format 99999
col TSname heading 'TSpace|Name|'
col TSname format a25
col SgmntOwner heading 'Sgmnt|Owner|'
col SgmntOwner format a15
col SgmntName heading 'Sgmnt|Name|'
col SgmntName format a35
col SgmntType heading 'Sgmnt|Type|'
col SgmntType format a5

SELECT
  ROUND(SUM(ds.bytes)/1024/1024,0) as "SgmntSize",
  ds.TableSpace_name as "TSname",
  ds.owner as "SgmntOwner",
  ds.segment_name as "SgmntName",
  ds.segment_type as "SgmntType"
FROM dba_segments ds
WHERE ds.segment_type IN ('TABLE','INDEX')
AND TableSpace_name = 'SYSAUX'
GROUP BY
  ds.TableSpace_name,
  ds.owner,
  ds.segment_name,
  ds.segment_type
ORDER BY "SgmntSize" DESC;
</sxh>


**Temp usage :**
<sxh sql>
SELECT * FROM dba_temp_free_space;
</sxh>

**Shrink temp tablespace :**
<sxh sql>
ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;
</sxh>

**Resize TEMP datafile :**
<sxh sql>
alter database tempfile '<tempfile>' resize XX M;
</sxh>
=== Filesystems ===

Compress & delete old trace files
<sxh bash>
find . -xdev -name "*.trc" -type f -mtime +10 -exec gzip -9f {} \;
find . -xdev -name "*.trm" -type f -mtime +10 -exec gzip -9f {} \;
find . -xdev -name "*.trc.gz" -type f -mtime +20 -exec rm {} \;
find . -xdev -name "*.trm.gz" -type f -mtime +20 -exec rm {} \;
</sxh>

Delete old trace files
<sxh bash>
find . -xdev -name "*.aud" -type f -mtime +15 -exec gzip -9f {} \;
find . -xdev -name "*.aud.gz" -type f -mtime +30 -exec rm {} \;
</sxh>