Oracle19c 定制脚本mystat， show_space

DB架构

于 2022-09-29 10:12:50 发布

阅读量198

点赞数

分类专栏： DBA 文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/u011868279/article/details/127099894

版权

DBA 专栏收录该内容

183 篇文章 12 订阅

订阅专栏

MYSTAT

mystat.sql和相应的mystat2.sql用于展示完成某操作之前和之后的某些Oracle“统计结果”的变化情况。mystat.sql只是获得统计结果的开始值：

[oracle@MaxwellDBA admin]$ cat mystat.sql
set echo off
set verify off
column value new_val V
define S="&1"

set autotrace off
select a.name,b.value
from v$statname a,v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
[oracle@MaxwellDBA admin]$

mystat2.sql用于报告差值（通过运行第一个脚本mystat.sql来填充&V，为此它使用了SQL*Plus NEW_VAL特性，其中包含一个查询选择的最后一个VALUE）；

[oracle@MaxwellDBA admin]$ cat mystat2.sql 
set echo off
set verify off
select a.name,b.value V,to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
[oracle@MaxwellDBA admin]$

例如，要查看某个UPDATE生成的redo数，可以使用以下命令：

scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> show con_name;

CON_NAME
------------------------------
ORCLPDB1
scott@orclpdb1:orclcdb> show user;
USER is "SCOTT"
scott@orclpdb1:orclcdb> @/opt/oracle/product/19c/dbhome_1/rdbms/admin/mystat.sql "redo size"

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          32179648
redo size for lost write detection                                        0
redo size for direct writes                                               0

3 rows selected.

scott@orclpdb1:orclcdb> set echo off

scott@orclpdb1:orclcdb> update big_table set owner = lower(owner) where rownum <= 1000;

1000 rows updated.

scott@orclpdb1:orclcdb> @/opt/oracle/product/19c/dbhome_1/rdbms/admin/mystat2.sql
scott@orclpdb1:orclcdb> set echo off

NAME                                                                      V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size                                                          32282456       32,282,456
redo size for lost write detection                                        0                0
redo size for direct writes                                               0                0

3 rows selected.

scott@orclpdb1:orclcdb> set echo off
scott@orclpdb1:orclcdb>

SHOW_SPACE

show_space 例程用于打印数据库段的空间利用率信息。其接口如下：

create or replace procedure show_space(p_segname_1 in varchar2,
                                       p_owner_1   in varchar2 default user,
                                       p_type_1    in varchar2 default 'TABLE',
                                       p_space     in varchar2 default 'AUTO',
                                       p_analyzed  in varchar2 default 'Y') as
  p_segname varchar2(100);
  p_type    varchar2(10);
  p_owner   varchar2(30);

  l_unformatted_blocks number;
  l_unformatted_bytes  number;
  l_fs1_blocks         number;
  l_fs1_bytes          number;
  l_fs2_blocks         number;
  l_fs2_bytes          number;
  l_fs3_blocks         number;
  l_fs3_bytes          number;
  l_fs4_blocks         number;
  l_fs4_bytes          number;
  l_full_blocks        number;
  l_full_bytes         number;

  l_free_blks          number;
  l_total_blocks       number;
  l_total_bytes        number;
  l_unused_blocks      number;
  l_unused_bytes       number;
  l_LastUsedExtFileId  number;
  l_LastUsedExtBlockId number;
  l_LAST_USED_BLOCK    number;

  procedure p(p_label in varchar2, p_num in number) is
  begin
    dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
  end;
