Check status :
select instance_name,status,database_status,TO_CHAR (startup_time, 'dd-mon-yyyy hh24:mi:ss') start_time from v$instance;
Show datafiles for tablespace :
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';
alter tablespace <TABLESPACE_NAME> add datafile '</path/to/datafile>' size <1234>[K|M|G];
Convert datafile to autoextendable :
alter database datafile '</path/to/datafile>' autoextend on maxsize <1234>[K|M|G];
Show tablespace usage overview :
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%)"
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
Tablespace growth report :
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;
Tablespace growth analysis and prediction (single tbsp):
##############################################
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;
/
Tablespace growth analysis and prediction (all tbsps except undo and temp) :
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;
/
Show Temp usage :
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'
/
Show me with home much space can be shrinked current datafiles – Advanced(autoextend)
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;
Show me with home much space can be shrinked current datafiles – Advanced(No autoextend)
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;
Show me the tablespaces, devided by all objects in them
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 /
###SYSAUX DEFREGMENT####
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;
Temp usage :
SELECT * FROM dba_temp_free_space;
Shrink temp tablespace :
ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;
Resize TEMP datafile :
alter database tempfile '<tempfile>' resize XX M;
Compress & delete old trace files
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 {} \;
Delete old trace files
find . -xdev -name "*.aud" -type f -mtime +15 -exec gzip -9f {} \;
find . -xdev -name "*.aud.gz" -type f -mtime +30 -exec rm {} \;