2020年10月28日星期三

oracle数据库高水位线问题处理

一、什么是高水线(High Water Mark)?

Oracle 数据库在创建一张表时，会为这张表分配一个段空间（segment）,为了方便理解，把段空间容纳数据的上限，称之为高水位线（HIGH WATER MARK） HWM ，HWM是一个标记，用来说明表示有多少未使用的块分配给这个段。
两个结论：
1.水位线以上表示已经分配但还未使用块（block),水位先以下爱表示已经分配且已经使用过的块（包含了正在使用的块和使用过的且被删除了数据的空块）
2.理论上来说，一张表的水位线只会增大不会减小（除非通过特殊的方法重置），即使将表中的数据全部删除，HWM还是为原值。

二、HWM数据库的操作有如下影响：

a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。

b) 即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，HWM也会不断增大，占用系统资源，表所占的实际空间会不断增大，导致系统出现问题

三、高水位线原因以及解决方法：

产生原因：
1.操作表时使用删除了大量数据。
2.在插入时使用了/append nologging/语句，append关键字会从为表分配段中的随机位置插入，水位线会不断增高。
3.Sql load 时默认使用truncate 自带了reuse storage参数，导致truncate以后水位线不会降低。

解决方法：
1.直接truncate table drop storage
2.建立一张维护表定期move并重建索引或者shrink space。
3.表数据落表时按照日期建立了备份表，保留一定天数数据
4.Rename表名，重建表，重建索引，将数据导入重建表，drop原表，然后rename重建表为原表
5.使用alter table 表名 shrink space（oracle10新增功能）
6.在线表重定义（功能强大，操作复杂，一般不使用，可以改变表的结构）
表重建的两个方法move与shrink的对比：
move是oracle8出现的命令，使用时会创建一块和原来表空间相同大小的另一块表空间，然后进行数据的复制，完成后使用后表替换原表，解决hwm的问题。
缺点：操作时锁表，索引会失效。
shrink是oracle10新增功能，使用时不会开辟新的表空间，操作分为两步，第一步整理数据，第二步降低水位线，进行第一步时，可以在线进行操作。可以再业务不繁忙的时候进行第二步操作。
缺点：相比move速度比较缓慢。
shrink的详细操作步骤：
详细收缩步骤

  1. 全表收缩
    不管分区表还是非分区表，收缩都可以表级别进行，具体语句如下：
    ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;（开启行移动会使游标失效，需谨慎）
    ALTER TABLE owner.table_name SHRINK SPACE COMPACT CASCADE（第一步整理数据，并不会降低高水位线，可以在线进行操作）;
    ALTER TABLE owner.table_name SHRINK SPACE CASCADE（第二步分析重置高水位线，会短暂锁表需要在业务量小的时候进行操作）;

  2. 单个分区收缩
    分区表的收缩还可以分区级别进行，具体语句如下：
    ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;
    ALTER TABLE owner.table_name MODIFY PARTITION partition_name SHRINK SPACE COMPACT CASCADE;
    ALTER TABLE owner.table_name MODIFY PARTITION partition_name SHRINK SPACE CASCADE;
    ALTER INDEX owner.index_name MODIFY PARTITION partition_name SHRINK SPACE COMPACT CASCADE;
    ALTER INDEX owner.index_name MODIFY PARTITION partition_name SHRINK SPACE CASCADE;

四、在线表重定义步骤：

1、确认表是否可以做在线重定义：
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'OWNER',
tname => 'ORIGTABLENAME',
options_flag => dbms_redefinition.cons_use_pk);
END;
/
--如果有主键则是options_flag => DBMS_REDEFINITION.cons_use_pk，如果没有DBMS_REDEFINITION.cons_use_rowid

2、创建新的中间表TABLENAME_TMP准备重定义 (可以新增删除字段、可以修改表存储参数、可以修改为分区表等需要的操作)
注意因为在线重定义过程中要求列的属性要相同，因此不可使用dbms_redefinition完成列类型的调整

--普通表
CREATE TABLE OWNER.TABLENAME_TMP ( ) TABLESPACE XXX;
--分区表
CREATE TABLE OWNER.TABLENAME_TMP ( )
PARTITION BY RANGE (PARTITIONNAME)
(
PARTITION P1 VALUES LESS THAN ('xxx'),
...
PARTITION PMAX VALUES LESS THAN (MAXVALUE)
)
TABLESPACE XXX;

3、开启并行提高在线重定义速度：
ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

4、开始在线重定义：
BEGIN
dbms_redefinition.start_redef_table(
uname => 'OWNER',
orig_table => 'ORIGTABLENAME',
int_table => 'TABLENAME_TMP',
options_flag => dbms_redefinition.cons_use_pk);
END;
/
--如果有主键则是options_flag => DBMS_REDEFINITION.cons_use_pk，如果没有DBMS_REDEFINITION.cons_use_rowid

5、使用COPY_TABLE_DEPENDENTS把原始表的权限、约束、索引等在中间表上创建一份
DECLARE
num_errors PLS_INTEGER;
BEGIN
dbms_redefinition.copy_table_dependents(
uname => 'OWNER',
orig_table => 'ORIGTABLENAME',
int_table => 'TABLENAME_TMP',
copy_indexes => dbms_redefinition.cons_orig_params,
copy_triggers => true,
copy_constraints => true,
copy_privileges => true,
ignore_errors => false,
num_errors => num_errors,
copy_statistics => true);
END;
/

6、假如在线重定义要很久，这期间应用往源表插入数据，中间表并不会有这条数据，使用sync_interim_table包同步在线重定义期间源表所有的DML的数据：
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'OWNER',
orig_table => 'ORIGTABLENAME',
int_table => 'TABLENAME_TMP');
END;
/

7、完成在线重定义：
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'OWNER',
orig_table => 'ORIGTABLENAME',
int_table => 'TABLENAME_TMP');
END;
/

9、中间重定义报错时需要执行以下命令终止重定义：
*BEGIN
*DBMS_REDEFINITION.ABORT_REDEF_TABLE(
*uname => 'OWNER',
*orig_table => 'ORIGTABLENAME',
*int_table => 'TABLENAME_TMP');
*END;
*/

10、确定数据索引等同步成功后，删除中间表：
drop table OWNER.TABLENAME_TMP;

原文转载：http://www.shaoqun.com/a/484481.html

二类电商：https://www.ikjzd.com/w/1457

转口贸易：https://www.ikjzd.com/w/1427

败欧洲网站：https://www.ikjzd.com/w/1555


一、什么是高水线(HighWaterMark)?Oracle数据库在创建一张表时，会为这张表分配一个段空间（segment）,为了方便理解，把段空间容纳数据的上限，称之为高水位线（HIGHWATERMARK）HWM，HWM是一个标记，用来说明表示有多少未使用的块分配给这个段。两个结论：1.水位线以上表示已经分配但还未使用块（block),水位先以下爱表示已经分配且已经使用过的块（包含了正在使用的块
旺店通：https://www.ikjzd.com/w/2390
csa认证：https://www.ikjzd.com/w/904
现在亚马逊无货源店群怎么这么火？怎么操作？有哪些优势！：https://www.ikjzd.com/home/95834
广州番禺有什么好玩的地方？：http://tour.shaoqun.com/a/3847.html
深圳有哪些景点，对于十一期间生日是免门票的？：http://tour.shaoqun.com/a/40132.html
发帖者 时间：

没有评论:

发表评论

订阅： 博文评论 (Atom)