ANBOB™

You can use this SQL script to report tablespace space details in 12c Multitenant database.

--
-- file: tablespace_rpt12.sql
-- purpose: To report tablespaces for 12c+ Multitenant database
-- author: weejar zhang (www.anbob.com)
--
SET LINES 300 PAGES 100
COL con_name        FORM A15 HEAD "Container|Name"
COL files           FORM 999,999 HEAD "Num Files"
COL tablespace_name FORM A30
COL fsm             FORM 999,999,999,999 HEAD "Free|Space Meg."
COL apm             FORM 999,999,999,999 HEAD "Alloc|Space Meg."
--
COMPUTE SUM OF fsm apm files ON con_id REPORT
BREAK ON REPORT ON con_id ON con_name ON tablespace_name
--
WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm
           FROM cdb_free_space cf1
               ,v$containers c1
           WHERE cf1.con_id = c1.con_id
           GROUP BY c1.con_id, cf1.tablespace_name),
     y AS (SELECT c2.con_id, cd.tablespace_name, count(*) files,SUM(cd.bytes)/1024/1024 apm
           FROM cdb_data_files cd
               ,v$containers c2
           WHERE cd.con_id = c2.con_id
           GROUP BY c2.con_id
                   ,cd.tablespace_name)
SELECT x.con_id, v.name  con_name, x.tablespace_name,files, x.fsm, y.apm, round(1-fsm/apm,2) pct
FROM x, y, v$containers v
WHERE x.con_id          = y.con_id
AND   x.tablespace_name = y.tablespace_name
AND   v.con_id          = y.con_id
UNION All
SELECT vc2.con_id, vc2.name , tf.tablespace_name,count(*) files, null, SUM(tf.bytes)/1024/1024, null
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.con_id, vc2.name , tf.tablespace_name
ORDER BY 1, 2; 

打赏

微信扫一扫，打赏作者吧～