ORACLE释放表空间中的空闲数据文件

匿名过程

​
-------------------only can move table，table partition  and index，indexpartition
set serveroutput on
declare
--param def
v_tbs_rel varchar2(100);
v_tbs_tmp varchar2(100);
v_file_no number(10);

--execstr def
x_move_tp_str varchar2(1000);
x_move_ip_str varchar2(1000);
x_move_ct_str varchar2(1000);
x_move_ci_str varchar2(1000);
x_back_tp_str varchar2(1000);
x_back_ip_str varchar2(1000);
x_back_ct_str varchar2(1000);
x_back_ci_str varchar2(1000);
x_drop_str varchar2(1000);


--cursor def
cursor x_move_tp_cur is 
select distinct 'alter table '||owner||'.'||segment_name||' move partition '||partition_name||' tablespace '||v_tbs_tmp
from dba_extents
where tablespace_name=v_tbs_rel 
and segment_type ='TABLE PARTITION'
and file_id=v_file_no ;


cursor x_back_tp_cur is 
select distinct 'alter table '||owner||'.'||segment_name||' move partition '||partition_name||' tablespace '||v_tbs_rel
from dba_extents
where tablespace_name=v_tbs_rel 
and segment_type ='TABLE PARTITION'
and file_id=v_file_no ;


cursor x_move_ip_cur is 
select distinct 'alter index '||owner||'.'||segment_name||' rebuild partition '||partition_name||' online tablespace '||v_tbs_tmp
from dba_extents
where tablespace_name=v_tbs_rel 
and segment_type ='INDEX PARTITION'
and file_id=v_file_no ;


cur