通过物化视图重建表实例分享

有一张表大概（暂且命名为TEST表）需要清除1亿调数据，目前表为46G，使用物化视图方式重建该表，预计重建后的大小在19G。

变更一:

变更原因:

(1) 完成TEST清理工作，共清除9800万记录,目前表大46G,重建TEST,预计表重建后的大小在19G，表上一共16个索引，空间也会释放。

变更步骤:

(1) 对要进行物化视图操作前，创建和TEST一样的表结构（只包含主键）MV_TEST。

(2) 对TEST创建MATERIALIZED VIEW LOG。

(3) 创建物化视图MV_TEST(ON PREBUILT TABLE方式)。

(4) 对MATERIALIZED VIEW进行一次全量刷新。

(5) 手工增量刷新一次。然后，创建增量刷新物化视图存储过程，放到数据库JOB中定时5分钟刷新一次。

(6) 对MV_TEST表，创建索引(IND_前缀改成IDX_前缀)。

变更风险:

(1) 这个变更主要对表MV_TEST进行操作，不会对原来的业务表有影响。

(2) 变更步骤(4)中进行全量刷新，可能会影响逻辑备库，需要评估。

(3) 表空间问题，表刷新后大概有20G的空间，但是TEST表上有153G的索引，其中最大的索引有16G，表空间问题。

先进行表TEST上索引rebuild来释放空间,需要评估？

########################################################################################################

-- 变更脚本如下：

-- 1、建表

-- Create table

create table MV_TEST

(

...

)

tablespace TBS_TEST

;

alter table MV_TEST

add constraint PK_TEST primary key (ID)

using index 

tablespace TBS_INDEX

;

-- 2、创建view log

CREATE MATERIALIZED VIEW LOG ON TEST;

-- 3、创建物化视图

CREATE MATERIALIZED VIEW MV_TEST

ON PREBUILT TABLE

REFRESH FAST ON DEMAND

AS

SELECT * FROM TEST;

-- 4、全量刷新物化视图：EXEC DBMS_MVIEW.REFRESH('MV_TEST', 'C')，大概需要20G的空间。检查TBS_TEST剩余空间。

-- 4.1、创建脚本/home/oracle/worksh/tmp_test1.sh

-- 脚本内容如下：

#!/bin/ksh

###########################################################################################

export ORACLE_BASE=''

export ORACLE_HOME=''

export ORACLE_SID=''

export ORACLE_TERM=xterm

export NLS_LANG=''

export ORA_NLS10=$ORACLE_HOME/nls/data

date

$ORACLE_HOME/bin/sqlplus -s xxx/xxx<

set serveroutput on

EXEC DBMS_MVIEW.REFRESH('MV_TEST', 'C');

exit;

EOF

date

-- 4.2、后台执行脚本

chmod 744 /home/oracle/tmp_test1.sh

nohup /home/oracle/worksh/tmp_test1.sh > /tmp/tmp_test1.log 2>&1 &

-- 4.3、全量刷新的脚本执行完成时间大概1个小时。完成后检查日志：

cat /tmp/tmp_test1.log

-- 确认OK，才进行后续的操作。

-- 5、创建增量刷新存储过程

CREATE OR REPLACE PROCEDURE SP_REFRESH_MV AS

BEGIN

 DBMS_MVIEW.REFRESH('MV_TEST');

END;

/

-- 6、手工跑一下增量刷新存储过程

EXEC SP_REFRESH_MV;

-- 7、创建JOB，用于定时增量刷新

variable jobno number;

variable instno number;

begin

  select instance_number into :instno from v$instance;

  dbms_job.submit(:jobno, 'SP_REFRESH_MV;', sysdate, 'sysdate+5/1440', TRUE, :instno);

  commit;

end;

/

-- 8、创建MV_TEST索引(IND_前缀改成IDX_前缀)，需要事先检查TBS_IND表空间大小。

-- Create/Recreate indexes 

...

create index IDX_TEST1 on MV_TEST (column 1) tablespace TBS_IND parallel 6 online compute statistics;

alter index IDX_TEST1 noparallel; 

...

########################################################################################################

变更二:

变更原因:

(1) 前面变更一已经完成表的物化视图全量刷新，和定时的增量刷新。接下来需要进行最后的rename操作。

变更步骤:

(1) 备份表TEST的全部统计信息，用于MV_TEST表rename后导入统计信息。

(2) 暂停增量刷新JOB。进行多次手动增量刷新。

(3) 删除物化视图。

(4) rename表和rename索引。

(5) 导入统计信息到rename后的TEST

(6) 给需要的用户授权。

(7) 清除job和增量刷新存储过程。

(8) 观察运行情况。

变更风险:

(1) rename过程，会有短暂时间应用访问会有问题，需要放在业务低峰期做。

(2) 变更完成后，出现SQL走错执行计划，修改统计信息？

########################################################################################################

-- 9、备份TEST表的统计信息，用于MV_TEST表rename后导入统计信息。

-- 创建临时统计信息表

exec dbms_stats.create_stat_table(ownname => user,stattab => 'tmp_stats_table');

-- 导出表的统计信息(默认包含列和索引的统计信息),statid=>'tmp_201202';

--This procedure retrieves statistics for a particular table and stores them in the user stat table. Cascade results in all index and column stats associated with the specified table being exported as well.

exec dbms_stats.export_table_stats(ownname => user, stattab => 'tmp_stats_table', tabname => 'TEST',cascade => true,statid=>'tmp_201202');

-- 10、查看job号，停job,进行手工刷新

--根据上面的job_no，用于暂停job

exec dbms_job.broken(XX,true);

commit;

-- 11、手动增量刷物化视图，多刷几次，直到跑增量刷新时候很快。

exec SP_REFRESH_MV;

-- 12、删除物化视图,和对应原表的物化视图日志

exec SP_REFRESH_MV;

DROP MATERIALIZED VIEW MV_TEST;

-- 检查MV_TEST表是否存在

desc MV_TEST

--删除对应原表的物化视图日志

drop MATERIALIZED VIEW LOG on TEST;

-- 13、rename操作

-- rename table

alter table TEST rename to TEST3;

alter table MV_TEST rename to TEST;

-- rename index

-- rename TEST3（原TEST表）的索引

...

-- rename 新TEST（原MV_TEST表）的索引，有些SQL有使用到HINT，所以索引名和原来一样

...

-- 14、导入统计信息(不包含索引的统计信息)到rename后的TEST,默认cascade => true

--This procedure retrieves statistics for a particular table from the user stat table identified by stattab and stores them in the dictionary. Cascade results in all index and column stats associated with the specified table being imported as well.

exec dbms_stats.import_table_stats(ownname => user, stattab => 'tmp_stats_table', tabname => 'TEST',cascade => false,statid=>'tmp_201202');

-- 15、grant授权

-- 15.1、检查这个表TEST是否给其他用户授权过？

select 'grant ' || tt.privilege || ' on ' || owner || '.' || table_name ||

' to ' || tt.grantee || ';'

from dba_tab_privs tt

where grantor = 'user_name'

and table_name = 'TEST';

-- 15.2、没有的话授权

grant SELECT on test.test to another_username;

-- 16、清除job和增量刷新存储过程

-- 根据上面的job_no，用于暂停job

exec dbms_job.remove(xx);

-- 删除增量刷新存储过程

drop PROCEDURE SP_REFRESH_MV;

-- 17、观察运行情况。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22503721/viewspace-734875/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22503721/viewspace-734875/