oracle 批量重建索引 回收表空间

oracle 重建索引 回收表空间

《PS：留下小赞赞，比心比心》

	BEGIN
	
	/*##################
	
	收缩表的空余存储空间
	
	把查询出的sql按顺序执行即可
	
	####################*/
	
	FOR i IN (select 'alter table '||table_name||' enable row movement' tsql
	
	from user_tables where initial_extent is not null AND segment_created='YES' AND table_name NOT IN ('T_DEMAND_GATHER')
	
	and temporary = 'N' order by table_name) LOOP
	
	--dbms_output.put_line(i.tsql);
	
	EXECUTE IMMEDIATE i.tsql; END LOOP;
	
	END;
	/
	BEGIN
	
	FOR i IN (select 'alter table '||table_name||' shrink space cascade' tsql,table_name
	
	from user_tables where initial_extent is not NULL AND segment_created='YES' AND table_name NOT IN ('PLAN_TABLE','T_DEMAND_GATHER')
	
	and temporary = 'N' order by table_name) LOOP
	
	--dbms_output.put_line(i.TSQL); 
	EXECUTE IMMEDIATE i.tsql;
	
	END LOOP;
	
	END;
	/
	
	BEGIN
	
	/*#################
	
	更新表的存储空间初始大小###################*/ 
	FOR i IN (
	
	select 'alter table '||table_name||' move storage(initial 64K)' tsql
	
	from user_tables where initial_extent is not NULL AND segment_created='YES' AND table_name NOT IN ('PLAN_TABLE','T_DEMAND_GATHER')
	
	and temporary = 'N' order by table_name) LOOP
	
	EXECUTE IMMEDIATE i.tsql;
	
	END LOOP; END;
	/
	
	BEGIN
	
	/*#################
	
	索引重建，重置存储空间初始化大小
	
	###################*/
	
	FOR i IN (
	
	select 'alter index "'||index_name||'" rebuild storage(initial 64k)' tsql from user_indexes
	
	where table_owner='SCOTT' and temporary='N' and index_type<>'LOB') LOOP
	
	--dbms_output.put_line(i.TSQL);
	
	EXECUTE IMMEDIATE i.tsql; END LOOP;
	
	END;
	/
	
	BEGIN
	FOR i IN (select 'alter table '||table_name||' disable row movement' tsql from user_tables where initial_extent is not null AND segment_created='YES' and temporary = 'N'
	order by table_name) LOOP
	EXECUTE IMMEDIATE i.tsql; 
	END LOOP;
	END;