学步园

最近为了清理索引表空间，需要删除一类表的索引和约束，保留这类表的数据：

       相关脚本操作如下:

先将要的表名放到临时表hch_temp_table;

create table HCH_TEMP_TABLE
(
  TABLE_NAME VARCHAR2(200)
);

declare   
   lv_sql varchar2(200);  
   lv_sql1 varchar2(200);  
   lv_sql2 varchar2(200);  
   begin  
       delete from HCH_TEMP_TABLE;
       commit;
    for j in 1..82    
      loop  
      lv_sql1  := '%OP_MAIL_DETAIL_'||to_char(j,'FM09')||'_12%';  
          lv_sql:='  INSERT INTO HCH_TEMP_TABLE   
         select table_name from  user_tables where table_name like :1';  
         execute immediate lv_sql using lv_sql1;  
           
      end loop;     
    commit;  
 end;

删除该表对应的约束和索引:
先删除主键和唯一索引的约束，然后删除索引: 这里注意不要把顺序搞反了。

declare 
 begin
    for x in (select * from hch_temp_table)
      loop
         for y in (select Constraint_name,table_name from   User_Constraints   where table_name = x.Table_Name
            and constraint_type in ('P','U'))
           loop
                execute immediate 'alter table '||y.Table_Name||' drop constraint '||y.Constraint_Name;
           end loop;
         for y in (select * from user_indexes where table_name = x.Table_Name)
           loop 
                execute immediate 'drop index '|| y.Index_Name;
           end loop;
           delete from HCH_TEMP_TABLE where table_name = x.Table_Name;
           commit;
     end loop;
  end;