Tablespace Report with Analytic Workspace Information

[ola...@gmail.com]

Run this script in SQL*Plus/iSQL*Plus to generate a report that even
includes AW information:

set lines 140 pages 500 feedback off head on

clear bre col comp buff

col name for a18 hea "Tablespace"
col ownr for 9,990 hea "Users"
col ttype for a1 hea "T|Y|P|E"
col auto for a1 hea "A|U|T|O"
col status for a1 hea "L|I|V|E"
col sz for 999,990.9 hea "Size(MB)"
col usd for 999,990.9 hea "Used(MB)"
col awsz for 999,990.9 hea "AW(MB)"
col aws for 990 hea "AW#"
col segs for 990 hea "AWPts"

bre on REPORT;

comp sum lab total of aws on REPORT;
comp sum lab total of awsz on REPORT;
comp sum lab total of fr on REPORT;
comp sum lab total of segs on REPORT;
comp sum lab total of sz on REPORT;
comp sum lab total of usd on REPORT;

-- optional create a view definition
-- create or replace view aw_storage as

SELECT d.tablespace_name name, substr(d.contents, 1, 1) ttype,
substr(a.autoextensible, 1, 1) auto,
decode(d.status, 'ONLINE', 'Y', 'OFFLINE', 'N', '?') status,
NVL(o.ownr,0) ownr, NVL(a.bytes/1024/1024,0) sz,
((NVL(a.bytes/1024/1024,0))-(NVL(NVL(f.bytes,0),0)/1024/1024)) usd,
NVL(g.bytes/1024/1024,0) awsz, NVL(g.awcnt,0) aws, NVL(g.segcnt,0)
segs
FROM sys.dba_tablespaces d,
(select tablespace_name, autoextensible, sum(bytes) bytes
from dba_data_files group by tablespace_name, autoextensible) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) f,
(select dbas.tablespace_name, count(distinct table_name) as awcnt,
count(*) as segcnt, sum(dbas.bytes) bytes
from dba_lobs dbal, dba_segments dbas
where dbal.column_name = 'AWLOB' and
dbal.segment_name = dbas.segment_name
group by dbas.tablespace_name) g,
(select tablespace_name, count(distinct owner) ownr
from dba_segments group by tablespace_name) o
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = f.tablespace_name(+) AND
d.tablespace_name = g.tablespace_name(+) AND
d.tablespace_name = o.tablespace_name(+) AND
NOT (d.extent_management = 'LOCAL' AND
d.contents = 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name name, substr(d.contents, 1, 1) ttype,
substr(a.autoextensible, 1, 1) auto,
decode(d.status, 'ONLINE', 'Y', 'OFFLINE', 'N', '?') status,
NVL(o.ownr, 0) ownr, NVL(a.bytes /1024/1024, 0) sz,
((NVL(a.bytes/1024/1024,0))-(NVL((a.bytes-t.bytes),
a.bytes)/1024/1024)) usd,
NVL(g.bytes/1024/1024,0) awsz, NVL(g.awcnt,0) aws, NVL(g.segcnt,0)
segs
FROM sys.dba_tablespaces d,
(select tablespace_name, autoextensible, sum(bytes) bytes
from dba_temp_files group by tablespace_name, autoextensible) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool group by tablespace_name) t,
(select dbas.tablespace_name, count(distinct table_name) as awcnt,
count(*) as segcnt, sum(dbas.bytes) bytes
from dba_lobs dbal, dba_segments dbas
where dbal.column_name = 'AWLOB' and
dbal.segment_name = dbas.segment_name
group by dbas.tablespace_name) g,
(select tablespace_name, count(distinct owner) ownr
from dba_segments group by tablespace_name) o
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND
d.tablespace_name = g.tablespace_name(+) AND
d.tablespace_name = o.tablespace_name(+) AND
d.extent_management = 'LOCAL' AND
d.contents = 'TEMPORARY'
ORDER BY ttype, name;

T A L
Y U I
P T V
Tablespace E O E Users Size(MB) Used(MB) AW(MB) AW# AWPts
------------ - - - ----- ---------- ---------- ---------- ---- -----
SCOTT_TEST P N Y 0 1.0 0.1 0.0 0 0
SYSAUX P Y Y 14 290.0 282.7 18.9 5 5
SYSTEM P Y Y 3 460.0 450.9 0.0 0 0
SCOTT P N Y 1 622,573.0 541,954.8 509,348.0 1 8
SCOTT_2000 P N Y 1 1,107.0 1,106.1 0.0 0 0
SCOTT_2001 P N Y 1 1,625.0 1,624.1 0.0 0 0
SCOTT_2002 P N Y 1 1,706.0 1,705.1 0.0 0 0
SCOTT_2003 P N Y 1 1,757.0 1,756.1 0.0 0 0
SCOTT_2004 P N Y 1 900.0 892.1 0.0 0 0
USERS P Y Y 1 5.0 0.4 0.0 0 0
SCOTT_TEMP T N Y 0 86,175.0 36,764.5 0.0 0 0
SCOTT_TMP T N Y 0 61,440.0 37,875.0 0.0 0 0
TEMP T Y Y 0 4,277.0 4,276.0 0.0 0 0
UNDO U N Y 1 8,192.0 2,332.9 0.0 0 0
UNDOTBS1 U Y Y 1 60.0 12.1 0.0 0 0
---------- ---------- ---------- ---- -----
total 790,568.0 631,032.6 509,366.9 6 13

[ola...@gmail.com]

Columns Explained:

Tablespace = name of tablespace
Users = how many users own objects in said tablespace
TYPE = U:UNDO T:TEMPORARY P:PERMANENT
AUTO = Is said tablespace auto extensible Y or N
LIVE = Status of said tablespace Y:ONLINE N:OFFLINE
Size(MB) = Total Size of said tablespace
Used(MB) = Total Space occupied by objects in said tablespace
AW(MB) = How much space is consumed by Analytic Workspaces
in said tablespace
AW# = Count of Analytic Workspaces in said tablepace
AWPts = Count of Partitions for Analytic Workspaces in
said tablespace

[ola...@gmail.com]