oracle 修改用户信息表,Oracle批量修改用户表table的表空间 | 学步园

一、修改用户表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;

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