begin
  p_segname := upper(p_segname_1); -- rainy changed
  p_owner   := upper(p_owner_1);
  p_type    := p_type_1;

  if (p_type_1 = 'i' or p_type_1 = 'I') then
    --rainy changed
    p_type := 'INDEX';
  end if;

  if (p_type_1 = 't' or p_type_1 = 'T') then
    --rainy changed
    p_type := 'TABLE';
  end if;

  if (p_type_1 = 'c' or p_type_1 = 'C') then
    --rainy changed
    p_type := 'CLUSTER';
  end if;

  dbms_space.unused_space(segment_owner             => p_owner,
                          segment_name              => p_segname,
                          segment_type              => p_type,
                          total_blocks              => l_total_blocks,
                          total_bytes               => l_total_bytes,
                          unused_blocks             => l_unused_blocks,
                          unused_bytes              => l_unused_bytes,
                          LAST_USED_EXTENT_FILE_ID  => l_LastUsedExtFileId,
                          LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
                          LAST_USED_BLOCK           => l_LAST_USED_BLOCK);

  if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
    dbms_space.free_blocks(segment_owner     => p_owner,
                           segment_name      => p_segname,
                           segment_type      => p_type,
                           freelist_group_id => 0,
                           free_blks         => l_free_blks);
  
    p('Free Blocks', l_free_blks);
  end if;

  p('Total Blocks', l_total_blocks);
  p('Total Bytes', l_total_bytes);
  p('Unused Blocks', l_unused_blocks);
  p('Unused Bytes', l_unused_bytes);
  p('Last Used Ext FileId', l_LastUsedExtFileId);
  p('Last Used Ext BlockId', l_LastUsedExtBlockId);
  p('Last Used Block', l_LAST_USED_BLOCK);

  /*IF the segment is analyzed */
  if p_analyzed = 'Y' then
    dbms_space.space_usage(segment_owner      => p_owner,
                           segment_name       => p_segname,
                           segment_type       => p_type,
                           unformatted_blocks => l_unformatted_blocks,
                           unformatted_bytes  => l_unformatted_bytes,
                           fs1_blocks         => l_fs1_blocks,
                           fs1_bytes          => l_fs1_bytes,
                           fs2_blocks         => l_fs2_blocks,
                           fs2_bytes          => l_fs2_bytes,
                           fs3_blocks         => l_fs3_blocks,
                           fs3_bytes          => l_fs3_bytes,
                           fs4_blocks         => l_fs4_blocks,
                           fs4_bytes          => l_fs4_bytes,
                           full_blocks        => l_full_blocks,
                           full_bytes         => l_full_bytes);
    dbms_output.put_line(rpad(' ', 50, '*'));
    dbms_output.put_line('The segment is analyzed');
    p('0% -- 25% free space blocks', l_fs1_blocks);
    p('0% -- 25% free space bytes', l_fs1_bytes);
    p('25% -- 50% free space blocks', l_fs2_blocks);
    p('25% -- 50% free space bytes', l_fs2_bytes);
    p('50% -- 75% free space blocks', l_fs3_blocks);
    p('50% -- 75% free space bytes', l_fs3_bytes);
    p('75% -- 100% free space blocks', l_fs4_blocks);
    p('75% -- 100% free space bytes', l_fs4_bytes);
    p('Unused Blocks', l_unformatted_blocks);
    p('Unused Bytes', l_unformatted_bytes);
    p('Total Blocks', l_full_blocks);
    p('Total bytes', l_full_bytes);
  
  end if;

end;
/

[root@MaxwellDBA ~]# 
[root@MaxwellDBA ~]# su - oracle
Last login: Thu Sep 29 06:28:24 CST 2022 on pts/2
[oracle@MaxwellDBA ~]$ 

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 29 10:02:07 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@cdb$root:orclcdb> show con_name;

CON_NAME
------------------------------
CDB$ROOT
sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;

Session altered.

sys@cdb$root:orclcdb> show con_name;

CON_NAME
------------------------------
ORCLPDB1
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME_1                    VARCHAR2                IN
 P_OWNER_1                      VARCHAR2                IN     DEFAULT
 P_TYPE_1                       VARCHAR2                IN     DEFAULT
 P_SPACE                        VARCHAR2                IN     DEFAULT
 P_ANALYZED                     VARCHAR2                IN     DEFAULT

scott@orclpdb1:orclcdb> exec show_space('BIG_TABLE');
Total Blocks............................1280
Total Bytes.............................10485760
Unused Blocks...........................118
Unused Bytes............................966656
Last Used Ext FileId....................12
Last Used Ext BlockId...................24576
Last Used Block.........................10
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................1134
Total bytes.............................9289728

PL/SQL procedure successfully completed.

scott@orclpdb1:orclcdb> create table test01 as select * from all_objects;

Table created.

scott@orclpdb1:orclcdb> set serveroutput on;
scott@orclpdb1:orclcdb> exec show_space('TEST01');
Total Blocks............................1152
Total Bytes.............................9437184
Unused Blocks...........................29
Unused Bytes............................237568
Last Used Ext FileId....................12
Last Used Ext BlockId...................25856
Last Used Block.........................99
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................1097
Total bytes.............................8986624

PL/SQL procedure successfully completed.

scott@orclpdb1:orclcdb> delete from TEST01;

54919 rows deleted.

scott@orclpdb1:orclcdb> exec show_space('TEST01');
Total Blocks............................1152
Total Bytes.............................9437184
Unused Blocks...........................29
Unused Bytes............................237568
Last Used Ext FileId....................12
Last Used Ext BlockId...................25856
Last Used Block.........................99
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........1097
75% -- 100% free space bytes............8986624
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0

PL/SQL procedure successfully completed.

scott@orclpdb1:orclcdb> alter table TEST01 move;

Table altered.

scott@orclpdb1:orclcdb> exec show_space('TEST01');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................12
Last Used Ext BlockId...................24712
Last Used Block.........................3
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0

PL/SQL procedure successfully completed.

scott@orclpdb1:orclcdb>