共计 3077 个字符，预计需要花费 8 分钟才能阅读完成。
随着 Oracle19c 被越来越多的客户应用，pdb 模式越来越多，如何更好更疾速的查看他们的信息呢，Oracle12c 当前多了一个 cdb_结尾的视图，来进行对立查看。上面节选几个常用命令、
– 查看 pdb 信息 or 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;
– 查看 CDB 局部信息
set lines 160
col con_name for a30
select c.con_id,c.name con_name,t.tablespace_name,t.contents,t.status
from v$containers c,cdb_tablespaces t where c.con_id=t.con_id and t.contents=’UNDO’
order by 1,2;
– 追随 CDB 启动
alter pluggable database firsoul01 save state;
– 查看每个 pdb 大小
select c.name,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 order by 2 desc;
– 连贯 or tnsnames
alter session set container=orclpdb;
– 查看 pdb 的服务名
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;
– 查看历史 pdb 信息
col db_name for a15
col pdb_name for a15
col CLONED_FROM_PDB_NAME for a20
SELECT DB_NAME, CON_ID, 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.sumbytes+d.Extend_bytes)/1024/1024/1024,2) free_with_extend_GB,
round((d.sumbytes-f.sumbytes)/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.name dbfile,round(f.bytes/1024/1024/1024,2) size_gb,f.AUTOEXTENSIBLE ,d.CREATION_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;
– 查看每个 pdb 下用户大小
break on pname 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;
– 查问用户名、锁定状态、表空间、配置文件、权限 —
break on cname skip 1
set pagesize 999
set linesize 150
col cname for a20
col username for 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;
其余能够参考以上语句，cdb 结尾视图 联合 v$containers，列出 pdb 的命令，再个 可依据本人爱好，能够抉择排除种子 pdb、cdb 等输入。