重庆思庄Oracle、Redhat认证学习论坛

标题: 检查Oracle数据库和PDB数据库的大小 [打印本页]

作者: jiawang 时间: 2022-12-17 12:51
标题: 检查Oracle数据库和PDB数据库的大小
检查Oracle数据库和PDB数据库的大小

NON-CDB数据库的以下查询：
检查磁盘上数据库消耗的物理消耗量。
SQL> select sum(bytes)/1024/1024 size_in_mb from dba_data_files;
SIZE_IN_MB
----------
   1790

检查数据使用的总空间。
SQL> select sum(bytes)/1024/1024 size_in_mb from dba_segments;
SIZE_IN_MB
----------
  1521.375

在Oracle中检查用户或模式的大小。
SQL> col owner for a50
SQL> select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner;
OWNER                                              SIZE_MB
-------------------------------------------------- ----------
SYS                                              1230.9375
SYSTEM                                              18.125
DBSNMP                                                 .375
CTXSYS                                              2.6875
OJVMSYS                                                 .375
DVSYS                                                 4.5625
AUDSYS                                                 .6875
GSMADMIN_INTERNAL                                        1
ORDDATA                                              1.3125
MDSYS                                              185.0625
LBACSYS                                              .3125
OUTLN                                                 .5625
XDB                                                 68.4375
WMSYS                                                 6.5625
ORDSYS                                                 .375

15 rows selected.

检查数据库中的可用空间和已用空间。
select
"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual );
Reserved_Space(MB) Used_Space(MB) Free_Space(MB)
------------------ -------------- --------------
      1807.65286    1543.02786       264.625

检查数据库，临时文件和重做文件的整体大小。
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual;

Size in GB
----------
2.40008545

跟踪CDB和PDB数据库的查询
检查数据库的PDB大小
select con_id, name, open_mode, total_size from v$pdbs;

检查数据库的CDB大小
select sum(size)/1024/1024/1024 from cdb_data_files;

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)