Segment tablespace growth details script
object growth over last N days, sorted by growth desc
set feedback on
select * from (select c.TABLESPACE_NAME,c.segment_name "Object Name",b.object_type,
sum(space_used_delta)/1024/1024 "Growth (MB)"
from dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner ='ANUJ_BIG_TABLE'
group by c.TABLESPACE_NAME,c.segment_name,b.object_type)
order by 3 asc;
set pages 80
set feedback off
column "OBJECT_NAME" justify left format A30
column "SUBOBJECT_NAME" justify left format A30
column "OBJECT_TYPE" justify left format A30
column "Tablespace Name" justify left format A30
set line 5000
SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,t.NAME "Tablespace Name", s.growth/(1024*1024) "Growth in MB",
(SELECT sum(bytes)/(1024*1024) FROM dba_segments
WHERE segment_name=o.object_name) "Total Size(MB)"
FROM DBA_OBJECTS o,( SELECT TS#,OBJ#,SUM(SPACE_USED_DELTA) growth FROM DBA_HIST_SEG_STAT
GROUP BY TS#,OBJ#
HAVING SUM(SPACE_USED_DELTA) > 0 ORDER BY 2 DESC ) s,
v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
AND o.OWNER='SCOTT' ---------------
ORDER BY 6 DESC
column "Percent of Total Disk Usage" justify right format 999.99
column "Space Used (MB)" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
set line 5000
column "SEGMENT_NAME" justify left format A30
column "TABLESPACE_NAME" justify left format A30
select * from (select c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, 'MM/DD/YY') mydate,
sum(space_used_delta)/1024/1024 "Space used (MB)", avg(c.bytes)/1024/1024 "Total Object Size (MB)",
round(sum(space_used_delta)/sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate)-10
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = 'ANUJ_BIG_TABLE'
group by c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, 'MM/DD/YY')
order by c.TABLESPACE_NAME,c.segment_name,to_date(mydate, 'MM/DD/YY'));
TABLESPACE_NAME SEGMENT_NAME MYDATE Space used (MB) Total Object Size (MB) Percent of Total Disk Usage
------------------------------ ------------------------------ -------- --------------- ---------------------- ---------------------------
USERS ANUJ_BIG_TABLE 10/31/11 -.47 2.44 -19.34
Monday, 31 October 2011
Oracle SQL space advisor
SYS@rac1> connect anuj/v123
Connected.
ANUJ@rac1> create table anuj_big_table (id number, name char(200));
Table created.
ANUJ@rac1> insert into anuj_big_table select rownum,'a' from dual connect by rownum<100000;
99999 rows created.
ANUJ@rac1> commit;
Commit complete.
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
22 24 0
ANUJ@rac1> delete from anuj_big_table where mod(id,10)<>0;
90000 rows deleted.
ANUJ@rac1> commit;
Commit complete.
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
4 24 0
Change folowing
my_task_name
attr1 owner
attr2 table name
DECLARE
my_task_id NUMBER;
obj_id NUMBER;
my_task_name VARCHAR2(100);
my_task_desc VARCHAR2(500);
BEGIN
my_task_name := 'tableBIG_TABLE';
my_task_desc := 'Manual Segment Advisor Run';
---------
-- Step 1
---------
dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => my_task_id, task_name => my_task_name, task_desc => my_task_desc);
---------
-- Step 2
---------
dbms_advisor.create_object ( task_name => my_task_name,
object_type => 'TABLE',
attr1 => 'ANUJ',
attr2 => 'ANUJ_BIG_TABLE',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
---------
-- Step 3
---------
dbms_advisor.set_task_parameter( task_name => my_task_name, parameter => 'recommend_all',value => 'TRUE');
---------
-- Step 4
---------
dbms_advisor.execute_task(my_task_name);
END;
/
PL/SQL procedure successfully completed.
SELECT 'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr(10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f ,dba_advisor_objects o ,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e.advisor_name = 'Segment Advisor'
AND e. execution_start > sysdate - 1
ORDER BY f.task_name;
ADVICE
--------------------------------------------------------------------------------
Task Name : tableBIG_TABLE
Start Run Time : 27-sep-17 02:11
Segment Name : ANUJ_BIG_TABLE
Segment Type : TABLE
Partition Name :
Message : Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perf
orm shrink, estimated savings is 20101729 bytes.
More Info : Allocated Space:25165824: Used Space:5064095: Reclaimable Spa
ce :20101729:
------------------------------------------------------
set pagesize 200 linesize 200
SELECT
'Segment Advice --------------------------' || chr(10)||
'TABLESPACE_NAME : ' || tablespace_name || chr(10)||
'SEGMENT_OWNER : ' || segment_owner || chr(10)||
'SEGMENT_NAME : ' || segment_name || chr(10)||
'ALLOCATED_SPACE : ' || allocated_space || chr(10)||
'RECLAIMABLE_SPACE:' || reclaimable_space || chr(10)||
'RECOMMENDATIONS : ' || recommendations || chr(10)||
'SOLUTION 1: ' || c1|| chr(10) ||
'SOLUTION 2: ' || c2|| chr(10) ||
'SOLUTION 3: ' || c3 Advice
FROM TABLE(dbms_space.asa_recommendations(ALL_RUNS=>'TRUE', SHOW_MANUAL=>'TRUE',SHOW_FINDINGS=>'FALSE'));
ADVICE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Segment Advice --------------------------
TABLESPACE_NAME : USERS
SEGMENT_OWNER : ANUJ
SEGMENT_NAME : ANUJ_BIG_TABLE
ALLOCATED_SPACE : 25165824
RECLAIMABLE_SPACE:20101729
RECOMMENDATIONS : Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perform shrink, estimated savings is 20101729 bytes.
SOLUTION 1: alter table "ANUJ"."ANUJ_BIG_TABLE" shrink space
SOLUTION 2: alter table "ANUJ"."ANUJ_BIG_TABLE" shrink space COMPACT
SOLUTION 3: alter table "ANUJ"."ANUJ_BIG_TABLE" enable row movement
col BENEFIT_TYPE FOR a120
SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='tableBIG_TABLE';
BENEFIT_TYPE
------------------------------------------------------------------------------------------------------------------------
Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perform shrink, estimated savings is 20101729 bytes.
SELECT tablespace_name,
-- segment_owner,
segment_name,
segment_type,
round (allocated_space/1024/1024,2) "Allocated, Mb",
round (used_space/1024/1024,2) "Used, Mb",
round (reclaimable_space/1024/1024,2) "Reclaimable, Mb",
recommendations,
c1,
c2,
c3
FROM TABLE (DBMS_SPACE.asa_recommendations ())
order by 4 desc;
SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE';
TASK_ID
----------
11520
SELECT
segment_owner,
segment_name,
ROUND(allocated_space/(1024*1024)) AS "Allocated (MB)",
ROUND(used_space/(1024*1024)) AS "Used (MB)",
ROUND(reclaimable_space/(1024*1024)) AS "Reclaimable (MB)",
ROUND(reclaimable_space*100/allocated_space) AS "Percentage gain"
FROM TABLE(dbms_space.asa_recommendations())
WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE');
SET pages 50
SELECT recommendations,c3,c2,c1 FROM TABLE(dbms_space.asa_recommendations()) WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE');
set linesize 200 pagesize 200
col TASK_NAME for a50
col EXECUTION_START FOR a30
col EXECUTION_END FOR a25
SELECT * FROM (SELECT task_name,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start, TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end,status, ROUND((execution_end-execution_start)*24*60,1) AS "Duration (min)"
FROM dba_advisor_tasks
WHERE advisor_name='Segment Advisor'
ORDER BY task_id DESC)
WHERE rownum<=10;
TASK_NAME EXECUTION_START EXECUTION_END STATUS Duration (min)
-------------------------------------------------- ------------------------------ ------------------------- ----------- --------------
tableBIG_TABLE1 27-sep-2017 02:42:46 27-sep-2017 02:42:47 COMPLETED 0
SYS_AUTO_SPCADV932072319092017 19-sep-2017 23:07:32 19-sep-2017 23:07:46 COMPLETED .2
SET pages 100 lines 200
SELECT * FROM TABLE(dbms_space.object_growth_trend('ANUJ','ANUJ_BIG_TABLE','TABLE'))
ORDER BY timepoint;
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
28-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
29-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
30-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
31-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
01-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
02-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
03-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
04-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
05-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
06-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
07-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
08-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
09-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
10-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
11-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
12-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
13-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
14-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
15-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
16-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
17-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
18-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
19-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
20-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
21-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
22-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
23-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
24-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
25-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
26-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
27-SEP-17 03.15.51.119584 AM 5064095 25165824 GOOD
ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE ENABLE ROW MOVEMENT;
Table altered.
ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE;
Table altered.
ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE COMPACT;
Table altered.
ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE CASCADE;
Table altered.
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
2 2 0
Table Size ---
with segment_rollup as (
select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
union all
select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
union all
select owner, table_name, owner segment_owner, segment_name from dba_lobs
union all
select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs ), ranked_tables as ( select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name
from segment_rollup r, dba_segments s
where s.owner =r.segment_owner
and s.segment_name =r.segment_name
and r.owner=upper('&schema_name')
group by r.owner, r.table_name
)
select rank, round(blocks*8/1024) mb, table_name from ranked_tables
where 1=1
and table_name= 'ANUJ_BIG_TABLE'
-- and rank<=20;
RANK MB TABLE_NAME
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
2 24 ANUJ_BIG_TABLE
SELECT tablespace_name,
-- segment_owner,
segment_name,
segment_type,
round (allocated_space/1024/1024,2) "Allocated, Mb",
round (used_space/1024/1024,2) "Used, Mb",
round (reclaimable_space/1024/1024,2) "Reclaimable, Mb",
recommendations,
c1,
c2,
c3
FROM TABLE (DBMS_SPACE.asa_recommendations ())
order by 4 desc;
set pagesize 200 linesize 200
col JOB_START_TIME for a40
col JOB_DURATION for a30
select job_start_time,job_status,job_duration,job_error from DBA_AUTOTASK_JOB_HISTORY
where client_name='auto space advisor'
and job_start_time > sysdate - 1
order by job_start_time;
JOB_START_TIME JOB_STATUS JOB_DURATION JOB_ERROR
---------------------------------------- ------------------------------ ------------------------------ ----------
26-SEP-17 10.00.08.476056 PM EST5EDT SUCCEEDED +000 00:00:29 0
26-SEP-17 11.03.26.726343 PM EST5EDT SUCCEEDED +000 00:00:33 0
26-SEP-17 11.14.22.829939 PM EST5EDT SUCCEEDED +000 00:00:32 0
26-SEP-17 11.23.30.956927 PM EST5EDT SUCCEEDED +000 00:00:20 0
26-SEP-17 11.24.25.594535 PM EST5EDT SUCCEEDED +000 00:00:19 0
26-SEP-17 11.33.32.428398 PM EST5EDT SUCCEEDED +000 00:00:21 0
26-SEP-17 11.34.26.762093 PM EST5EDT SUCCEEDED +000 00:00:19 0
26-SEP-17 11.43.33.801698 PM EST5EDT SUCCEEDED +000 00:00:22 0
26-SEP-17 11.44.28.435450 PM EST5EDT SUCCEEDED +000 00:00:20 0
select ss.DATAOBJ#,sn.END_INTERVAL_TIME,ss.DB_BLOCK_CHANGES_DELTA,ss.PHYSICAL_WRITES_DELTA from DBA_HIST_SEG_STAT ss,DBA_HIST_SNAPSHOT sn,DBA_HIST_SEG_STAT_OBJ so
where so.OWNER='ANUJ'
and so.OBJECT_NAME='ANUJ_BIG_TABLE'
and so.OBJECT_TYPE='TABLE'
and ss.OBJ#=so.OBJ#
and ss.DATAOBJ#=so.DATAOBJ#
and ss.snap_id=sn.snap_id
order by ss.snap_id,ss.DATAOBJ#;
DATAOBJ# END_INTERVAL_TIME DB_BLOCK_CHANGES_DELTA PHYSICAL_WRITES_DELTA
---------- --------------------------------------------------------------------------- ---------------------- ---------------------
142685 27-SEP-17 02.00.29.291 AM 115552 2962
142685 27-SEP-17 02.00.29.358 AM 115552 2962
EXEC DBMS_ADVISOR.DELETE_TASK('tableBIG_TABLE');
PL/SQL procedure successfully completed.
Oracle Tablespace space report
Tablespace space report
Bug 28821847 DBA_TABLESPACE_USAGE_METRICS Does Not Include Undo Tablespace Info
-- Tablespace space reportfrom 12cset linesize 300 pagesize 300 col tablespace_name for a25 col force_logging for a12 col bigfile for a8 col encrypted for a10 col allocation_type for a15 col PDB_NAME for a15 col "TABLESPACE_SIZE(GB)" for a20 col free for a12 col "USED_SPACE" for a12 col "TABLESPACE_SIZE" for a18 select a.tablespace_name,a.CON_ID,nvl(PDB_NAME,'CDB$ROOT' ) PDB_NAME, round(a.used_percent, 2) as "USED _%",b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management, dbms_xplan.FORMAT_SIZE(a.tablespace_size * b.block_size) as "TABLESPACE_SIZE", dbms_xplan.FORMAT_SIZE((a.used_space * b.block_size)) as "USED_SPACE", dbms_xplan.FORMAT_SIZE((a.tablespace_size * b.block_size) - (a.used_space * b.block_size) ) Free, round(a.used_percent, 2) as "USED_%" from cdb_tablespace_usage_metrics a , cdb_tablespaces b ,dba_pdbs c where a.tablespace_name = b.tablespace_name -- and a.tablespace_name like '%' -- and CONTENTS='TEMPORARY' -- and CONTENTS='PERMANENT' and CONTENTS!='UNDO' and a.con_id=b.con_id and a.con_id=c.con_id(+) union all-- for Undo <<<<< SELECT a.tablespace_name,a.con_id,nvl(PDB_NAME,'CDB$ROOT') PDB_NAME,round ((Used*100)/Total,2) "%Used_%",block_size,force_logging, bigfile,encrypted,allocation_type,status,contents,retention,extent_management,dbms_xplan.FORMAT_SIZE(Total) total ,dbms_xplan.FORMAT_SIZE(Used) Used,dbms_xplan.FORMAT_SIZE((Total - Used)) Free,round ((Used*100)/Total,2) "%Used_%" FROM ( SELECT b.con_id,SUM (maxbytes) Total, b.tablespace_name,b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management,PDB_NAME FROM cdb_data_files a, cdb_tablespaces b,dba_pdbs c WHERE a.tablespace_name = b.tablespace_name and c.con_id=a.con_id AND b.contents like '%UNDO%' GROUP BY b.con_id,b.tablespace_name,b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management,PDB_NAME) a, ( SELECT con_id,c.tablespace_name, SUM (bytes) Used FROM cdb_UNDO_EXTENTS c WHERE status <> 'EXPIRED' GROUP BY con_id,c.tablespace_name) b WHERE a.tablespace_name = b.tablespace_name and a.con_id=b.con_id order by "USED_%" desc ; ************************************************************************ set linesize 300 pagesize 300 col tablespace_name for a25 col force_logging for a12 col bigfile for a8 col encrypted for a10 col allocation_type for a15 col PDB_NAME for a15 col "TABLESPACE_SIZE(GB)" for a20 select a.tablespace_name,a.CON_ID,nvl(PDB_NAME,'CDB$ROOT' ) PDB_NAME, round(a.used_percent, 2) as "USED _%",b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management, to_char(round((a.tablespace_size * b.block_size)/1024/1024/1024, 2)) as "TABLESPACE_SIZE(GB)", round((a.used_space * b.block_size)/1024/1024/1024, 2) as "USED_SPACE(GB)", round((a.tablespace_size * b.block_size)/1024/1024/1024, 2) - round((a.used_space * b.block_size)/1024/1024/1024, 2) Free_GB, round(a.used_percent, 2) as "USED_%" from cdb_tablespace_usage_metrics a , cdb_tablespaces b ,dba_pdbs c where a.tablespace_name = b.tablespace_name -- and a.tablespace_name like '%' -- and CONTENTS='TEMPORARY' -- and CONTENTS='UNDO' -- and CONTENTS='PERMANENT' and a.con_id=b.con_id and a.con_id=c.con_id(+) order by "USED_%" desc ;set head on pagesize 300 linesize 200 numf 999999.99 col tablespace_name for a28 col status for a20 col pdb_name for a15 col CON_ID for 9999 col TABLESPACE_SIZE for a10 col USED_SPACE for a10 col TABLESPACE_FREE_SIZE for a10 col TotalSpace for a10 col currentspace for a10 VARIABLE value1 NUMBER exec SELECT to_number(value) into :value1 FROM v$parameter WHERE name = lower('DB_BLOCK_SIZE') ; / col NUM_FILES for 999 col NUM_FILES1 for 9999 col "USED_PERCENT%" for 99 col num_files_left for 9999 col "Tb_status" for a10 col "Datafilecountstatus" for a20 select tf.con_id,tf.PDB_NAME,tf.status,tf.tablespace_name ,num_files --,num_files1 num_files_left , (case when ( num_files1 <= 300 and num_files1 >= 250) then 'Bad <300 ***' when ( num_files1<= 250 and num_files1>= 201) then 'warning <250 ***' when ( num_files1<= 200) then 'critical <200 ***' else 'Good' end ) as "Datafilecountstatus" , tf.TABLESPACE_SIZE,dbms_xplan.FORMAT_SIZE(TotalSpace) TotalSpace ,dbms_xplan.FORMAT_SIZE(currentspace) currentspace,tf.USED_SPACE,tf.TABLESPACE_FREE_SIZE,tf."USED_PERCENT%",tf."status1" Tb_status from (select tb.con_id con_id, nvl(pdb_name,'CDB$ROOT') PDB_NAME, nvl(pdb.status,'CDB$ROOT') status, tablespace_name tablespace_name , dbms_xplan.FORMAT_SIZE(tb.TABLESPACE_SIZE * :value1 ) TABLESPACE_SIZE, dbms_xplan.FORMAT_SIZE(tb.USED_SPACE * :value1 ) USED_SPACE, dbms_xplan.FORMAT_SIZE((tb.TABLESPACE_SIZE - tb.USED_SPACE) * :value1 ) TABLESPACE_FREE_SIZE, trunc(used_percent) "USED_PERCENT%", case when ((used_percent) > 95.00) then '---(>95.00)% full ##' else 'good' end as "status1" from cdb_tablespace_usage_metrics tb,cdb_pdbs pdb where 1=1 and tb.con_id= pdb.con_id(+) -- and pdb.con_id=3 )tf, (select tablespace_name,count(distinct(file_id)) num_files,(1022 - count(distinct(file_id))) num_files1, round(sum(decode(AUTOEXTENSIBLE,'YES',GREATEST(MAXBYTES,bytes),'NO',bytes))) TotalSpace, round(sum(bytes)) currentspace, round(sum(decode(AUTOEXTENSIBLE,'YES',greatest(maxbytes,bytes)-bytes,'NO',0))) reservespace from cdb_data_files group by tablespace_name ) df where 1=1 and df.tablespace_name=tf.tablespace_name ;*****undo tablespace onlyset pagesize 10000 linesize 300 tab off col tablespace_name format A28 heading "Tablespace" col ts_type format A13 heading "TS Type" col segments format 999999 heading "Segments" col files format 9999 col allocated format a14 heading "Allocated Size" col used format a14 heading "Used Space" col Free format a14 heading "Free Space" col used_pct format 999 heading "Used|%" col max_ext_mb format a14 heading "Max Size" col max_free_mb format a14 heading "Max Free" col max_used_pct format 999 heading "Max Used|(%)" col max_size for a15 BREAK ON REPORT COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name), tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name), tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics), ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name) -- UNDO TS SELECT d.tablespace_name, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, NVL(s.segcnt,0) segments, dbms_xplan.FORMAT_SIZE(a.bytes ) Allocated, dbms_xplan.FORMAT_SIZE(u.bytes) Used, dbms_xplan.FORMAT_SIZE(NVL(a.bytes - NVL(u.bytes, 0), 0)) Free, ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, dbms_xplan.FORMAT_SIZE(a.maxbytes ) max_size, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' ORDER BY 11 desc /set linesize 300 pagesize 300 col tablespace_name for a25 col force_logging for a12 col bigfile for a8 col encrypted for a10 col allocation_type for a15 col PDB_NAME for a15 col TABLESPACE_SIZE for a15 col USED_SPACE for a12 col FREE for a8 col "USED_%" for 999.99 select a.tablespace_name,a.CON_ID,nvl(PDB_NAME,'CDB$ROOT' ) PDB_NAME, round(a.used_percent, 2) as "USED _%",b.block_size,b.force_logging, bigfile,encrypted,allocation_type,b.status,contents,retention,extent_management, dbms_xplan.FORMAT_SIZE(a.tablespace_size * b.block_size) "TABLESPACE_SIZE", dbms_xplan.FORMAT_SIZE(a.used_space * b.block_size ) "USED_SPACE", dbms_xplan.FORMAT_SIZE((a.tablespace_size * b.block_size) - (a.used_space * b.block_size)) Free,b.CONTENTS ,a.used_percent as "USED_%" from cdb_tablespace_usage_metrics a , cdb_tablespaces b ,dba_pdbs c where a.tablespace_name = b.tablespace_name -- and a.tablespace_name like '%' --and CONTENTS='TEMPORARY' --and b.CONTENTS like '%UNDO%' -- ?? --and CONTENTS='PERMANENT' and a.con_id=b.con_id(+) and a.con_id=c.con_id order by "USED_%" desc ;--- with cdb_tablespace_usage_metrics and dbms_xplan.format_sizeset pagesize 10000 linesize 300 tab off col tablespace_name format A27 heading "Tablespace" col ts_type format A13 heading "TS Type" col segments format 999999 heading "Segments" col files format 9999 col used_pct format 999 heading "Used|%" col max_used_pct format 999 heading "Max Used|(%)*" col Max_Free for A10 heading "Max_Free" col ALLOCATED for a12 col USED for a10 col FREE for a10 col MAX_EXT for a10 BREAK ON REPORT --COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated used Free max_ext Max_Free ON REPORT select distinct tablespace_name,con_id,ts_type,status,files,segments,dbms_xplan.format_size(Allocated) Allocated,dbms_xplan.format_size(Used) Used,dbms_xplan.format_size(Free) Free,Used_pct --, dbms_xplan.format_size(max_ext) max_ext ,dbms_xplan.format_size(Max_Free) Max_Free, Max_used_pct from ( WITH df AS (SELECT con_id,tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM cdb_data_files GROUP BY con_id,tablespace_name), tf AS (SELECT con_id,tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM cdb_temp_files GROUP BY con_id,tablespace_name), tm AS (SELECT con_id,tablespace_name, used_percent FROM cdb_tablespace_usage_metrics), ts AS (SELECT con_id,tablespace_name, COUNT(*) segcnt FROM cdb_segments GROUP BY con_id,tablespace_name) SELECT distinct d.tablespace_name, d.con_id, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt files, NVL(s.segcnt,0) segments, (a.bytes) Allocated, (a.bytes - f.bytes) Used, (f.bytes) Free , ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct, (a.maxbytes) max_ext, (a.maxbytes - (a.bytes - f.bytes) ) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM cdb_tablespaces d, df a, tm m, ts s, (SELECT con_id,tablespace_name, SUM(bytes) bytes FROM cdb_free_space GROUP BY con_id,tablespace_name) f WHERE d.tablespace_name = a.tablespace_name and d.con_id = a.con_id AND d.con_id = s.con_id AND d.con_id = m.con_id AND d.tablespace_name = f.tablespace_name AND d.tablespace_name = m.tablespace_name AND d.tablespace_name = s.tablespace_name AND d.con_id = f.con_id AND NOT d.contents = 'UNDO' AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) --and d.contents != 'TEMPORARY' -- TEMP TS union SELECT distinct d.tablespace_name, d.con_id, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, 0, (a.bytes) Allocated, (t.ub*d.block_size) Used , (a.bytes - (t.ub*d.block_size)) Free, ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct, (a.maxbytes ) max_size, (a.maxbytes - (t.ub*d.block_size)) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM cdb_tablespaces d, tf a, tm m, (SELECT con_id,ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY con_id,ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name and d.con_id = a.con_id AND d.con_id = m.con_id AND d.tablespace_name = t.tablespace_name AND d.tablespace_name = m.tablespace_name AND d.con_id = t.con_id AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' /* union -- UNDO TS SELECT distinct d.tablespace_name, d.con_id, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, NVL(s.segcnt,0) segments, (a.bytes) Allocated, (u.bytes) Used, (a.bytes - u.bytes) Free, ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, (a.maxbytes) max_size, (a.maxbytes - u.bytes) Max_Free , ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM cdb_tablespaces d, df a, tm m, ts s, (SELECT con_id,tablespace_name, SUM(bytes) bytes FROM cdb_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY con_id,tablespace_name) u WHERE 1=1 --and d.tablespace_name = a.tablespace_name and d.con_id = a.con_id AND d.con_id = m.con_id --and d.tablespace_name = a.tablespace_name and d.con_id = a.con_id AND d.tablespace_name = u.tablespace_name AND m.tablespace_name = u.tablespace_name --AND d.tablespace_name = m.tablespace_name --AND d.tablespace_name = s.tablespace_name AND d.contents = 'UNDO' */ ) --ORDER BY 13 desc /--- with distinct !!!!!!!!!!!!!!!!!!!!! set pagesize 10000 linesize 300 tab off col tablespace_name format A27 heading "Tablespace" col ts_type format A13 heading "TS Type" col segments format 999999 heading "Segments" col files format 9999 col used_pct format 999 heading "Used|%" col max_used_pct format 999 heading "Max Used|(%)*" col Max_Free for A10 heading "Max_Free" col ALLOCATED for a12 col USED for a10 col FREE for a10 col MAX_EXT for a10 BREAK ON REPORT --COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated used Free max_ext Max_Free ON REPORT select distinct * from ( WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM cdb_data_files GROUP BY tablespace_name), tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM cdb_temp_files GROUP BY tablespace_name), tm AS (SELECT tablespace_name, used_percent FROM cdb_tablespace_usage_metrics), ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM cdb_segments GROUP BY tablespace_name) SELECT distinct d.tablespace_name, d.con_id, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt files, NVL(s.segcnt,0) segments, dbms_xplan.format_size(a.bytes) Allocated, dbms_xplan.format_size(a.bytes - f.bytes) Used, dbms_xplan.format_size(f.bytes) Free , ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct, dbms_xplan.format_size(a.maxbytes) max_ext, dbms_xplan.format_size(a.maxbytes - (a.bytes - f.bytes) ) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM cdb_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM cdb_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND NOT d.contents = 'UNDO' AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) UNION ALL -- TEMP TS SELECT distinct d.tablespace_name, d.con_id, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, 0, dbms_xplan.format_size(a.bytes) Allocated, dbms_xplan.format_size(t.ub*d.block_size) Used , dbms_xplan.format_size(a.bytes - (t.ub*d.block_size)) Free, ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct, dbms_xplan.format_size(a.maxbytes ) max_size, dbms_xplan.format_size(a.maxbytes - (t.ub*d.block_size)) Max_Free, ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM cdb_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' UNION ALL -- UNDO TS SELECT distinct d.tablespace_name, d.con_id, d.status, DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type, a.cnt, NVL(s.segcnt,0) segments, dbms_xplan.format_size(a.bytes) Allocated, dbms_xplan.format_size(u.bytes) Used, dbms_xplan.format_size(a.bytes - u.bytes) Free, ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct, dbms_xplan.format_size(a.maxbytes) max_size, dbms_xplan.format_size(a.maxbytes - u.bytes) Max_Free , ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM cdb_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM cdb_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' --ORDER BY 12 desc ) ORDER BY 13 desc /set linesize 300 pagesize 100set heading oncol fname heading "Filename" format a85col fnum heading "#" format 999col ts heading "Tablespace|Name" format a15col tb heading "Total|Potential|File Size" format a10col cb heading "Total|Current|File Size" like tbcol used heading "Used" like tbcol free heading "Potential|Free" like tbcol autoext heading "Auto|Ext." format a4col percentfree heading "% Free|of|Total|Bytes" format 999break on reportcompute sum of tb cb used free on report-- spool TablespaceUsage.txtselect substr(tablespace_name,1,15) ts,d.file_id fnum,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) ) tb,dbms_xplan.FORMAT_SIZE(d.bytes) cb,dbms_xplan.FORMAT_SIZE(decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) used,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) free,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/ decode(e.file#,null,d.bytes,(e.maxextend * blksize))),2) * 100 percentfree,decode(e.file#,null,'No','Yes') autoext,''''||substr(file_name,1,80)||'''' fnamefrom sys.dba_data_files d,(select file_id,sum(bytes) freebytesfrom sys.dba_free_spacegroup by file_id) f,sys.filext$ e,v$datafile v,(select value blksize from v$parameterwhere name = 'db_block_size') bwhere d.file_id=f.file_id(+)and d.file_id=e.file#(+)and v.file#=d.file_idand d.tablespace_name='Ts_TS'order by tablespace_name,creation_time/alter session set container=XXXXXXX; -- alter session set "_push_join_predicate" = FALSE ; define t_name='TS_TS' set linesize 300 pagesize 40 time on timing on set heading on col fname heading "Filename" format a90 col fnum heading "#" format 999 col ts heading "Tablespace|Name" format a15 col tb heading "Total|Potential|File Size" format 999,999,999,999,999,999 col cb heading "Total|Current|File Size" like tb col used heading "Bytes Used" like tb col free heading "Potential|Bytes Free" like tb col autoext heading "Auto|Ext." format a4 col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999 col tb1 heading "Total|File Size" format a10 col cb1 like tb1 col free1 like tb1 col used1 like tb1 break on report compute sum of tb cb used free on report -- spool TablespaceUsage.txt --select /*+ PARALLEL(50) */ substr(tablespace_name,1,15) ts select substr(tablespace_name,1,15) ts ,d.file_id fnum ,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) ) tb1 ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb ,d.bytes cb ,dbms_xplan.FORMAT_SIZE(d.bytes) cb1 ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used ,dbms_xplan.FORMAT_SIZE(decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) used1 ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free ,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) free1 ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/decode(e.file#,null,d.bytes,(e.maxextend * blksize))),2) * 100 percentfree ,decode(e.file#,null,'No','Yes') autoext ,''''||substr(file_name,1,85)||'''' fname from sys.dba_data_files d ,(select file_id,sum(bytes) freebytes from sys.dba_free_space group by file_id) f ,sys.filext$ e ,v$datafile v ,(select value blksize from v$parameter where name = 'db_block_size') b where d.file_id=f.file_id(+) and d.file_id=e.file#(+) and v.file#=d.file_id and d.tablespace_name='&t_name' order by tablespace_name,creation_time /size in gbdefine t_name='xxx' set linesize 300 pagesize 40 time on timing on set heading on col fname heading "Filename" format a90 col fnum heading "FNo#" format 9999 col ts heading "Tablespace|Name" format a15 col tb heading "Total|Potential|File GbSize" format 99999 col cb heading "Total|Current|File GbSize" like tb col used heading "Gb Used" like tb col free heading "Potential|GB Free" like tb col autoext heading "Auto|Ext." format a4 col percentfree heading "% Free|of|Total" format 999 col tb1 heading "Total|File Size" format a10 col cb1 like tb1 col free1 like tb1 col used1 like tb1 break on report compute sum of tb cb used free on report -- spool TablespaceUsage.txt --select /*+ PARALLEL(50) */ substr(tablespace_name,1,15) ts select substr(tablespace_name,1,15) ts ,d.file_id fnum ,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) ) tb1 ,(decode(e.file#,null,d.bytes,(e.maxextend * blksize)))/1024/1024/1024 tb ,(d.bytes/1024/1024/1024) cb ,dbms_xplan.FORMAT_SIZE(d.bytes) cb1 ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))/1024/1024/1024 used ,dbms_xplan.FORMAT_SIZE(decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) used1 ,(decode(e.file#,null,d.bytes,(e.maxextend * blksize))-decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/1024/1024/1024 free ,dbms_xplan.FORMAT_SIZE(decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0))) free1 ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize)) -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/decode(e.file#,null,d.bytes,(e.maxextend * blksize))),2) * 100 percentfree ,decode(e.file#,null,'No','Yes') autoext ,''''||substr(file_name,1,85)||'''' fname from sys.dba_data_files d ,(select file_id,sum(bytes)/1024/1024/1024 freebytes from sys.dba_free_space group by file_id) f ,sys.filext$ e ,v$datafile v ,(select value blksize from v$parameter where name = 'db_block_size') b where d.file_id=f.file_id(+) and d.file_id=e.file#(+) and v.file#=d.file_id and d.tablespace_name='&t_name' order by tablespace_name,creation_time /
DBA_TABLESPACE_USAGE_METRICS Returns Incorrect Information After applying 12.1.0.2.170418 (25397136) Bundle patch (Doc ID 2289448.1)
set pagesize 10000 linesize 300 tab off
col tablespace_name format A22 heading "Tablespace"
col ts_type format A13 heading "TS Type"
col segments format 999999 heading "Segments"
col files format 9999
col allocated_mb format 999,999,990.00 heading "Allocated Size|(Mb)"
col used_mb format 999,999,990.00 heading "Used Space|(Mb)"
col Free_mb format 999,999,990.00 heading "Free Space|(Mb)"
col used_pct format 999 heading "Used|%"
col max_ext_mb format 999,999,990.00 heading "Max Size|(Mb)"
col max_free_mb format 999,999,990.00 heading "Max Free|(Mb)"
col max_used_pct format 999 heading "Max Used|(%)*"
col max_used_pct1 format 999 heading "Max Used1|(%)****"
col Max_Free for 999,999,990.00 heading "Max_Free|(MB)"
BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb Max_Free ON REPORT
select tablespace_name,
status,
ts_type,
files,
segments,
Allocated_MB,
Used_MB,
Free_MB,
Used_pct,
max_ext_mb,
Max_Free,
Max_used_pct, --- <<<<<<<<<from dba_tablespace_usage_metrics Sometime this may be wrong due to bug
(Used_MB/max_ext_mb)*100 Max_used_pct1
from (
WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),
tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),
tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt files,
NVL(s.segcnt,0) segments,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB,
ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB,
ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_ext_mb,
ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Max_Free,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND NOT d.contents = 'UNDO'
AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' )
UNION ALL
-- TEMP TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
0,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,
ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Max_Free,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
UNION ALL
-- UNDO TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
NVL(s.segcnt,0) segments,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB,
ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,
ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Max_Free ,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND d.contents = 'UNDO'
ORDER BY 12 desc
)
Tablespace report
prompt - LM/DM - Local/Dictionary Managed |- SYS/UNI - SYStem/UNIform Extent Management (LM only) |ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only)
set pagesize 10000 linesize 300 tab off
col tablespace_name format A22 heading "Tablespace"
col ts_type format A13 heading "TS Type"
col segments format 999999 heading "Segments"
col files format 9999
col allocated_mb format 999,999,990.00 heading "Allocated Size|(Mb)"
col used_mb format 999,999,990.00 heading "Used Space|(Mb)"
col Free_mb format 999,999,990.00 heading "Free Space|(Mb)"
col used_pct format 999 heading "Used|%"
col max_ext_mb format 999,999,990.00 heading "Max Size|(Mb)"
col max_free_mb format 999,999,990.00 heading "Max Free|(Mb)"
col max_used_pct format 999 heading "Max Used|(%)*"
col Max_Free for 999,999,990.00 heading "Max_Free|(MB)"
BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb Max_Free ON REPORT
WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),
tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),
tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt files,
NVL(s.segcnt,0) segments,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB,
ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB,
ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_ext_mb,
ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Max_Free,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND NOT d.contents = 'UNDO'
AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' )
UNION ALL
-- TEMP TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
0,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,
ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Max_Free,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
UNION ALL
-- UNDO TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
NVL(s.segcnt,0) segments,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB,
ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,
ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Max_Free ,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND d.contents = 'UNDO'
ORDER BY 12 desc
/
Allocated Size Used Space Free Space Used Max Size Max_Free Max Used
Tablespace STATUS TS Type FILES Segments (Mb) (Mb) (Mb) % (Mb) (MB) (%)*
---------------------- --------- ------------- ----- -------- --------------- --------------- --------------- ---- --------------- --------------- --------
UNDOTBS1 ONLINE UNDO-SYS-MSSM 9 307 214,771.94 212,140.88 2,631.06 99 294,911.86 82,770.98 73
R80DBFS_TS ONLINE LM-SYS-ASSM 1 6 131,072.00 130,018.19 1,053.81 99 307,200.00 177,181.81 42
R80DATA ONLINE LM-SYS-ASSM 1 44210 12,001,380.00 11,867,990.56 133,389.44 99 33,554,431.98 21,686,441.41 35
OOOETL_DATA ONLINE LM-SYS-ASSM 1 58 2,068,580.00 2,038,569.44 30,010.56 99 33,554,431.98 31,515,862.54 6
SYSAUX ONLINE LM-SYS-ASSM 1 5053 2,470.00 1,745.63 724.38 71 32,767.98 31,022.36 5
R80DATA3 ONLINE LM-SYS-ASSM 70 5136 1,570,025.78 95,078.53 1,474,947.25 6 2,293,758.91 2,198,680.38 4
SYSTEM ONLINE LM-SYS-MSSM 1 1688 32,750.00 885.25 31,864.75 3 32,767.98 31,882.73 3
R80INDEX ONLINE LM-SYS-ASSM 1 15655 338,020.00 332,294.94 5,725.06 98 33,554,431.98 33,222,137.04 1
R80ARC ONLINE LM-SYS-ASSM 4 333 2,524.00 325.13 2,198.88 13 131,071.94 130,746.81 0
USERS ONLINE LM-SYS-ASSM 1 59 81.25 4.69 76.56 6 32,767.98 32,763.30 0
TEMP_BIGFILE ONLINE TEMP-UNI-MSSM 1 0 7,605,500.00 209.00 7,605,291.00 0 33,554,431.98 33,554,222.98 0
TEMP ONLINE TEMP-UNI-MSSM 1 0 32,767.00 0.00 32,767.00 0 32,767.00 32,767.00 0
----- -------- --------------- --------------- --------------- --------------- ---------------
AVERAGE ==========> 8 6042 1,999,995.16 1,223,271.85 776,723.31 11,447,978.46 10,224,706.61
TOTAL SUM ==========> 92 72505 23,999,941.97 14,679,262.22 9,320,679.75 137,375,741.56 122,696,479.34
12 rows selected.
=========================
set pause off
set feed off
set verify off
set pagesize 55
set linesize 255
clear screen
col tn format a10 heading 'Name' trunc
col fn format a50 heading 'Located in file_ID + File Name'
col bts format a15 heading 'Size'
-- col bts format 999999999999 heading 'Size'
col used format a10 heading 'Used'
col ex format 9999 heading 'NrExt'
col rs format 999,999,999 heading 'RBSsize'
col init format 999,999 heading 'Init'
col next format 999,999 heading 'Next'
col mi format 999 heading 'Min'
col ma format 9999999999 heading 'Max'
col pct format 990 heading '%Inc'
col st format a4 heading 'Stat'
col sn format a15 heading 'Segm Name'
col ts format a15 heading 'In Table Space'
create or replace view free_view
as
select file_id, sum(bytes) free_bytes from sys.dba_free_space group by file_id;
clear screen
prompt Tablespace Datafiles
select d.tablespace_name tn,
f.file_id||' '||file_name fn,
to_char(f.bytes/1024,'999999,999')||'K' bts,
to_char( (f.bytes - s.free_bytes)/1024,'999,999')||'K' used
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and f.file_id = s.file_id(+)
order by d.tablespace_name;
prompt
prompt Tablespace definitions
define part1="rpad('|',29*(f.bytes-s.free_bytes)/f.bytes,'*')"
col gr format a30 heading 'Percent full'
select d.tablespace_name tn,
d.initial_extent init,
d.next_extent next,
d.pct_increase pct,
d.min_extents mi, max_extents ma,
decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st,
rpad(&part1,29,' ')||'|' gr
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and f.file_id = s.file_id
order by d.tablespace_name;
drop view free_view;
set feed on
===================================
Tablespace Datafiles
Name Located in file_ID + File Name Size Used
---------- -------------------------------------------------- --------------- ----------
ANUJTEST 6 /opt/app/oracle/oradata/orcl/anujtest.dbf 10,240K 8,192K
DROP1 11 /opt/app/oracle/oradata/orcl/drop.dbf 2,048K 1,024K
EXAMPLE 5 /opt/app/oracle/oradata/orcl/example01.dbf 102,400K 80,640K
PERFSTAT 12 /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf 1024,000K 117,760K
RMAN 10 /opt/app/oracle/oradata/orcl/rman.dbf 51,200K 7,104K
SYSAUX 2 /opt/app/oracle/oradata/orcl/sysaux01.dbf 849,920K 743,488K
SYSTEM 1 /opt/app/oracle/oradata/orcl/system01.dbf 849,920K 832,960K
TEST 9 /opt/app/oracle/oradata/orcl/test.dbf 20,480K 1,024K
TSAPEXF 7 /opt/app/oracle/oradata/orcl/tsapexf01.dbf 51,200K 1,024K
TSAPEXU 8 /opt/app/oracle/oradata/orcl/tsapexu01.dbf 112,640K 99,840K
UNDOTBS1 3 /opt/app/oracle/oradata/orcl/undotbs01.dbf 332,800K 12,800K
USERS 4 /opt/app/oracle/oradata/orcl/users01.dbf 604,160K 542,208K
Tablespace definitions
Name Init Next %Inc Min Max Stat Percent full
---------- -------- -------- ---- ---- ----------- ---- ------------------------------
ANUJTEST 65,536 1 2147483645 OnL |********************** |
DROP1 65,536 1 2147483645 OnL |************* |
EXAMPLE 65,536 1 2147483645 OnL |********************* |
PERFSTAT 524,288 524,288 0 1 2147483645 OnL |** |
RMAN 65,536 1 2147483645 OnL |*** |
SYSAUX 65,536 1 2147483645 OnL |************************ |
SYSTEM 65,536 1 2147483645 OnL |*************************** |
TEST 65,536 1 2147483645 OnL | |
TSAPEXF 65,536 1 2147483645 OnL |
TSAPEXU 65,536 1 2147483645 OnL |************************ |
UNDOTBS1 65,536 1 2147483645 OnL | |
USERS 65,536 1 2147483645 OnL |************************* |==================
for PDB
set pagesize 9999 line 9999
col TS_Name format a35
col PDBNAME format a15
col TS_Name format a35
col TYPE format a12
col LOGGING format a10
SELECT CON_ID,
PDBNAME,
TS#,
TS_NAME,
TYPE,
TS_SIZE_M,
FREE_SIZE_M,
USED_SIZE_M,
USED_PER,
MAX_SIZE_G,
USED_PER_MAX,
BLOCK_SIZE,
LOGGING,
TS_DF_COUNT
FROM (WITH wt1 AS (SELECT ts.CON_ID,
(SELECT np.NAME
FROM V$CONTAINERS np
WHERE np.CON_ID = tS.con_id) PDBNAME,
(SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(tS.TABLESPACE_NAME)
AND a.CON_ID = tS.con_id) TS#,
ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,
'D',
nvl(fs.FREESIZ, 0),
'T',
df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
df.ts_df_count
FROM cdb_tablespaces ts,
(SELECT d.CON_ID,
'D' TYPE,
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM cdb_data_files d
GROUP BY d.CON_ID,
TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
'T',
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
FROM cdb_temp_files d
GROUP BY d.CON_ID,
TABLESPACE_NAME) df,
(SELECT d.CON_ID,
TABLESPACE_NAME,
SUM(BYTES) FREESIZ
FROM cdb_free_space d
GROUP BY d.CON_ID,
TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
tablespace_name,
SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a,
cdb_tablespaces d
WHERE a.tablespace = d.tablespace_name
AND a.CON_ID = d.CON_ID
GROUP BY d.CON_ID,
tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.CON_ID = df.CON_ID
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)
AND ts.CON_ID = fs.CON_ID(+))
SELECT T.CON_ID,
(CASE
WHEN T.PDBNAME = LAG(T.PDBNAME, 1)
OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN
NULL
ELSE
T.PDBNAME
END) PDBNAME,
TS#,
t.TABLESPACE_NAME TS_Name,
CONTENTS type,
round(t.all_bytes / 1024 / 1024) ts_size_M,
round(t.freesiz / 1024 / 1024) Free_Size_M,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,
round(MAXSIZ / 1024 / 1024 / 1024, 3) MAX_Size_g,
round(decode(MAXSIZ,
0,
to_number(NULL),
(t.all_bytes - FREESIZ)) * 100 / MAXSIZ,
3) USED_per_MAX,
round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.ts_df_count
FROM wt1 t
UNION ALL
SELECT DISTINCT T.CON_ID,
'' PDBNAME,
to_number('') TS#,
'ALL TS:' TS_Name,
' ' type,
round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,
round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round(SUM(t.all_bytes - t.FREESIZ) * 100 /
SUM(t.all_bytes),
3) Used_per,
round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,
to_number('') "USED,% of MAX Size",
to_number('') BLOCK_SIZE,
'' LOGGING,
to_number('') ts_df_count
FROM wt1 t
GROUP BY rollup(CON_ID,PDBNAME)
)
ORDER BY CON_ID,TS# ;
====
from
https://github.com/carlos-sierra/cscripts/blob/master/cdb_tablespace_usage_metrics.sql
SET HEA ON LIN 2490 PAGES 0 TAB OFF FEED OFF ECHO OFF VER OFF TRIMS ON TRIM ON TI OFF TIMI OFF LONG 240000 LONGC 2400 SERVEROUT OFF;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
SET RECSEP OFF;
--
CLEAR BREAK COMPUTE;
COL pdb_tablespace_name1 FOR A35 HEA 'PDB|TABLESPACE_NAME';
COL pdb_tablespace_name2 FOR A35 HEA 'PDB|TABLESPACE_NAME';
COL used_space_gbs1 FOR 999,990.000 HEA 'USED_SPACE|(GB)';
COL used_space_gbs2 FOR 999,990.000 HEA 'USED_SPACE|(GB)';
COL max_size_gbs1 FOR 999,990.000 HEA 'MAX_SIZE|(GB)';
COL max_size_gbs2 FOR 999,990.000 HEA 'MAX_SIZE|(GB)';
COL used_percent1 FOR 990.000 HEA 'USED|PERCENT';
COL used_percent2 FOR 990.000 HEA 'USED|PERCENT';
--
BREAK ON REPORT;
COMPUTE SUM LABEL 'TOTAL' OF used_space_gbs1 max_size_gbs1 used_space_gbs2 max_size_gbs2 ON REPORT;
--
COL output_file_name NEW_V output_file_name NOPRI;
SELECT 'cdb_tablespace_usage_metrics_'||LOWER(name)||'_'||LOWER(REPLACE(SUBSTR(host_name, 1 + INSTR(host_name, '.', 1, 2), 30), '.', '_'))||'_'||TO_CHAR(SYSDATE, 'yyyymmdd"T"hh24miss') output_file_name FROM v$database, v$instance;
--
WITH
t AS (
SELECT c.name||'('||c.con_id||')' pdb,
m.tablespace_name,
ROUND(m.used_percent, 3) used_percent, -- as per maximum size (considering auto extend)
ROUND(m.used_space * t.block_size / POWER(10, 9), 3) used_space_gbs,
ROUND(m.tablespace_size * t.block_size / POWER(10, 9), 3) max_size_gbs,
ROW_NUMBER() OVER (ORDER BY c.name, m.tablespace_name) row_number1,
ROW_NUMBER() OVER (ORDER BY m.used_percent DESC, m.used_space * t.block_size DESC, m.tablespace_size * t.block_size DESC) row_number2
FROM cdb_tablespace_usage_metrics m,
cdb_tablespaces t,
v$containers c
WHERE t.con_id = m.con_id
AND t.tablespace_name = m.tablespace_name
AND t.status = 'ONLINE'
AND t.contents = 'PERMANENT'
-- AND t.tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND c.con_id = m.con_id
AND c.open_mode = 'READ WRITE'
)
SELECT t1.pdb||CHR(10)||' '||
t1.tablespace_name pdb_tablespace_name1,
t1.used_percent used_percent1,
t1.used_space_gbs used_space_gbs1,
t1.max_size_gbs max_size_gbs1,
'|'||CHR(10)||'|' "|",
t2.used_percent used_percent2,
t2.used_space_gbs used_space_gbs2,
t2.max_size_gbs max_size_gbs2,
t2.pdb||CHR(10)||' '||
t2.tablespace_name pdb_tablespace_name2
FROM t t1, t t2
WHERE t1.row_number1 = t2.row_number2
ORDER BY
t1.row_number1
/
Subscribe to:
Posts (Atom)
