`

Oracle Reorg 的方式与相关的script ---- 2016-02-26

阅读更多

1. 在Reorg之前收集table,index,lod的空间占用信息

 

 在做Reorg之前,为了分析方便一般要先拿到相关tables,indexes,clobs所占用的空间大小数据。在这之前需要先分析一下表,因为DB中表的相关信息未必是最新的。

 

(1)分析表,以更新DB相关视图,保证其中的信息是最新的,例如:DBA_indexes,Dba_tables,中的信息

set serveroutput on

set time on timing on
spool analyze_tables.log
alter session set nls_date_format='ddmmyyyy hh24:mi:ss';

Declare
V_sql varchar2(2000);
JOB_NAME varchar2(24);
v_tableName varchar2(200);
BEGIN
JOB_NAME:= 'JOB1';
Dbms_output.put_line('gather table infor for ' || JOB_NAME || ' start at: '||sysdate);
For v in 
(SELECT TABLE_NAME FROM REORG_TABLES WHERE JOBNAME=JOB_NAME)
LOOP
v_tableName:=v.TABLE_NAME;
-- 1. gather table info
Dbms_output.put_line('analyze table:'||v_tableName || ' start at:'||sysdate);
dbms_stats.gather_table_stats(ownname => 'SHAPE',tabname => v_tableName,estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,granularity=>'ALL');
Dbms_output.put_line('analyze table:'||v_tableName || ' complete at:'||sysdate);

END LOOP;
Dbms_output.put_line('gather info for ' || JOB_NAME || ' complete at:'||sysdate);
EXCEPTION
WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLCODE||SQLERRM);
END;
/
spool off

 

(2) 获得相关表的Block used status

 

SELECT segment_name, segment_type, owner, blocks, bytes/1024/1024 as Mb
FROM dba_segments
WHERE segment_name in (SELECT TABLE_NAME FROM REORG_TABLES WHERE JOBNAME='JOB1')
and owner='SHAPE'
ORDER BY segment_name;

 

 

(3)获得相关表的实际占用空间大小

SELECT table_name,num_rows,blocks,empty_blocks, (avg_row_len+3)*num_rows/1024/1024 as Mb
FROM all_tables
WHERE table_name in (SELECT TABLE_NAME FROM REORG_TABLES WHERE JOBNAME='JOB1');

 

(4)获得表上Index的空间占用信息

 

SELECT t2.table_name, t1.segment_name, t1.segment_type, t1.blocks, t1.bytes/1024/1024 as Mb
FROM dba_segments t1,
(
SELECT INDEX_NAME, TABLE_NAME FROM dba_indexes where table_name in (SELECT TABLE_NAME FROM REORG_TABLES WHERE JOBNAME='JOB1')
) t2
WHERE t1.segment_name = t2.INDEX_NAME
AND t1.owner='SHAPE'
ORDER BY t2.table_name, t1.segment_name;

 

 

(5)获得表中的相关clob数据的空间占用信息

 

set time on timing on
spool shape_info_1.log

select ul.table_name, ul.column_name, ul.segment_name, se.segment_type, 
se.owner,se.blocks,se.bytes/1024/1024 as Mb, ul.chunk from dba_lobs ul ,dba_segments se
where ul.segment_name=se.segment_name
and ul.table_name in('CONFIRMATION_ACKS', 'PDF_STORE_IN', 'SHARK_ACTIVITY_LOG', 'MDIS_BATCH_LOG')
and se.OWNER='SHAPE'
order by ul.table_name;

spool off

 

 

2. 完成Reorg Job

 

Declare
V_sql varchar2(2000);
JOB_NAME varchar2(24);
v_tableName varchar2(200);
BEGIN
JOB_NAME:= 'JOB1';
Dbms_output.put_line('reorg for ' || JOB_NAME || ' start at: '||sysdate);
For v in 
(SELECT TABLE_NAME FROM REORG_TABLES WHERE JOBNAME=JOB_NAME)
LOOP
v_tableName:=v.TABLE_NAME;
-- 1. reorg table
Dbms_output.put_line('reorg table:'||v_tableName || ' start at:'||sysdate);
V_sql:='Alter table ' || v_tableName || ' move parallel 4';
Execute immediate v_sql;
Dbms_output.put_line('reorg table:'||v_tableName || ' complete at:'||sysdate);
-- 2. reorg clob
Dbms_output.put_line('reorg clobs start at:'||sysdate);
For c in
(SELECT COLUMN_NAME FROM DBA_TAB_COLS WHERE TABLE_NAME=v_tableName AND DATA_TYPE='CLOB' AND OWNER='SHAPE')
LOOP
Dbms_output.put_line('reorg clob:'||c.COLUMN_NAME || ' start at:'||sysdate);
V_sql:='Alter table ' || v_tableName || ' move lob (' || c.COLUMN_NAME || ') store as (tablespace SHAPE_DATA) parallel 4';
Execute immediate v_sql;
Dbms_output.put_line('reorg clob:'||c.COLUMN_NAME || ' complete at:'||sysdate);
END LOOP;
Dbms_output.put_line('reorg clobs complete at:'||sysdate);

END LOOP;
Dbms_output.put_line('reorg for ' || JOB_NAME || ' complete at:'||sysdate);
EXCEPTION
WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLCODE||SQLERRM);
END;

 

3. rebuld index。 

由于在实际的测试中,当把Rebuild index和reorg放在一起时,rebuild index常常会失败,所以写了独立的script用来rebuild index。

 

set time on timing on
spool shape_rebuild_indexes.log
alter session set nls_date_format='ddmmyyyy hh24:mi:ss';

Declare
V_sql varchar2(2000);
JOB_NAME varchar2(24);
v_tableName varchar2(200);
BEGIN
JOB_NAME:='JOB1';
Dbms_output.put_line('REBUILD_INDEX_JOB for ' || JOB_NAME || ' start at: '||sysdate);
For v in 
(SELECT TABLE_NAME FROM REORG_TABLES WHERE JOBNAME=JOB_NAME)
LOOP
v_tableName:=v.TABLE_NAME;
-- 1. rebuild indexes
Dbms_output.put_line('rebuild indexes start at:'||sysdate);
For x in 
(SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME=v_tableName AND INDEX_TYPE='NORMAL' AND OWNER='SHAPE')
LOOP
Dbms_output.put_line('rebuild index:'||x.index_name || ' start at:'||sysdate);
V_sql:='ALTER INDEX '||x.index_name||' nologging';
Execute immediate v_sql;
V_sql:='ALTER INDEX '||x.index_name||' parallel 4';
Execute immediate v_sql;
V_sql:='ALTER INDEX '||x.index_name||' rebuild online';
Execute immediate v_sql;
V_sql:='ALTER INDEX '||x.index_name||' logging';
Execute immediate v_sql;
V_sql:='ALTER INDEX '||x.index_name||' noparallel';
Execute immediate v_sql;
Dbms_output.put_line('rebuild index:'||x.index_name || ' complete at:'||sysdate);
End LOOP;
Dbms_output.put_line('rebuild indexes complete at:'||sysdate);
-- 2. analyze table
Dbms_output.put_line('analyze table:'||v_tableName || ' start at:'||sysdate);
dbms_stats.gather_table_stats(ownname => 'SHAPE',tabname => v_tableName,estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,granularity=>'ALL');
Dbms_output.put_line('analyze table:'||v_tableName || ' complete at:'||sysdate);
END LOOP;
Dbms_output.put_line('REBUILD_INDEX_JOB for ' || JOB_NAME || ' complete at:'||sysdate);
EXCEPTION
WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002,SQLCODE||SQLERRM);
END;
/
spool off

 

4. 按照1中的步骤,重新分析表并拿到相关空间占用信息。

 

 

注意:无论Reorg成功或者失败,都要检查一下是不是所有的Index都VALID的。

 

 

select * from user_indexes where status != 'VALID';

分享到:
评论

相关推荐

    DB2与ORACLE常用语句对照

    "DB2与ORACLE常用语句对照"的主题旨在帮助用户理解和转换这两种数据库系统中的SQL命令。下面将对一些常见的SQL语句和概念进行详细解释,以便于在两个系统间进行迁移或比较。 1. 数据定义语言(DDL): - 在Oracle中...

    完整的REORG表的过程

    db2 中 REORG 表的完整过程及应用场景 REORG(重组)是 db2 中一种重要的表维护操作,它可以帮助解决死锁或锁超时的问题,但是在进行 REORG 操作之前,需要了解完整的 REORG 表过程。下面我们将详细介绍 REORG 表的...

    DB2pureScale_Practical_Design_Guide_2016-Feb-5.pptx

    3-7 Large buffer pool may slow down LOAD and REORG 3-8 Consider multiple table space containers 4. Tables and Index Design 4-1 Considerations for SEQUENCE and IDENTITY columns 4-2 First column ...

    pg_reorg:PostgreSQL在线表聚类工具-开源

    pg_reorg可以在不保留任何锁的情况下重新组织PostgreSQL数据库上的表,以便您可以检索或更新正在重新组织的表中的行。 开发此模块是为了更好地替代CLUSTER和VACUUM FULL。 ------- pg_reorg位于pgfoundry,但是已经...

    restfultiger-reorg:重组restfultiger的文件

    标题中的"restfultiger-reorg"可能是一个项目或库的名字,它与RESTful API设计有关,而"reorg"通常意味着对原有的结构或代码进行了重新组织。描述中提到的"悠闲的老虎"可能是一个隐喻,暗示这个项目在经过重组后变得...

    DB2数据库管理最佳实践笔记-10日常运维.docx

    #### 三、Reorg与Reorgchk:数据重组 **1. Reorg的作用** - **减少碎片**:通过重新组织表和索引中的数据,减少物理存储上的碎片。 - **提高性能**:优化数据存储结构,加快数据检索速度。 **2. Reorgchk的作用** -...

    Itrenzheng IBM DB2 000-701认证题库

    ### Itrenzheng IBM DB2 000-701 认证题库知识点解析 #### 一、概述 本篇文章将针对...它会显示有关表的物理存储结构的状态信息,如碎片率、空间利用率等,以便管理员决定是否需要执行重组织操作来优化性能。

    Oracle_Database_10g_SQL_Tuning

    根据提供的文件信息,我们可以深入探讨Oracle Database 10g中的SQL调优技术及相关知识点。这份文档作为一本关于Oracle管理的参考书,对于那些希望深入了解如何优化数据库性能的专业人士来说非常有价值。 ### Oracle...

    DB2巡检报告相关内容以及图表

    - `db2listhistorybackup(reorg)since日期 forkdbs(库)`: 查看最近一次数据库全备份或数据重组的时间 - **查询统计信息收集时间:** `db2 'select stats_time, tabname from syscat.tables where stats_time is not...

    db2 000-731试题

    **解析:** 在DB2中,SCHEMA 是一种组织数据库对象的方式,它允许将相关的表、视图等分组在一起。选项 C 描述的是一个真实的场景:当创建新数据库时,所有具有适当权限的用户都能够创建新的 SCHEMA,用于组织他们自己的...

    suselinuxDB2常用管理命令

    | `sysplex` | 返回和所有数据库或者某个数据库的别名相关信息 | 实例 | | `tablespace` | 返回表空间的信息 | 数据库 | | `tcbstats` | 返回表和索引的信息 | 数据库 | | `transactions` | 返回活动事务的信息 | ...

    DB2数据库管理最佳实践笔记-10日常运维.pdf

    数据分布有频率采样(Frequency)和百分比采样(Quantile)两种方式,它们用于估算查询条件下的数据量。 - **频率采样**:关注具有相同值的行的分布,例如,如果大部分行的某个字段值相同,频率采样会记录这一情况...

    sap标准后台作业

    - **更新内容**:本文档介绍了在SAP R/3系统中定期运行的标准作业和重组作业的相关信息,并提供了这些作业的推荐配置方法。 2. **作业调度** - **调度方式**:自4.6C版本起,用户可以通过事务代码`SM36`选择...

    reorg-tester:在私有的geth网络中测试以太坊重组

    有关示例用法,请参见index.spec.ts 。用法等待将其设置为实际可用的包!如何在您的机器上运行此仓库克隆仓库并npm install 使用npm start启动两个geth节点使用npm test运行测试,这将在node-2中触发重组! # ...

    DB2数据库用户手册

    - **其他系统与DB2的交互方式**:包括使用ODBC驱动程序、JDBC等技术实现与其他系统的数据交换。 #### 六、DB2表及SP管理 - **权限管理**: - **数据库权限控制**:控制用户对整个数据库的访问。 - **SCHEMA权限...

    DB2性能安全

    #### 一、表压缩与REORG命令 - **压缩属性的重要性**:在DB2中,表的`COMPRESSYES`属性对于数据压缩至关重要。如果表没有设置此属性,即使执行了`REORG`命令,也不会有任何实际的压缩效果发生。 - **设置压缩属性的...

    DB2数据库优化.doc

    DB2 数据库优化是提升数据库性能的关键步骤,主要包括运行统计(Runstats)和重组(Reorg)两大方面。本文将详细介绍这两个概念以及何时和如何执行它们。 **一、Runstats** 1. **Runstats的作用**:Runstats命令...

    DB2 基础---DB2进程

    进程与内存管理密切相关。DB2使用私有地址空间(PAS)和共享地址空间(SAS)来组织内存。PAS包含每个SQL进程的私有内存,用于存储执行计划、数据缓冲等。SAS是所有进程共享的,用于存储全局信息,如系统目录、锁信息...

Global site tag (gtag.js) - Google Analytics