SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL>
SQL>
SQL>
SQL> SELECT
2 METRICS.TABLESPACE_NAME,
3 C.NAME,
4 CASE WHEN METRICS.CON_ID <= 1 THEN 'FALSE' ELSE 'TRUE' END AS PDB,
5 METRICS.USED_PERCENT AS USAGE,
6 TBS.BLOCK_SIZE * METRICS.TABLESPACE_SIZE AS MAX_B,
7 TBS.BLOCK_SIZE * METRICS.USED_SPACE AS USED_B,
8 TBS.CONTENTS AS CONTENT_TYPE,
9 T.BYTES AS ALLOCATED_B,
10 (METRICS.TABLESPACE_SIZE - METRICS.USED_SPACE)*TBS.BLOCK_SIZE AS FREE_B
11 FROM
12 (
13 SELECT
14 TABLESPACE_NAME,
15 CON_ID,
16 SUM(BYTES) BYTES
17 FROM
18 CDB_DATA_FILES
19 GROUP BY
20 TABLESPACE_NAME,
21 CON_ID
22 UNION ALL
23 SELECT
24 TABLESPACE_NAME,
25 CON_ID,
26 SUM(BYTES) BYTES
27 FROM
28 CDB_TEMP_FILES
29 GROUP BY
30 TABLESPACE_NAME,
31 CON_ID
32 ) T
33 JOIN (SELECT * FROM CDB_TABLESPACE_USAGE_METRICS) METRICS
34 ON T.TABLESPACE_NAME=METRICS.TABLESPACE_NAME AND T.CON_ID=METRICS.CON_ID
35 JOIN ( SELECT * FROM CDB_TABLESPACES ) TBS
36 ON METRICS.TABLESPACE_NAME=TBS.TABLESPACE_NAME AND METRICS.CON_ID=TBS.CON_ID
37 JOIN V$CONTAINERS C
38 ON METRICS.CON_ID = C.CON_ID;
TABLESPACE_NAME NAME PDB USAGE MAX_B USED_B CONTENT_TYPE ALLOCATED_B FREE_B
------------------------------ ------------------------------ --------------- ---------- ---------- ---------- --------------------------------------------------------------- ----------- ----------
AUFTRAG_TS MEWA05 TRUE 1.25178343 3.4359E+10 430096384 PERMANENT 462094336 3.3929E+10
MSG_IDX MEWA05 TRUE .167851802 3.4359E+10 57671680 PERMANENT 325058560 3.4301E+10
TOUR_TS MEWA05 TRUE .83077105 3.4359E+10 285442048 PERMANENT 320798720 3.4073E+10
DT_IDX MEWA05 TRUE .135616626 3.4359E+10 46596096 PERMANENT 188743680 3.4312E+10
AUFTRAG_IDX MEWA05 TRUE .290831858 3.4359E+10 99926016 PERMANENT 515899392 3.4259E+10
KONFIG_MTS_TS MEWA05 TRUE .022459716 3.4359E+10 7716864 PERMANENT 31457280 3.4351E+10
ZOLL_TS MEWA05 TRUE .347148045 3.4359E+10 119275520 PERMANENT 125829120 3.4239E+10
AUFTRAG_HIST_TS MEWA05 TRUE 2.18958384 3.4359E+10 752312320 PERMANENT 1080033280 3.3606E+10
INDX MEWA05 TRUE .003051851 3.4359E+10 1048576 PERMANENT 104857600 3.4358E+10
INTRADIA_IDX MEWA05 TRUE .005340739 3.4359E+10 1835008 PERMANENT 10485760 3.4357E+10
WAESCHE_IDX MEWA05 TRUE .257261498 3.4359E+10 88391680 PERMANENT 702545920 3.4270E+10
ZDISPO_TS MEWA05 TRUE .229651784 3.4359E+10 78905344 PERMANENT 113246208 3.4280E+10
WASCHPOSTEN_TS MEWA05 TRUE .009918516 3.4359E+10 3407872 PERMANENT 10485760 3.4355E+10
SYSTEM CDB$ROOT FALSE 2.52800108 3.4360E+10 868614144 PERMANENT 870318080 3.3491E+10
SPRACHE_TS MEWA05 TRUE .585955382 3.4359E+10 201326592 PERMANENT 220200960 3.4157E+10
SYSTEM_TS MEWA05 TRUE 11.0631027 3.4359E+10 3801137152 PERMANENT 4210917376 3.0558E+10
ALERT_IDX MEWA05 TRUE .004530091 3.4359E+10 1556480 PERMANENT 104857600 3.4357E+10
ZTM_IDX MEWA05 TRUE .21744438 3.4359E+10 74711040 PERMANENT 576716800 3.4284E+10
MKS_IDX MEWA05 TRUE .003051851 3.4359E+10 1048576 PERMANENT 10485760 3.4358E+10
TOUR_IDX MEWA05 TRUE .241072382 3.4359E+10 82829312 PERMANENT 125321216 3.4276E+10
WAESCHE_MTS_IDX MEWA05 TRUE .566619045 3.4359E+10 194682880 PERMANENT 237002752 3.4164E+10
ZOLL_IDX MEWA05 TRUE .032044435 3.4359E+10 11010048 PERMANENT 20971520 3.4348E+10
WSPRUEF_IDX MEWA05 TRUE .118831446 3.4359E+10 40828928 PERMANENT 62914560 3.4318E+10
HOST_IDX MEWA05 TRUE 1.86584922 3.4359E+10 641081344 PERMANENT 1513799680 3.3718E+10
SPRACHE_IDX MEWA05 TRUE .415051729 3.4359E+10 142606336 PERMANENT 230686720 3.4216E+10
SYSTEM_IDX MEWA05 TRUE .738476402 3.4359E+10 253730816 PERMANENT 325468160 3.4105E+10
WAESCHE_MTS_TS MEWA05 TRUE 1.34305284 3.4359E+10 461455360 PERMANENT 506871808 3.3897E+10
IDOCDATA_TS MEWA05 TRUE 27.6459548 3.4359E+10 9498787840 PERMANENT 9978249216 2.4860E+10
ZDISPO_IDX MEWA05 TRUE .003051851 3.4359E+10 1048576 PERMANENT 10485760 3.4358E+10
SYSTEM MEWA05 TRUE .945091698 3.4360E+10 324730880 PERMANENT 325058560 3.4035E+10
INTRADIA_TS MEWA05 TRUE .009918516 3.4359E+10 3407872 PERMANENT 10485760 3.4355E+10
TEILEBEWEGUNG_TS MEWA05 TRUE 1.19961585 3.4359E+10 412172288 PERMANENT 681574400 3.3947E+10
TEMP CDB$ROOT FALSE .15625 5368709120 8388608 TEMPORARY 24117248 5360320512
AA_TS MEWA05 TRUE .562065111 3.4359E+10 193118208 PERMANENT 244318208 3.4166E+10
DEPOT_TS MEWA05 TRUE 1.16316053 3.4359E+10 399646720 PERMANENT 507510784 3.3959E+10
HOST_TS MEWA05 TRUE 15.7721326 3.4359E+10 5419098112 PERMANENT 5697273856 2.8940E+10
LV_IDX MEWA05 TRUE .053931928 3.4359E+10 18530304 PERMANENT 62914560 3.4340E+10
MSG_TS MEWA05 TRUE 2.28888821 3.4359E+10 786432000 PERMANENT 3942645760 3.3572E+10
STATISTIK_TS MEWA05 TRUE .035358554 3.4359E+10 12148736 PERMANENT 17825792 3.4347E+10
TASKFORCE_TS MEWA05 TRUE .003051851 3.4359E+10 1048576 PERMANENT 5242880 3.4358E+10
TEIL_IDX MEWA05 TRUE 1.50031854 3.4359E+10 515489792 PERMANENT 819986432 3.3843E+10
TEIL_TS MEWA05 TRUE 2.52714717 3.4359E+10 868294656 PERMANENT 915775488 3.3490E+10
WSPRUEF_TS MEWA05 TRUE .839259011 3.4359E+10 288358400 PERMANENT 304087040 3.4070E+10
DT_TS MEWA05 TRUE 1.66974395 3.4359E+10 573702144 PERMANENT 807403520 3.3785E+10
ESD_IDX MEWA05 TRUE .100711081 3.4359E+10 34603008 PERMANENT 41943040 3.4324E+10
SYSAUX MEWA05 TRUE 28.6897637 1.0308E+11 2.9573E+10 PERMANENT 9.2525E+10 7.3506E+10
UNDOTBS1 MEWA05 TRUE .537872571 6.8719E+10 369623040 UNDO 3.9725E+10 6.8350E+10
ALERT_TS MEWA05 TRUE .079848819 3.4359E+10 27435008 PERMANENT 104857600 3.4331E+10
KARDEX MEWA05 TRUE 1.5 209715200 3145728 PERMANENT 209715200 206569472
USERS CDB$ROOT FALSE .003051759 3.4360E+10 1048576 PERMANENT 5242880 3.4359E+10
USERS MEWA05 TRUE .095367477 3.4360E+10 32768000 PERMANENT 35389440 3.4327E+10
ARTIKEL_TS MEWA05 TRUE .359307764 3.4359E+10 123453440 PERMANENT 149258240 3.4235E+10
TEILEBEWEGUNG_IDX MEWA05 TRUE .259455016 3.4359E+10 89145344 PERMANENT 1081081856 3.4270E+10
WAESCHE_TS MEWA05 TRUE .876405759 3.4359E+10 301121536 PERMANENT 334151680 3.4058E+10
WASCHPOSTEN_IDX MEWA05 TRUE .035096286 3.4359E+10 12058624 PERMANENT 20971520 3.4347E+10
TEMP MEWA05 TRUE 0 3.4351E+10 0 TEMPORARY 1.3019E+10 3.4351E+10
ARTIKEL_IDX MEWA05 TRUE .099995804 3.4359E+10 34357248 PERMANENT 84934656 3.4324E+10
MKS_TS MEWA05 TRUE .003051851 3.4359E+10 1048576 PERMANENT 10485760 3.4358E+10
IDOCBUFFER_TS MEWA05 TRUE 20.7922605 3.4359E+10 7143948288 PERMANENT 7511998464 2.7215E+10
AA_IDX MEWA05 TRUE .379693175 3.4359E+10 130457600 PERMANENT 181657600 3.4228E+10
DEPOT_IDX MEWA05 TRUE .280603389 3.4359E+10 96411648 PERMANENT 257949696 3.4262E+10
ZTM_TS MEWA05 TRUE .544755394 3.4359E+10 187170816 PERMANENT 220200960 3.4172E+10
ESD_TS MEWA05 TRUE .019837031 3.4359E+10 6815744 PERMANENT 10485760 3.4352E+10
SYSAUX CDB$ROOT FALSE 5.38273114 3.4360E+10 1849491456 PERMANENT 2002780160 3.2510E+10
KONFIG_MTS_IDX MEWA05 TRUE .028468047 3.4359E+10 9781248 PERMANENT 31457280 3.4349E+10
LV_TS MEWA05 TRUE .100019646 3.4359E+10 34365440 PERMANENT 52428800 3.4324E+10
STATISTIK_IDX MEWA05 TRUE .000548379 3.4359E+10 188416 PERMANENT 45088768 3.4359E+10
USERS_PDB MEWA05 TRUE .748275704 3.4359E+10 257097728 PERMANENT 318767104 3.4102E+10
VORWAESCHE_TS MEWA05 TRUE 3.02591021 3.4359E+10 1039663104 PERMANENT 1101004800 3.3319E+10
VORWAESCHE_IDX MEWA05 TRUE .088503677 3.4359E+10 30408704 PERMANENT 52428800 3.4328E+10
IDOCDATA_IDX MEWA05 TRUE .287636952 3.4359E+10 98828288 PERMANENT 216006656 3.4260E+10
UNDOTBS1 CDB$ROOT FALSE .084114115 3.4360E+10 28901376 UNDO 387973120 3.4331E+10
72 rows selected.
From the general metric, we can only see the tablespaces inside the ROOT container and not in the Pluggable Database:
We tried to filter the ROOT container and to see only the tablespaces inside the Pluggable Database and for this purpose we used the Data Explorer. Unfortunately, that was NOT possible:
And in the end please check and test from your side and make it working, because this is essential and in the current client which is LIVE with Dynatrace as a primary monitoring it is not possible to monitor the tablespaces inside the Pluggable Database and it is IMPORTANT to monitor them!
The current version of the Oracle Extension is 2.3.0
Featured Posts
