DBA Hub

📋Steps in this guide1/1

Tablespace monitoring in oracle 12c multitenant database DBACLASS

You can use this script to check tablespace space details in 12c Multitenant database. It will provide information of both root CDB and PDB. SET LINES 132 PAGES 100 COL con_name FORM A15 HEAD "Container|Name" COL tablespace_name FORM A15 COL fsm FORM 999,999,999,999 HEAD "Free|Space Meg." COL apm FORM 999,999,999,999 […]

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

You can use this script to check tablespace space details in 12c Multitenant database. It will provide information of both root CDB and PDB. OUTPUT

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SET LINES 132 PAGES 100
COL con_name        FORM A15 HEAD "Container|Name"
COL tablespace_name FORM A15
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 ON 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, 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, x.fsm, y.apm
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
SELECT vc2.con_id, vc2.name, tf.tablespace_name, null, SUM(tf.bytes)/1024/1024
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;

Container				       Free	       Alloc
    CON_ID Name 	   TABLESPACE_NAME	 Space Meg.	  Space Meg.
---------- --------------- --------------- ---------------- ----------------
	 1 CDB$ROOT	   CHARSETCONVERSI		199		 200
			   ON

			   PRODUCING			 99		 100
			   SYSAUX			382		 920
			   SYSTEM			  3		 810
			   TEMP 					 197
			   TEST_ENCRY		      2,047	       2,048
			   UNDOTBS1			360		 405
			   USERS			  4		   5
	 5 PROD_MN	   SYSAUX			253		 570
			   SYSTEM			  1		 270
			   TEMP 					  20
********** *************** *************** ---------------- ----------------
sum						      3,347	       5,545

11 rows selected.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!