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';