Tablespace info .. ....
Oracle Tablespace space report
http://anuj-singh.blogspot.com/2011/10/oracle-space-used-in-tablespace.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 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 Reporthttp://anuj-singh.blogspot.com/2011/10/oracle-space-used-in-tablespace.htmlset 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.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 ;
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
