Oracle批量修改用户表table的表空间

来源：互联网发布：玫瑰花数 c语言编辑：程序博客网时间：2024/06/05 16:33

一、修改用户表table的表空间

1、修改用户表table的表空间：alter table 表名 move tablespace 新表空间名;

2、查询所有用户表：select * from user_tables;

【脚本】

查询当前用户的所有的数据表，并把表空间为“EAS_D_HFY_STANDARD”的用户表，修改为：EAS_D_HFY120929_STANDARD。

/* Formatted on 2012-10-11 14:17:04 (QP5 v5.115.810.9015) */DECLARE   i_count   INT := 0;   execsql   VARCHAR2 (1000);   CURSOR c_mysql   IS      SELECT      'alter table  '               || table_name               || '  move tablespace EAS_D_HFY120929_STANDARD'                  mysql        FROM   user_all_tables       WHERE   tablespace_name = 'EAS_D_HFY_STANDARD';BEGIN   FOR r_mysql IN c_mysql   LOOP      DBMS_OUTPUT.put_line (r_mysql.mysql);      EXECUTE IMMEDIATE r_mysql.mysql;      i_count := i_count + 1;   END LOOP;   DBMS_OUTPUT.put_line ('i_count: ' || i_count);EXCEPTION   WHEN OTHERS   THEN      DBMS_OUTPUT.put_line (         '异常:' || 'sqlcode:' || SQLCODE || ' sqlerrm : ' || SQLERRM      );END;

二、修改索引index的表空间

【问题】

修改表table的表空间后，在操作相关表时，遇到ORA-01502问题：

ORA-01502: index 'HFY120401.PK_MULTIAPPROVE' or partition of such index is in unusable state

【分析】

1、经过系统分析，查询索引状态，可发现索引“PK_MULTIAPPROVE”的状态是“UNUSABLE”；

2、进一步分析，发现系统中的索引index_type有3类：NORMAL、FUNCTION-BASED NORMAL、LOB，除index_type为LOB类型的索引状态为VALID外，NORMAL、FUNCTION-BASED NORMAL类型的索引状态均被设置为不可用状态了UNUSABLE。

3、分析索引的语句如下：

select index_name,index_type,tablespace_name,table_type,status from user_indexes;

【脚本】

查询当前用户所有非LOB索引，并把表空间为“EAS_D_HFY_STANDARD”的索引，更新为：EAS_D_HFY120929_STANDARD。

/* Formatted on 2012-10-11 14:31:42 (QP5 v5.115.810.9015) */DECLARE   i_count   INT := 0;   CURSOR c_mysql   IS      SELECT      'alter index '               || index_name               || ' rebuild tablespace EAS_D_HFY120929_STANDARD'                  mysql        FROM   user_indexes       WHERE   tablespace_name = 'EAS_D_HFY_STANDARD' and index_type<>'LOB';BEGIN   FOR r_mysql IN c_mysql   LOOP      DBMS_OUTPUT.put_line (r_mysql.mysql);      EXECUTE IMMEDIATE r_mysql.mysql;      i_count := i_count + 1;   END LOOP;   DBMS_OUTPUT.put_line ('i_count: ' || i_count);END;

关于ORA-01502的问题，详见：《ORA-01502错误成因和解决方法》

其实重建普通索引成功后，也试着努力更新LOB索引的，只是在详细阅读《如何重建LOB类型的索引和LOB段》后只好无奈地放弃！