Tablespace info .. ....
http://anuj-singh.blogspot.com/2010/05/oracle-tablespace-cron-job.html
https://anuj-singh.blogspot.com/search?q=fs.tablespace_name+tablespace%2C+num_files%2Cnum_filesOracle Tablespace space report
http://anuj-singh.blogspot.com/2011/10/oracle-space-used-in-tablespace.html
https://anuj-singh.blogspot.com/2025/08/oracle-datafile-info.html
or set head off verify off echo off pages 1500 linesize 110 feedback off alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'; select 'TABLESPACE_NAME......................................: '||TABLESPACE_NAME , 'BLOCK_SIZE...........................................: '||BLOCK_SIZE , 'INITIAL_EXTENT.......................................: '||INITIAL_EXTENT , 'NEXT_EXTENT..........................................: '||NEXT_EXTENT , 'MIN_EXTENTS..........................................: '||MIN_EXTENTS , 'MAX_EXTENTS..........................................: '||MAX_EXTENTS , 'MAX_SIZE.............................................: '||MAX_SIZE , 'PCT_INCREASE.........................................: '||PCT_INCREASE , 'MIN_EXTLEN...........................................: '||MIN_EXTLEN , 'STATUS...............................................: '||STATUS , 'CONTENTS.............................................: '||CONTENTS , 'LOGGING..............................................: '||LOGGING , 'FORCE_LOGGING........................................: '||FORCE_LOGGING , 'EXTENT_MANAGEMENT....................................: '||EXTENT_MANAGEMENT , 'ALLOCATION_TYPE......................................: '||ALLOCATION_TYPE , 'PLUGGED_IN...........................................: '||PLUGGED_IN , 'SEGMENT_SPACE_MANAGEMENT ..........................: '||SEGMENT_SPACE_MANAGEMENT , 'DEF_TAB_COMPRESSION..................................: '||DEF_TAB_COMPRESSION , 'RETENTION............................................: '||RETENTION , 'BIGFILE..............................................: '||BIGFILE , 'PREDICATE_EVALUATION.................................: '||PREDICATE_EVALUATION, 'ENCRYPTED............................................: '||ENCRYPTED , 'COMPRESS_FOR ........................................: '||COMPRESS_FOR , 'DEF_INMEMORY.........................................: '||DEF_INMEMORY , 'DEF_INMEMORY_PRIORITY................................: '||DEF_INMEMORY_PRIORITY , 'DEF_INMEMORY_DISTRIBUTE.............................: '||DEF_INMEMORY_DISTRIBUTE , 'DEF_INMEMORY_COMPRESSION.............................: '||DEF_INMEMORY_COMPRESSION , 'DEF_INMEMORY_DUPLICATE...............................: '||DEF_INMEMORY_DUPLICATE , 'SHARED...............................................: '||SHARED , 'DEF_INDEX_COMPRESSION ...............................: '||DEF_INDEX_COMPRESSION , 'INDEX_COMPRESS_FOR...................................: '||INDEX_COMPRESS_FOR , 'DEF_CELLMEMORY ......................................: '||DEF_CELLMEMORY , 'DEF_INMEMORY_SERVICE.................................: '||DEF_INMEMORY_SERVICE , 'DEF_INMEMORY_SERVICE_NAME............................: '||DEF_INMEMORY_SERVICE_NAME, 'LOST_WRITE_PROTECT...................................: '||LOST_WRITE_PROTECT , 'CHUNK_TABLESPACE ....................................: '||CHUNK_TABLESPACE from dba_tablespaces where 1=1 -- and TABLESPACE_NAME='XXX' ;
SET LINESIZE 300 PAGESIZE 200
CLEAR COLUMNS
-- Format Columns
COL TABLESPACE_NAME FOR A28 HEADING 'Tablespace Name'
COL PERUSD FOR 999.0 HEADING 'Used % (Max)'
COL PERC FOR 999.0 HEADING 'Used % (Cur)'
COL TOTALM FOR 999,999,999 HEADING 'Max (MB)'
COL TOTAL FOR 999,999,999 HEADING 'Alloc (MB)'
COL USED FOR 999,999,999 HEADING 'Used (MB)'
COL FREEM FOR 999,999,999 HEADING 'Max Free (MB)'
COL FREE FOR 999,999,999 HEADING 'Alloc Free (MB)'
COL FREEM_GB FOR 999,999.0 HEADING 'Max Free (GB)'
COL FREE_GB FOR 999,999.0 HEADING 'Alloc Free (GB)'
COL DBF_NO FOR 999 HEADING 'Files'
COL WARN FOR 999 HEADING 'Warn'
COL CRIT FOR 999 HEADING 'Crit'
COL "OK?" FOR A4 HEADING 'OK?'
SELECT
m.tablespace_name,
round(max(m.used_percent), 1) PERUSD,
round(max(m.used_space * t.block_size) * 100 / (sum(d.bytes) * count(distinct d.file_id) / count(d.file_id)), 1) PERC,
round(max(m.tablespace_size * t.block_size / 1024 / 1024), 1) TOTALM,
round((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) / 1024 / 1024, 1) TOTAL,
round(max(m.used_space * t.block_size / 1024 / 1024), 1) USED,
round(max((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024), 1) FREEM,
round(max((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024 / 1024), 2) FREEM_GB,
round(((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) - max(m.used_space * t.block_size)) / 1024 / 1024, 1) FREE,
round(((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) - max(m.used_space * t.block_size)) / 1024 / 1024 / 1024, 2) FREE_GB,
count(distinct d.file_id) DBF_NO,
max(to_number(tt.warning_value)) WARN,
max(to_number(tt.critical_value)) CRIT,
max(case when m.used_percent > tt.warning_value OR m.used_percent > tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_thresholds tt
WHERE m.tablespace_name = t.tablespace_name
AND d.tablespace_name = t.tablespace_name
AND tt.metrics_name = 'Tablespace Space Usage'
AND tt.object_name IS NULL
-- AND d.tablespace_name LIKE 'DATA%' <<<<<
GROUP BY m.tablespace_name
ORDER BY 2 DESC;
SET LINESIZE 300 PAGESIZE 200
CLEAR COLUMNS
-- Format Columns
COL CON_ID FOR 999 HEADING 'Con ID'
COL CONTAINER_NAME FOR A15 HEADING 'Container Name'
COL TABLESPACE_NAME FOR A28 HEADING 'Tablespace Name'
COL PERUSD FOR 999.0 HEADING 'Used % (Max)'
COL PERC FOR 999.0 HEADING 'Used % (Cur)'
COL TOTALM FOR 999,999,999 HEADING 'Max (MB)'
COL TOTAL FOR 999,999,999 HEADING 'Alloc (MB)'
COL USED FOR 999,999,999 HEADING 'Used (MB)'
COL FREEM FOR 999,999,999 HEADING 'Max Free (MB)'
COL FREE FOR 999,999,999 HEADING 'Alloc Free (MB)'
COL FREEM_GB FOR 999,999.0 HEADING 'Max Free (GB)'
COL FREE_GB FOR 999,999.0 HEADING 'Alloc Free (GB)'
COL DBF_NO FOR 999 HEADING 'Files'
COL WARN FOR 999 HEADING 'Warn'
COL CRIT FOR 999 HEADING 'Crit'
COL "OK?" FOR A4 HEADING 'OK?'
SELECT
m.con_id,
c.name AS container_name,
m.tablespace_name,
round(max(m.used_percent), 1) PERUSD,
round(max(m.used_space * t.block_size) * 100 / (sum(d.bytes) * count(distinct d.file_id) / count(d.file_id)), 1) PERC,
round(max(m.tablespace_size * t.block_size / 1024 / 1024), 1) TOTALM,
round((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) / 1024 / 1024, 1) TOTAL,
round(max(m.used_space * t.block_size / 1024 / 1024), 1) USED,
round(max((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024), 1) FREEM,
round(max((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024 / 1024), 2) FREEM_GB,
round(((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) - max(m.used_space * t.block_size)) / 1024 / 1024, 1) FREE,
round(((sum(d.bytes) * count(distinct d.file_id)) / count(d.file_id) - max(m.used_space * t.block_size)) / 1024 / 1024 / 1024, 2) FREE_GB,
count(distinct d.file_id) DBF_NO,
max(to_number(tt.warning_value)) WARN,
max(to_number(tt.critical_value)) CRIT,
max(case when m.used_percent > tt.warning_value OR m.used_percent > tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM cdb_tablespace_usage_metrics m, cdb_tablespaces t, cdb_data_files d, cdb_thresholds tt, v$containers c
WHERE m.tablespace_name = t.tablespace_name
AND m.con_id = t.con_id
AND d.tablespace_name = t.tablespace_name
AND d.con_id = t.con_id
AND m.con_id = c.con_id
AND tt.con_id (+ = m.con_id
AND tt.metrics_name (+)= 'Tablespace' ---<<<<
AND tt.object_name IS NULL
GROUP BY m.con_id, c.name, m.tablespace_name
ORDER BY m.con_id, PERUSD DESC;
set linesize 300 pagesize 200
col TABLESPACE_NAME for a25
col PERUSD for 999999999
SELECT m.tablespace_name,
round(max(m.used_percent),1) PERUSD,
round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
round(max(m.used_space*t.block_size/1024/1024),1) USED,
round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,
count(distinct d.file_id) DBF_NO,
max(to_number(tt.warning_value)) WARN,
max(to_number(tt.critical_value)) CRIT,
max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_thresholds tt
WHERE m.tablespace_name =t.tablespace_name
AND d.tablespace_name =t.tablespace_name
and tt.metrics_name ='Tablespace Space Usage'
and tt.object_name is null
-- and d.tablespace_name ='DATA'
GROUP BY m.tablespace_name
order by 2 desc;
--- CDB
set linesize 300 pagesize 200
col TABLESPACE_NAME for a25
col PERUSD for 999999999
SELECT m.tablespace_name,
round(max(m.used_percent),1) PERUSD,
round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
round(max(m.used_space*t.block_size/1024/1024),1) USED,
round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,
count(distinct d.file_id) DBF_NO,
max(to_number(tt.warning_value)) WARN,
max(to_number(tt.critical_value)) CRIT,
max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM dba_tablespace_usage_metrics m, dba_tablespaces t, dba_data_files d, dba_thresholds tt
WHERE m.tablespace_name =t.tablespace_name
AND d.tablespace_name =t.tablespace_name
and tt.metrics_name ='Tablespace Space Usage'
and tt.object_name is null
-- and d.tablespace_name ='DATA'
GROUP BY m.tablespace_name
order by 2 desc;
Oracle tablespace Report
http://anuj-singh.blogspot.com/2011/10/oracle-space-used-in-tablespace.html
set linesize 300
COLUMN tablespace_name FORMAT a115
SELECT con_id , LOWER(LISTAGG(tablespace_name, ', ') WITHIN GROUP (ORDER BY tablespace_name)) AS tablespace_name
FROM cdb_tablespaces
group by con_id;
break on resized
with ts_history as (
select * from (
select v.name
, v.ts#
, s.instance_number
, h.tablespace_size
* p.value/1024/1024 ts_mb
, h.tablespace_maxsize
* p.value/1024/1024 max_mb
, h.tablespace_usedsize
* p.value/1024/1024 used_mb
, to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time
, lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
over (partition by v.ts# order by h.snap_id) last
, (h.tablespace_usedsize * p.value/1024/1024)
- lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
over (partition by v.ts# order by h.snap_id) incr
from dba_hist_tbspc_space_usage h
, dba_hist_snapshot s
, v$tablespace v
, dba_tablespaces t
, v$parameter p
where h.tablespace_id = v.ts#
and v.name = t.tablespace_name
and t.contents not in ('UNDO', 'TEMPORARY')
and p.name = 'db_block_size'
and h.snap_id = s.snap_id
order by v.name, h.snap_id asc)
where incr > 0)
select to_char(resize_time, 'YYYY-MM') as resized
, name
, sum(incr) incr
from ts_history
group by name
, to_char(resize_time, 'YYYY-MM')
order by 1, 3 desc;
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_name varchar2(200) := UPPER('DATA_2022');
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_count 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 count(*) INTO v_count FROM dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF v_count = 0 THEN
RAISE not_in_awr;
END IF ;
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 TABLESPCE '||V_TS_NAME||' !!!');
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;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE DOES NOT EXIST !!!');
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');
END;
/
Summary
========
1) Allocated Space: 11138772.61 MB (10877.71 GB)
2) Used Space: 11130561.36 MB (10869.69 GB)
3) Used Space Percentage: 99.93 %
History
========
1) Allocated Space on 25-MAR-24: 9767973.16 MB (9539.04 GB)
2) Current Allocated Space on 27-JUN-24: 11138772.61 MB (10877.71 GB)
3) Used Space on 25-MAR-24: 9760848.09 MB (9532.08 GB)
4) Current Used Space on 27-JUN-24: 11130561.36 MB (10869.69 GB)
5) Total growth during last 94 days between 25-MAR-24 and 27-JUN-24: 1369713.27 MB (1337.61 GB)
6) Per day growth during last 94 days: 14571.42 MB (14.23 GB)
Expected Growth
===============
1) Expected growth for next 30 days: 437142.53 MB (426.9 GB)
2) Expected growth for next 60 days: 874285.07 MB (853.79 GB)
3) Expected growth for next 90 days: 1311427.6 MB (1280.69 GB)
PL/SQL procedure successfully completed.
set linesize 300 pagesize 300
col USED_PERCENT for 99.99
col TABLESPACE_NAME for a20
SELECT tbm.con_id,tbm.TABLESPACE_NAME,
round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
tbm.USED_PERCENT
FROM cdb_tablespace_usage_metrics tbm
join cdb_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME
and tb.con_id = tbm.con_id
order by 6 desc
/
set linesize 300 pagesize 300
col USED_PERCENT for 99.99
col TABLESPACE_NAME for a25
col CONTAINER_NAME for a15
SELECT
tbm.con_id,
c.name AS CONTAINER_NAME,
tbm.TABLESPACE_NAME,
round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) FREE_SIZE_GB,
tbm.USED_PERCENT
FROM cdb_tablespace_usage_metrics tbm
JOIN cdb_tablespaces tb ON tb.tablespace_name = tbm.tablespace_name
AND tb.con_id = tbm.con_id
JOIN v$containers c ON tbm.con_id = c.con_id
ORDER BY tbm.USED_PERCENT DESC;
/
With colour !!!!
cd $ORACLE_HOME/sqldeveloper/
pwd
/u01/app/oracle/product/19.0.0/dbhome_1/sqldeveloper
sql /nolog
SQL> connect / as sysdba
Connected.
SQL>
SQL> def
DEFINE _DATE = "12-SEP-24" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "CDB$ROOT" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1901000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0" (CHAR)
DEFINE _O_RELEASE = "2002000000" (CHAR)
DEFINE _PWD = "/u01/app/oracle/product/19.0.0/dbhome_1/sqldeveloper/sqldeveloper/bin" (CHAR)
col TABLESPACE_SIZE for a12
col USED_SPACE for a12
col TABLESPACE_FREE_SIZE for a22
col tablespace_name for a20
col USED_PERCENT for 99
VARIABLE value NUMBER
SELECT value into :value FROM v$parameter WHERE name = lower('DB_BLOCK_SIZE');
print value
VARIABLE value1 NUMBER
SELECT to_number(value) into :value1 FROM v$parameter WHERE name = lower('DB_BLOCK_SIZE');
define value1=8192
set head on pagesize 300 linesize 200 numf 999999.99
col tablespace_name for a28
col status for a10
col pdb_name for a15
col CON_ID for 9999
with ts_details as
(
select
tb.con_id||' '||
nvl(pdb_name,'CDB$ROOT')||' '||nvl(pdb.status,' ')||' '||rpad(tablespace_name,20, ' ')||' ' ||dbms_xplan.FORMAT_SIZE(tb.TABLESPACE_SIZE * &value1 )||' '||dbms_xplan.FORMAT_SIZE(tb.USED_SPACE * &value1 )||' ' ||dbms_xplan.FORMAT_SIZE((tb.TABLESPACE_SIZE - tb.USED_SPACE) * &value1 ) ts_line ,
case when ((used_percent) > 95.00) then '---(>95.00)% full ##'
else 'good' end as "STATUS"
, trunc(used_percent) used_percent
from cdb_tablespace_usage_metrics tb,cdb_pdbs pdb
where 1=1
and tb.con_id= pdb.con_id(+)
-- and pdb.con_id=3
-- and used_percent >1
--and tablespace_name like 'TEMP%'
--order by 1 desc
)
select
case
when used_percent > 70 then '@|bg_red '||ts_line||' '||STATUS||'|@'
when used_percent < 1 then '@|bg_green '||ts_line||' '||STATUS||'|@'
else '@|bg_yellow '||ts_line||' '||STATUS||'|@'
end as ts_usage_percentage
from ts_details
;
https://anuj-singh.blogspot.com/search?q=fs.tablespace_name+tablespace%2C+num_files%2Cnum_files
Tablespace metadata !!! http://anuj-singh.blogspot.com/2011/11/oracle-tablespace-metadata.html
http://anuj-singh.blogspot.com/ How to Check Tablespace Creation Time in Oracle
https://anuj-singh.blogspot.com/2012/03/asm-shell-script-add-file.html
