oracle19c使用pdb,19c cdb/pdb 常见监控sql

You must be familiar with checking tablespace utilization in Oracle 11g, here we will discuss about monitoring tablespace in CDB and PDB.

Oracle introduced multitenant architecture from Oracle 12c and in other higher versions like 19c which contains container and pluggable databases CDB and PDB.

There are some new views introduced in 12c multitenant architecture to check size of datafiles and tempfiles tablespace used in CDB and PDB in Oracle database. We have used these views in below below script for monitoring tablespace usage in Oracle.

cdb_data_files

cdb_segments

v$containers

Script to Monitor Tablespace Utilization in Oracle 12c, 19c and other higher versions.

set pages 80

set lin 120

set echo off

set feed off

column PCT format 999.99

column tablespace format A30

col container for a30

select substr(t.tablespace_name,1,30) tablespace,

round(t.tsbytes/(10241024),0) "ALLOCATED(MB)", round(nvl(s.segbytes,0)/(10241024),0) "USED(MB)",

round((nvl(s.segbytes,0)/t.tsbytes)*100,2) PCT,

lower(vc.name) as container

from

(select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) t,

(select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s,

v$containers vc

where t.con_id=s.con_id(+)

and t.tablespace_name=s.tablespace_name(+)

and t.con_id=vc.con_id

order by container, tablespace;

TABLESPACE ALLOCATED(MB) USED(MB) PCT CONTAINER

------------- ------- ------ --------

SYSAUX 1630 1268 77.78 cdb$root

SYSTEM 1250 1244 99.55 cdb$root

UNDOTBS1 545 17 3.18 cdb$root

USERS 8 6 78.33 cdb$root

APPS_TS_ARCHIVE 5986 489 8.17 tst31ut

APPS_TS_INTERFACE 1041 431 41.43 tst31ut

APPS_TS_MEDIA 40394 38294 94.80 tst31ut

Check Tablespace Usage Percentage in Oracle PDB and CDB Database.

set line 200 pages 999

column name for a10

column tablespace_name for a15

column "MAXSIZE (MB)" format 9,999,990.00

column "ALLOC (MB)" format 9,999,990.00

column "USED (MB)" format 9,999,990.00

column "PERC_USED" format 99.00

select a.con_id,c.name,b.tablespace_name,a.bytes_alloc/(10241024) "MAXSIZE (MB)", nvl(a.physical_bytes,0)/(10241024) "ALLOC (MB)" ,nvl(b.tot_used,0)/(10241024) "USED (MB)" ,(nvl(b.tot_used,0)/a.bytes_alloc)100 "PERC_USED"

from

(select con_id,tablespace_name, sum(bytes) physical_bytes,sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc

from cdb_data_files group by con_id,tablespace_name ) a,

(select con_id,tablespace_name, sum(bytes) tot_used from cdb_segments group by con_id,tablespace_name ) b,

(select name,con_id from v$containers) c

where a.con_id= b.con_id and a.con_id = c.con_id and a.tablespace_name = b.tablespace_name (+)

order by 1,3;

CON_ID NAME TABLESPACE_NAME MAXSIZE (MB) ALLOC (MB) USED (MB) PERC_USED

--------------- ------------ ----------- ---------------- --------------

1 CDB$ROOT SYSAUX 32,767.98 1,630.00 1,274.00 3.89

1 CDB$ROOT SYSTEM 32,767.98 1,250.00 1,244.38 3.80

1 CDB$ROOT UNDOTBS1 32,767.98 545.00 17.25 .05

1 CDB$ROOT USERS 32,767.98 7.50 5.88 .02

3 tst31ut APPS_TS_ARCHIVE 21,346.00 5,986.00 489.25 2.29

3 tst31ut APPS_TS_INTERFA 1,041.00 1,041.00 431.25 41.43

To Check how much Space Used in Oracle

select sum(BYTES)/1024/1024/1024 from cdb_segments;

SUM(BYTES)/1024/1024/1024

-----------------

310.454269

select CON_ID,sum(BYTES)/1024/1024/1024 from cdb_segments group by CON_ID;

CON_ID SUM(BYTES)/1024/1024/1024

------- --------------

1 2.47595215 -CDB Size

3 307.978317 -PDB Size

To Check Datafile Size in Oracle

col FILE_NAME for a55

set lines 200

select FILE_NAME,BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024,AUTOEXTENSIBLE from cdb_data_files where TABLESPACE_NAME='APPS_TS_MEDIA';

FILE_NAME BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024 AUT

/scratch/u01/E-BIZ/db/apps_st/data/a_media01.dbf 1.41210938 0 NO

/scratch/u01/E-BIZ/db/apps_st/data/a_media03.dbf 18.8696289 20 YES

/scratch/u01/E-BIZ/db/apps_st/data/a_media02.dbf 19.1652832 30 YES

Conclusion: Above script is used to check tablespace details in Oracle including tablespace size, freespace, percentage used etc.