移动指定datafile数据到新表空间 释放磁盘空间

--生成环境，数据文件时常存在高水位，导致不能直接收缩数据文件的大小，释放磁盘空间

create or replace package PKG_TOOL is

-- Author : LILEI
-- Created : 2019/5/17 8:57:03
-- Purpose : SOME TOOLS

--移动指定datafile数据到新表空间
procedure move_ddl(v_fileid in number,
v_newtbs in varchar2,
v_parallel in pls_integer default 1);

--执行move ddl语句
procedure exec_ddl;

--输出sql的信息（执行计划，表信息，索引信息）
procedure test_plan (txt in varchar2);

 

 

end PKG_TOOL;
/
create or replace package body PKG_TOOL is

/*
------初始化
grant select on dba_extents to system;
grant select on dba_lobs to system;
grant select on dba_lob_partitions to system;
grant select on dba_part_indexes to system;
grant select on dba_indexes to system;
grant select on dba_ind_columns to system;
Grant ALTER ANY INDEX to system;
grant alter any table to system;
grant create any index to system;
grant drop any index to system;

create table tools_ddl
(
owner varchar2(50),
segment_name varchar2(50),
segment_type varchar2(50),
operation_type varchar2(20),
sql_text varchar2(200),
is_over number(1),
execute_result varchar2(200)
)tablespace users;

grant select on dba_part_tables to system;
grant select on dba_tab_partitions to system;
grant select on dba_ind_partitions to system;
grant select on dba_tab_partitions to system;
grant select on dba_segments to system;
grant select on dba_tables to system;
grant select on dba_tab_cols to system;
grant select on dba_part_tables to system;
grant select on dba_part_key_columns to system;
grant select on dba_tab_partitions to system;
grant select on dba_ind_partitions to system;
*/

procedure move_ddl(v_fileid in number,
v_newtbs in varchar2,
v_parallel in pls_integer default 1) is
v_sql varchar2(200);
v_column_name varchar2(50);
v_table_name varchar2(50);
v_partition_name varchar2(50);
v_locality varchar2(50);
v_UNIQUENESS varchar2(20);
v_operation_type varchar2(20);
v_count pls_integer;
v_index_type varchar2(50);
begin
for cur in (select owner,segment_name,partition_name,segment_type from dba_extents where file_id=v_fileid
group by owner,segment_name,partition_name,segment_type

/*也可以直接换视图查询，从一个表空间移动到另外一个表空间,查询方式换一下就行了

select owner,segment_name,partition_name,segment_type from dba_segments where tablespace_name=v_fileid
group by owner,segment_name,partition_name,segment_type

*/

)loop

v_sql:='';
v_operation_type:='ALTER';
if cur.segment_type='TABLE' then
v_sql:= 'alter table '||cur.owner||'.'||cur.segment_name ||
' move tablespace '||v_newtbs||' parallel '||v_parallel;
elsif cur.segment_type='TABLE PARTITION' then
v_sql:= 'alter table '||cur.owner||'.'||cur.segment_name ||' move partition '||
cur.partition_name||' tablespace '||v_newtbs||' parallel '||v_parallel;
elsif cur.segment_type='TABLE SUBPARTITION' then
v_sql:='alter table '||cur.owner||'.'||cur.segment_name ||' move subpartition '||
cur.partition_name ||' tablespace '||v_newtbs||' parallel '||v_parallel;
elsif cur.segment_type='LOBSEGMENT' then
select column_name,table_name into v_column_name,v_table_name from dba_lobs where segment_name=cur.segment_name;
v_sql:='alter table '||cur.owner||'.'||v_table_name ||' move lob ( '|| v_column_name ||
')store as(tablespace '||v_newtbs||') parallel '||v_parallel;
elsif cur.segment_type='LOB PARTITION' then
select column_name,table_name,partition_name into v_column_name,v_table_name,v_partition_name
from dba_lob_partitions where lob_name=cur.segment_name and lob_partition_name=cur.partition_name;
v_sql:= 'alter table '||cur.owner||'.'||v_table_name||' move partition '||v_partition_name||
' lob( '|| v_column_name ||')store as(tablespace '||v_newtbs||') parallel '||v_parallel;
elsif cur.segment_type='INDEX' then
v_sql:='alter index '||cur.owner||'.'||cur.segment_name ||' rebuild tablespace '||v_newtbs
||' parallel '||v_parallel;
elsif cur.segment_type='INDEX PARTITION' then
select index_type into v_index_type from dba_indexes where index_name=cur.segment_name and OWNER=cur.owner;
if v_index_type='LOB' then
continue;
end if;
select locality into v_locality from dba_part_indexes where index_name =cur.segment_name;
if v_locality='GLOBAL' then
select count(*) into v_count from tools_ddl where owner=cur.owner and segment_name=cur.segment_name;
if v_count>1 then
continue;
end if;
select b.column_name,a.UNIQUENESS,a.TABLE_name into v_column_name,v_UNIQUENESS,v_table_name from dba_indexes a,dba_ind_columns b
where a.INDEX_NAME=b.INDEX_NAME and a.OWNER=b.INDEX_OWNER and a.INDEX_NAME=cur.segment_name;

if v_UNIQUENESS='UNIQUE' then
v_sql:= 'create UNIQUE index '||cur.owner||'.'||cur.segment_name ||' on '||cur.owner||'.'||v_table_name||
'('||v_column_name||')'||'GLOBAL PARTITION BY HASH ('||v_column_name||') PARTITIONS 128 TABLESPACE '
||v_newtbs||' parallel '||v_parallel;
else
v_sql:= 'create index '||cur.owner||'.'||cur.segment_name ||' on '||cur.owner||'.'||v_table_name||
'('||v_column_name||')'||'GLOBAL PARTITION BY HASH ('||v_column_name||') PARTITIONS 128 TABLESPACE '
||v_newtbs||' parallel '||v_parallel;
end if;
v_operation_type:='CREATE';
insert into tools_ddl(owner,segment_name,segment_type,operation_type,sql_text,is_over,execute_result)values
(cur.owner,cur.segment_name,cur.segment_type,v_operation_type,v_sql,0,null);
v_operation_type:='DROP';
v_sql:='drop index '||cur.owner||'.'||cur.segment_name;
else
v_sql:='alter index '||cur.owner||'.'||cur.segment_name ||' rebuild PARTITION '
||cur.partition_name ||' tablespace '||v_newtbs||' parallel '||v_parallel;
end if;
elsif cur.segment_type='INDEX SUBPARTITION' then
v_sql:='alter index '||cur.owner||'.'||cur.segment_name ||' rebuild SUBPARTITION '||
cur.partition_name ||' tablespace '||v_newtbs||' parallel '||v_parallel;
end if;
if v_sql is not null then
insert into tools_ddl(owner,segment_name,segment_type,operation_type,sql_text,is_over,execute_result)values
(cur.owner,cur.segment_name,cur.segment_type,v_operation_type,v_sql,0,null);
end if;
end loop;
commit;
end move_ddl;

 

procedure exec_ddl is
v_error varchar2(500);
begin
for cur in(select sql_text,rowid from tools_ddl where is_over=0
order by segment_type desc,operation_type desc) loop
begin
execute immediate cur.sql_text;
exception
when others then
v_error:=substr(sqlerrm,1,400);
update tools_ddl set is_over=2,execute_result=v_error
where rowid=cur.rowid;
continue;
end;
update tools_ddl set is_over=1 where rowid=cur.rowid;
end loop;
commit;
end exec_ddl;

 

end PKG_TOOL;
/

 

--别忘记重建失效的索引