As more and more customers use Oracle19c, more and more PDB mode, how to better and faster check their information, Oracle12c later added a view beginning with CDB_, for unified check. Here are a few common commands,
Dba_pdbs set lines 160 col name for a20 col open_time for a40 select con_id,dbid,name,open_mode,open_time,creation_time from v$pdbs;
Set lines 160 col con_name for a30 select c.on_id,c.name con_name, t.tabespace_name, t.ontents, t.atus From v$containers c,cdb_tablespaces t where c.on_id = t.on_id and t.ontents =’UNDO’ order by 1,2;
ALTER Pluggable database FIRSoul01 save state;
SELECT c.name,round(sum(s.bytes/1024/1024/1024),2) SIZE_GB FROM CDB_SEGMENTS, V $containers C WHERE c.con_id=s.con_id group by c.name order by 2 desc;
Or tnsnames alter session set container=orclpdb;
– check the PDB service name col PDB for a15 col name for a20 col network_name for a20 SELECT PDB, name, network_name, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL AND CON_ID > 2 ORDER BY PDB;
Col CLONED_FROM_PDB_NAME for a15 col CLONED_FROM_PDB_NAME for a20 SELECT db_name, CON_ID, CON_ID for a15 col CLONED_FROM_PDB_NAME for a20 pdb_id,PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME FROM CDB_PDB_HISTORY WHERE CON_ID<>pdb_id and pdb_id>2 and db_name=(select name from v$database) ORDER BY PDB_ID;
Break on name skip 1 col name for a20 col tablespace_name for a30 set linesize 200 pagesize 500 select c.name name,f.tablespace_name tablespace_name, round((d.sumbytes/1024/1024/1024),2) total_without_extend_GB, round(((d.sumbytes+d.extendwww.walajiao.com_bytes)/1024/1024/1024),2) total_with_extend_GB, Round (f.s umbytes + d.E xtend_bytes) / 1024/1024/1024, 2) free_with_extend_GB, Round (d.s umbytes – f.s umbytes) / 1024/1024/1024, 2) used_GB, round((d.sumbytes-f.sumbytes)*100/(d.sumbytes+d.extend_bytes),2) used_percent_with_extend from (select con_id,tablespace_name,sum(bytes) sumbytes from cdb_free_space group by con_id,tablespace_name) f, (select con_id,tablespace_name,sum(aa.bytes) sumbytes,sum(aa.extend_bytes) extend_bytes from (select con_id,nvl(case when autoextensible =’YES’ then (case when (maxbytes-bytes)>=0 then (maxbytes-bytes) end) end,0) Extend_bytes ,tablespace_name,bytes from cdb_data_files) aa group by con_id,tablespace_name) d,v$containers c where (f.con_id=d.con_id and f.tablespace_name=d.tablespace_name) and f.con_id=c.con_id order by name,used_percent_with_extend desc;
Break on cdbname skip 1 set lines 200 pages 999 col cdbname for a10 col dbfile for a80 select c.name Cdbname,d.file# dfile, d.namedbfile,round(f.ytes /1024/1024/1024,2) size_gb, f.utotensible, d.reation_time from v$datafile d,v$containers c,cdb_data_files f where c.con_id=d.con_id and d.name=f.file_name order by cdbname,d.creation_time;
Skip 1 col owner for a20 col pname for a15 select c.name pname,s.owner,round(sum(s.bytes/1024/1024/1024),2) size_gb from cdb_segments s,v$containers c where c.con_id=s.con_id Group by c.name,s.owner order by 1,3 desc;
SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname SELECT * FROM cname a25 col ACCOUNT_STATUS for a20 col DEFAULT_TABLESPACE for a20 col PROFILE for a20 alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’; select v.name cname,c.username,’,’,c.ACCOUNT_STATUS,’,’ ,c.DEFAULT_TABLESPACE,’,’,c.PROFILE,c.CREATED from cdb_users c,v$containers v where c.con_id=v.con_id order by v.name,c.created; For others, you can refer to the above statement. The beginning view of CDB is combined with V $containers, and the commands of PDB are listed. In addition, according to your preferences, you can choose to exclude seed PDB, CDB and other outputs.